Retrieve data from previous record

Hi,

Hi! I am trying to retrieve Date and Time from another record in the
same Table tbl_Group1 having a similar Serial # (Field name : SN).
The Table have 2 duplicates record and I am trying to retrieve 2
fields from the earlier record.

My coding is working retrieving the Date but how can I changed it to
retrieve both the Date and Time values from the earlier record? Right
now, it is getting the Date (as PriorValue) only.

This is my coding to retrieve the Date only .... as PriorValue

SELECT [qry_Search].[ProductID] AS ProductID, qry_Search.Date,
[qry_Search].[Time] AS [Time], qry_Search.ID, (Select TOP 1 Dupe.Date
FROM qry_Search AS Dupe
WHERE Dupe.SN = qry_Search.SN
AND Dupe.Date < qry_Search.Date
ORDER BY Dupe.Date DESC, Dupe.Time) AS PriorValue
FROM qry_Search;

Thanks a lot for any suggestion or help.

Sincerely
stvlai

Re: Retrieve data from previous record

Grettings,

If you have a key field in your table like - say pkID you can try
something like this:

Select fld1, fld2,
(select date_1 & time_1 from tbl1 where pkID = (select top 1 pkID from
tbl1 where date_1 = (select date_1 From tbl1 group by date_1 having
count(*) > 1) Order by pkID)) as fld3 from yourQry...

What you are missing in your query is the key field in the subquery.
And don't forget that you have to concatenate your date and time fields
because a subquery in this context can only return one field.


Rich

*** Sent via Developersdex http://www.developersdex.com ***