Transpose issue

[Jafa]

Hi,

I'd like to transpose some data in Access but am not sure how to go about it.

The attached file shows the source data and the desired result (used Excel screen shots but need to do this in Access)

I understand I might be able to do this in Excel with arrays, but the workflow would be more fluid in Access.

Any ideas would be great.

Thanks

 

[Jon K]

You can do it with a Union Query. Type in the SQL View of a new query:-

SELECT Name, "Month1" AS [Month], Month1 AS [Worked]
FROM [TableName]
UNION
SELECT Name, "Month2" , Month2
FROM [TableName]
UNION
SELECT Name, "Month3" , Month3
FROM [TableName]
UNION ...... etc
.

 

[Pat Hartman]

Except, don't use "Month" or any other function or property names as column names.

 

[Jafa]

Additional question

Thanks Jon K, that is a good solution, should have thought about it a bit more myself.

I am only just starting to understand the data I am working with but I anticipate that the number of months will vary on a month to month basis (looking at combining data relating to 20-30 projects with different durations).

I could put in a very large number of Unions to cover expectations but am wondering if there is a way to make the system more robust as other will take on maintenance of the solution.

Appreciate your help

Cheers

 

[Jon K]

Your original table is a spreadsheet-like table.

You may consider storing the data in a normalized table e.g. with these fields (assuming the table records employee activities):-

RecordID - possibly an autonumber field, set as primary key

EmployeeName - Name is an Access key word. It's better to use something like EmployeeName, ClientName etc.

ActivityMonth - Month is a function name. It's better to use a more descriptive name such as ActivityMonth. You may also use a date field instead of a text field here as a date field is more flexible for data retrieval.

Quantity - a numeric field.


If there are other info that you need to store regarding the Employees, you can change the EmployeeName to EmployeeID in the activities table and create an Employees table with the fields:-

EmployeeID - primary key
EmployeeName
DateOfBirth - a date field
Address
.... etc.

The Employees table is related to the activities table by EmployeeID in a one-to-many relationship. This way, you can easily link the activities table to the employees table in a query to pull the personal info e.g. the EmployeeName for the activities.


Search for Normalize and Normalization in these forums. There's quite a lot of good stuff there, particularly the posts by Pat.

When your data are normalized, you seldom need the Union Queries.
.

 

[Jafa]

Thanks for your thoughts

Thanks for the ideas Jon, I'll definitely use descriptive field names.

As the data will be coming from Excel in the original format, I'll have to go down the path of Unions I think.

I have researched the forum for normalization comments and found some useful tips.

Thanks again for you ideas.

Over and out..