Changing row data to columns in a query

[TallGuyinIT]

I am new to access and struggling with a query. I am pulling data from an odbc table which stores the information in rows. I need to move the data to columns in a query. The data exists as

emp1 ded1 amount
emp1 ded2 amount
emp1 ded3 amount
emp2 ded1 amount
emp2 ded2 amount

I need to put it in columns
emp1 ded1 amount ded2 amount ded3 amount
emp2 ded1 amount ded2 amount

Each employee will have a different number of deductions, but no employee will have more than 15. Can anyone help?

Thanks

 

[lameid]

Make a query based on you ODBC link and include all 3 columns (fields in Access jargon).
From the query menu select crosstab query.
On the Crosstab line put Row Heading under EMP, Column Heading under deduction and value under amount.
On the total line for amount, change it to sum. That query will show each emp with amounts under deduction headings.

You can then make a report to put the lables in according to column heading.

Alternately, you could make a report that groups by Emp and has multiple columns. Then you would simply put ded and amount fields in once and change the report to 3 columns with a layout of Across then down.