Getting data from rows to columns based on common fields.

[Swarles_Barkley]

I have a big Excel file with payroll information about employees. Per employee per date, there are a couple of rows with mostly identical data (such as the employee's home address) but 2 differences: one will have paycode A with amount B, another will have paycode C with amount D, etc. I want to simplify the file to have 1 row per employee per date: in the example given that row would have paycode A with amount B as well as paycode C with amount D, in 4 separate columns.



I think I can technically solve this by creating Excel files per pay code/amount, and then linking them together by person and date, 1 by 1, through Access. This is feasible because there aren't that many different pay codes. But still, I was wondering if there was anyway to solve my problem in Access itself.

Thanks.

[JoeM]

Yes, create a new query with Employee Name, Date, and then four calculated fields, the first of which will look like this:

Amount_A:IIF([PayCode]="A",[Amount],0)

repeat for codes B-D.

Then, you van do an Aggregate Query on this, Grouping by your Name and Date fields, and Summing all four calculated fields. This will collapse the records down to a single row for each Name/Date combination.