Large Source Data Table, need to convert columns to rows??

[Guest]

Hello,
I have a very large table of source data to bring into Acces and convert to
the format I need it. It is a database that shows our clients and how many
hours each person spends on them.

The source data looks something like this.,...
Position_Num Employee Client_A CLient_B CLient_C
123 Joe Smith 100 0 400
456 Sue Person 500 600 0

There are about different clients and obviously employees only work on a
few. So I need to convert the data to look like a report like this.....

123 Joe Smith
Client A 100
Client C 400

456 Sue Person
Client A 500
Client B 600

It seems like such an easy request, but I am scratching my head on the best
way to do this with so many columns of clients.

I need to basically make the 50 columns of clients convert to rows under
each person and suppress any clients where the person = 0 hours.

Please help!!
Thank you,
Jenni

 

[Guest]

Hi Jenni,

You need three tables. One for the Employees, another for the ClientHours,
and a last table for the Clients.

Employees is easy after you import your data into Access into a table named
ClientHours.

SELECT ClientHours.Position_Num, ClientHours.Employee INTO Employees
FROM ClientHours;

I'm assuming heavily that Position_Num is unique and directly related to the
Employee. If so after creating the table, make Position_Num the primary key.

Next create a table named ClientEmpHours with the following fields
CEH_PK Autonumber Primary Key
Position_Num Number or Text
Client Text
Hours Number Double

Next run a query to start populating the new table.
INSERT INTO ClientEmpHours ( Position_Num, Client, Hours )
SELECT ClientHours.Position_Num, "Client_A" AS Client, ClientHours.Client_A
FROM ClientHours;

You will need to modify and run this query for each client. Make sure to
keep the "Client_A"-like info aligned with the other fields.

This brings up the big question: Is this a one-time-deal or will you need to
do it often? More than once, you probably want to save the append queries for
future use.

Lastly you need a table for customer information. You can start one like so
then add needed fields later:
SELECT ClientEmpHours.Client INTO Clients
FROM ClientEmpHours
GROUP BY ClientEmpHours.Client
ORDER BY ClientEmpHours.Client;

After all the above you should be able to create the report that you need by
joining 2 of the tables in a query and using sorting and grouping in a report.

To get rid of the zero hours you could either (1) add >0 criteria in the
append query above or (2) put in the >0 criteria in the query for the report.