Combining 2 queries

[peterlee516]

Thank you in advance for your assistance.

I've read through the other threads but can't seem to find anything that is simple (not an experienced SQLer so can't really tweak too much) and that would work for my problem.

I have a Project table linked to an Invoice Table (1 to many respectively)

The Invoice table linked to a Transaction table (1 to many respectively). 1 invoice can be paid out to many different cost centers and amounts.

I also have a Reclass table that is linked to the Project table.

Basically Invoices can be processed to multiple cost centers originally. But can also be reclassed at a later date to other cost centers.

The way the database is setup is so that if there are 10 invoices each having multiple records in the Transaction table (charging 1 invoice to multiple cost centers) AND we need to reclass them. We don't want to reclass each an every invoice and transaction record. It setup so that we can take the total invoiced amount for the project (sumed up all the invoices for that project) and reclass one total amount.

The problem is now I have 2 separate "financial" tables. One for the original invoice payment transaction (Transaction table which has the original cost centers and amounts) and a Reclass table with the "new" cost centers and amounts.

I need to combine them so that the financial reporting will reflect the reclasses (moving dollars from one cost center to another).

I tried creating a separate table (All Financials Table) that I can append the "original" Transaction table and Reclass table to use for reporting. I have this working but I can forsee possible problems going forward (i.e. user may append multiple times by a button click).


Is there a simple way to do a combined query on both the Transaction and Reclass table. Both queries have the following: Project ID, Cost Center and Amount. The problem I'm having is that if one table has 10 records of say cost center 123456 and the other has 3 records of 123456. The query ends up giving me 30 records.


I know my explanation is long but I think it will help paint the bigger picture.

Thanks again for reading this!

 

[neileg]

You should only have one table for these financial transactions. Either use a field to flag transactions that have been superceded, or select only the most recent transactions using Max on the date or ID number.

 

[peterlee516]

I played with that idea but here is the problem:

My Transaction table is defined as follows:
TransactionID
InvoiceID
CostCenter
Amount

How can I put the reclasses in the same table without having to individually reclass all say 10 invoices one by one. And how would I do it? Creating another "reclass" invoice for each invoice I want to reclass? I would need to enter in an invoice amount. What would I enter? $0? Because that invoice amount is being calculated against (i.e. total invoice amount for project).

I could just go into the Transaction table and create extra records of the reclass and tie it to the same invoice record.

To be honest, my deadline is next friday and changing this would take a lot time. Is there a way to just combine the two queries?

Thanks for your input.

 

[peterlee516]

Actually, the change didn't take that long. Realized I already had the functionalilty in place to reclass individual invoices in place. Thanks for your help!

I'm relieved to know that the database will be robust.

 

[neileg]

Pleased you got it sorted!