Modifying imported tables

[tfoale]

Hi. I'm a newbie trying to modify imported files in VBA (Access 2003) and struggling with it. I have a set of large CSV files (too large to handle in Excel) each containing data related to a different system. I import these as individual tables, then I need to put them into one big table for further processing. Ideally I would like to create another table with a set of fields identifying each system and then add a link to a record in that table to every record in a new field in one of the imported tables, then do this for all of the imported tables.

Currently all I can do is add the system fields to the imported tables, put the system data against each record and then combine them, which causes loads of duplication. I cannot find a way to put a new link field to another table in an existing table. Can anyone please suggest a way to do this properly in ADO? Many thanks

Tom

[MMcCarthy]

Hi. I'm a newbie trying to modify imported files in VBA (Access 2003) and struggling with it. I have a set of large CSV files (too large to handle in Excel) each containing data related to a different system. I import these as individual tables, then I need to put them into one big table for further processing. Ideally I would like to create another table with a set of fields identifying each system and then add a link to a record in that table to every record in a new field in one of the imported tables, then do this for all of the imported tables.

Currently all I can do is add the system fields to the imported tables, put the system data against each record and then combine them, which causes loads of duplication. I cannot find a way to put a new link field to another table in an existing table. Can anyone please suggest a way to do this properly in ADO? Many thanks

Tom

Tom

I know what you posted made perfect sense to you but remember we can't see the data you're looking at:

• What are the system fields you are talking about and how are they related to the record data?
• It sounds like you want to create relationships but from what to what?
• Do all the csv files contain the same type of data?

Read what you posted and think of it from the point of view of someone who can't see the database and try explaining again.

Mary

[tfoale]

Thanks Mary

Each of the imported files has about 17,000 records. The systems have three parameters A, B and C, which together uniquely identify them (sorry I can't be more specific than that). I can extract A, B and C from the imported table names. So I want to create a table tblSystemParameters with the fields

UniqueID Autonum
A String
B String
C String

and then add a field to each of the imported tables for which every record has the relevant UniqueID in it (the same UniqueID for every record in one of the imported tables). After this I want to combine all of the imported tables into one table. I know how to create tblSystemParameters, but not how to add the field with the link to tblSystemParameters into the imported tables.

Currently I add A, B and C fields to each of the imported tables using .Fields.Append .CreateField, then use an Update query to fill in the A, B and C fields for each record. Then I use an 'Insert Into' query to combine the modified imported tables into a new table. This works but is not ideal.

[MMcCarthy]

This can all be done by the use of queries.

Each of the imported files has about 17,000 records. The systems have three parameters A, B and C, which together uniquely identify them (sorry I can't be more specific than that). I can extract A, B and C from the imported table names. So I want to create a table tblSystemParameters with the fields

UniqueID Autonum
A String
B String
C String

Create the table as above (tblSystemParameters). You can delete the records when you are finished and use the table again. It's easier than recreating it each time in code.

Q. Does each imported table only have A or B or C or is there a mix of both and if so then can you give me some clue how you extract this from the table names. I understand confidentiality but maybe if you try to explain on the apples and oranges level I will better follow what you are talking about.

and then add a field to each of the imported tables for which every record has the relevant UniqueID in it (the same UniqueID for every record in one of the imported tables). After this I want to combine all of the imported tables into one table. I know how to create tblSystemParameters, but not how to add the field with the link to tblSystemParameters into the imported tables.

Q. Not sure what you mean here. Is the unique ID in the imported tables or the one you are creating in the system parameters tables.

Currently I add A, B and C fields to each of the imported tables using .Fields.Append .CreateField, then use an Update query to fill in the A, B and C fields for each record. Then I use an 'Insert Into' query to combine the modified imported tables into a new table. This works but is not ideal.

Sorry to be so dense Tom. If might help if you substitute a real life example of what you're doing (a la apples and oranges) as it would help a lot to know how this data is related and how the records are uniquely identified. Substituting something else for what you have to keep confidential will help.

Mary

[tfoale]

This can all be done by the use of queries.



Create the table as above (tblSystemParameters). You can delete the records when you are finished and use the table again. It's easier than recreating it each time in code.

I keep the table - I build it up as the system grows.

Q. Does each imported table only have A or B or C or is there a mix of both and if so then can you give me some clue how you extract this from the table names. I understand confidentiality but maybe if you try to explain on the apples and oranges level I will better follow what you are talking about.

It's like:

A           B            C
1            1             1
1            1             2
..            ..            ..
n            2            10
 

ie, B is binary, C a variable number up to 10, and n, which is a 4 character text string, just keeps on growing.

[Q. Not sure what you mean here. Is the unique ID in the imported tables or the one you are creating in the system parameters tables.

The imported tables have their own two fields X and Y that make them unique, the uniqueID I'm referring to is the one in tblSystemParameters. All I'm doing is making sure I understand which of the imported tables the data in the collected data comes from.

Sorry, as this is my first post I'm still working out how to phrase the question properly.

[MMcCarthy]

This is based on 3 tables but you can include as many as you want.

INSERT INTO tblSystemParameters ([A String], [B String], [C String])
SELECT tempTable.[A], tempTable.[b], tempTable.[C]
FROM (SELECT [A], [b], [C]
FROM Table1
UNION
SELECT [A], [b], [C]
FROM Table2
UNION
SELECT [A], [b], [C]
 FROM Table3
Group By [A], [b], [C]) As tempTable;
 

This will insert a unique set of values into the new table from the combination of the three values.

Now create a new table using the following sql. [A] is surrounded by single quotes because you said it was text.

SELECT * INTO NewTableName
FROM (SELECT Table1.*, DLookup("[UniqueID]","tblSystemParameters","[A String]='" & [A] & "' AND [B String]=" & [b] & " AND [C String]=" & [C]) As UniqueID
FROM Table1
UNION
SELECT Table2.*, DLookup("[UniqueID]","tblSystemParameters","[A String]='" & [A] & "' AND [B String]=" & [b] & " AND [C String]=" & [C]) As UniqueID
FROM Table2
UNION
SELECT Table3.*, DLookup("[UniqueID]","tblSystemParameters","[A String]='" & [A] & "' AND [B String]=" & [b] & " AND [C String]=" & [C]) As UniqueID
FROM Table3) AS tempData;

I'm not sure if I've caught all your requirements but try this and see what else you need.

Mary

[NeoPa]

Hi. I'm a newbie trying to modify imported files in VBA (Access 2003) and struggling with it. I have a set of large CSV files (too large to handle in Excel) each containing data related to a different system. I import these as individual tables, then I need to put them into one big table for further processing. Ideally I would like to create another table with a set of fields identifying each system and then add a link to a record in that table to every record in a new field in one of the imported tables, then do this for all of the imported tables.

Currently all I can do is add the system fields to the imported tables, put the system data against each record and then combine them, which causes loads of duplication. I cannot find a way to put a new link field to another table in an existing table. Can anyone please suggest a way to do this properly in ADO? Many thanks

Tom

You say you need ADO explicitly. Is that necessary or are you just looking for the best way to do the job (which we hope you'll explain a little better as per Mary's post)?

[MMcCarthy]

A few amendments to previous post ...

INSERT INTO tblSystemParameters ([A String], [B String], [C String])
SELECT tempTable.[A], tempTable.[b], tempTable.[C]
FROM (SELECT [A], [b], [C]
FROM Table1
UNION
SELECT [A], [b], [C]
FROM Table2
UNION
SELECT [A], [b], [C]
 FROM Table3) As tempTable;
 

This will insert a unique set of values into the new table from the combination of the three values.

Now create a new table using the following sql.

SELECT tempData.*, tblSystemParameters.[uniqueID] 
INTO NewTableName
FROM (SELECT * FROM Table1
UNION ALL SELECT * FROM Table2
UNION ALL SELECT * FROM Table3) AS tempData
INNER JOIN tblSystemParameters
ON tempData.[A]=tblSystemParameters.[A String]
AND tempData.[b]=tblSystemParameters.[B String]
AND tempData.[C]=tblSystemParameters.[C String];