Tricky Accounting query - and not sure how to build it

Hi All,

Sorry for the 'not so descriptive' subject - but there wasn't enough room to
write
"Need help with Financial data from multiple tables to follow where a
transaction went". :-D

I have 8 tables - all identical - for 8 different 'accounts' (not bank
accounts, but accounting 'accounts').

I have been asked if I can pull data from the 8 to try and follow a
'transaction' through these accounts.

I had thought a crosstab query might do the trick, but was having
difficulties in putting it together. So I then started by creating a union
(all) query to pull all the data into one set, then crosstab that.

Here is what I have for table structure (for all 8 tables):

[BookingID],[TransDate],[Descr],[Debit],[Credit]

I added a field in the union query to indicate the origin (called 'Origin'),
and then added the Debit & Credit amounts together to give just one column -
lets call in "TransAmt". So my union looks like this:

[Origin],[BookingID],[TransDate],[TransAmt]

Now I am doing the crosstab using the [BookingID] as the Row Heading, and the
[Origin] as the Column. And I added the [TransDate] as another Row Heading,
and did 'Sum' of [TransAmt] as the value.

I am getting results, but I'm not 100% sure if they are accurate - so I
thought I would ask in anyone thought I did this right or wrong, and any
pointers they might have in doing this sort of query.

I cringe when they suggest I do it in Excel - so if you have any thoughts or
idea's, please feel free to add them!!

Thanks in advance,

Corey

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201005/1