How to get needed records based on date of one record

[TonyG]

Hello.

We have a database used just for reporting with status records that have TIMESTAMP and WORKSTATION fields as in:

TempStatusRecNo TIMESTAMP WORKSTATION JOBNO
2091705 4/22/2004 6:58:32 AM #4- IN WORK 2803
2091703 4/22/2004 6:55:39 AM #2- RECEIVED 2803
2092176 4/28/2004 1:04:06 PM #5- COMPLETED 2803
2092401 5/3/2004 11:14:34 AM #7- INVOICED 2803
2091704 4/22/2004 6:55:47 AM #2- RECEIVED 2803

It gives me the records with the right TIMESTAMP, but it only gives me the WORKSTATION="#2- RECEIVED" records.

I want to be able to select all of the job records that match a set of criteria from a form. The form will ask for a date range which i will use to select records whose TIMESTAMP falls in the date range but only use the record with WORKSTATION = "#2-RECEIVED". Once i find a JOBNO that has a "#2-RECEIVED" record that falls within the date range from the form, I then want all records for that JOBNO.

If it finds two or more records that fall within the same criteria with different JOBNO's, thats OK, because the report groups the records by JOBNO, showing each of the WORKSTATION's for that JOBNO.

If a JOBNO does NOT have a WORKSTATION = "#2-RECEIVED" record, then that JOBNO will not appear on the report.

Here is the reports underlying query:

SELECT DISTINCT TempStatus.WORKSTATION, TempStatus.TIMESTAMP,
TempStatus.JOBNO, [PENDING ORDERS].DESCRIPTION, [PENDING ORDERS].TARGET,
[PENDING ORDERS].[COMPANY NAME], [PENDING ORDERS].SIDEMARK, [PENDING
ORDERS].TIMESTAMP, [PENDING ORDERS].[NUMBER OF FABRICS], INSTALL.INSTALLER,
INSTALL.[INSTALL DATE], INSTALL.[INSTALL NOTES], INSTALL.WORKSTATION,
INSTALL.TIMESTAMP FROM ([PENDING ORDERS] LEFT JOIN TempStatus ON [PENDING
ORDERS].JOBNO=TempStatus.JOBNO) LEFT JOIN INSTALL ON
[PENDING ORDERS].JOBNO=INSTALL.[JOB NO]
ORDER BY TempStatus.TIMESTAMP;

Here is my where statement for the "DoCmd.OpenReport stDocName, acViewPreview, , strWhere" statement:

"[COMPANY NAME] = "BLAIR HOUSE 10023" AND [SIDEMARK] = "MARGARGEL" AND
[TempStatus].WORKSTATION = "#2- RECEIVED" AND [TempStatus].TIMESTAMP Between
#04/22/2004# And #04/23/2004#"

Any help would be greatly appreciated.

Thanks,
Tony

 

[PHV]

You may try something like this:
strWhere = "[COMPANY NAME]='BLAIR HOUSE 10023' AND SIDEMARK='MARGARGEL'" _
& " AND TempStatus.JOBNO In (SELECT JOBNO FROM TempStatus WHERE WORKSTATION='#2- RECEIVED'" _
& " AND TIMESTAMP Between #2004-04-22# And #2004-04-23#)" _
& " AND TempStatus.TIMESTAMP Between #2004-04-22# And #2004-04-23#"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TonyG]

Hello PH.

Thanks for your reply.

I tried your suggestion and it yields an empty report.

Thanks,
Tony