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?
lthamesAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
something like this

TRANSFORM Sum(XXX.SaleAmount) AS SumOfSaleAmount
SELECT XXX.SaleType, Sum(XXX.SaleAmount) AS SumOfSaleAmount1
FROM XXX
GROUP BY XXX.SaleType
PIVOT XXX.EmployeeID;
0
 
Rey Obrero (Capricorn1)Commented:
try creating a crosstab query using the wizard
0
 
htowntechyConnect With a Mentor Commented:
I tried this in Access 2003.  I called the table 'MainData' in my query.
You can also use the Query Wizard and select 'Crosstab Query Wizard' and follow the promts.

The code for a crosstab with all the ShiftIDs is

TRANSFORM Sum(MainData.SaleAmount) AS SumOfSaleAmount
SELECT MainData.ShiftID, MainData.SaleType, Sum(MainData.SaleAmount) AS [Total Of SaleAmount]
FROM MainData
GROUP BY MainData.ShiftID, MainData.SaleType
PIVOT MainData.EmployeeID;

If you just want to pull the data for ShiftID the code is

TRANSFORM Sum(MainData.SaleAmount) AS SumOfSaleAmount
SELECT MainData.ShiftID, MainData.SaleType, Sum(MainData.SaleAmount) AS [Total Of SaleAmount]
FROM MainData
Where MainData.ShiftID = 1
GROUP BY MainData.ShiftID, MainData.SaleType
PIVOT MainData.EmployeeID;
0
 
lthamesAuthor Commented:
Thanks to both of you for your help!
0
 
Richard DanekeTrainerCommented:
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.
0
All Courses

From novice to tech pro — start learning today.