help with SQL querry

[Guest]

I wonder if I can do this in Access.
I have a table tbBoars

bo_ID bo_Location
1 A
2 B
3 B
........

and a table tbDeliveries

deID de_boID de_Date de_Qty
1 2 01/08/2006 10
2 2 01/09/2006 20
3 2 01/10/2006 30
4 2 01/11/2006 40
5 1 01/08/2006 50
6 3 01/08/2006 60


I would like to create a report which will show each boar and its three
latest deliveris in one line

BoarID Qty1 Qty2 Qty3
1 50 - -
2 40 30 20
3 60 - -

Is there a way how can I do it via SQL querry or report in Access

Thanks for your help
Supicek

 

[John Spencer]

This query should get the three most recent deliveries for each de_BOID

SELECT de_BoID, de_qty, de_Date
FROM tbDeliveries
WHERE de_Date in
(SELECT TOP 3 temp.de_Date
FROM tbDeliveries as Temp
WHERE Temp.de_BOID = tblDeliveries.de_boID
ORDER BY Temp.de_Date Desc, de_boID)

You should be able to use that query as the basis for a crosstab query.

 

[Guest]

thanks it is close, but not exactly what I intended.
Now I have a table with three latest deliveries for each boar (3 rows for
each boar).
What I do not know is how to trasfer three lines into one line with more
columns

I have

de_boID de_Date de_Qty
2 01/09/2006 20
2 01/10/2006 30
2 01/11/2006 40

I'd like to get

de_boID Qty1 Qty2 Qty3
2 40 30 20

 

[Guest]

I mean I do not know how to built the crosstab querry
thanks Pavel

 

[Guest]

I have found it in the querry wizard. I will try to make it work.
thanks a lot.
supicek

 

[Guest]

One more question. I have figure out how to create a crosstab query.
Can you ammend your querry in order to get a sequence for each row.
The sequense should indicate a delivery for each boar, so the output from
the querry should look like:

deID de_boID de_Date de_Qty Seq
1 2 01/08/2006 10 1
2 2 01/09/2006 20 2
3 2 01/10/2006 30 3
4 1 01/09/2006 40 1
5 1 01/10/2006 50 2
6 1 01/11/2006 60 3

thanks

 

[John Spencer]

SELECT de_BoID, de_qty, de_Date
FROM tbDeliveries
WHERE de_Date in
(SELECT TOP 3 temp.de_Date
FROM tbDeliveries as Temp
WHERE Temp.de_BOID = tblDeliveries.de_boID
ORDER BY Temp.de_Date Desc, de_boID)

Save the above query as qTopThree and use it in the query below
SELECT de_BoID, de_qty, de_Date
, (SELECT Count(*)
FROM qTopThree as Temp
WHERE TEMP.de_boID = qTopThree.de_BOID
AND TEMP.de_Date <= qTopThree.deDate) as SequenceNumber
FROM qTopThree


Save that query and use as the source for your crosstab.

You could probably put that all together in one query if you wanted, but it
is often easier to build and understand complex queries using this method.
See what this might look like before you even created the crosstab

SELECT de_BoID, de_qty, de_Date
, (SELECT Count(*)
FROM (SELECT de_BoID, de_qty, de_Date
FROM tbDeliveries
WHERE de_Date in
(SELECT TOP 3 temp.de_Date
FROM tbDeliveries as Temp
WHERE Temp.de_BOID = tblDeliveries.de_boID
ORDER BY Temp.de_Date Desc, de_boID)) as Temp
WHERE TEMP.de_boID = qTopThree.de_BOID
AND TEMP.de_Date <= qTopThree.deDate) as SequenceNumber
FROM (SELECT de_BoID, de_qty, de_Date
FROM tbDeliveries
WHERE de_Date in
(SELECT TOP 3 temp.de_Date
FROM tbDeliveries as Temp
WHERE Temp.de_BOID = tblDeliveries.de_boID
ORDER BY Temp.de_Date Desc, de_boID)) as qTopThree

 

[Guest]

Thanks John,
I will not have a chance to check it in the following two days, but I think
I understand the logic of the querry.

thanks again.

Supicek