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.

Signature
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
[quoted text clipped - 23 lines]
>
> thanks
subs - 29 Feb 2008 02:38 GMT
> Create a query that gives the yearly totals.
> Then join 2 copies of this query to match this year's and last year's
[quoted text clipped - 77 lines]
>
> - 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 - 29 Feb 2008 02:55 GMT
On Feb 28, 8:49 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
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.

Signature
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.
subs - 29 Feb 2008 03:49 GMT
> On Feb 28, 8:49 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
> Thanks a lot- there are two queries- Is there any way to get the same
[quoted text clipped - 12 lines]
> 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
Allen Browne - 29 Feb 2008 03:58 GMT
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.

Signature
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.
On Feb 28, 9:55 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
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 - 29 Feb 2008 04:11 GMT
On Feb 28, 10:58 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
> Why are they repeated 2ce?
> Is this for the 2 years?
[quoted text clipped - 13 lines]
> 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 - 29 Feb 2008 04:20 GMT
What happened to the field named TheYear?

Signature
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.
On Feb 28, 10:58 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
> Why are they repeated 2ce?
> Is this for the 2 years?
[quoted text clipped - 10 lines]
> 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 - 29 Feb 2008 04:32 GMT
> On Feb 28, 10:58 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
> wrote:
[quoted text clipped - 31 lines]
>
> - 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 - 29 Feb 2008 04:39 GMT
> > On Feb 28, 10:58 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
> > wrote:
[quoted text clipped - 37 lines]
>
> - 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 - 29 Feb 2008 04:55 GMT
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 - 29 Feb 2008 05:03 GMT
On Feb 28, 11:55 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
> 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?