Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Crosstab query - returning 1 record per week instead of 1 record

Status
Not open for further replies.

JimStrand

Technical User
May 7, 2014
33
US
I have a crosstab query which is returning a new record for each column header instead of 1 row per Mode.Name

TRANSFORM Sum(WK_25_OT_Qry.SumOfEXPN_AMT) AS SumOfSumOfEXPN_AMT
SELECT Mode.ModeName
FROM CalendarPayroll_TY INNER JOIN ((WK_25_OT_Qry INNER JOIN SOF_Dept_Roll_Up_Tbl ON WK_25_OT_Qry.INDEX = SOF_Dept_Roll_Up_Tbl.INDEX) INNER JOIN Mode ON SOF_Dept_Roll_Up_Tbl.[Report Description] = Mode.ModeName) ON CalendarPayroll_TY.Description = WK_25_OT_Qry.INVOICE_DESCR
GROUP BY Mode.ModeName, WK_25_OT_Qry.INVOICE_DESCR, Mode.ReportSort
ORDER BY Mode.ReportSort
PIVOT CalendarPayroll_TY.Week_Ending;


I'm thinking it has to be something obvious that I changed but cannot see it. It should return only 1 line of data for each mode such as Bus Transportation but it is returning 1 line per each week up to the 25th week of the fiscal year (see attached) any thoughts on this obvious mistake would be appreciated. I'm back into the Access development game after several years off and could use some support. Thank you.
 
 https://files.engineering.com/getfile.aspx?folder=8e3bd789-3343-4619-8ba1-be47d46c6535&file=Screenshot_2023-12-22_162435.gif
I figured it out. I had invoice description in one of the row headings and of course this is unique for each week. Thank you!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top