first record query

[jon]

Hi
I am having trouble with the following query

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

What I need is one record per 'Componet short Name'
and this record is the oldest one i.e. first when sorted by 'Baan.dueDate'

What I am getting is one record per 'Componet short Name'
but a not the oldest record in the field 'Baan.dueDate' it seams to be a
random record i.e. it could be the 1st,2nd or 5th in the order,.

Any advise is very welcome.

Thanks

Jon

 

[John W. Vinson]

What I need is one record per 'Componet short Name'
and this record is the oldest one i.e. first when sorted by 'Baan.dueDate'

What I am getting is one record per 'Componet short Name'
but a not the oldest record in the field 'Baan.dueDate' it seams to be a
random record i.e. it could be the 1st,2nd or 5th in the order,.

"First" is misleading. Tables HAVE no controllable order. What you're getting
is the first record *in disk storage order* - as you see, that order isn't
necessarily chronological (or any other sort of logical!)

You need a Subquery instead:

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

Air code, untested of course but that should give you an alternative.

Note the simplification of the DueWeek code - Right ("01", 2) and Right("052",
2) both give a two digit week string.

John W. Vinson [MVP]

 

[jon]

Hi John

the shortened version did not work but I will try it later

Note the simplification of the DueWeek code - Right ("01", 2) and
Right("052",
2) both give a two digit week string.

Also I don't understand the following

AND Baan.DueDate = (SELECT Min(DueDate] FROM Baan AS Early WHERE Early.Job =
Baan.Job);


What is early?
also it is missing 1 record it should be displaying 4 records but there are
only 3
and when I secect a diffrent frame no there are no records when there should
be 11

Thanks Jon

 

[John W. Vinson]

AND Baan.DueDate = (SELECT Min(DueDate] FROM Baan AS Early WHERE Early.Job =
Baan.Job);

Slight typo there on my part: should have been

=(SELECT Min([DueDate] ...

What is early?

I'm using the Baan table twice: once to select the records (in the outer
query), and once to find the earliest due date for that job - what you were
trying to do using First. Early is just an alias for the second instance of
Baan, since you need to refer to both tables.

also it is missing 1 record it should be displaying 4 records but there are
only 3
and when I secect a diffrent frame no there are no records when there should
be 11

You can see your data. I cannot. I have no way to know what data you're
working with or what you expect. My *intention* - which may be a
misinterpretation of your intention! - was to record for each job with the
earliest DueDate for that job. If that's not what you want please explain,
perhaps with some sample data (just the essential fields) from the table, the
desired result, and the rationale for that result.

John W. Vinson [MVP]

 

[jon]

Hi John
With the criteria of the query it displays the following 3 records


28/11/2007 J52430955-401 I/HE DUCT COVER 200748 11537101 24
16/11/2007 J55300508-002 STY FIN TIP OR/HE STAG 200746 11544480 24
06/12/2007 J55300508-001 STY FIN TIP I/HE STAG 200749 11544481 24

It is missing this record

05/12/2007 J52430955-402 OR/HE DUCT COVER 200749 11546427 24



and this all the records from the query with out the criteria in it

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

I cant see any reason why it does not select this order and on a different
frame from 124 records it should select about 10 records it only sects 1
record.

Thanks for the help

Jon

 

[John W. Vinson]

Hi John
With the criteria of the query it displays the following 3 records


28/11/2007 J52430955-401 I/HE DUCT COVER 200748 11537101 24
16/11/2007 J55300508-002 STY FIN TIP OR/HE STAG 200746 11544480 24
06/12/2007 J55300508-001 STY FIN TIP I/HE STAG 200749 11544481 24

It is missing this record

05/12/2007 J52430955-402 OR/HE DUCT COVER 200749 11546427 24

Hrm. Can't see why it would! Could you copy and paste the actual SQL that
you're using to a message?

John W. Vinson [MVP]

 

[jon]

Hi John

The data base is at work and I have finished now so have no access to it
until the new year.
should I continue this thread in the new year or start a new one?


Thanks for the help
Jon

 

[John W. Vinson]

Hi John

The data base is at work and I have finished now so have no access to it
until the new year.
should I continue this thread in the new year or start a new one?

Better start a new one: it might get someone else's eyes on the problem who
can see what we're both apparently missing!


John W. Vinson [MVP]