RE: Relationships, Default Numbers, Queries

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#));
--
Thanks


"KARL DEWEY" wrote:

Use a left join from program to the other tables.

If you post your query SQL folks can suggest how to make it work.
--
KARL DEWEY
Build a little - Test a little


"knowshowrosegrows" wrote:

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.
--
Thanks
.

Relevant Pages

  • RE: Relationships, Default Numbers, Queries
    ... Your SQL has no LEFT JOIN as you can see it has INNER JOIN. ... The SQL for that query follows: ... FROM tblAgency INNER JOIN (tblContactInfo INNER JOIN (tblLOC INNER JOIN ... I need a query that tells me about the census events for a given census date ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Date Parameters Irritation
    ... If you are going to use that query as the source for another query, ... it may be that the query is running twice and therefore ... "Choose Beginning Census Date" and Choose Ending Census Date" message boxes ... FROM tblAgency INNER JOIN (tblLOCID INNER JOIN (((tblLOCOld INNER JOIN ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... I have students, courses, exam groups containing exams of courses, ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... I solved my problem using stored queries to act as subqueries. ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... The first query ... TRANSFORM FirstAS FirstOfscore ... FROM tblStudent INNER JOIN (((tblEduYear INNER JOIN tblExamGrp ON ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)