[brejackson]

How do create a union query that will give a sum of all like lines items by date and then calculate a trend?

[ScottGem]

A union query consolidates multiple tables of like structure into a single result set. So I'm not sure a Union query is what you want. Do you have multiple tables with the same structure like a table of transactions for a period or separate tables for each line?

The syntax for a union query is:

SELECT field list FROM table1

UNION SELECT field list FROM table2

UNION SELECT field list FROM table3;

etc. The field list has to be the same number of fields in the same order and of the same datatypes. So if field 2 is text in one segment it has to be text in ALL segments.

Once you have the UNION query you can use it as the source of a GROUP BY or crosstab query to sum amounts by time.

[brejackson]

Yes, each table has the same structure. I've created the union query as you suggested and the group query. How do I get it calculate the trends I need?
There are 9 tables. See Attachment for example of the results I'm trying to achieve.

[ScottGem]

I would start creating two Crosstab queries. One to summarize Actual Volumes, the other for Invoice amount. So you would have the Elements as your rows, the dates as your columns. You would then add columns in Design view for the other calcs.

[brejackson]

How do I put the calculations in the query so that it will automatically update the query without me having to do it manually every time? I'm not a novice at this so I don't know how to write expressions or anything like that.

[ScottGem]

This could get quite complex. I'm not even sure if it can be done completely dynamically. You would probably need several queries to get the results for each period. And then join these queries to pull the historical data for comparison. This is something I would have to spend considerable time playing with to try and figure out the correct configuration.

[brejackson]

Is this something you will do? Or are there others on this forum who can do this if you don't have the time?
Thanks,

[brejackson]

Yes, I did have to create several queries to get the results. I've created a union, cross-tab query and query from the cross-tab.

How do I put the calculations in the query so that it will automatically update the query without me having to do it manually?

Is this still going to be a complex process?

[ScottGem]

Yes, I did have to create several queries to get the results. I've created a union, cross-tab query and query from the cross-tab.

How do I put the calculations in the query so that it will automatically update the query without me having to do it manually?

Is this still going to be a complex process?

Good for you.

Well it depends on the calculations. For example, If you wanted to include transactions for the previous months you could use criteria like:

BETWEEN DateSerial(Year(Date()),Month(Date())-1,1) AND DateSerial(Year(Date()),Month(Date()),0)

Using 0 as the day returns the last day of the previous month.

[brejackson]

I need the query to calculate the results AUTOMATICALLY based on the columns listed below:

1. Monthly Increase/Decrease to Prior Month - (Example)05/15/2011 - 04/15/2011 divided by 04/15/2011
2. 3mos Increase/Decrease to Current Month - (Example)05/15/2011 - 02/15/2011 divided by 02/15/11
3. 6mos Increase/Decrease to Current Month - (Example)05/15/2011 - 11/15/2010 divided by 11/15/10
4. 9mos Increase/Decrease to Current Month - (Example)05/15/2011 - 07/15/2010 divided by 07/15/10
5. Prior MTD Increase/Decrease to Current YTD-(Example)05/15/2011 - 05/15/2010 divided by 05/15/2010

How can I incorporate your solution to achieve the results needed?