How can yoou get the previous record in a query (field value) 2

[jeep2001]

I need to calculate the numberof business days between records in a query. For instance, I need to compare
record #2 to record #1, and record #3, to record #2, and record #4 to record # 3....and soon.
SO if record #5 calculates to 40, and record #6 36, then I want the value 4...In other words 4 business days elapsed between the two records.

I want the value of the previous record in the query.

 

[dhookom]

There are no record numbers in Access. You need to define your data a little so your "previous" can be specified in a query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jeep2001]

I am sorry I wasn't clearer in the problem description.

1. I have a function which calculates business days.
2. I use it as a column in a query.
each record in the query displays a status. I need to show the number of business days between each status.
Based on the data which follows, in the query, I want to show the # of days of each status before the change.

Account TransDate Status BusinessDays
xxxxx1 12/22/2005 xyz 65
xxxxx1 1/23/2005 hhh 22

 

[dhookom]

SELECT Account, TransDate, Status,
(SELECT Max(TransDate)
FROM TblOrQry TQ
WHERE TQ.TransDate < TblOrQry.TransDate
AND TQ.Status <> TblOrQry.Status) as PrevDate
FROM TblOrQry;

This SQL should get you a result set that you can use your Business Days function on.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jeep2001]

Hi

I did it a different way, I used a DLookup and looked up the previous ID# of the record.
The only thing is...if you use a make table query, how do you get the ID into it?

 

[lespaul]

that's not a make table query.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[jeep2001]

Sorry, there are two things going on here.

I changed the original query into a make-table query and manually added the ID field...Then I created a new select query using the DLookup ID -1 to get the previous value.
It all works perfectly, except I must somehow get the ID into the table that was created by the make-table query.

 

[lespaul]

Still not sure why you are doing a make table query.

With the sample table and data you describe, the query Duane provided will give you a result set that has the two days you need in a single record.

Account    TransDate      Status         PreviousDate
xxxxx1      12/22/2005     xyz              12/16/2005
xxxxx1       1/23/2005     hhh              1/18/2005

pass those two days into your function and you'll get the number of days you need, right?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[jeep2001]

Hi

The only thing I found with the SQL is if the previous record is the same as the current one, it is bypassing that record, and going to the next one that is <...
If I make it <= then it acts strangely.

 

[PHV]

Any chance you could post some samples input and expected result ?
if the previous record is the same as the current one
You MUST have a way to uniquely sort (rank, order, ...) such records as you claim you deal with previous record.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dhookom]

Could you provide some sample records where the suggested sql doesn't work?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jeep2001]

Transaction_Date Bus Days PREVIOUS ACCT

29-Jan-06 20 29-Dec-05 ABMAN
29-Dec-05 5 22-Dec-05 ABMAN
22-Dec-05 5 16-Dec-05 ABMAN
22-Dec-05 5 16-Dec-05 ABMAN
16-Dec-05 7 08-Dec-05 ABMAN
08-Dec-05 5 02-Dec-05 ABMAN
08-Dec-05 5 02-Dec-05 ABMAN
02-Dec-05 ABMAN
02-Dec-05 ABMAN
29-Dec-05 22 29-Nov-05 AGPAM
29-Nov-05 10 15-Nov-05 AGPAM
15-Nov-05 7 07-Nov-05 AGPAM
15-Nov-05 7 07-Nov-05 AGPAM
15-Nov-05 7 07-Nov-05 AGPAM
07-Nov-05 38 15-Sep-05 AGPAM
15-Sep-05 AGPAM
15-Sep-05 AGPAM
12-Jan-06 21 13-Dec-05 BFTGE
13-Dec-05 5 07-Dec-05 BFTGE
07-Dec-05 15 16-Nov-05 BFTGE
07-Dec-05 15 16-Nov-05 BFTGE
16-Nov-05 2 15-Nov-05 BFTGE
15-Nov-05 6 08-Nov-05 BFTGE
08-Nov-05 BFTGE
13-Jan-06 21 14-Dec-05 COMAD

On the ABMAN records, transaction date 12/2/2005, the previous date was 12/2/2005, its not handling that situation, othewise its perfect. Same situation forthe account AGPAM.

 

[dhookom]

Why did your previous posting mention a status field that seemed significant to your situation and now we don't see it? Can we see the SQL view of your current query?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jeep2001]

You are correct, I did mention a status field, and its part of the outside SELECT. I am using the account in its place in the inside select (as a control break).
When the account changes, thats the end of the group. The sql is essentially the same as you gave me. I onlyplugged in my field names. (When I get to the office I will send it).