Combining 3 Queries to Form One Query With Columns from each query by month

[springa]

Hi all,

I am battling to get this right, if someone could help me, I'd hugely appreciate it. Basically, I have 3 queries running from seperate tables. The first query gives me order value per month; the second query gives me amount paid per month and the 3rd query shows me budget per month (as per attached picture).
I would like to combine these into one query with columns for Warehouse, Month, Expense Category, Expense Details, LPO Value, DocValue and Budget.

I'm sure this is fairly simple to do, but have just hit a complete blank today. A simple query doesn't seem to work as the date by month fields do not seem to work correctly.

I'd appreciate any help with this. Thanks

 

[DCrake]

Having looked at your queries it seems that the common fields (fields that appear in all queries) are the warehouse and the month year fields.

Create a new query and link each one together using these common fields, that way you should be able to pull down all the info from the sub queries to give you one big query.

David

 

[springa]

Hi DCrake,

Thanks for your quick response, I have already tried this, and with no links or when linking warehouse, I get the same results as per attached picture. If I link the date by month fields from each query, I am given no results at all. Yet they are displayed by month in each query individually.

 

[DCrake]

Your problem is that data is not appearing in each of the 3 queries that match on both period and warehouse. You need to decide which sub query is the master, ie the one with the most data. Then when you do your joins make sure that you set the properties to include all records in your main sub query and the other query.

To do this create the join then click on the actual join line to enbold it. Then right click and select properties from the sub menu.

Play around with that until you get it right.

To prove the point you warehouse has invoices for 4 months but only payments for one month.

Your LPO raised month query has April and March only
Your Payments has only February
Your budget has only March

To compare like for like you wiould need to have February, March & April figures for each warehouse. This may not happen because there may be no payments for a particular month so your join must state that show all records from the LPO raised query and all records in the Payments query even if there are no payments for that particular month - It would return zero payments that period.


Get my Gist?

David