Adding data from a new table

[Jeff]

I have a query that current pulls data on the expected due dates of
pregnant women. This query contains the first dr visit date and the
expected due date. My client now wants information on the delivery
information as well. Obviously this cannot be exactly matched as
anything can happen between the 1st dr visit and the due date.

I'm not sure how to go about writing a second query that uses the
first query to looks at the delivery date (or termination date) in
another table and matching it to the first query. I would like to
match the record based on the delivery date or termination date being
between the 1st dr visit date and the expected due date, and then
adding certain fields to the query results.


Mother
1st dr visit 2/1/08
Due date 8/1/08


Delivery information that would match -- 7/31/08


I included a between date check for matching records, but it now
excludes all women who have not yet delivered. Is
there a way to get the query to leave the information blank if the
woman has not delivered?

The query is as follows:

SELECT Demographics.DemogID, Demographics.[SS#], Demographics.[Ok To
Contact by Mail], Demographics.[Ok to Contact by Phone],
Demographics.
[Home Visit], Demographics.[Home Visitor], Demographics.Address,
Demographics.[Apartment Number], Demographics.City,
Demographics.State, Demographics.[Active Van Client], Demographics.
[ZIP Code], Demographics.[Home Phone Number], Demographics.[Date of
Birth], Demographics.IntakeDate, Demographics.[Van Start Date], [HMB
Test].LMP, DateAdd("d",90,Date()) AS Plus90, DateAdd("d",120,[HMB
Test]!EDC) AS DuePlus90, Demographics.[Van Neighborhood Served],
Demographics.FirstName, Demographics.LastName,
Demographics.MiddleName, [HMB Test].Date, [HMB Test].EDC, [HMB Test].
[Test Results], [Pregnancy Outcomes].[Lost to Followup Date],
Count([HMB Test].TestID) AS CountOfTestID, [Pregnancy Outcomes].
[Baby's DOB], [Pregnancy Outcomes].[Deceased Date]
FROM (Demographics INNER JOIN [HMB Test] ON Demographics.DemogID =
[HMB Test].DemogID) LEFT JOIN [Pregnancy Outcomes] ON
Demographics.DemogID = [Pregnancy Outcomes].DemogID
GROUP BY Demographics.DemogID, Demographics.[SS#], Demographics.[Ok
To
Contact by Mail], Demographics.[Ok to Contact by Phone],
Demographics.
[Home Visit], Demographics.[Home Visitor], Demographics.Address,
Demographics.[Apartment Number], Demographics.City,
Demographics.State, Demographics.[Active Van Client], Demographics.
[ZIP Code], Demographics.[Home Phone Number], Demographics.[Date of
Birth], Demographics.IntakeDate, Demographics.[Van Start Date], [HMB
Test].LMP, Demographics.[Van Neighborhood Served],
Demographics.FirstName, Demographics.LastName,
Demographics.MiddleName, [HMB Test].Date, [HMB Test].EDC, [HMB Test].
[Test Results], [Pregnancy Outcomes].[Lost to Followup Date],
[Pregnancy Outcomes].[Baby's DOB], [Pregnancy Outcomes].[Deceased
Date]
HAVING (((Demographics.[Active Van Client])=-1) AND (([HMB Test].
[Test
Results])="Positive" Or ([HMB Test].[Test Results])="Already
Pregnant") AND (([Pregnancy Outcomes].[Baby's DOB]) Between [HMB
Test].
[Date] And [HMB Test].[EDC]))
ORDER BY Demographics.LastName DESC;