Ask Experts Questions for FREE Help !
Ask
    brejackson's Avatar
    brejackson Posts: 15, Reputation: 1
    New Member
     
    #1

    Sep 26, 2011, 08:10 PM
    Union Query
    How do create a union query that will give a sum of all like lines items by date and then calculate a trend?
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #2

    Sep 27, 2011, 03:35 AM
    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's Avatar
    brejackson Posts: 15, Reputation: 1
    New Member
     
    #3

    Sep 27, 2011, 06:53 AM
    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.
    Attached Files
  1. File Type: zip Help Desk.zip (11.0 KB, 82 views)
  2. ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #4

    Sep 27, 2011, 08:47 AM
    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's Avatar
    brejackson Posts: 15, Reputation: 1
    New Member
     
    #5

    Sep 28, 2011, 08:43 AM
    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's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #6

    Sep 28, 2011, 09:24 AM
    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's Avatar
    brejackson Posts: 15, Reputation: 1
    New Member
     
    #7

    Sep 29, 2011, 05:38 AM
    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's Avatar
    brejackson Posts: 15, Reputation: 1
    New Member
     
    #8

    Oct 6, 2011, 08:39 PM
    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's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #9

    Oct 7, 2011, 03:37 AM
    Quote Originally Posted by brejackson View Post
    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's Avatar
    brejackson Posts: 15, Reputation: 1
    New Member
     
    #10

    Oct 7, 2011, 06:15 AM
    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?

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Can union member start union shop [ 4 Answers ]

Can a union member open a union shop?

Union issues [ 1 Answers ]

How are union employees affected when a plant shuts down operation. What are some of the legal and ethical issues

Union or non union [ 9 Answers ]

What are the advantages of not being in a union?

DNS Query Query. A Query about DNS Queries... [ 12 Answers ]

I am a software developer, I don’t know much about networks. I have a working network but with one weird (well to me it’s weird) problem. If you can offer any insight I’ll be very grateful! <!--- Image Attachment Below (I couldn't find a way to paste it here in the editor :-( ) ---> ...

Union and non-Union works [ 2 Answers ]

What are the advantages and disadvantages of Union and non-union works in a Tool and Die industry? :confused:


View more questions Search