Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / February 2008

Tip: Looking for answers? Try searching our database.

Query for comparison of totals

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
subs - 29 Feb 2008 00:44 GMT
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 - 29 Feb 2008 01:49 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
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?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.