Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Help with query

Posted on 2014-04-03
Medium Priority
231 Views
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
Question by:rfedorov
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 6
• 3
• 2
• +2

LVL 32

Expert Comment

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

Author Comment

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

LVL 32

Expert Comment

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

LVL 32

Expert Comment

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

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

Author Comment

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

Author Comment

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 120

Expert Comment

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 41

Assisted Solution

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

Author Comment

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

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 120

Accepted Solution

Rey Obrero (Capricorn1) earned 1800 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

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with â€¦
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: â€¦
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a qâ€¦
###### Suggested Courses
Course of the Month6 days, left to enroll