Help with Dlookup in a query

[cooh23]

I am having difficulties setting a criteria in DLookup.
How do I setup the Dlookup criteria to get the result if date of service = MinDOV?

Let's say i have the following table
PatientID--DateofVisit--ScreeningType--Result
1234--01/01/2011--LDL--140

if MinDate =DateofVisit it should give me 140.

Thank you,

John

SELECT [tbl_LDL-BSA1C].PatientID, Min([tbl_LDL-BSA1C].DateOfVisit) AS MinDOV, Max([tbl_LDL-BSA1C].DateOfVisit) AS MaxDov, [tbl_LDL-BSA1C].ScreeningType, DateDiff("ww",[mindov],[maxdov]) AS WeeksApart, DLookUp("result","tbl_ldl-bsa1c","dateofvisit='#mindov#'") AS Result
FROM [tbl_LDL-BSA1C]
GROUP BY [tbl_LDL-BSA1C].PatientID, [tbl_LDL-BSA1C].ScreeningType;

 

[bob fitz]

Hi

How do I setup the Dlookup criteria to get the result if date of service = MinDOV?

Let's say i have the following table
PatientID--DateofService--ScreeningType--Result
1234--01/01/2011--LDL--140

if MinDate =DateofService it should give me 140.

SELECT [tbl_LDL-BSA1C].PatientID, Min([tbl_LDL-BSA1C].DateOfVisit) AS MinDOV, Max([tbl_LDL-BSA1C].DateOfVisit) AS MaxDov, [tbl_LDL-BSA1C].ScreeningType, DateDiff("ww",[mindov],[maxdov]) AS WeeksApart, DLookUp("result","tbl_ldl-bsa1c","dateofvisit='#mindov#'") AS Result
FROM [tbl_LDL-BSA1C]
GROUP BY [tbl_LDL-BSA1C].PatientID, [tbl_LDL-BSA1C].ScreeningType;

What is DateofService and what is DateOfVisit.

 

[cooh23]

Hi




What is DateofService and what is DateOfVisit.

Opps... Sorry, I meant DateofVisit instead of DateofService.

Thank you.

 

[stopher]

I would do this as two queries. The first query would simply find the max and min dates for each PatientID/ScreeningType:

SELECT PatientID, ScreeningType, Min(DateOfVisit) AS MinDov, Max(DateOfVisit) AS MaxDOV
FROM [tbl_LDL-BSA1C]
GROUP BY PatientID, ScreeningType

Lets call the above query: qryMinMaxDates.

The second query would use the above query to obtain the rest of the details by joining the above query with the original table:

SELECT qryMinMaxDates.PatientID, qryMinMaxDates.MinDov, qryMinMaxDates.MaxDOV, qryMinMaxDates.ScreeningType, DateDiff("ww",[mindov],[maxdov]) AS WeeksApart, [tbl_LDL-BSA1C].Result
FROM [tbl_LDL-BSA1C] INNER JOIN qryMinMaxDates ON ([tbl_LDL-BSA1C].PatientID=qryMinMaxDates.PatientID) AND ([tbl_LDL-BSA1C].DateOfVisit=qryMinMaxDates.MinDov)

I would avoid using Dlookup in queries. It's not going to be very efficient.

hth
Chris

 

[cooh23]

I would do this as two queries. The first query would simply find the max and min dates for each PatientID/ScreeningType:

SELECT PatientID, ScreeningType, Min(DateOfVisit) AS MinDov, Max(DateOfVisit) AS MaxDOV
FROM [tbl_LDL-BSA1C]
GROUP BY PatientID, ScreeningType

Lets call the above query: qryMinMaxDates.

The second query would use the above query to obtain the rest of the details by joining the above query with the original table:

SELECT qryMinMaxDates.PatientID, qryMinMaxDates.MinDov, qryMinMaxDates.MaxDOV, qryMinMaxDates.ScreeningType, DateDiff("ww",[mindov],[maxdov]) AS WeeksApart, [tbl_LDL-BSA1C].Result
FROM [tbl_LDL-BSA1C] INNER JOIN qryMinMaxDates ON ([tbl_LDL-BSA1C].PatientID=qryMinMaxDates.PatientID) AND ([tbl_LDL-BSA1C].DateOfVisit=qryMinMaxDates.MinDov)

I would avoid using Dlookup in queries. It's not going to be very efficient.

hth
Chris

Thank you! It worked. I joined ScreeningType on both queries and I am now getting the results I want. However, the Result only show the MinDOV result value. How do I show MaxDOV result Value?

For example:

PatientID--DateofVisit--ScreeningType--Result
1234--01/01/2009--LDL--130
1234--01/09/2009--LDL--150

MinDOV should show 01/01/2009
MaxDOV should show 01/09/2009

MinDOV Result should show 130
MaxDOV Result should show 150

This was the original reason why I was using DLookup so I could match the DateofVisit to MinDOV and show the result for MinDOV

Thank you,

John

 

[Galaxiom]

Using a Dlookup in a query is incredibly inefficient because it is run for every record. The query should be rewritten with a join.

BTW The problem with the DLookup is that it is asking for the dateofvisit to be equal to a literal string:
"dateofvisit='#mindov#'"

 

[cooh23]

Using a Dlookup in a query is incredibly inefficient because it is run for every record. The query should be rewritten with a join.

BTW The problem with the DLookup is that it is asking for the dateofvisit to be equal to a literal string:
"dateofvisit='#mindov#'"

I see. I tried so many different combinations for making those two equal and I have had no success.

 

[stopher]

You can join to the main table again (see attached)

 

[cooh23]

You can join to the main table again (see attached)

Thank you again! You have been very helpful and I really appreciate it! *thumbs up*

I joined screeningtype on both sides of the table in the query to show the results I wanted.

Now I can finally go to sleep. It's 2:30am here.

Thanks again!

John