Converting Row-Based Data to Column-Based

[patchesohouli]

Hi,



I am trying to utilize Access to take weekly data in a Row-based format and transpose to Column based. So for example, I have the following row-based report of employee benefits:

EEID	Last Name	Benefit Plan	Deduction Amount
1234	Smith	Medical	$2
1234	Smith	Dental	$4
2245	Doe	Vision	$3
2245	Doe	Medical	$2

These values will change each week, employees will have some but not all possible benefits (there will need to be blank values where an employee is not enrolled). I need to import weekly changes into access and have the report come out as such:

EEID	Last Name	Medical	Dental	Vision
1234	Smith	$2	$4
2245	Doe	$2		$3

Can this be done? Thanks in advance for your help.

[Minty]

Yes Create a Cross tab query, use the query designer, and all will become clear (after a bit of fiddling).

[Perceptus]

You could also export the data to Excel and then Transpose the table

[ssanfu]

This is similar to a cross tab query... I didn't know about cross tab queries at the time (long, long ago) I needed to create a report, so I created this type of query.
Note that there is no date/time filter for the query. But it could/should be added.

Also note that you should not have spaces in object names (especially in field names).

[patchesohouli]

Wow - thank you everyone. I think I got it using the crosstab query. Ssanfu, I am going to dissect your query however as I think I have other uses for it!