Crosstab query question (1 Viewer)

[Euler271]

I want to create a crosstab query where I can get an average for a select number of row entries. In other words, let's say I have a database with three fields: days of the week, company name, hours worked. The row heading would be the days of the week, the column heading would be the company name and the values would be average hours worked. Now, let's say I just want the average for the group of Tuesday, Thursday and Friday instead of individual averages for each day. Is there a way to do this? Thank you.

 

[Gasman]

Crossposted https://www.accessforums.net/showthread.php?t=86868

 

[ebs17]

Maybe this helps:

TRANSFORM
   AVG(HoursWorked) AS XX
SELECT
   CompanyName
FROM
   TableX
WHERE
   IWeekdayNumber IN (2, 4, 5)
GROUP BY
   CompanyName
PIVOT
   IWeekdayNumber

 

[Euler271]

That doesn't work either because each WeekdayNumber is still a row heading.

 

[plog]

Why'd you confuse the issue initially? All this talk about cross-tab and days as row headings and it turns out you want something totally different than what you have. This should have been the entirety of your post:

I have a database with three fields: days of the week, company name, hours worked. I just want the average for the group of Tuesday, Thursday and Friday instead of individual averages for each day. Is there a way to do this?

And this is the code for that:

SELECT YourTableNameHere.CompanyName, Avg(YourTableNameHere.HoursWorked) AS AverageHours
FROM YourTableNameHere
WHERE (((YourTableNameHere.WeekDay)="Tuesday" Or (YourTableNameHere.WeekDay)="Thursday" Or (YourTableNameHere.WeekDay)="Friday"))
GROUP BY YourTableNameHere.CompanyName;

And if that isn't what you want, you need to demonstrate your issue with data. Not words, but show us what you expect your ultimate query to look like.

 

[Euler271]

My question was correct. I wanted to know how to get the results using a crosstab query. And I don't need a lecture about "confusing the issue"

 

[ebs17]

...
PIVOT
   IWeekdayNumber

For me, this results in column headings. So I don't know what you're doing.