Convert layout of data

[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_hours CLient_B_hours 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.

Please help!!
Thank you,
Jenni

PositionNum

 

[jahoobob via AccessMonster.com]

If Position_Num is the Employee Id then I would import the data into Access
and assign a Primary Key when importing. Create and run a make table query
of PK, Position_Num and Client_A_hours (but call it Hours_Billed) and add a
field Client and put "A" in it. Do the same for Client_B_hours but make it
an append table to the first. Repeat for Client_C_hours.
Your table will look like this:
Position_Num Hours Client
123 100 A
123 100 C
456 500 A
456 600 B
123 200 A

If you don't already have a table of Employees, create an Duplicates query
looking for duplicates of Position_Num and then change it to a make table
query and run it. Open this new table and delete the number of duplicates
field. Delete all of your make table and append queries because these create
problems if you happen to have one highlighted and you try to create a new
query.

You didn't say how many clients you have and that may create another issue.
What form is this database from which you are plling this data. Is it the
dreaded Excel "database?" If it is, then it should be easy to
pull those column headers (client names) into a table in Access. Let us know
iwhat form the other db is.

 

[John Vinson]

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_hours CLient_B_hours 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.

You didn't say how many columns of clients you have to deal with... if
it's over 253, you've got a real problem (an Access table can have
only 255 fields and you need two for the Position_Num and Employee).

If it is fewer, you can use a "Normalizing Union Query" to migrate the
data from this spreadsheet into a properly structured database, such
as:

Employees
Position_Num <Long Integer Primary Key>
EmployeeName <Text> <<< I'd recommend splitting into FirstName,
LastName>
<other employee bio info>

Clients
ClientID Autonumber
ClientName Text
SpreadsheetFieldName Text

Assignments
Position_Num
ClientID
Hours <Long Integer or Single, matching your existing fields>

You could fill your Clients table manually with the list of clients,
and fill in your current column headers into the SpreadsheetFieldName
field.

You'ld then run an Append query to append the PositionNum and
EmployeeName fields into Employees.

Then create a monster UNION query (or you might need more than one if
you get the Query Too Complex error):

SELECT Position_Num, "Client_A_Hours" AS SpreadsheetFieldName,
[Client_A_Hours] AS Hours
FROM YourWideFlatTable
WHERE [Client_A_Hours] IS NOT NULL
UNION ALL
SELECT Position_Num, "Client_B_Hours" AS SpreadsheetFieldName,
[Client_B_Hours] AS Hours
FROM YourWideFlatTable
WHERE [Client_B_Hours] IS NOT NULL
UNION ALL
SELECT Position_Num, "Client_C_Hours" AS SpreadsheetFieldName,
[Client_C_Hours] AS Hours
FROM YourWideFlatTable
WHERE [Client_C_Hours] IS NOT NULL
UNION ALL
....

<etcetera>

Save this query as uniAllHours, and then create an Append query
joining it to Clients:

INSERT INTO Assignments(Position_Num, ClientID, Hours)
SELECT uniAllHours.PositionNum, Clients.ClientID, uniAllHours.Hours
FROM uniAllHours INNER JOIN Clients
ON uniAllHours.SpreadsheetFieldName = Clients.SpreadsheetFieldName;

Run this query to populate the assignments table and then use a Totals
query to generate your report.

John W. Vinson[MVP]