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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
try creating a crosstab query using the wizard
0
Rey Obrero (Capricorn1)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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
htowntechyCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.