Solved

Help with query

Posted on 2014-04-03
13
217 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
[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
  • Learn & ask questions
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 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 120

Accepted Solution

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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

726 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