Compare table and aggregate query to find differences

[vicissitude]

What is the best way to compare a table with an aggregate query? The aggregate query has two group byand one sum. The three fields on either side have the same name and data type.

Someone mentioned union queries are good for comparing but not with aggregate queries.

 

[vbaInet]

Compare them in what way? Why are you needing to compare calculated values with field values in a table? Sounds like a problem with your table not being normalized.

 

[vicissitude]

Hello again

I have an orders table on one side and an invoices table on the other

There is a relationship of one invoice to many orders, one order to many category-costs (budget) And also one invoice to many category-costs

So one invoice has a few category-costs but may have many orders with many category-costs

I could not work out how to normalise this situation so i opted for two category-cost tables, one for the invoices and one for the orders (the costs are input manually for invoices and orders)

I then used an aggregate query on the orders / category-cost table side to insert rows into the category-cost table on the invoice side.

So my problem is a way to compare these two category-cost tables so that i dont insert multiples and when a category is removed on one side it goes on the other too.

I hope that all makes sense and appologise for the long winded response, i made it as short as i could.

Many thanks.

 

[vbaInet]

Is there any reason why you don't want to use that one Categories table for both sides?

Let's see a screenshot of your Relationships diagram.

 

[vicissitude]

I did not want to use one categories table for both sides because of the procedure when users are entering data.

First orders are created which may have many category-costs and then the invoice comes in which is then linked to the appropriate order(s).

The orders and their category-costs need to be recorded but so do the invoices and their category-costs.

The orders category-costs consist of many records but the Invoice category-costs usually only have a few.

It was this descrepancy that led me to create two tables becuase i could not see any way to get round that.

Here is a link to an image of the relationships (orders table is called Requisitions table here)
http://auctionpix.co.uk/users/aug1499770.png