Convert rows into columns

[Davtre]

I got a very simple access database of assets that’s automatically generated by a third party software into an access database table.
The data I get ends up int the 'wrong order' and I need to change the rows and columns.
This is how it looks:
Asset# - Item - ItemValue2
Asset1 - Name - PC1
Asset1 - Serial - 123ABC
Asset2 - Name - PC2
Asset2 - Serial - 456DEF

And this is how I want it to look:
Asset# - Name - Serial
Asset1 - PC1 --- 123ABC
Asset2 - PC2 --- 456DEF

I tried to search for it but didn’t get any good results out of it. Can someone help me with a simple way to fix this?

 

[jzwp22]

Welcome to the forum!

Basically the data you are getting from the third party software is not structured properly, so I would transfer the data into a new table of the correct structure much like you propose. So I would create the new table first. It is generally not recommended to have spaces or special characters in your table/field names. Also, the word Name is a reserved word in Access, so it is best not to use in as a field or table name. With that, the new table would look something like this

tblGoodTable
-txtAssetNo
-txtName
-txtSerialNo

Now to migrate the data to the new design, I'll show you a series of queries that will get you to the final structure we need.

First a query to get the Assets

query name: qryGetAsset
SELECT DISTINCT tblThirdParty.[Asset#] AS txtAssetNo
FROM tblThirdParty;

Next, a query to get the name of the asset

query name: qryGetAssetName
SELECT tblThirdParty.[Asset#] AS txtAssetNo, tblThirdParty.ItemValue2 AS txtName
FROM tblThirdParty
WHERE (((tblThirdParty.Item)="Name"));

Next, a query to get the serial number

query name: qryGetSerialNumber
SELECT tblThirdParty.[Asset#] AS txtAssetNo, tblThirdParty.ItemValue2 AS txtSerialNo
FROM tblThirdParty
WHERE (((tblThirdParty.Item)="serial"));

Now, a query to bring the data from the 3 above queries together

query name: qryFinal
SELECT qryGetAsset.txtAssetNo, qryGetAssetName.txtName, qryGetSerialNumber.txtSerialNo
FROM (qryGetAsset INNER JOIN qryGetAssetName ON qryGetAsset.txtAssetNo = qryGetAssetName.txtAssetNo) INNER JOIN qryGetSerialNumber ON qryGetAsset.txtAssetNo = qryGetSerialNumber.txtAssetNo;

Now, I could have just converted the above Select query into an append query but I wanted to break it down for you.

To migrate the data to the new table design, we would use the following:

query name: qryFinalAppend
INSERT INTO tblGoodTable ( txtAssetNo, txtName, txtSerialNo )
SELECT qryFinal.txtAssetNo, qryFinal.txtName, qryFinal.txtSerialNo
FROM qryFinal;


I've attached a sample database with all of the above queries for reference. I did not run the append query. I'll let you try it.