Query for comparison of totals

[subs]

ost ocity dst dcity shipdate paid amt transport amt
A B C D 1/1/07 7888 777
E F G H 2/3/07 733 887
I J K L 4/5/08 998 756
A B M N 5/1/08 778 697
E F S T 9/1/08 877 345
A B O Q 1/2/07 20 10

OUTPUT required is

ost ocity sumoftransportamt(2007)
sumoftransportamt(2008) sumofpaidamt(2007) sumofpaidamt(08)
A B
787
697 7908 778
E F
887
345 877 733


i want to calculate the sum of paid amt and transport amt for every
origin(ost and ocity) and then compare the totals across two years 07
and 08.

can this be done using a single query- SQL ? what would be a single
SQL statement?

thanks

 

[Allen Browne]

Create a query that gives the yearly totals.
Then join 2 copies of this query to match this year's and last year's
figures.

Steps:

1. In query design view, depress the Total button on the Toolbar.
Access adds a Total row to the grid.

2. In a fresh column in the Field row, enter:
TheYear: Year([shipdate])
Accept Group By in the Total row.
(We will assume shipdate is a required field, so cannot be null.)

3. In the Total row under OSt and OCity, accept Group By.
Under the amount columns, choose Sum.
This gives you the figures for each year.
Save the query as (say) qryOriginByYear.

4. Create a new query using qryOriginByYear as an input "table."
Add a 2nd copy of qryOriginByYear.
Access will alias the the 2nd one as qryOriginByYear_1.
In the Properties box, change the alias to (say) PriorYear.

5. In the upper pane of query design, join the 2 copies of the query on the
fields OSt, OCity, and TheYear. Now change these to outer joins, by
double-clicking each of the 3 join lines in turn, and choosing:
All records from qryOriginByYear, and any matches from PriorYear.

6. Drag the fields you want from both tables into the grid.
(After step 7, you won't be able to edit graphically any more.)

7. Switch to SQL View. (View menu.)
Edit the FROM clause.
It will look like this:
SELECT ...
FROM qryOriginByYear LEFT JOIN qryOriginByYear AS PriorYear
ON ...
And ...
And qryOriginByYear.TheYear = PriorYear.TheYear
WHERE ...
Change the 3rd part of the join to:
And qryOriginByYear.TheYear = PriorYear.TheYear - 1

At this point, you will not be able to switch back to Design view, since
Access can't display non-equi joins graphically.

 

[subs]

Create a query that gives the yearly totals.
Then join 2 copies of this query to match this year's and last year's
figures.

Steps:

1. In query design view, depress the Total button on the Toolbar.
Access adds a Total row to the grid.

2. In a fresh column in the Field row, enter:
    TheYear: Year([shipdate])
Accept Group By in the Total row.
(We will assume shipdate is a required field, so cannot be null.)

3. In the Total row under OSt and OCity, accept Group By.
Under the amount columns, choose Sum.
This gives you the figures for each year.
Save the query as (say) qryOriginByYear.

4. Create a new query using qryOriginByYear as an input "table."
Add a 2nd copy of qryOriginByYear.
Access will alias the the 2nd one as qryOriginByYear_1.
In the Properties box, change the alias to (say) PriorYear.

5. In the upper pane of query design, join the 2 copies of the query on the
fields OSt, OCity, and TheYear. Now change these to outer joins, by
double-clicking each of the 3 join lines in turn, and choosing:
    All records from qryOriginByYear, and any matches from PriorYear.

6. Drag the fields you want from both tables into the grid.
(After step 7, you won't be able to edit graphically any more.)

7. Switch to SQL View. (View menu.)
Edit the FROM clause.
It will look like this:
    SELECT ...
    FROM qryOriginByYear LEFT JOIN qryOriginByYear AS PriorYear
        ON ...
            And ...
            And qryOriginByYear.TheYear = PriorYear.TheYear
    WHERE ...
Change the 3rd part of the join to:
    And qryOriginByYear.TheYear = PriorYear.TheYear - 1

At this point, you will not be able to switch back to Design view, since
Access can't display non-equi joins graphically.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

ost ocity dst dcity shipdate  paid amt    transport amt
A    B       C  D      1/1/07     7888          777
E    F       G  H       2/3/07    733               887
I     J         K  L      4/5/08       998            756
A    B       M  N      5/1/08     778                697
E   F        S  T        9/1/08    877                  345
A    B      O   Q      1/2/07      20                   10

OUTPUT required is

ost    ocity     sumoftransportamt(2007)
sumoftransportamt(2008)       sumofpaidamt(2007)   sumofpaidamt(08)
A        B
787
697                            7908                            778
E        F
887
345                             877                              733

i want to calculate the sum of paid amt and transport amt for every
origin(ost and ocity) and then compare the totals across two years 07
and 08.

can this be done using a single query- SQL ? what would be a single
SQL statement?

thanks- Hide quoted text -

- Show quoted text -

Thanks a lot- there are two queries- Is there any way to get the same
output in a single query

 

[Allen Browne]

Thanks a lot- there are two queries- Is there any way to get the same
output in a single query

Yes: it is possible to do this with subqueries, but it would probably be
considerably less efficient.

For an example, see how the Year-To-Date calculation is done here:
http://allenbrowne.com/subquery-01.html#YTD
Your scenario is similar, but you ar summing the previous year instead of
the previous months of this year.

 

[subs]

Thanks a lot- there are two queries-  Is there any way to get the same
output in a single query

Yes: it is possible to do this with subqueries, but it would probably be
considerably less efficient.

For an example, see how the Year-To-Date calculation is done here:
   http://allenbrowne.com/subquery-01.html#YTD
Your scenario is similar, but you ar summing the previous year instead of
the previous months of this year.

i looked at your query- it is not giving the desired results- the
origin st and city are repeated twice. i.e there are two rows are
being shown in the output. Can you please help

 

[Allen Browne]

Why are they repeated 2ce?
Is this for the 2 years?
Do you need to add criteria to the 2nd stage query to limit it to this
year's figures only?

Ultimately, you will have to do your own debugging.
We can only suggest approaches for your to investigate.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

i looked at your query- it is not giving the desired results- the
origin st and city are repeated twice. i.e there are two rows are
being shown in the output. Can you please help

 

[subs]

Why are they repeated 2ce?
Is this for the 2 years?
Do you need to add criteria to the 2nd stage query to limit it to this
year's figures only?

Ultimately, you will have to do your own debugging.
We can only suggest approaches for your to investigate.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


i looked at your query- it is not giving the desired results- the
origin st and city are repeated twice. i.e there are two rows are
being shown in the output. Can you please help

for the above example that i have posted originially- i am getting the
following output after all your steps
ost ocity sum2008 sum2007
A B 7908 778
A B 778 7908

As you could see there are two rows and the sums are getting
interchanged- the output should have the second row only- can you tell
me what i am doing wrong/ pl help

 

[Allen Browne]

What happened to the field named TheYear?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Why are they repeated 2ce?
Is this for the 2 years?
Do you need to add criteria to the 2nd stage query to limit it to this
year's figures only?

Ultimately, you will have to do your own debugging.
We can only suggest approaches for your to investigate.


i looked at your query- it is not giving the desired results- the
origin st and city are repeated twice. i.e there are two rows are
being shown in the output. Can you please help

for the above example that i have posted originially- i am getting the
following output after all your steps
ost ocity sum2008 sum2007
A B 7908 778
A B 778 7908

As you could see there are two rows and the sums are getting
interchanged- the output should have the second row only- can you tell
me what i am doing wrong/ pl help

 

[subs]

for the above example that i have posted originially- i am getting the
following output after all your steps
ost        ocity           sum2008  sum2007
A            B              7908        778
A           B                778        7908

As you could see there are two rows and the sums are getting
interchanged- the output should have the second row only- can you tell
me what i am doing wrong/ pl help- Hide quoted text -

- Show quoted text -

I DID Not Drop in the field Theyear onto the grid since i did not want
it in the output. The columns in the output are perfectly ok but the
rows are repeated. Anything wrong with join?

 

[subs]

I DID Not Drop in the field Theyear onto the grid since i did not want
it in the output. The columns in the output are perfectly ok but the
rows are repeated. Anything wrong with join?- Hide quoted text -

- Show quoted text -

Can you please post the inner join statement again? i am getting the
error in output. or even a single SQL statement replacing all the
steps would help. This would be greatly appreciated.

 

[Allen Browne]

You can look back to the previous thread and see the statement.

Go back, and understand how it works.

It's time for me to move on to other threads.

 

[subs]

You can look back to the previous thread and see the statement.

Go back, and understand how it works.

It's time for me to move on to other threads.

IS there a Where statement in JOIN Statement? Can you pl post the join
statement again?