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
7 1935 MMcCarthy 14,534
Recognized Expert Moderator MVP
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
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 tblSystemParame ters 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 tblSystemParame ters, but not how to add the field with the link to tblSystemParame ters 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 14,534
Recognized Expert Moderator MVP
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 tblSystemParame ters with the fields
UniqueID Autonum
A String
B String
C String
Create the table as above (tblSystemParam eters). 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 tblSystemParame ters, but not how to add the field with the link to tblSystemParame ters 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
This can all be done by the use of queries.
Create the table as above (tblSystemParam eters). 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 tblSystemParame ters. 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 14,534
Recognized Expert Moderator MVP
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 32,566
Recognized Expert Moderator MVP
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 14,534
Recognized Expert Moderator MVP
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];
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: J. Kenney |
last post by:
Good Morning,
Is there a way for a function called within an _imported_
library to call back to _calling_ python program to run a
function? (Shown below)
Also can you overload the print function to have it do
redirect output to an array for a bit, and then switch
it back. (for example: right before importing make print
append each line...
|
by: Jan Gregor |
last post by:
Hello folks
I want to apply changes in my source code without stopping jython
and JVM. Preferable are modifications directly to instances of
classes. My application is a desktop app using swing library.
Python solutions also interest me.
Solution similiar to "lisp way" is ideal.
|
by: Stewart Allen |
last post by:
Hi there
Opinions on the best way to do this.
I want to import data from an Excel spreadsheet into Access and want the
data to be tested before the tables are updated. At the moment all the data
is validated via the data entry form where the BeforeUpdate event is used
for some fields and also in the form. I know these events won't fire if...
|
by: deko |
last post by:
What I'm trying to do is allow advanced users to compose SQL queries to
update imported tables - and only imported tables. So I have a table that
contains all the internal table names (tblTablesInternal) and I loop through
that making the internal table name the pattern to search for in the user's
SQL statement:
Public Function...
|
by: MikeY |
last post by:
Hi everyone,
Using C#, Windows forms. I am trying to learn how to modify existing data
with in MSDE table/fields. If anyone could help me out with my code, I would
appreciate it. My code is as follows:
private void btnEmpModify_Click(object sender, System.EventArgs e)
{
DataTable thisTable = dsModifyRecord.Tables;
//THIS IS WHERE I'M...
| |
by: JohnR |
last post by:
I have a table with 1 row which is used to hold some application wide items
(one item per field, hence I only need 1 row).
I want to bind one of the fields to a textbox. After setting up the
oledbconnection and dataAdapter and filling the DataSet (ds) I tried this:
TextBox1.DataBindings.Add("text", ds.Tables.Item("MyFile"), "MyField")
...
|
by: pemigh |
last post by:
A while back I imported tables to a new database via Files-->Get
External Data --> Import...
All was well for several months, and then the database started behaving
badly in a couple of ways, including slow (actually speed was fine when
users opened database, then deteriorated after a few minutes of normal
use) and errors when trying to...
|
by: imrosie |
last post by:
Hello all,
My Customer table with Autonumbers used as the customer account number. There are two main forms (Order frm, Customer frm); the Customer's account number displays in a bound control on each. All relationships are tied to this account number, it's an Order entry system. The trouble comes in after adding a new customer.
there's a...
|
by: John Google |
last post by:
Hi,
Access 2002.
I import tables from another database where I only copy the definition
and not the data.
I select the Import Relationships option on the import dialog. After I
do the import the empty tables are there with the correct defintion
and the MSysRelationships table is updated with the relationships
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |