Someone volunteered me to take over a database on extremely short notice just 2 to 3 weeks ago for our local "Christmas Project" and I've been pulling my hair out trying to learn Access and generate reports under tight deadlines. Having very little experience in Access I felt pretty good being able to generate the following query:
http://i35.photobucket.com/albums/d186/HoodRiverDude/QueryData.jpg

I thought that once I had that I could easily piece together a report having the following layout: http://i35.photobucket.com/albums/d186/HoodRiverDude/ReportData.jpg
Anyway, I'm failing miserably with this. I tried doing each of the 4 sets as separate reports, but the report ends up looking something like this:
http://i35.photobucket.com/albums/d186/HoodRiverDude/BadReportData.jpg

Is there a way to set up a query/report to display rows numbered 1 to max(HouseholdSize) and display the Count(HouseholdSize) that match the row number even if they are zero quantities? (Ref. middle table)

I'd appreciate any help I can get.

Thanks much,

aldo

Hi aldo,

Yes it is. I'm pretty sure you'll find it in the way you have joined the tables in your query. you need outside joins, they will retun a 0

This seemed to work:

SELECT Table0.HouseholdSize, AL, DELIVER, SDSD, MZ, ALL_COUNT
FROM ((([SELECT HouseholdSize, Count(*) AS ALL_COUNT FROM 4_Unsponsored_All
GROUP BY HouseholdSize]. AS Table0

LEFT JOIN

[SELECT HouseholdSize, Count(*) AS AL FROM 4_Unsponsored_All
WHERE SDSD = 0 AND Deliver = 0 AND LastNames<'M'
GROUP BY HouseholdSize]. AS Table1 ON Table0.HouseholdSize = Table1.HouseholdSize)

LEFT JOIN

[SELECT HouseholdSize, Count(*) AS MZ FROM 4_Unsponsored_All
WHERE SDSD = 0 AND Deliver = 0 AND LastNames>='M'
GROUP BY HouseholdSize]. AS Table2 ON Table0.HouseholdSize=Table2.HouseholdSize)

LEFT JOIN

[SELECT HouseholdSize, Count(*) AS SDSD FROM 4_Unsponsored_All
WHERE SDSD = -1
GROUP BY HouseholdSize]. AS Table3 ON Table0.HouseholdSize=Table3.HouseholdSize)

LEFT JOIN

[SELECT HouseholdSize, Count(*) AS DELIVER FROM 4_Unsponsored_All
WHERE (Deliver = -1 AND SDSD = 0) GROUP BY HouseholdSize]. AS Table4 ON Table0.HouseholdSize=Table4.HouseholdSize
WHERE 1;

This is what happens when you send your kid to college. It's finally paying off.:)

-aldo