converting excel columns data

[Adrian]

I am importing an excel spreadsheet that I need to normalize so it can fit
into my database. Since I will get a new spreadsheet every month I will need
to repeat the process. I figured it would be easier to write a query or
series of queries in Access to convert the spreadsheet rather than fixing it
in excel everytime.

My source spreadsheet has one record per person with a pay from date and a
pay thru date. The problem it also has several different columns for
payment/deduction types.
this is the table as is

ID Name Pmt1 Pmt2 Pmt 3 Ded1 Ded2 Ded3 Ded4 PayFrm PayThru

I would like to convert the payment and deduction columns into two fields,
one for type and one form amount so that I can append the data to the
following table.

ID Name PayType Amt PayFrom PayThru


I have seen a couple of answers that suggest a union query but I am a little
unclear on the syntax and I want to confirm that if I do this on monthly
basis that I add the new data to the table and not overwrite last month's
info.

 

[John W. Vinson]

I am importing an excel spreadsheet that I need to normalize so it can fit
into my database. Since I will get a new spreadsheet every month I will need
to repeat the process. I figured it would be easier to write a query or
series of queries in Access to convert the spreadsheet rather than fixing it
in excel everytime.

My source spreadsheet has one record per person with a pay from date and a
pay thru date. The problem it also has several different columns for
payment/deduction types.
this is the table as is

ID Name Pmt1 Pmt2 Pmt 3 Ded1 Ded2 Ded3 Ded4 PayFrm PayThru

I would like to convert the payment and deduction columns into two fields,
one for type and one form amount so that I can append the data to the
following table.

ID Name PayType Amt PayFrom PayThru


I have seen a couple of answers that suggest a union query but I am a little
unclear on the syntax and I want to confirm that if I do this on monthly
basis that I add the new data to the table and not overwrite last month's
info.

Union would indeed work nicely for this. I would suggest two queries: a UNION
query to extract data, with an Append query based on it to add new records to
your table.

You could use File... Get External Data... Link to connect to the spreadsheet
(without the need to import the data in non-normal form). The UNION query
would be

SELECT ID, [Name], "Pmt" AS PayType, Pmt1 AS [Amount], PayFrm, PayTo
FROM linkedtablename
WHERE Pmt1 IS NOT NULL
UNION ALL
SELECT ID, [Name], "Pmt" AS PayType, Pmt2 AS [Amount], PayFrm, PayTo
FROM linkedtablename
WHERE Pmt2 IS NOT NULL
UNION ALL
SELECT ID, [Name], "Pmt" AS PayType, Pmt3 AS [Amount], PayFrm, PayTo
FROM linkedtablename
WHERE Pmt3 IS NOT NULL
UNION ALL
SELECT ID, [Name], "Ded" AS PayType, Ded1 AS [Amount], PayFrm, PayTo
FROM linkedtablename
WHERE Ded1 IS NOT NULL
UNION ALL
<etc for the additional columns>

Save this query, and then base an Append query on it to populate your local
table.

You may want to consider having an additional Autonumber primary key field in
the table. Just don't append anything to it and it will autoincrement and
provide a unique identifier for the row.

 

[Adrian]

Thanks John,

I was planning on using an automated number key for the table, just left
that off of the question.

I think my basic problem has been understanding the syntax of the coding. I
get how the one you provided works now, but I think I need to do more self
education. Can you recommend a site or two that could help me understand the
coding better. Beginner level of course.

I am importing an excel spreadsheet that I need to normalize so it can fit
into my database. Since I will get a new spreadsheet every month I will need
to repeat the process. I figured it would be easier to write a query or
series of queries in Access to convert the spreadsheet rather than fixing it
in excel everytime.

My source spreadsheet has one record per person with a pay from date and a
pay thru date. The problem it also has several different columns for
payment/deduction types.
this is the table as is

ID Name Pmt1 Pmt2 Pmt 3 Ded1 Ded2 Ded3 Ded4 PayFrm PayThru

I would like to convert the payment and deduction columns into two fields,
one for type and one form amount so that I can append the data to the
following table.

ID Name PayType Amt PayFrom PayThru


I have seen a couple of answers that suggest a union query but I am a little
unclear on the syntax and I want to confirm that if I do this on monthly
basis that I add the new data to the table and not overwrite last month's
info.

Union would indeed work nicely for this. I would suggest two queries: a UNION
query to extract data, with an Append query based on it to add new records to
your table.

You could use File... Get External Data... Link to connect to the spreadsheet
(without the need to import the data in non-normal form). The UNION query
would be

SELECT ID, [Name], "Pmt" AS PayType, Pmt1 AS [Amount], PayFrm, PayTo
FROM linkedtablename
WHERE Pmt1 IS NOT NULL
UNION ALL
SELECT ID, [Name], "Pmt" AS PayType, Pmt2 AS [Amount], PayFrm, PayTo
FROM linkedtablename
WHERE Pmt2 IS NOT NULL
UNION ALL
SELECT ID, [Name], "Pmt" AS PayType, Pmt3 AS [Amount], PayFrm, PayTo
FROM linkedtablename
WHERE Pmt3 IS NOT NULL
UNION ALL
SELECT ID, [Name], "Ded" AS PayType, Ded1 AS [Amount], PayFrm, PayTo
FROM linkedtablename
WHERE Ded1 IS NOT NULL
UNION ALL
<etc for the additional columns>

Save this query, and then base an Append query on it to populate your local
table.

You may want to consider having an additional Autonumber primary key field in
the table. Just don't append anything to it and it will autoincrement and
provide a unique identifier for the row.

 

[John W. Vinson]

I think my basic problem has been understanding the syntax of the coding. I
get how the one you provided works now, but I think I need to do more self
education. Can you recommend a site or two that could help me understand the
coding better. Beginner level of course.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials