Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2005
    Posts
    14

    calendar and financial year queries in Access 2002

    Hi

    Just wondering if anyone has come across a fix for this. I have a sales table setup in financial year July-June 03 and another for July-June 04.

    My boss wants me to run a calendar year report - Jan - Dec, which requires taking data from both tables.

    The query works but only brings up the data that matches in both tables. Some customers have sales in one year but not the other, and as they don't match, the query will not show them.

    Is there a way to get the query to show not only the matching records but also listing the records that don't match?

    Any help would be appreciated.

    Thanx, Lynne.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do you have to compare rows from one fiscal year to another?

    if not, i think you're looking for a UNION query, not a JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2005
    Posts
    14

    Union queries

    Thank you for your reply. I haven't used union queries before so I am a little unsure as to how they work.

    Would the union query give me all records that don't match as well as the ones that do?

    ie. One table has 100 records, the other has 150 records. Out of these, perhaps 70 records would find a match in each other, and my query would return 70 records. I need these 70 matching records, but then also the rest of the records in both tables that don't match are required as well. There are three fields that must match (company, customer & product).

    I hope this is clear

    Apreciate your help, Lynne.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, i guess it depends on why you need it to "match"

    all you said was "a calendar year report - Jan - Dec" which can be done with a union

    can you translate this into your table and column names? --
    Code:
    select company
         , customer 
         , product
         , fiscalyear
         , fiscalmonth
         , amount
      from financialsJul02Jun03
     where fiscalyear = 2003
    union all   
    select company
         , customer 
         , product
         , fiscalyear
         , fiscalmonth
         , amount
      from financialsJul03Jun04
     where fiscalyear = 2003
    order 
        by company
         , customer 
         , product
         , fiscalyear
         , fiscalmonth
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    That's beautifully formatted code, rudy.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thank you :blush:

    aligning the keywords on the left and their clauses on the right of a "gutter" has long been known as a programming style condusive to easier recognition and comprehension
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2005
    Posts
    14

    union query attemped

    Thank you again for your help. I have tried my hand at a union query using your code as a basis.

    select [CompanyID]
    , [ProductID]
    , [Jan 04]
    , [Feb 04]
    , [Mar 04]
    , [Apr 04]
    , [May 04]
    , [Jun 04]
    from [Sales FY04]
    union all
    select [CompanyID]
    , [ProductID]
    , [Jul 04]
    , [Aug 04]
    , [Sep 04]
    , [Oct 04]
    , [Nov 04]
    , [Dec 04]
    from [Sales FY05]
    order
    by [CompanyID]
    , [ProductID]
    , [Jan 04]
    , [Feb 04]
    , [Mar 04]
    , [Apr 04]
    , [May 04]
    , [Jun 04]
    , [Jul 04]
    , [Aug 04]
    , [Sep 04]
    , [Oct 04]
    , [Nov 04]
    , [Dec 04]

    The query only shows information from the first table as far as I can tell. Columns only go up to Jun 04 and don't continue on. I would assume that if the query works correctly it should have columns Jan04 thru to Dec 04, with records listing either in Jan04-Jun 04 or Jul04 - Dec04 for each record. Does this make sense?

    ie. CompanyID ProductID Jan04 Feb04 etc July04 Aug04 Sep04 etc.
    1 4 200 25
    2 4 300 20 50

    Company ID 1 coming from Sales FY04 table and Company ID 2 coming from Sales FY05 table.

    Please bear with me and let me know where I am going wrong!

    Thank you, Lynne

  8. #8
    Join Date
    Feb 2005
    Posts
    14

    union query attemped and more explanations

    um, sorry that example of the way I would like the query to appear didn't come out quite the way it looked here.

    I have attached a word doc - with basic idea of what I mean.

    Thanks!

    Lynne
    Attached Files Attached Files

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you should have mentioned that your table layout was so horribly denormalized

    the query i cave you was based on a better design

    try this
    Code:
    select [CompanyID] 
         , [ProductID]
         , sum([Jan 04]) as Jan04
         , sum([Feb 04]) as Feb04
         , sum([Mar 04]) as Mar04
         , sum([Apr 04]) as Apr04
         , sum([May 04]) as May04
         , sum([Jun 04]) as Jun04
         , sum([Jan 04]) as Jul04
         , sum([Feb 04]) as Aug04
         , sum([Mar 04]) as Sep04
         , sum([Apr 04]) as Oct04
         , sum([May 04]) as Nov04
         , sum([Jun 04]) as Dec04     
      from (
           select [CompanyID] 
                , [ProductID]
                , [Jan 04]
                , [Feb 04]
                , [Mar 04]
                , [Apr 04]
                , [May 04]
                , [Jun 04]
                , 0 as [Jul 04]
                , 0 as [Aug 04]
                , 0 as [Sep 04]
                , 0 as [Oct 04]
                , 0 as [Nov 04]
                , 0 as [Dec 04]
             from [Sales FY04]
           union all 
           select [CompanyID] 
                , [ProductID]
                , 0 as [Jan 04]
                , 0 as [Feb 04]
                , 0 as [Mar 04]
                , 0 as [Apr 04]
                , 0 as [May 04]
                , 0 as [Jun 04]
                , [Jul 04]
                , [Aug 04]
                , [Sep 04]
                , [Oct 04]
                , [Nov 04]
                , [Dec 04]
             from [Sales FY05]
           ) as dt
    group
        by [CompanyID] 
         , [ProductID]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2005
    Posts
    14
    I'm a bit cut that you think my tables are denormalized but any suggested improvements are humbly accepted.

    I tried running your new code (thanks for that) and the information is looking closer to what it should be. However, it still isn't adding up correctly.

    For example, one company has sold product in 04 year and nothing in 05. However, the company does appear in the query but has zero balances for each month.

    Any further suggestions?

    Thanx

    Lynne.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by lynjay
    I'm a bit cut that you think my tables are denormalized but any suggested improvements are humbly accepted.
    it's probably too late for that

    Quote Originally Posted by lynjay
    For example, one company has sold product in 04 year and nothing in 05. However, the company does appear in the query but has zero balances for each month.
    you should be able to do things like add WHERE conditions yourself now, if you understand what a UNION query is, and how to select from a subselect
    Code:
    select [CompanyID] 
         , [ProductID]
         , sum([Jan 04]) as Jan04
         , sum([Feb 04]) as Feb04
         , sum([Mar 04]) as Mar04
         , sum([Apr 04]) as Apr04
         , sum([May 04]) as May04
         , sum([Jun 04]) as Jun04
         , sum([Jan 04]) as Jul04
         , sum([Feb 04]) as Aug04
         , sum([Mar 04]) as Sep04
         , sum([Apr 04]) as Oct04
         , sum([May 04]) as Nov04
         , sum([Jun 04]) as Dec04     
      from (
           select [CompanyID] 
                , [ProductID]
                , [Jan 04]
                , [Feb 04]
                , [Mar 04]
                , [Apr 04]
                , [May 04]
                , [Jun 04]
                , 0 as [Jul 04]
                , 0 as [Aug 04]
                , 0 as [Sep 04]
                , 0 as [Oct 04]
                , 0 as [Nov 04]
                , 0 as [Dec 04]
             from [Sales FY04]
            where not ( 
                  [Jan 04] = 0
              and [Feb 04] = 0
              and [Mar 04] = 0
              and [Apr 04] = 0
              and [May 04] = 0
              and [Jun 04] = 0
                      ) 
           union all 
           select [CompanyID] 
                , [ProductID]
                , 0 as [Jan 04]
                , 0 as [Feb 04]
                , 0 as [Mar 04]
                , 0 as [Apr 04]
                , 0 as [May 04]
                , 0 as [Jun 04]
                , [Jul 04]
                , [Aug 04]
                , [Sep 04]
                , [Oct 04]
                , [Nov 04]
                , [Dec 04]
             from [Sales FY05]
            where not ( 
                  [Jul 04] = 0
              and [Aug 04] = 0
              and [Sep 04] = 0
              and [Oct 04] = 0
              and [Nov 04] = 0
              and [Dec 04] = 0
                      )
           ) as dt
    group
        by [CompanyID] 
         , [ProductID]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2005
    Posts
    14
    Thank you rudy. I guess I don't know enough about union queries to attempt what I am trying to do - not even with the help of your code.

    Thanks for your help.

    Lynne.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •