Adding data in a 2nd query

[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

How would I go about doing that?

 

[Ken Snell \(MVP\)]

One way is to use non-equi join in a query. Here's an example that you can
tweak to your setup:

SELECT TableName.Field1, TableName.Field2
FROM TableName INNER JOIN OtherTable
ON OtherTable.DeliveryDate Between
TableName.FirstVisitField And TableName.DueDateField
AND OtherTable.ForeignKeyField = TableName.PrimaryKeyField;

 

[Jeff]

I did that and it works great for women that have a delivery date on
them, 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?

Thanks

 

[Ken Snell \(MVP\)]

Post the SQL statement that you're now using, and we can show you how to
modify it.

 

[Jeff]

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;

 

[Ken Snell \(MVP\)]

Looks like you didn't use a non-equi-join after all... but this change
should give you the records where no delivery date is provided, if I'm
understanding your tables' structures correctly:

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] OR [Pregnancy Outcomes].[Baby's DOB] Is Null))
ORDER BY Demographics.LastName DESC;

--

Ken Snell
<MS ACCESS MVP>

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;