Question about including null data

hilltop804

Registered User.
Local time
Today, 13:35
Joined
Dec 5, 2013
Messages
13
Everyone,

I'm trying to make a query to return payroll data, but I'm not sure how to get Access to return 0 where data doesn't exist for a particular month.

For example, imagine this table:
Name Month Hours
John 1 160
John 2 160
Dave 1 160
Dave 2 160
Matt 1 160

How would I get this ouput:
Name Month Hours
John 1 160
John 2 160
Dave 1 160
Dave 2 160
Matt 1 160
Matt 2 0

If anyone has any suggestions, I'd greatly appreciate them.

Thanks!
 
Thanks for the reply.

I'm aware of the nz() function, but I didn't think it would work here. In an instance where there are 12 months, I couldn't specify which month each value needs to be.
 
You can't create records in a query that don't exist in the underlying data. So if whatever data source you have doesn't have any records for Matt in February, its not going to appear in your query.

So, you need a data source in your query that has all the records you want reported. Do you have a table/query with all the month/name permutations you want in your results? If not, can you post a non-generized table? Include table and field names, along with sample data if its not really Month/Name/Hours data.
 
I don't follow. Nz() would specify the value *only* when the field is Null. You're saying the value might not be the same when the field is Null?
 
You can't create records in a query that don't exist in the underlying data. So if whatever data source you have doesn't have any records for Matt in February, its not going to appear in your query.

So, you need a data source in your query that has all the records you want reported. Do you have a table/query with all the month/name permutations you want in your results? If not, can you post a non-generized table? Include table and field names, along with sample data if its not really Month/Name/Hours data.

Thanks,

I didn't think Access would create records that didn't exist. My thought was the easiest way to do this is to have a table with all possible name/months and then join that to the actual data table. The issue is I'm not really sure how to specify column values that must exist for each record.

The data source I'm working with omits any records that are 0 for a given month (so Matt - 2 would not even be in the data set).

The real data I'm working with is much more complex, and I can't post it because of corporate policy/sensitivity. I will do my best to create some fake data that more accurately portrays my actual situation, but I'll need a little time. If you can think of an idea that would work for my sample, though, I'm sure I could scale it up to the actual data.
 
I don't follow. Nz() would specify the value *only* when the field is Null. You're saying the value might not be the same when the field is Null?

The issue is that Matt has no record with month 2. So if I select name and nz(Month), I can't always specify the correct month because it could be any number between 1 and 12, right? Any given employee could potentially be omitted from the data if they have 0 hours for that month, but every employee that has ever existed for any month needs to show up for every month.
 
The issue is that Matt has no record with month 2. So if I select name and nz(Month), I can't always specify the correct month because it could be any number between 1 and 12, right? Any given employee could potentially be omitted from the data if they have 0 hours for that month, but every employee that has ever existed for any month needs to show up for every month.
I was on the wrong track. Plog is on the right one and I'll bow out and let them complete the thread.
 
I was on the wrong track. Plog is on the right one and I'll bow out and let them complete the thread.

I think I may have poorly communicated my issue initially, so sorry for any confusion there. Thanks for responding, though!
 
I'll try and explain the method. I don't like your sample field names and you didn't provide a table name so let's use this data:

Sales
SalesMan, SalesMonth, SalesAmount
Steve, 1, 42
Fred, 1, 38
Steve, 2, 54
Fred, 3, 31
Burt, 3, 19

3 months x 3 names= 9 results. However, there's only 5 records, so here's how we make the other 4 appear:

sub_Months:
Code:
SELECT SalesMonth FROM Sales GROUP BY SalesMonth;

sub_SalesMen:
Code:
SELECT SalesMan FROM Sales GROUP BY SalesMan;

Those 2 queries get all salesmen and months to report on. Now you combine them to get all permutations:

sub_MonthsMen
Code:
SELECT SalesMonth, SalesMan FROM sub_Months, sub_SalesMen;

Run that and you have the 9 records you want to report on. Next, you LEFT JOIN Sales to it to get your final results:

Code:
SELECT sub_MonthsMen.SalesMan, sub_MonthsMen.SalesMonth, NZ(Sales.SalesAmount,0) AS TotalSales
FROM sub_MonthsMen 
LEFT JOIN Sales ON sub_MonthsMen.SalesMonth=Sales.SalesMonth
  AND sub_MonthsMen.SalesMan=Sales.SalesMan;

Rural was right--NZ() is needed, its just that you have to do more work to get there when its an entire record missing and not just a field's value.
 
I'll try and explain the method. I don't like your sample field names and you didn't provide a table name so let's use this data:

Sales
SalesMan, SalesMonth, SalesAmount
Steve, 1, 42
Fred, 1, 38
Steve, 2, 54
Fred, 3, 31
Burt, 3, 19

3 months x 3 names= 9 results. However, there's only 5 records, so here's how we make the other 4 appear:

sub_Months:
Code:
SELECT SalesMonth FROM Sales GROUP BY SalesMonth;

sub_SalesMen:
Code:
SELECT SalesMan FROM Sales GROUP BY SalesMan;

Those 2 queries get all salesmen and months to report on. Now you combine them to get all permutations:

sub_MonthsMen
Code:
SELECT SalesMonth, SalesMan FROM sub_Months, sub_SalesMen;

Run that and you have the 9 records you want to report on. Next, you LEFT JOIN Sales to it to get your final results:

Code:
SELECT sub_MonthsMen.SalesMan, sub_MonthsMen.SalesMonth, NZ(Sales.SalesAmount,0) AS TotalSales
FROM sub_MonthsMen 
LEFT JOIN Sales ON sub_MonthsMen.SalesMonth=Sales.SalesMonth
  AND sub_MonthsMen.SalesMan=Sales.SalesMan;

Rural was right--NZ() is needed, its just that you have to do more work to get there when its an entire record missing and not just a field's value.

THIS! This is EXACTLY what I was looking for. Thank you so much. I had no idea you could do a multi-table FROM clause without a join.

I apologize for the lack of sample data (I was in the process of fudging some for you), but I greatly appreciate you proposing a solution without it.
 

Users who are viewing this thread

Back
Top Bottom