Sum groups from a table to ONE record returned in the query 1

[TinaS]

Hey all,

I've been searching and can't find exactly what I need - Part of this may be due to not knowing EXACTLY what I am looking for ;-) I am pretty sure I've done this in the past, but I've been out of the loop for a couple years (working with other software) - and am pretty danged rusty - so I'd appreciate some help

First off - I can't change the table setup at all to help achieve results, because I am linked to an excel spreadsheet - and there are over 100 of these sheets and currently there is no plan to modify this setup. So I have to link to the sheet in question and just make it work.

That said - here is my scenario:
These sheets are basically time logs - that have daily entries for each employee, the project they worked on, the number of hours, and the date (among other things).

So here's an example from the original time log table:

Project Date Tech Numeric
ABC Corp 2/1/05 Jill .25
XYZ Corp 2/22/05 Mike 1.25
ZZZ Corp 2/4/05 Sam .75
ABC Corp 3/2/05 Mike .50
ABC Corp 2/7/05 Jill 1.00
ABC Corp 3/3/05 Jill .50
XYZ Corp 2/23/05 Sam .75


Desired query Result:
Project Tech 2/05 3/05 4/05
ABC Corp Jill 1.25 .50
ABC Corp Mike .50
XYZ Corp Sam .75
XYZ Corp Mike 1.25
ZZZ Corp sam .75


I know i can do this with a report - and have done something similar with a report - however - I have been instructed to provide these results BACK to an excel format. The report when exported doesn't seem to be populating the excel sheet properly (I am summarizing totals in the report and hiding the detail level, only showing the results from the summary - when I export this data to excel, because the 'detail' is blank effectively, the exported data has nothing).

I am hoping I can do all of the calculations in the query, get the reslulting data, and either display it in a report that can be exported sucessfully, or just create a new table with the query results. Either way - I need to get it to work.

Everyhing I try gives me multiple records - I can't seem to only give one result per project, per tech.

I'm sure it's something simple that I've missed - but if someone can give me some tips to jog my memory I'd greatly appreciate it.

Thank you!!

tina

 

[M8KWR]

this would be done in a cross tab query...

use the wizard to set it up for you, then just edit the date field to only display the month/year, then it should do all the rest for you.

Hope this helps.

If not, let me know and i'll do the sql code for you. But let me know a table name.

 

[PHV]

No need for access I guess.
In Excel play with PivotTable.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TinaS]

M8K - Thank you so much!! Once I had the key (a cross tab query) I was able to work my way through my ulimate solution and get what I needed. I feel a bit embarrassed I had been trying in vain to set this up in query design view (I rarely use the wizards anymore) - and wasn't getting anywhere. Once I tried the cross tab - with some modification - I got the results I needed. I also took it a step further and used the sql created to build new queries on the fly for different kinds of reports using the different source data from the spreadsheets.

This helped immensely, and I appreciate it greatly!


PH - I would have gone this route, but unfortunately I set up this database to do all of the time reporting, and for all but this one instance it has been working fine. This was an exception, but i wanted them to be able to still use the database to filter and select what records they want to show. This way they have a central location to select report criteria (access form), and then depending on that criteria i can build whatever report they need. Thank you for your response though!

I did notice something hoakey - and that is I was getting numeric overflow errors every 3rd time or so that I ran the query(using the SAME data, and SAME criteria) - it just seemed to error out of the blue. I'd make a minor change to the query, resave it, and the error wouldn't happen for a few more runs.

By building the query each time a report is called (deleting it and rebuilding it) - this also managed to prevent the numeric overflow from happening again.

I will say this - building strings for a sql statement with variables and other misc can be a painful processs. I think I'll share it here so you can see how painful it was it works though!!

thanks again!

Tina

qrySpreadsheet = "TRANSFORM Sum(" & cmpName & ".Numeric) AS SumOfNumeric" _
& Chr(13) & "SELECT " & cmpName & "." & sortCol1 & "," & cmpName & "." & sortCol2 & "," & "Sum(" & cmpName & ".Numeric) AS Total," _
& Chr(34) & cmpName & Chr(34) & " as Company" _
& Chr(13) & "FROM " & cmpName _
& Chr(13) & "WHERE (((" & cmpName & ".Date) Between [Forms]![ReportSelection]![DateFrom] And [Forms]![ReportSelection]![DateTo]))" _
& Chr(13) & "GROUP BY " & cmpName & "." & sortCol1 & ", " & cmpName & "." & sortCol2 _
& Chr(13) & "ORDER BY " & cmpName & "." & sortCol1 & ", " & cmpName & "." & sortCol2 & ", Format([Date]," & Chr(34) & "mmmyy" & Chr(34) & ")" _
& Chr(13) & "PIVOT Format([Date]," & Chr(34) & "mmmyy" & Chr(34) & ") In (" & Chr(34) & "Jan05" & Chr(34) & "," & Chr(34) & "Feb05" & Chr(34) & "," & Chr(34) & "Mar05" & Chr(34) & "," & Chr(34) & "Apr05" & Chr(34) & "," & Chr(34) & "May05" & Chr(34) & "," & Chr(34) & "Jun05" & Chr(34) & "," & Chr(34) & "Jul05" & Chr(34) & "," & Chr(34) & "Aug05" & Chr(34) & "," & Chr(34) & "Sep05" & Chr(34) & "," & Chr(34) & "Oct05" & Chr(34) & "," & Chr(34) & "Nov05" & Chr(34) & "," & Chr(34) & "Dec05" & Chr(34) & ");"


Can we say OUCH!

whew - glad thats over with... thanks again..

 

[PHV]

A little simplification:

qrySpreadsheet = "TRANSFORM Sum([Numeric]) AS SumOfNumeric" _
& vbCr & "SELECT " & sortCol1 & "," & sortCol2 & "," & "Sum([Numeric]) AS Total," _
& "'" & cmpName & "' AS Company" _
& vbCr & "FROM " & cmpName _
& vbCr & "WHERE [Date] Between [Forms]![ReportSelection]![DateFrom] And [Forms]![ReportSelection]![DateTo]" _
& vbCr & "GROUP BY " & sortCol1 & "," & sortCol2 _
& vbCr & "ORDER BY " & sortCol1 & "," & sortCol2 & ",Format([Date],'mmmyy')" _
& vbCr & "PIVOT Format([Date],'mmmyy') In ('Jan05','Feb05','Mar05','Apr05','May05','Jun05','Jul05','Aug05','Sep05','Oct05','Nov05','Dec05')"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TinaS]

Thanks PH! - I'll look at this tomorrow and see about implementing it

I had completely forgotten about the use of single quotes in place of doubles in the above scenario - that REALLY cleans it up. You can imagine how painful it was to code in the quotes each time

Thanks for the input!