Query misses a record

  • Thread starter Thread starter Bob Dolmetsch
  • Start date Start date
B

Bob Dolmetsch

I have a query as below meant to select the most recent
scan for this patient and evaluate the CRL (Crown-Rump
length for a fetus):

SELECT Growth.SSN, Max(Growth.[SCAN DATE]) AS [MaxOfSCAN
DATE], Growth.[PREGNANCY NUMBER], Growth.[INFANT NUMBER],
Growth.Crl INTO backGrowth
FROM Growth
GROUP BY Growth.SSN, Growth.[PREGNANCY NUMBER], Growth.
[INFANT NUMBER], Growth.Crl
HAVING (((Growth.SSN)=[forms]![growth screen1]![ssn]) AND
((Max(Growth.[SCAN DATE]))<CDate([forms]![growth screen1]!
[scan date])) AND ((Growth.[PREGNANCY NUMBER])=[forms]!
[growth screen1]![pregnancy number]) AND ((Growth.[INFANT
NUMBER])=[forms]![growth screen1]![infant number]) AND
((Growth.Crl) Is Not Null));

This is the second scan for the current patient during the
current pregnancy;
I can see the records in table view. I've retyped the
data to confirm that it is typed correctly and yet the SQL
does not find the first record.

Can anyone suggest why?

(the current date is 10/13/2003; the next previous record
is for 10/06/2003; CRL is .4 in both records.
 
Bob Dolmetsch said:
SELECT Growth.SSN, Max(Growth.[SCAN DATE]) AS [MaxOfSCAN
DATE], Growth.[PREGNANCY NUMBER], Growth.[INFANT NUMBER],
Growth.Crl INTO backGrowth
FROM Growth
GROUP BY Growth.SSN, Growth.[PREGNANCY NUMBER], Growth.
[INFANT NUMBER], Growth.Crl
HAVING (((Growth.SSN)=[forms]![growth screen1]![ssn]) AND
((Max(Growth.[SCAN DATE]))<CDate([forms]![growth screen1]!
[scan date])) AND ((Growth.[PREGNANCY NUMBER])=[forms]!
[growth screen1]![pregnancy number]) AND ((Growth.[INFANT
NUMBER])=[forms]![growth screen1]![infant number]) AND
((Growth.Crl) Is Not Null));

This is the second scan for the current patient during the
current pregnancy;
I can see the records in table view. I've retyped the
data to confirm that it is typed correctly and yet the SQL
does not find the first record.
Click to expand...

Bob,

you're grouping on SSN, [PREGNANCY NUMBER], [INFANT NUMBER] and Crl
and therefore you can only have one calculated value for the date pro
group. You've choosen the Max function, so the query will display the
newest date for each group. I guess SSN and the two numbers are the
same in the 2 records, and because the CRL has also the same value,
there will be only 1 record for this group.

In order to see all records, you need a simple SELECT statement
without grouping, and with a WHERE clause instead of HAVING. You don't
need the grouping anyway, since you have given values for all group
fields:

SELECT Growth.SSN, Growth.[SCAN DATE], Growth.[PREGNANCY NUMBER],
Growth.[INFANT NUMBER], Growth.Crl INTO backGrowth FROM Growth
WHERE (((Growth.SSN)=[forms]![growth screen1]![ssn]) AND (Growth.[SCAN
DATE])<CDate([forms]![growth screen1]![scan date])) AND
((Growth.[PREGNANCY NUMBER])=[forms]![growth screen1]![pregnancy
number]) AND ((Growth.[INFANT NUMBER])=[forms]![growth
screen1]![infant number]) AND ((Growth.Crl) Is Not Null));

Beware syntax errors for missing brackets etc., untested!

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
 
Bob said:
I have a query as below meant to select the most recent
scan for this patient and evaluate the CRL (Crown-Rump
length for a fetus):

SELECT Growth.SSN, Max(Growth.[SCAN DATE]) AS [MaxOfSCAN
DATE], Growth.[PREGNANCY NUMBER], Growth.[INFANT NUMBER],
Growth.Crl INTO backGrowth
FROM Growth
GROUP BY Growth.SSN, Growth.[PREGNANCY NUMBER], Growth.
[INFANT NUMBER], Growth.Crl
HAVING (((Growth.SSN)=[forms]![growth screen1]![ssn]) AND
((Max(Growth.[SCAN DATE]))<CDate([forms]![growth screen1]!
[scan date])) AND ((Growth.[PREGNANCY NUMBER])=[forms]!
[growth screen1]![pregnancy number]) AND ((Growth.[INFANT
NUMBER])=[forms]![growth screen1]![infant number]) AND
((Growth.Crl) Is Not Null));

This is the second scan for the current patient during the
current pregnancy;
I can see the records in table view. I've retyped the
data to confirm that it is typed correctly and yet the SQL
does not find the first record.

Can anyone suggest why?

(the current date is 10/13/2003; the next previous record
is for 10/06/2003; CRL is .4 in both records.
Click to expand...

I don't think I can help with this, but I do have a couple
of observations:

1.) The SCAN DATE])) < Cdate( part should probably use <=

2.) Except possibly for the SCAN DATE part, the conditions
should be in a WHERE clause not the HAVING clause.
 
Back
Top