Solved

Help with query

Posted on 2014-04-03
13
213 Views
Last Modified: 2014-04-07
Please help
I have a simple table

Inspectors      Month Category      TotalMonthly
John                      Jan       MILEAGE      1,000      
John                      Feb      MILEAGE      2,000      
John                      March  MILEAGE      5,000      
i need some help with making the summary query showing:
Inspectors      Category      YTD        TotalMonthly
John                      MILEAGE      8,000     5,000
0
Comment
Question by:rfedorov
  • 6
  • 3
  • 2
  • +2
13 Comments
 
LVL 31

Expert Comment

by:awking00
ID: 39976052
Is TotalMonthly of 5,000 because it is the maximum total for any give month?
0
 

Author Comment

by:rfedorov
ID: 39976060
thank you for such fast respond, no, just total number of miles per month
0
 
LVL 31

Expert Comment

by:awking00
ID: 39976063
If so -
select inspector, category, sum(TotalMonthly) as YTD, max(TotalMonthly) as TotalMonthly
from yourtable
group by inspector, category;
0
 
LVL 31

Expert Comment

by:awking00
ID: 39976077
That doesn/t explain how the 5,000 gets into your query summary. Is it because it's the latest TotalMonthly available (i.e. from the latest month)?
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39976083
is total monthly and ytd not the same thing?
0
 

Author Comment

by:rfedorov
ID: 39976092
Inspectors      Category             YTD              TotalMonthly(last month mileage)
John                      MILEAGE      8,000     5,000

no, it does not work the way i want...   with your query i am getting the same number
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:rfedorov
ID: 39976125
simple count YTD: should 1000+2000+5000 gives 8000. This is year to date for three month and number for march which is latest month is 5000.  I assume we need to use switch function
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39976153
SELECT Travel.Inspectors, Max(Travel.Month) AS MaxOfMonth, Travel.Category, Sum(Travel.TotalMonthly) AS SumOfTotalMonthly, Max(Travel.TotalMonthly) AS MaxOfTotalMonthly
FROM Travel
GROUP BY Travel.Inspectors, Travel.Category;
0
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 50 total points
ID: 39976373
select inspector, category, sum(TotalMonthly) as YTD, last(TotalMonthly) as TotalMonthly
from yourtable
group by inspector, category;

Open in new window

0
 

Author Comment

by:rfedorov
ID: 39976428
No, guys, it is not working and it does not supposed to work your way, sorry to say.it gives the same numbers
0
 

Author Comment

by:rfedorov
ID: 39976438
we need to use Switch function, which will take care of the Month  
Count everything for all month and count only month where is March...
Switch does that, i can not figure out how
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 450 total points
ID: 39976501
<No, guys, it is not working and it does not supposed to work your way, sorry to say.it gives the same numbers >

the query i posted gives this result


Inspectors      MaxOfMonth       Category        SumOfTotalMonthly      MaxOfTotalMonthly
John                       Mar                MILEAGE                      8000                                    5000


now, tell us what is wrong ?


.
0
 

Author Comment

by:rfedorov
ID: 39978180
To: Rey Obrero
Thank you very much, nothing is wrong...everything is great...Working...it took me a while to bring "real" data.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now