first record query

J

jon

I am having trouble with a query which selects records with certain
criteria.
the query selects the records ok but when the criteria is added it is
missing certain jobs. the criteria is should select one record per job which
is the one with the oldest date.
The query without the criteria selects 44 records and when the criteria is
added it displays 3 records but there should be 4.
Also with different options selected on the query there might be more than
100 records and when the criteria is added there might be one where there
should be 9 etc.

The sql is

SELECT DISTINCTROW Baan.dueDate, Baan.Job, Componets.[Componet short Name],
((Format([DueDate],"yyyy")) & IIf(Format([DueDate],"ww")<10,0 &
Format([DueDate],"ww"),Format([DueDate],"ww"))) AS DueWeek, Baan.OrderNo,
Componets.Frame
FROM Componets INNER JOIN (ComponentNos INNER JOIN Baan ON
ComponentNos.[Componet No] = Baan.Job) ON Componets.RefNo =
ComponentNos.RefNo
WHERE (((Baan.dueDate)=(SELECT Min(DueDate) FROM Baan AS Early WHERE
Early.Job =(Baan.Job))) AND
((Componets.Frame)=[Forms]![Fr_WorkToList]![CMB_frame]) AND
((Baan.dateIcyBaan) Is Null));


It returns these 3 records

dueDate Job Componet short Name DueWeek OrderNo Frame
28/11/2007 J52430955-401 L/H DUCT COVER 200748 11537101 24
16/11/2007 J55300508-002 ST1 FIN TIP R/H STG1 200746 11544480 24
06/12/2007 J55300508-001 ST1 FIN TIP L/H STG1 200749 11544481 24

it is missing this record from the query below

05/12/2007 J52430955-402 R/H DUCT COVER 200749 11546427 24



The query without the criteria selects the following 44 records which the
criteria should select one for each job which has the oldest date in the
field (Baan.dueDate)

dueDate Job Componet short Name DueWeek OrderNo Frame
10/03/2008 J52430955-401 L/H DUCT COVER 200811 11516855 24
28/11/2007 J52430955-401 L/H DUCT COVER 200748 11537101 24
20/12/2007 J52430955-401 L/H DUCT COVER 200751 11550913 24
20/12/2007 J52430955-401 L/H DUCT COVER 200751 11559342 24
11/02/2008 J52430955-401 L/H DUCT COVER 200807 11559343 24
30/01/2008 J52430955-401 L/H DUCT COVER 200805 11559344 24
11/02/2008 J52430955-401 L/H DUCT COVER 200807 11559345 24
11/02/2008 J52430955-401 L/H DUCT COVER 200807 11559346 24
25/02/2008 J52430955-401 L/H DUCT COVER 200809 11567193 24
17/03/2008 J52430955-401 L/H DUCT COVER 200812 11570347 24
17/03/2008 J52430955-401 L/H DUCT COVER 200812 11570348 24
18/03/2008 J52430955-402 R/H DUCT COVER 200812 11537106 24
18/03/2008 J52430955-402 R/H DUCT COVER 200812 11537107 24
01/04/2008 J52430955-402 R/H DUCT COVER 200814 11537108 24
05/12/2007 J52430955-402 R/H DUCT COVER 200749 11546427 24
20/12/2007 J52430955-402 R/H DUCT COVER 200751 11548746 24
30/01/2008 J52430955-402 R/H DUCT COVER 200805 11554337 24
30/01/2008 J52430955-402 R/H DUCT COVER 200805 11554338 24
11/02/2008 J52430955-402 R/H DUCT COVER 200807 11567245 24
11/02/2008 J52430955-402 R/H DUCT COVER 200807 11567246 24
25/02/2008 J52430955-402 R/H DUCT COVER 200809 11568778 24
10/03/2008 J52430955-402 R/H DUCT COVER 200811 11568779 24
06/12/2007 J55300508-002 ST1 FIN TIP R/H STG1 200749 11533554 24
16/11/2007 J55300508-002 ST1 FIN TIP R/H STG1 200746 11544480 24
14/01/2008 J55300508-002 ST1 FIN TIP R/H STG1 200803 11549627 24
28/01/2008 J55300508-002 ST1 FIN TIP R/H STG1 200805 11552763 24
21/01/2008 J55300508-002 ST1 FIN TIP R/H STG1 200804 11560555 24
04/02/2008 J55300508-002 ST1 FIN TIP R/H STG1 200806 11560558 24
08/02/2008 J55300508-002 ST1 FIN TIP R/H STG1 200806 11565486 24
15/02/2008 J55300508-002 ST1 FIN TIP R/H STG1 200807 11565487 24
21/02/2008 J55300508-002 ST1 FIN TIP R/H STG1 200808 11570496 24
03/03/2008 J55300508-002 ST1 FIN TIP R/H STG1 200810 11570497 24
10/03/2008 J55300508-002 ST1 FIN TIP R/H STG1 200811 11571432 24
17/03/2008 J55300508-002 ST1 FIN TIP R/H STG1 200812 11571434 24
13/12/2007 J55300508-001 ST1 FIN TIP L/H STG1 200750 11533553 24
06/12/2007 J55300508-001 ST1 FIN TIP L/H STG1 200749 11544481 24
21/01/2008 J55300508-001 ST1 FIN TIP L/H STG1 200804 11552760 24
28/01/2008 J55300508-001 ST1 FIN TIP L/H STG1 200805 11552761 24
04/02/2008 J55300508-001 ST1 FIN TIP L/H STG1 200806 11560556 24
08/02/2008 J55300508-001 ST1 FIN TIP L/H STG1 200806 11560557 24
15/02/2008 J55300508-001 ST1 FIN TIP L/H STG1 200807 11565484 24
21/02/2008 J55300508-001 ST1 FIN TIP L/H STG1 200808 11565485 24
03/03/2008 J55300508-001 ST1 FIN TIP L/H STG1 200810 11571433 24
10/03/2008 J55300508-001 ST1 FIN TIP L/H STG1 200811 11571435 24

Thanks

Jon
 
J

Jeanette Cunningham

Hi jon,
I am having a look at this. I have set up 3 tables
Baan
ComponentNos
Components
I copied the SQL into a new query,
replaced (Componets.Frame)=[Forms]![Fr_WorkToList]![CMB_frame])
with (Componets.Frame)=24)
and the query just kept giving me errors and wouldn't open.

I tried your subquery as a separate query
SELECT Min(DueDate) FROM Baan AS Early WHERE Early.Job =Baan.Job
this is something wrong with the subquery as it gives and errors and won't
run as a separate query.

This query gives just one record:
SELECT DISTINCT Baan.DueDate, Baan.Job, Componets.[Componet Short Name],
Baan.DueWeek, Baan.OrderNo, Componets.Frame
FROM (ComponentNos INNER JOIN Baan ON ComponentNos.[Componet No] = Baan.Job)
INNER JOIN Componets ON ComponentNos.RefNo = Componets.RefNo
WHERE (((Baan.DueDate)=(SELECT Min(Baan.DueDate) AS MinOfDueDate
FROM Baan)) AND ((Componets.Frame)=24) AND ((Baan.DateIcyBaan) Is Null));


This query gives 16 records
SELECT Baan.dueDate, Baan.Job, Componets.[Componet short Name],
((Format([DueDate],"yyyy")) & IIf(Format([DueDate],"ww")<10,0 &
Format([DueDate],"ww"),Format([DueDate],"ww"))) AS DueWeek, Baan.OrderNo,
Componets.Frame
FROM Componets INNER JOIN (ComponentNos INNER JOIN Baan ON
ComponentNos.[Componet No] = Baan.Job) ON Componets.RefNo =
ComponentNos.RefNo
WHERE (((Baan.DueDate)=(SELECT Min(Baan.DueDate) AS MinOfDueDate
FROM Baan)) AND ((Componets.Frame)=24) AND ((Baan.DateIcyBaan) Is Null));

I don't have the full answer, but here are some pointers, hope this helps.
Jeanette Cunningham










jon said:
I am having trouble with a query which selects records with certain
criteria.
the query selects the records ok but when the criteria is added it is
missing certain jobs. the criteria is should select one record per job
which
is the one with the oldest date.
The query without the criteria selects 44 records and when the criteria is
added it displays 3 records but there should be 4.
Also with different options selected on the query there might be more than
100 records and when the criteria is added there might be one where there
should be 9 etc.

The sql is

SELECT DISTINCTROW Baan.dueDate, Baan.Job, Componets.[Componet short
Name],
((Format([DueDate],"yyyy")) & IIf(Format([DueDate],"ww")<10,0 &
Format([DueDate],"ww"),Format([DueDate],"ww"))) AS DueWeek, Baan.OrderNo,
Componets.Frame
FROM Componets INNER JOIN (ComponentNos INNER JOIN Baan ON
ComponentNos.[Componet No] = Baan.Job) ON Componets.RefNo =
ComponentNos.RefNo
WHERE (((Baan.dueDate)=(SELECT Min(DueDate) FROM Baan AS Early WHERE
Early.Job =(Baan.Job))) AND
((Componets.Frame)=[Forms]![Fr_WorkToList]![CMB_frame]) AND
((Baan.dateIcyBaan) Is Null));


It returns these 3 records

dueDate Job Componet short Name DueWeek OrderNo Frame
28/11/2007 J52430955-401 L/H DUCT COVER 200748 11537101 24
16/11/2007 J55300508-002 ST1 FIN TIP R/H STG1 200746 11544480 24
06/12/2007 J55300508-001 ST1 FIN TIP L/H STG1 200749 11544481 24

it is missing this record from the query below

05/12/2007 J52430955-402 R/H DUCT COVER 200749 11546427 24



The query without the criteria selects the following 44 records which the
criteria should select one for each job which has the oldest date in the
field (Baan.dueDate)

dueDate Job Componet short Name DueWeek OrderNo Frame
10/03/2008 J52430955-401 L/H DUCT COVER 200811 11516855 24
28/11/2007 J52430955-401 L/H DUCT COVER 200748 11537101 24
20/12/2007 J52430955-401 L/H DUCT COVER 200751 11550913 24
20/12/2007 J52430955-401 L/H DUCT COVER 200751 11559342 24
11/02/2008 J52430955-401 L/H DUCT COVER 200807 11559343 24
30/01/2008 J52430955-401 L/H DUCT COVER 200805 11559344 24
11/02/2008 J52430955-401 L/H DUCT COVER 200807 11559345 24
11/02/2008 J52430955-401 L/H DUCT COVER 200807 11559346 24
25/02/2008 J52430955-401 L/H DUCT COVER 200809 11567193 24
17/03/2008 J52430955-401 L/H DUCT COVER 200812 11570347 24
17/03/2008 J52430955-401 L/H DUCT COVER 200812 11570348 24
18/03/2008 J52430955-402 R/H DUCT COVER 200812 11537106 24
18/03/2008 J52430955-402 R/H DUCT COVER 200812 11537107 24
01/04/2008 J52430955-402 R/H DUCT COVER 200814 11537108 24
05/12/2007 J52430955-402 R/H DUCT COVER 200749 11546427 24
20/12/2007 J52430955-402 R/H DUCT COVER 200751 11548746 24
30/01/2008 J52430955-402 R/H DUCT COVER 200805 11554337 24
30/01/2008 J52430955-402 R/H DUCT COVER 200805 11554338 24
11/02/2008 J52430955-402 R/H DUCT COVER 200807 11567245 24
11/02/2008 J52430955-402 R/H DUCT COVER 200807 11567246 24
25/02/2008 J52430955-402 R/H DUCT COVER 200809 11568778 24
10/03/2008 J52430955-402 R/H DUCT COVER 200811 11568779 24
06/12/2007 J55300508-002 ST1 FIN TIP R/H STG1 200749 11533554 24
16/11/2007 J55300508-002 ST1 FIN TIP R/H STG1 200746 11544480 24
14/01/2008 J55300508-002 ST1 FIN TIP R/H STG1 200803 11549627 24
28/01/2008 J55300508-002 ST1 FIN TIP R/H STG1 200805 11552763 24
21/01/2008 J55300508-002 ST1 FIN TIP R/H STG1 200804 11560555 24
04/02/2008 J55300508-002 ST1 FIN TIP R/H STG1 200806 11560558 24
08/02/2008 J55300508-002 ST1 FIN TIP R/H STG1 200806 11565486 24
15/02/2008 J55300508-002 ST1 FIN TIP R/H STG1 200807 11565487 24
21/02/2008 J55300508-002 ST1 FIN TIP R/H STG1 200808 11570496 24
03/03/2008 J55300508-002 ST1 FIN TIP R/H STG1 200810 11570497 24
10/03/2008 J55300508-002 ST1 FIN TIP R/H STG1 200811 11571432 24
17/03/2008 J55300508-002 ST1 FIN TIP R/H STG1 200812 11571434 24
13/12/2007 J55300508-001 ST1 FIN TIP L/H STG1 200750 11533553 24
06/12/2007 J55300508-001 ST1 FIN TIP L/H STG1 200749 11544481 24
21/01/2008 J55300508-001 ST1 FIN TIP L/H STG1 200804 11552760 24
28/01/2008 J55300508-001 ST1 FIN TIP L/H STG1 200805 11552761 24
04/02/2008 J55300508-001 ST1 FIN TIP L/H STG1 200806 11560556 24
08/02/2008 J55300508-001 ST1 FIN TIP L/H STG1 200806 11560557 24
15/02/2008 J55300508-001 ST1 FIN TIP L/H STG1 200807 11565484 24
21/02/2008 J55300508-001 ST1 FIN TIP L/H STG1 200808 11565485 24
03/03/2008 J55300508-001 ST1 FIN TIP L/H STG1 200810 11571433 24
10/03/2008 J55300508-001 ST1 FIN TIP L/H STG1 200811 11571435 24

Thanks

Jon
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need help with query 2
Query Question 1
Outlook crashes with Outlook connector 2002 0
Outlook connector crashes outlook 0
Cannot do anything until drives spin 1
insert rows--->protected sheet 1
How can I fix these errors..... 16
was I hacked? 8

Top