Merging\combining multiple results from the same field

[shabbaranks]

Hi,

Im trying to work out the best way to combine results from a table. Not knowing the correct terminology I didn't want to post in a specific section of the forum until its clear in my head.

So I have a query currently which shows the entries into my database for example (these aren't the column names precisely as I know some are reserved words its just to demonstrate):

UserA - Hours - Date - ProjectA
UserB - Hours - Date - ProjectA
UserA - Hours - Date - ProjectB
UserA - Hours - Date - ProjectB
UserB - Hours - Date - ProjectA
UserA - Hours - Date - ProjectA

What I am trying to do is combine UserA's entries with ProjectA and UserA's entries with ProjectB (seperatly) and UserB's with ProjectA and UserB with Project B (again seperatly).

So the end result would show a total of User A's hours on Project A and separately ProjectB and the same for UserB.

If I have over complicated the issue apologies (this is why I need it clear in my head).

Thanks as always!

 

[CJ_London]

Looks like all you need is a Group By query which will look something like

SELECT Project, User, Sum(Hours) as SumofHours 
FROM myTable 
GROUP By Project, User
ORDER BY Project, User

 

[shabbaranks]

Looks like all you need is a Group By query which will look something like

SELECT Project, User, Sum(Hours) as SumofHours 
FROM myTable 
GROUP By Project, User
ORDER BY Project, User

Thank you, I've just realised I forgot quite an important bit (hangs head in shame). The end product will be to select a month of a year. Is there a way to combine all records for that month. As an example you would end up with:

UserA - TotalHours - Month - ProjectA
UserA - TotalHours - Month - ProjectB
UserB - TotalHours - Month - ProjectA

And so on, the month would be the selected month for all results.

 

[pr2-eugin]

Just add that to the Query.

SELECT Project, User, Month(theDate) As MthName, Sum(Hours) as SumofHours 
FROM myTable 
GROUP By Project, User, Month(theDate)
ORDER BY Project, User, Month(theDate)

 

[CJ_London]

Sure, no problem - you probably need year as well otherwise data from March in two different years would be combined:

SELECT Project, User, Format(myDate,"yyyymm") as Period, Sum(Hours) as SumofHours 
FROM myTable 
GROUP By Project, User,Format(myDate,"yyyymm") 
ORDER BY Project, User,Format(myDate,"yyyymm")

 

[shabbaranks]

Legends nice one... I'm trying to convert the returned month which is showing as yyyymm to the month name - is this possible?

So although Im setting the criteria of yyyyymm I wont tick that field to show it I thought I would create another expression which shows the month name. I tried Expres: monthname unless Im going about it incorrectly?

Thanks again

 

[pr2-eugin]

You can use MonthName function I guess.

 

[shabbaranks]

You can use MonthName function I guess.

Thanks I did actually try that before posting the question. Do I add the monthname to the format([Task Date],"yyyymm") or is there a different way of going about it? As if I add it to a separate expression it prompts for some text.

Thanks again

 

[CJ_London]

Not tested but try this

SELECT Project, User, year(myDate) as Yr, format(mydate,"mmm") as Mth, Sum(Hours) as SumofHours 
FROM myTable 
GROUP By Project, User,year(myDate),format(mydate,"mmm") 
ORDER BY Project, User,Format(myDate,"yyyymm"), month(mydate)

 

[shabbaranks]

Thanks its working and not only that but Ive learnt something new with Access, I knew that you could create the additional expressions when creating queries but I was never 100% sure how it works - I am now

Also I haven't got the month name showing but Im thinking I might not need it as I have split the month and year into 2 separate results.

Thanks again

 

[shabbaranks]

Not tested but try this

SELECT Project, User, year(myDate) as Yr, format(mydate,"mmm") as Mth, Sum(Hours) as SumofHours 
FROM myTable 
GROUP By Project, User,year(myDate),format(mydate,"mmm") 
ORDER BY Project, User,Format(myDate,"yyyymm"), month(mydate)

Sorry to pester - am I missing a trick? Ive tried to apply this code to a different query (after the same result but from a different table) and I cant seem to get my results to sum for each particular month - any ideas please?

SELECT ([F2]) AS Project, ([sUser]) AS Employee, Month([F4]) AS [Month], Year([F4]) AS [Year], Sum([JT Data].f8) AS [Total Hours]
FROM [JT Data] INNER JOIN UserNames_tbl ON [JT Data].F7 = UserNames_tbl.WorksNumber
GROUP BY ([F2]), ([sUser]), Month([F4]), Year([F4]), [JT Data].F4, [JT Data].F2, UserNames_tbl.sUser, Format([f4],"yyyymm")
ORDER BY ([F2]), ([sUser]), Month([F4]), Year([F4]), UserNames_tbl.sUser;

 

[CJ_London]

SELECT ([F2]) AS Project, ([sUser]) AS Employee, Month([F4]) AS [Month], Year([F4]) AS [Year], Sum([JT Data].f8) AS [Total Hours]
FROM [JT Data] INNER JOIN UserNames_tbl ON [JT Data].F7 = UserNames_tbl.WorksNumber
GROUP BY ([F2]), ([sUser]), Month([F4]), Year([F4]), [COLOR=red][JT Data].F4[/COLOR], [COLOR=orange][JT Data].F2[/COLOR], [COLOR=orange]UserNames_tbl.sUser[/COLOR], [COLOR=royalblue]Format([f4],"yyyymm")
[/COLOR]ORDER BY ([F2]), ([sUser]), [COLOR=darkgreen]Month([F4]), Year([F4][/COLOR]), UserNames_tbl.sUser

The bits in red look suspect - looks like F4 is a date so you are grouping by date (so you'll get all dates). The bits in Orange are repeated so can be removed and the bit in blue also doesn't look like it is required

Finally the bit in green I think needs to be the other way round - your listing will show 'years within months' i.e.

Jan 2012
Jan 2013
Feb 2012
Feb 2013
etc

 

[shabbaranks]

Thanks again - I didn't realise that you could just delete the grouped by if you didn't want to group, deleted it and jobs a gooden