×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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.

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

(OP)
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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close