Improve company productivity with a Business Account.Sign Up

x
?
Solved

Help with query

Posted on 2014-04-03
13
Medium Priority
?
244 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 32

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 32

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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
LVL 32

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
 

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

by:Sharath
Sharath earned 200 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 120

Accepted Solution

by:
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

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

607 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