By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,497 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

Modifying imported tables

P: 3
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
Feb 11 '07 #1
Share this Question
Share on Google+
7 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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
Feb 11 '07 #2

P: 3
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.
Feb 11 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Feb 11 '07 #4

P: 3
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:
Expand|Select|Wrap|Line Numbers
  1. A           B            C
  2. 1            1             1
  3. 1            1             2
  4. ..            ..            ..
  5. n            2            10
  6.  
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.
Feb 11 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
This is based on 3 tables but you can include as many as you want.

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblSystemParameters ([A String], [B String], [C String])
  2. SELECT tempTable.[A], tempTable.[b], tempTable.[C]
  3. FROM (SELECT [A], [b], [C]
  4. FROM Table1
  5. UNION
  6. SELECT [A], [b], [C]
  7. FROM Table2
  8. UNION
  9. SELECT [A], [b], [C]
  10.  FROM Table3
  11. Group By [A], [b], [C]) As tempTable;
  12.  
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT * INTO NewTableName
  2. FROM (SELECT Table1.*, DLookup("[UniqueID]","tblSystemParameters","[A String]='" & [A] & "' AND [B String]=" & [b] & " AND [C String]=" & [C]) As UniqueID
  3. FROM Table1
  4. UNION
  5. SELECT Table2.*, DLookup("[UniqueID]","tblSystemParameters","[A String]='" & [A] & "' AND [B String]=" & [b] & " AND [C String]=" & [C]) As UniqueID
  6. FROM Table2
  7. UNION
  8. SELECT Table3.*, DLookup("[UniqueID]","tblSystemParameters","[A String]='" & [A] & "' AND [B String]=" & [b] & " AND [C String]=" & [C]) As UniqueID
  9. 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
Feb 11 '07 #6

NeoPa
Expert Mod 15k+
P: 31,186
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)?
Feb 11 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
A few amendments to previous post ...

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblSystemParameters ([A String], [B String], [C String])
  2. SELECT tempTable.[A], tempTable.[b], tempTable.[C]
  3. FROM (SELECT [A], [b], [C]
  4. FROM Table1
  5. UNION
  6. SELECT [A], [b], [C]
  7. FROM Table2
  8. UNION
  9. SELECT [A], [b], [C]
  10.  FROM Table3) As tempTable;
  11.  
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT tempData.*, tblSystemParameters.[uniqueID] 
  2. INTO NewTableName
  3. FROM (SELECT * FROM Table1
  4. UNION ALL SELECT * FROM Table2
  5. UNION ALL SELECT * FROM Table3) AS tempData
  6. INNER JOIN tblSystemParameters
  7. ON tempData.[A]=tblSystemParameters.[A String]
  8. AND tempData.[b]=tblSystemParameters.[B String]
  9. AND tempData.[C]=tblSystemParameters.[C String];
Feb 12 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.