Comparing pairs of records in a query

[lydonw]

I need to identify pairs of records by their ID and date fields, then compare their number values to get a text result.

I have a query that has the following fields
AuthInstanceID, ConsumerID, AuthNumberID, AuthStart, AuthEnd, PSRUnits, BSTUnits.

Sample data is below (PSRChange and BSTChange are fields I want to self populate based on the comparison I'm trying to do)

AuthInstanceID....ConsumerID....AuthNumberID....AuthStart....AuthEnd....PSRUnits....BSTUnits - PSRChange -BSTChange
1374006036.........356679..........20255102...........4/22/13.......7/21/13....0.............416~~~~-Loss~~~~~-None
-432536491.........356679...........20255102..........1/21/13.......4/21/13....104..........416~~~~-Loss~~~~~-Loss
-124970517.........356679...........20147863..........10/23/12.....1/20/13....208..........520~~~~-None~~~~~-Null
504564357...........469432..........20254788..........4/22/13.......7/21/13....0.............520~~~~-None~~~~~-None
282523535...........469432..........20254788..........1/21/13.......4/21/13....0.............520~~~~-None~~~~~-Gain
356661326...........469432..........20254788..........11/20/12......1/20/13....0.............416~~~~-None~~~~~-Null

What I need to do is compare these records in the following fashion:
Identify pairs of records where ConsumerID is the same AND AuthStart dates are successive. Then compare PSRUnits of more recent record (by AuthStart) with the previous record (by AuthStart) and determine the change of PSRUnits between them. If change is positive, "Gain" in field PSRChange, "Loss" for a loss, "None" for no change, and "Null" if there is an error due to no previous record to compare with. Do the same with BSTUnits/BSTChange

I'm REALLY hoping someone can help me with this. I've tried adding the following fields, but this did not work

PrevEnd: (Select Max(AuthEnd) from tblAuthorizations Where AuthEnd < Auths.[AuthStart])

PrevBST: DLookUp("[BSTUnits]","tblAuthorizations","AuthEnd=" & [PrevEnd] And "ConsumerID=" & [ConsumerID])

 

[lydonw]

Is there nobody that can help with this? I can't imagine it is all that difficult but I still haven't got anything to work properly.

 

[plog]

You should create a sub-query with all the fields you need, and a claculated field that determines the prior day:

PriorDay: DateAdd("d", -1, [AuthStart])

You then take that query and link it to the underlying data of it. Link ConsumerID to ConsumerID and PriorDay to AuthStart. That lines up your data with its previous day's data so you can do your calculations.

 

[lydonw]

This seems to require the AuthStart days of the paired records to actually be one after another. IE: AuthStart of 1st record 8/13/13, thus AuthStart of 2nd is 8/12/13.

This isn't the case, however. These AuthStart dates are generally 2-3 months apart, where the number of days varies. Further, it is possible that large gaps (even as much as a year) occur.

Will calculating a field in the way you suggested workd? (ALSO, HUGE THANKS just for replying)

 

[lydonw]

While I've not done much with VBA you've at least given me something to search for that can hopefully lead to a solution.

Appreciated.

Any specific advise or direction you might be able to provide pat? You've been a huge help everywhere in this forum and I have to assume you can, in the very least, point me in the best direction

 

[plog]

No VBA necessary for this. You can still use my method (sub-query). Instead of the DateAdd() function you would use the DMAX function to return the highest date that's less than the current record's AuthStart

PriorDay: DMax("[AuthStart]", "YourTableNameHere", "[ConsumerID]=" & [ConsumerID] & " AND [AuthStart]<#" & [AuthStart] & "#")

*I always screw up date comparisons, that above may not exactly work, but its pretty close.

 

[lydonw]

I think I'll give plog's suggestion a go first, if only because I'm not very familiar with VBA.

 

[lydonw]

"The specified field '[ConsumerID]' could refer to more than one table listed in the FROM clause of your SQL statement."

I would assume this is because my query's data is sourced from three tables, tblAuthorizations, tblAuthorizationNumbers, and tblConsumers. ConsumerID is a primary key in tblConsumers, and a foreign key in the others

 

[lydonw]

If it is going to result in slowness I guess I'll try finding a VBA solution, although like I said I don't know much there. While the dataset isn't huge yet, it will get there, and it is used to support a form that is itself a bit clunky (there is nothing to be done about this).

So I'm back to asking for your advise on where to look, because I've done almost nothing with VBA

 

[lydonw]

While I do intend to use this information in a report, I primarily wanted to display the results of this comparison in a form that shows Consumer records. My company works with consumers receiving mental health services, and we need to request authorization to continue services for them every 2-3 months. The responses to these requests sometimes allow us to work more hours with an individual, but sometimes we must reduce our work with them.

Adding the ability to see the most recent change while viewing a consumer's records in the form would ensure we don't A) work hours we can't bill or B) fail to work hours we can bill.

 

[lydonw]

I'm not sure why I hadn't considered doing any of this on the form itself. I had originally thought it best to do get these results in the query and use that data in the form and reports, it seems easier to just do it on the form and report separately.

I'll be out of town the remainder of the week, but I'll be back to it next week. If for no other reason I appreciate the direction.

 

[lydonw]

So this is what I have.

I have a field that finds the previous authorization's end date

PrevEnd: (Select Max(AuthEnd) from tblAuthorizations Where AuthEnd < Auths.[AuthStart])

And a field that uses that end date to find the previous auth's BSTunits

PrevBST: DLookUp("[BSTUnits]","tblAuthorizations","AuthEnd=" & [PrevEnd] And "ConsumerID=" & [ConsumerID])

PrevEnd works fine, but PrevBST is not. It returns a result, but that result is not correct, and appears to have come from elsewhere in the table. I feel like this is primarily me not formatting things correctly. AuthEnd is a Date/Time field, ConsumerID is a number field.

 

[lydonw]

That does make sense, and I appreciate the help quite a lot Pat.