Solved

Help with query

Posted on 2014-04-03
13
215 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
 
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

864 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

20 Experts available now in Live!

Get 1:1 Help Now