交叉表查询:从Access DB获取空数据以获取丢失的数据 [英] Crosstab query: Getting Null Data for Missing Data from Access DB

问题描述

我在Access数据库中有数据，其中包含多天的数据. 但有时某些日期缺少数据.

I have data in Access Database which contains data for multiple days. But it sometime have missing data for some dates.

例如，我有数据

myDate           Location    Price
11/1/2013        South       10
11/1/2013        West        20
11/1/2013        East        10   
11/2/2013        South       10
11/2/2013        West        20
11/2/2013        East        10   
11/4/2013        South       10   <---- 11/3/2013 Data Missing
11/4/2013        West        30
11/4/2013        East        10   

我试图解决此问题的方法是在Access数据库中查找丢失的日期，并使用日历表将其填充为Null值.

The way I tried to solve it was to find missing date in Access Database, and filled it with Null value using calender table.

myDate           Location    Price
11/1/2013        South       10
11/1/2013        West        20
11/1/2013        East        10   
11/2/2013        South       10
11/2/2013        West        20
11/2/2013        East        10   
11/3/2013                         <---- Null values
11/4/2013        South       10   
11/4/2013        West        30
11/4/2013        East        10  

但是当我使用交叉表查询获取每个位置的价格时，由于没有与之相关的位置，因此跳过了11/3/2013.

But when I use crosstab query to get get price for each location, 11/3/2013 is skipped because it doesn't have any location related to it.

myDate    South   West   East
11/1/2013 10      20     10
11/2/2013 10      20     10    <---- 11/3/2013 Data skipped
11/4/2013 10      30     10

我希望我的解决方案如下:

What I want my solution to be is following:

myDate    South   West   East
11/1/2013 10      20     10
11/2/2013 10      20     10    
11/3/2013                  
11/4/2013 10      30     10

我认为，当我从Access调用此数据时，我将能够使用 NZ 函数，并且每当找到空值时，我都可以在Excel电子表格中添加#N/A错误，因此我将能够捕获丢失的数据并在以后填写.

I thought I would be able to use NZ function when I'm calling this data from Access, and whenever I find null value, I can just put #N/A error in Excel spreadsheet so I would be able to catch what data is missing and fill it in later.

我将如何做到这一点?我是在做正确的方法还是有更好的方法?

How would I accomplish this? Am I doing the right way or is there better way?

任何帮助或提示将不胜感激.

Any help or hint would be greatly appreciated.

推荐答案

这就是我要做的方式.

不要在数据表中包含空日期.返回到以前的方式，该表仅包含有效数据.

Don't include empty dates in the data table. Go back to the way you had it before, where the table only includes valid data.

在表上创建一个交叉表查询.这将不包括没有数据的日期. (根据您的示例:)

Create a crosstab query on the table. This will not include the dates that have no data. (From your example:)

myDate    South   West   East
11/1/2013 10      20     10
11/2/2013 10      20     10    <---- 11/3/2013 Data skipped
11/4/2013 10      30     10

将交叉表查询保存在Access数据库中.我称它为qryXtab.创建一个新查询，将您的日历表连接到qryXtab.

Save the crosstab query in your Access database. I'll call it qryXtab. Create a new query left joining your calendar table to qryXtab.

SELECT tblCalendar.cdate, South, West, East
FROM tblCalendar
LEFT JOIN qryXtab
ON tblCalendar.cdate = qryXtab.myDate
ORDER BY tblCalendar.cdate

使用此新查询来填充您的Excel文件.对于没有数据的日期，它将包括空白行.

Use this new query to populate your Excel file. It will include blank rows for dates that have no data.

这篇关于交叉表查询:从Access DB获取空数据以获取丢失的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！