Relationships, Default Numbers, Queries

[knowshowrosegrows]

I am having trouble figuring out how to track dates in my database.

Tables =

tblProgram
Program_ID PK
LOC_ID FK
Contact_ID FK
Agency_ID FK

tblCensusEvent
Census_ID PK
Program_ID FK
CensusDate
Census - Default = 0
Admissions - Default = 0
Discharges - Default = 0

tblAgency
Agency_ID PK

tblContact
Contact_ID PK

tblLOC
LOC_ID PK

I need a query that tells me about the census events for a given census date
for all the programs regardless of whether information was entered or not for
each program. So, if only 3 of 100 programs reported a census on 1/1/01, I
have 97 rows with zeros in the Census, Admission and Discharge fields for
that date.

When I write the queries I have tried, I end up with a report of the 3
records that had data, rather than the 100 records with all the zeros.

 

[KARL DEWEY]

Use a left join from program to the other tables.

If you post your query SQL folks can suggest how to make it work.

 

[knowshowrosegrows]

Thanks for your swift reply.

When I made the left join as you suggested, I got records for the 37 of the
total 67 programs. these are the programs with data for the date requested.
The SQL for that query follows:

SELECT tblProgram.Prm_Code, tblCensusEvent.CensusDate,
tblCensusEvent.Census, tblCensusEvent.Admiss, tblCensusEvent.Discharges,
tblContactInfo.Contact_ID, tblLOC.LOC_ID, tblAgency.Agency_ID
FROM tblAgency INNER JOIN (tblContactInfo INNER JOIN (tblLOC INNER JOIN
(tblCensusEvent INNER JOIN tblProgram ON tblCensusEvent.Prm_Code =
tblProgram.Prm_Code) ON tblLOC.LOC_ID = tblProgram.LOC_ID) ON
tblContactInfo.Contact_ID = tblProgram.Contact_ID) ON tblAgency.Agency_ID =
tblProgram.Agency_ID
WHERE (((tblCensusEvent.CensusDate)=#8/22/2007#));

 

[KARL DEWEY]

Your SQL has no LEFT JOIN as you can see it has INNER JOIN.
Try this ---
SELECT tblProgram.Prm_Code, tblCensusEvent.CensusDate,
tblCensusEvent.Census, tblCensusEvent.Admiss, tblCensusEvent.Discharges,
tblContactInfo.Contact_ID, tblLOC.LOC_ID, tblAgency.Agency_ID
FROM tblAgency RIGHT JOIN (tblContactInfo RIGHT JOIN (tblLOC RIGHT JOIN
(tblCensusEvent RIGHT JOIN tblProgram ON tblCensusEvent.Prm_Code =
tblProgram.Prm_Code) ON tblLOC.LOC_ID = tblProgram.LOC_ID) ON
tblContactInfo.Contact_ID = tblProgram.Contact_ID) ON tblAgency.Agency_ID =
tblProgram.Agency_ID
WHERE (((tblCensusEvent.CensusDate)="#08/22/2007#"));

Your earlier post of your table structure definitely did not match what is
in your query.

 

[knowshowrosegrows]

I have just started reading SQL Queries for Mere Mortals. I clearly have to
read faster because I am out of my league.

I put your SQL (it follows) in a new query and still only got the 37
programs that submitted data for 8/22/07. I want all 67 records in the
tblProgram (with any census data they submitted for the date) whether they
submitted data for discharges, admissions and census or not.

Your SQL =
SELECT tblProgram.Prm_Code, tblCensusEvent.CensusDate,
tblCensusEvent.Census, tblCensusEvent.Admiss, tblCensusEvent.Discharges,
tblContactInfo.Contact_ID, tblLOC.LOC_ID, tblAgency.Agency_ID
FROM tblAgency RIGHT JOIN (tblContactInfo RIGHT JOIN (tblLOC RIGHT JOIN
(tblCensusEvent RIGHT JOIN tblProgram ON tblCensusEvent.Prm_Code =
tblProgram.Prm_Code) ON tblLOC.LOC_ID = tblProgram.LOC_ID) ON
tblContactInfo.Contact_ID = tblProgram.Contact_ID) ON tblAgency.Agency_ID =
tblProgram.Agency_ID
WHERE (((tblCensusEvent.CensusDate)="#08/22/2007#"));

Could this have something to do with a two field index I have in
tblCensusEvent? The index is called "No Duplicates" and has two fields =
Prm_Code and CensusDate. I don't want the user to be able to submit data for
a program twice on one date.

 

[KARL DEWEY]

Change the WHERE to this ---
WHERE (((tblCensusEvent.CensusDate)="#08/22/2007#" Or
(tblCensusEvent.CensusDate) Is Null));