calendar and financial year queries in Access 2002

lynjay · #1 (**permalink**) 02-15-05, 03:15

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.

r937 · #2 (**permalink**) 02-15-05, 09:41

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

lynjay · #3 (**permalink**) 02-15-05, 17:27

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.

r937 · #4 (**permalink**) 02-15-05, 19:36

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

certus · #5 (**permalink**) 02-15-05, 23:09

That's beautifully formatted code, rudy.

r937 · #6 (**permalink**) 02-15-05, 23:20

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

lynjay · #7 (**permalink**) 02-16-05, 05:58

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

lynjay · #8 (**permalink**) 02-16-05, 06:08

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

r937 · #9 (**permalink**) 02-16-05, 07:23

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]

lynjay · #10 (**permalink**) 02-16-05, 16:20

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.

r937 · #11 (**permalink**) 02-16-05, 20:27

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]

lynjay · #12 (**permalink**) 02-17-05, 04:26

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech