"Paired" records

[Duncan Bachen]

I have an interesting problem that I need some fresh minds to look at
for me. I've been trying to for weeks to come up with a solution, and
I can't get my head around it.

In my application, there is an audit process where a transaction may
be modified. In the audit table, we save the original transaction, and
then the modified transaction. Each is stamped with a modified date,
so that we can later pull up all modified transactions for a period,
and compare them with their original transaction.

Since the code writes both records one after another, their Record IDs
are sequential. Then when reporting on them, you can sort by RecordID
and have the "paired" records show up next to each other.

Now here's the problem. We're trying to implement a process to allow a
different posting date than transaction date. In basic terms, it will
allow transactions that happen in this billing period to instead be
accountable in a prior period.

When storing the transaction in the audit table, the Post Date *may*
only be stored with one of the two transactions.

i.e. If the original transaction didn't have a post date, but you add
one, the "changed" record will have a value in the post date field.

i.e. If the original transaction had a post date, but then you deleted
it, the "changed" record will have no value in the post date field.

I need to be able to query BOTH records of the "pair" if *either* has
a value defined in the post date, so that I am able to compare them.

My latest idea was to add a field called "MasterTransID". This would
store the Record ID of the "original" transaction in both the original
and paired transaction. Then you would know which two would go
together.

Creating a query and adding the table twice, joining the Record ID <->
MasterTransID will pull both records as needed.

When I add criteria to limit it by the post date however, only one
record is returned.

So, again, the question is, how can I return both records if either
one has the value I am looking for.

I have the luxary of redesigning the fields as needed, so don't be
limited to my initial design ideas - especially if I was barking up
the wrong tree

Here's an example (if needed for the explanation) of a paired record
where the second record has a changed bus number, and then a post date
for the previous month:
"Id","Company","Permit","Bus_Number","ArriveDate","ArriveTime","DepartDate","DepartTime",
"Destination","Type","OrigCharge","AddCharge","OrigPaid","AddPaid","Receipt","Emp_In","Emp_Out",
"BillingStatus","Bus_Id","OrigHrs","OrigDays","AddHrs","AddDays","TB_Num","Shift","ShiftOut","Credit",
"PostDate","MasterTransID","Mod_Type","Mod_Date","Mod_User","Mod_Reason","PrintedOn","TrackingID"
27313,"Express Tours, Inc.","85000016","9803",4/14/2004,
3:27:55,,,,"P",19.40,0.00,0.00,0.00,727345,108,,"B",191,0,1,0,0,1,16396,0,0,,27313,"ORIG",4/14/2004
9:10:04,"John",,,
27314,"Express Tours, Inc.","85000016","9805",4/14/2004,
3:27:55,4/15/2004 0:00:00,12/30/1899
3:27:55,"","P",19.40,0.00,0.00,0.00,727345,108,52,"B",191,0,1,0,0,1,16396,0,0,3/31/2004,27313,"CHANGE",4/14/2004
9:10:04,"John","left in",,

Thanks in advance,
-Duncan
-D

 

[Chris]

Try this...
Create a query

Select * from YourTable where MasterTransID in (Select
distinct mastertransId from YourTable Where ... <Postdate
in selected range>);

 

[Duncan Bachen]

Try this...
Create a query

Select * from YourTable where MasterTransID in (Select
distinct mastertransId from YourTable Where ... <Postdate
in selected range>);

I'll give that a try. After I posted yesterday, I started thinking
about using a nested query like this to solve the problem, so I'm glad
to see I was on the right track.

-D

 

[Duncan Bachen]

Try this...
Create a query

Select * from YourTable where MasterTransID in (Select
distinct mastertransId from YourTable Where ... <Postdate
in selected range>);

Just wanted to thank you for putting me on this road. The use of the
subquery was exactly what I wanted. Here's the final version of the
SQL that I used to solve the problem.

SELECT *
FROM Audit
WHERE (((Audit.MasterTransID) In (SELECT DISTINCT [MasterTransID] FROM
[Audit] WHERE ( DateDiff ("m", [PostDate],
Forms![frmReportSelection]![txtFromDate])))));



-D