If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Convert layout of data
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 |
#2
|
|||
|
|||
Convert layout of data
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. JenL wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200603/1 |
#3
|
|||
|
|||
Convert layout of data
On Fri, 3 Mar 2006 06:54:28 -0800, JenL
wrote: 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] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
ranking query | JaimeTimbrell | General Discussion | 2 | February 16th, 2006 08:09 AM |
Inserting a new line when external data changes | Rental Man | General Discussion | 0 | January 11th, 2006 07:05 PM |
Query does not order the same data in the same way | Milković Aleksandar | Running & Setting Up Queries | 10 | December 15th, 2005 12:01 PM |
PST file has reached maximum size | Jeff C | General Discussion | 2 | October 6th, 2005 01:35 PM |
SQL view of messed up action queries | Kendra | Running & Setting Up Queries | 2 | August 31st, 2004 09:53 PM |