Query Help - Access 2003

[Sean]

I was given this query (see below) but it is not doing what I needed. Can
someone please help?

My table = Recv, Fields = TXN, Material, Rdate, Rtime

I want to capture all Materials with a 101 TXN type (each one has a date and
time associated with it) and then I want to see the very next 311 Txn for the
same Material. Usually there is only 1, 101 TXN per material for the same
date, but there can be multiple 311 TXN for that material.

In my work, a 101 TXN shows the date and time a material was received at my
warehouse, and the 311 TXN is when that material was put away and ready for
use. Can someone assist?

Here is a sample set of data

TXN Material RDate RTime
101 Y10238 10/08/08 07:43:29
311 Y10238 10/08/08 09:20:37
311 Y10238 10/08/08 9:45:38
101 Y10238 10/10/08 12:33:17
311 Y10238 10/10/08 12:51:00
101 Y10238 11/05/08 10:12:16
311 Y10238 11/05/08 10:48:34



SELECT s.txn, s.material, min(s.rdate) AS Thedate
FROM recv AS s INNER JOIN (select temp.txn, temp.material, temp.rdate
from recv as Temp
where txn = "101") AS Q ON (s.rdate > q.rdate) AND (s.material = q.material)
AND (s.txn = q.txn)
GROUP BY s.txn, s.material;

 

[bcap]

You suggest that the date and time are in separate fields in the table, but
the attempted query you posted refers only to the date, suggesting the
opposite. I have assumed the latter, because it's much easier. If you
really do have date and time in separate fields then you are in for a world
of pain.

Warning: Untested!

SELECT R1.Material, Min(R2.RDate) FROM Recv R1 LEFT JOIN Recv R2 ON
(R1.Material = R2.Material AND R2.TXN = 311 AND R2.RDate > R1.RDate) WHERE
R1.TXN = 101
GROUP BY R1.Material

 

[Ken Snell \(MVP\)]

Try this:

SELECT Recv.TXN, Recv.Material, Recv.Rdate, Recv.Rtime
FROM Recv
WHERE Recv.TXN = "101" OR
(Recv.TXN = "311" AND
(Recv.Rdate + Recv.Rtime) =
(SELECT Min(R.Rdate + R.Rtime) AS Rdt
FROM Recv AS R
WHERE R.TXN = Recv.TXN AND
R.Material = Recv.Material)
ORDER BY Recv.Material, Recv.TXN;

 

[John Spencer]

It appears that you need to combine the date and time fields into one.
Hopefully they are both datetime fields.

Also, you can't join on txn between the two instances of data. If you do then
you will not get any matches "311" is never going to be equal to "101"

SELECT s.txn
, s.material
, min(s.rdate + s.Rtime) AS Thedate
FROM recv AS s INNER JOIN
(SELECT temp.txn
, temp.material
, temp.rdate + temp.RTime as ActionDate
FROM recv as Temp
where txn = "101") AS Q
ON (s.rdate + s.RTime > q.ActionDate)
AND (s.material = q.material)
WHERE S.txn = "311"
GROUP BY s.txn, s.material;

That may still have problems if you have two TXN 101 for the same material
before you have one (or more) TXN 311 for that material.

If the two fields RDate and RTime are not datetime fields, but are text fields
that contain a date string and a time string, then there are more
modifications to the query that must be made.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Ken Snell \(MVP\)]

Left out a ) character:

SELECT Recv.TXN, Recv.Material, Recv.Rdate, Recv.Rtime
FROM Recv
WHERE Recv.TXN = "101" OR
(Recv.TXN = "311" AND
(Recv.Rdate + Recv.Rtime) =
(SELECT Min(R.Rdate + R.Rtime) AS Rdt
FROM Recv AS R
WHERE R.TXN = Recv.TXN AND
R.Material = Recv.Material))
ORDER BY Recv.Material, Recv.TXN;