SELECT QUERY?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello -
I am not am expert at developing databases.
I am working on a sales order database that will also track the shipping
records.
I need to retrive data from a query that will give me Previous Quantiy
shipped.
I need the actual ship form the current record to be less than the actual
ship date from the previous records. How do i run this type of query based on
the information from the current record being entered.

Please HELP! TCI Dawn
 
Hi,


SELECT a.somefield1, b.somefield2
FROM (mytable As a INNER JOIN myTable As b
ON a.DateShipped > b.DateShipped )
INNER JOIN myTable as c
ON a.DateShipped > c.DateShipped
GROUP BY a.somefield1, b.somefield2
HAVING b.DateShipped = MAX(c.DateShipped)





The SQL code refers to your table 3 times (once as a, once as b, once as c).
For a given record in "a", the other two references get only the records
that occurred before. While each record from "b" make a potential group,
only these group matching the "latest" (max) date referred in "c" are kept,
so, effectively, "b" is then limited to just the latest record following the
"a" record.

Remember that each field you need in the SELECT clause should appear in the
GROUP BY clause: use "a" for the actual record, "b" for the "previous"
record:


SELECT a.qty, b.qty FROM ...


as example, a.qty is the actual quantity, and b.qty is the quantity from the
"previous" record.


If you also need a limit "per client" , like the previous record ***from the
same client***, change to ON clauses to something like:



SELECT a.ClientID, a.somefield1, b.somefield2
FROM (mytable As a INNER JOIN myTable As b
ON a.DateShipped > b.DateShipped AND a.ClientID=b.ClientID)
INNER JOIN myTable as c
ON a.DateShipped > c.DateShipped AND a.ClientID=c.ClientID
GROUP BY a.ClientID, a.somefield1, b.somefield2
HAVING b.DateShipped = MAX(c.DateShipped)




Hoping it may help,
Vanderghast, Access MVP
 
Thank you so much for your response.
But I am lost.
I need to retrive all previous records (based on the current record) from a
shipment grouped by orderdetailID and LineItem.
Based on a query my fields are as followings:
OrderDetailID, LineItem, QtyShipped, ActualShipDate
All fields are from the same table Shipping Details.

Please help me put this together.
The placement of the SQL code goes... where?
I am well - lost.

Please Help!
 
Hi,


If you need ALL the records preceding the "actual" one, not just the single
one immediately before, try:


SELECT a.*, b.*

FROM myTable As a LEFT JOIN myTable as b
ON a.OrderDetail=b.OrderDetail
AND a.LineItem = b.LineItem
AND a.ActualShipDate> b.ActualShipDate

ORDER BY a.OrderDetail, a.LineItem, a.ActualShipDate ASC, b.ActualShipDate
DESC


Fields from reference "a" are your "actual record", fields from reference
"b" are from one of the previous record (If they are all null, it is because
"a" is the earliest record for that OrderDetail AND LineItem value). If
the actual record has 5 previous records, you will get 5 records, with the
actual record, and each of the 5 records in the result will list ONE of the
previous record.


The ORDER BY is not mandatory, but I put it in place so the result you get
is easier to understand.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top