Can I create an Access query with variable column headings?
I need what I thing would be a cross tab query in Access but the columns are also determined by the data in the table. Let me explain!
Here is the table data:
PrimaryKeyID ShiftID EmployeeID SaleType SaleAmount
1 1 1 Food 13.65
2 1 1 Bev 42.40
3 1 1 Prod 14.00
4 1 2 Food 54.30
5 1 2 Dess 15.63
6 1 3 Food 23.65
7 1 3 Prod 46.70
8 2 1 Food 14.00
9 2 1 Dess 54.30
10 2 1 Prod 15.63
The query would pull results for a specific ShiftID.
I need the columns across the top to be the EmployeeID, so a query for ShiftID=1 would have 3 columns.
BUT I would need the first column to identify the row data . . that's where I get totally lost! For ShiftID=1 the rows under the first column would be "Food", "Bev", "Dess", and "Prod"
Then the data in the cells for each row would be the SalesAmount for that category for each employee. Looking like this:
SaleType 1 2 3
Food 13.65 54.30 23.65
Bev 42.40 0.00 0.00
Dess 0.00 15.63 0.00
Prod 14.00 0.00 46.70
Is this possible?
Here is the table data:
PrimaryKeyID ShiftID EmployeeID SaleType SaleAmount
1 1 1 Food 13.65
2 1 1 Bev 42.40
3 1 1 Prod 14.00
4 1 2 Food 54.30
5 1 2 Dess 15.63
6 1 3 Food 23.65
7 1 3 Prod 46.70
8 2 1 Food 14.00
9 2 1 Dess 54.30
10 2 1 Prod 15.63
The query would pull results for a specific ShiftID.
I need the columns across the top to be the EmployeeID, so a query for ShiftID=1 would have 3 columns.
BUT I would need the first column to identify the row data . . that's where I get totally lost! For ShiftID=1 the rows under the first column would be "Food", "Bev", "Dess", and "Prod"
Then the data in the cells for each row would be the SalesAmount for that category for each employee. Looking like this:
SaleType 1 2 3
Food 13.65 54.30 23.65
Bev 42.40 0.00 0.00
Dess 0.00 15.63 0.00
Prod 14.00 0.00 46.70
Is this possible?
Rey Obrero (Capricorn1)
try creating a crosstab query using the wizard
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to both of you for your help!
capricorn1 is correct, a crosstab query will work.
Select Crosstab query at the type,
use Shift ID as a row heading (providing a new row for each shift)
use SaleType as a column heading (providing a new column for each food type)
use SaleAmount as a SUM (providing the totals)
The wizard will let you select these options or if you go straight to design, you can set the options.
When in place, Choose, View, SQL and you will see the SQL code suggested by others.
Select Crosstab query at the type,
use Shift ID as a row heading (providing a new row for each shift)
use SaleType as a column heading (providing a new column for each food type)
use SaleAmount as a SUM (providing the totals)
The wizard will let you select these options or if you go straight to design, you can set the options.
When in place, Choose, View, SQL and you will see the SQL code suggested by others.