locked
create a table from multiples tables with multiples KEY id RRS feed

  • Question

  • Hello,

    I'm rather new at programming vba and I would appreciate your help with this task. I've created a complexe query that gets information from multiple tables in an ODBC database.  The query creates a new table in a separate database for me to be able to work with.  From the ODBC query, I get multiple rows for the same identifier.  I managed to create a series of queries that will find the first row and copy into a cumulative table, and then other queries that will append the records with the information required from the other rows.

    My problem is that the duplication of the records needs to be based on 2 fields rather than 1, and I just dont know how to do that.  I've used the wizard to create a non-duplicate query where the 2 tables are linked by AccessNumber, but I need the duplication to be based on BlockNumber and StainID.  In my cumulative table BlockNumber and StainID are the KEYS, so I can have the same block many times with different stainIDs.  There are no KEY in my makeup Table since it is recreated each time I run de createTableQuery.   The way it is currently set up, it will allow me to insert "duplicate" new records based on AccessNumber with different StainID only if they are added at the same time.  So if one day I add the record  ABC (blockID) with stainID (12), then tomorrow I won't be able to add ABC(blockID), StainID(14).  Is there a way I can do that?

    I'm using MS Access 2003, and like i mentioned at the beginning, I'm rather new at programming VBA and SQL.

    Thank you for any help you may bring me.


    ChantalBG
    Tuesday, May 3, 2011 8:35 PM

Answers

  • Rather than using a 'make table' query each time, first create an empty table with the relevant columns and then set its composite primary key to the two columns.  Then, instead of executing the 'make table' query, first delete all rows from the makeup table, e.g.

    DELETE *
    FROM [MakeUpTable];

    and then fill it again with an 'update' query based on the external data:

    INSERT INTO [MakeUpTable]
    SELECT [YourQuery].*
    FROM [YourQuery;

    By virtue of the composite primary key only one row will be inserted for each distinct combination of the two columns which make up the primary key.  You'll get a message telling you all rows can't be inserted, at which you should elect to continue.


    Ken Sheridan, Stafford, England
    • Marked as answer by Vanderghast Thursday, May 5, 2011 10:52 AM
    Tuesday, May 3, 2011 10:55 PM

All replies

  • Rather than using a 'make table' query each time, first create an empty table with the relevant columns and then set its composite primary key to the two columns.  Then, instead of executing the 'make table' query, first delete all rows from the makeup table, e.g.

    DELETE *
    FROM [MakeUpTable];

    and then fill it again with an 'update' query based on the external data:

    INSERT INTO [MakeUpTable]
    SELECT [YourQuery].*
    FROM [YourQuery;

    By virtue of the composite primary key only one row will be inserted for each distinct combination of the two columns which make up the primary key.  You'll get a message telling you all rows can't be inserted, at which you should elect to continue.


    Ken Sheridan, Stafford, England
    • Marked as answer by Vanderghast Thursday, May 5, 2011 10:52 AM
    Tuesday, May 3, 2011 10:55 PM
  • Hi Ken,

    I partially followed your advice in that I change the make table to delete all records and append new ones.

    However, I cannot put keys in my MAKE_table, when I do, I dont get all the information transfered from the multiple tables in the ODBC system.  Also, my download time from there is limited so the less i use the better it is.  By downloading all the records separately, I can rearrage them in my CONTINUED_table without affecting the traffic on the ODBC.

    So from the MAKE_table which has many rows from the ODBC for the same record, I use multiple queries to take from each row in MAKE_table to append into 1 record in my CONTINUED_table.  My only problem is that my CONTINUED_table has 2 keys defined to prevent duplication, but there are none in the MAKE_table.  In my Append_query, I managed to link the 2 tables by one of the 2 keys, but I need to intergrate the other as well.

    The Append_query is a non-duplicate query created by the wisard.  My problem is that when the same BLOCKNUMBER comes along with a different STAINID, it wont pick it up to be appended cause there is already a record in the CONTINUED_table with the same blocknumber and a diffent STAINID.

    Do you have suggestions on how I should handle this?


    ChantalBG
    Thursday, May 5, 2011 8:54 PM
  • I'm afraid I'm having some difficulty in understanding what you are saying. I thought your original question concerned how to import one of each row with distinct values in two columns, i.e. where there is a composite primary key of those two columns in the target table, so 'I dont get all the information transfered from the multiple tables in the ODBC system' was what you wanted. Apparently not, however.
    Ken Sheridan, Stafford, England
    Friday, May 6, 2011 8:51 PM
  • Here is a revemp of what I do and what I'm trying to accomplish:

    First I run a query in the ODBC system to collect all the data I need.  I transfer this data to my MAKE_tbl (before I was deleting the table and recreating it everytime, but I tried the delete all records and append instead and it works faster).  The query to get the data from the ODBC system includes 12 tables, which is why I get lots of duplicates for the 2 key field, but thats OK because I need most of that info to be added together in 1 record.  So through a series of 20 or queries, I take the info found in duplicate records and put them in different fields of the same record.

    Data from ODBC system looks like this:
    BlockID; StainID; Doc; Note; ...
    ABC; 12; John Doe; new record; ...
    ABC; 12; Johnny Smith; new record; ...
    ABC; 45; John Doe; new record; ...
    ABC; 45; Johnny Smith; new record; ...
    DEF; 04; Kent Butcher;   ; ...
    ADE; 75; Sam Bower;  ; ...
    ADE; 34; Sam Bower;  ; ...

    Through the queries, I can make the records in my CONTINUED_tbl look like this:
    RecordID; BlockID; StainID; Doc1; Doc2; Note; ...
    1; ABC; 12; John Doe; Johnny Smith; new record; ...
    2; ABC; 45; John Doe; Johnny Smith; new record; ...
    3; DEF; 04; Kent Butcher;  ;   ; ...
    4; ADE; 75; Sam Bower;  ;  ; ...
    5; ADE; 34; Sam Bower;  ;  ; ...

    However if in my next run through the ODBC system I get, the append query will not work because the BlockIDs already exist in my CONTINUED_tbl.  It only adds the records that I dont already have an recordID in the CONTINUED_tbl.:
    ABC; 33; John Doe; new record; ...
    ABC; 33; Johnny Smith; new record; ...
    DEF; 12; Kent Butcher;   ; ...
    DEF; 24; Kent Butcher;   ; ...

    As mentionned before, my append query is based on a find "no match" query in wizard.  In my query my 2 tables are linked by only 1 field (BlockID), and I guess I would like them to be joined by 2 fields (BlockID and StainID) this way it would find everything and transfer automatically.  Since its a query made by the wizard, I dont know how to modify it to make it work for me.  I know I cant put keys in my MAKE_tbl because I will not get all the records from the ODBC system.

    I hope this makes sense.  Thanks for your time


    ChantalBG
    Monday, May 9, 2011 2:50 PM
  • The problem here is that your CONTINUED_tbl is fundamentally flawed in its structure by having multiple Doc# columns.  As the result table of a query this would be fine, and it can be done via a join of two instances of the same base table.  As a base table, however, this is not how data is stored in a relational database.  The structure of the result table of the query on the ODBC data is more correct in that each row represents an entity and each column a distinct attribute of that entity.  Its rows could be appended to a single base table, which would have a composite one of the BlockID, StainID and  Doc columns.  Note (and presumably others) would be a non-key column.

    However, that's not the complete story, as when a table has a composite key, this is generally symptomatic of it modelling a relationship type, which appears to be the case here, the relationship type being a ternary (3-way) one between Blocks, Stains and Docs entity types.  So, these three would each be modelled by a table, whose primary key is referenced by one of the columns in the table modelling the relationship type.

    In this normalized model the sequence of appending data is important, as it is necessary to append unrepresented rows in to the referenced tables Blocks, Stains and Docs first, before a row can be inserted into the table modelling the relationship type.  This is simply a question of appending the relevant columns from the query on the ODBC data; in each case duplicates will be rejected by virtue of the key violations, e.g. an attempt to insert an existing BlockID into Blocks would fail, and an attempt to insert multiple rows with the same BlockID value where this value does not yet exist in the target table, would insert only one row.

    Having inserted any unrepresented rows into each of the three referenced tables, rows can then be inserted into the table which models the relationship type.  Once again, only one instance would be inserted of rows where the values of the three columns which make up the key.

    Once you have the data into correctly normalized tables like this you can return it in the current format of your CONTINUED_tbl if you wish,  If there is a maximum of  two Doc values per BlockID/StainID then this is best done by join.  If the number of Doc values per BlockID/StainID is variable it is best done via a crosstab query.  However, if the need is simply to present the data in this format, then a report with one row per BlockID/StainID value, and within the detail section a multi-column subreport whose column layout is 'across then down' might be more appropriate.  Another method would be to concatenate the Doc vales per BlockID/StainID inot a single string expression and return this as a single column in a query or computed control in a report.  There are various methods of doing this.


    Ken Sheridan, Stafford, England
    Monday, May 9, 2011 4:28 PM
  • Wow that was a big answer, and I hope I understand it corretly.  You're saying that it might be better for me to have more tables (maybe not all 12 of the ODBC system) to be able to solve my problem.

    I can try to create another database with the tables I use from the ODBC system, but I cant see how I will be able to concante the info to show up properly on my reports (reports done in access).

    I already have something like 40 queries to make all my fields show up properly, not sure how I can do that with more records to handle.

    Is there somewhere you would recommend me to go look for info on that?


    ChantalBG
    Monday, May 9, 2011 6:39 PM
  • The number and structure of the tables you have in the Access database should be determined by the nature of the reality which is being modelled.  In essence you have one table for each entity type in the real world, and each column in each table represents an attribute of the entity type modelled by the table.  In the real world there will be relationship types between the entity types.  A relationship type is really just a special kind of entity type.  A relationship type might be modelled simply by a foreign key column in one table referencing the primary key column of another table; this is a one-to-many relationship type, or exceptionally a one-to-one relationship type where the foreign key in the referencing table is also its primary key.  A relationship type can also be modelled by a another table; this is a many-to-many relationship type and can be between two or more entity types.  This works by having foreign key columns in the table which models the relationship type, each of which references the primary key column of one of the other tables.

    Let's take a few simple examples.  If you are running a small business you could have customers, products and orders for those products.  So each of these is modelled by a table.  The relationship between customers and orders is one-to-many (each customer can make one or more orders), so the Orders table would have a foreign key column CustomerID as well as its primary key OrderID and non-key columns such as OrderDate.

    The relationship type between Orders and Products, however, is many-to-many  as each order can be for one or more products and each product can be in one or more orders.  So this relationship type is modelled by a table OrderDetails, which has two foreign key columns OrderID and ProductID referencing the primary keys of the Orders and Products tables respectively.  The primary key of this table is a composite one made up of the two columns OrderID and ProductID.  This table also will have non-key columns such as UnitPrice and Quantity.

    An important aspect of a model made up of a set of normalized tables like this is that there is no redundancy in the tables, i.e. each fact is stored once and once only.  For instance the name and address of a customer is stored in just one row in the Customers table, the name of a product is stored in one row of the Products table and the date of an order is stored just once in a row in the Orders table.  Normalization is the formal process which eliminates redundancies by decomposing a non-normalized table into a set of correctly normalized related tables.  You'll find more information on the subject at:

    http://en.wikipedia.org/wiki/Database_normalization

    but don't worry if you find the concepts a little difficult at first.  By and large normalization is applied common sense.

    So in your case what you need to do is analyse the underlying reality being modelled in terms of its entity types and relationships types, and their attributes.  This is not a task to be undertaken quickly, however, and should be given very careful thought.   Spend time with a pencil and paper drawing it out diagrammatically, with boxes representing the tables and directional lines representing the relationships between them.  This is known, not surprisingly as an entity relationship diagram.  When designing a database from scratch this would give you the basis for designing the tables and relationships in the database, but in your case you will obviously need to take account of the nature of the data available from the ODBC system, so won't have an entirely free hand.  The aim should be to arrive at a set of related tables which model the reality as accurately as possible and without redundancy.  If the model is an accurate representation of the reality you should then be able to query the data and produce reports relatively simply.   It's when the model is not an accurate representation of the reality that difficulties arise and solutions of Byzantine complexity may be needed.

    To recast the data from the query on the ODBC data into the set of related tables will need a series of append queries executed systematically in the correct order.  This essentially means that data is appended to the referenced tables (those on the one side of a relationship) before data is appended to the referencing tables (those on the many side).  I did produce a little demo of this some time ago, which firstly imports data from Excel into a 'master' table and then decomposes it into a set of related tables.  The following are the tables and queries from the demo, which will give you an idea of the general principles involved.  The demo uses a simple international contacts lists so there are countries, regions and cities involved in the addresses.  Also each contact can have more than one employer:

    The query below inserts rows into the Countries table  with unique values of the Country column imported from Excel into the table MasterTable.  This is necessary before rows can be inserted into the Regions table as that table references the Countries table in a many-to-one relationship.

    INSERT INTO Countries (Country)
    SELECT DISTINCT Country
    FROM MasterTable;

    Having inserted rows into the Countries table rows can now be inserted into the Regions table with the query below.  This joins the MasterTable to the newly filled Countries table on the Country columns and inserts unique values from the Region column of the MasterTable and the CountryID column of the Countries table into the Regions table.

    INSERT INTO Regions (Region, CountryID)
    SELECT DISTINCT Region, CountryID
    FROM MasterTable INNER JOIN Countries
    ON MasterTable.Country=Countries.Country;

    Having inserted rows into the Regions table rows can now be inserted into the Cities table with the query below.  This joins the MasterTable to the newly filled Regions table on the Region columns.  The Countries table is joined to the  MasterTable on the Country columns and to the Regions table on the CountryID columns, thus taking account of any regions of the same name in different countries.  The query inserts unique values from the City column of the MasterTable and the RegionID column of the Regions table into the Cities table.

    INSERT INTO Cities (City, RegionID)
    SELECT DISTINCT MasterTable.City, Regions.RegionID
    FROM Countries INNER JOIN (MasterTable INNER JOIN Regions
    ON MasterTable.Region=Regions.Region)
    ON (MasterTable.Country=Countries.Country)
    AND (Countries.CountryID=Regions.CountryID);

    The previous queries inserted rows into the Countries, Regions and Cities tables. Following the insertion of data into the last of these, Cities, it is now possible to insert rows into the Contacts table as this only needs to reference the Cities table, the relevant Region and Country being referenced via the relationships between these three tables.  The query below does this by joining the MasterTable to both the Cities table, on the City columns, and to the Regions table, on the Region columns.  The Cities table is also joined to Regions on RegionID and the Countries table is joined to the MasterTable on Country and the Regions table on Country ID. This is to take account of the possibility of two cities having the same name, but being in different regions,  which themselves could theoretically  have the same name but be in different countries, so that the correct CityID value is inserted into Contacts.

    For simplicity it is assumed that contacts at the same address have unique names.  This might not always be the case, particularly with commercial premises (I once worked with two Maggie Taylors in the same building!).  In such cases, however, there is likely to be some distinguishing value such as Job Title or Department which could be used.

    INSERT INTO Contacts ( FirstName, LastName, Address, CityID )
    SELECT DISTINCT MasterTable.FirstName, MasterTable.LastName, MasterTable.Address, Cities.CityID
    FROM Countries INNER JOIN ((MasterTable INNER JOIN Cities ON MasterTable.City = Cities.City)
    INNER JOIN Regions ON (Regions.RegionID = Cities.RegionID)
    AND (MasterTable.Region = Regions.Region))
    ON (Countries.CountryID = Regions.CountryID)
    AND (Countries.Country = MasterTable.Country);

    The query below inserts rows into the Employers table  with unique values of the Employer column imported from Excel into the table MasterTable.  This is necessary before rows can be inserted into the ContactEmployers table as that table references the Employers table.

    INSERT INTO Employers (Employer)
    SELECT DISTINCT Employer
    FROM MasterTable;

    Having inserted rows into the Contacts and Employers table it is now possible to insert rows into the ContactEmployers table which models the many-to-many relationship between Contacts and Employers.  The query below does this by joining the MasterTable to Contacts on the Address, LastName and Firstname columns and to the Employers table on the Employer columns.  The ContactID values from Contacts and EmployerID values from Employers are inserted into the two columns of ContactEmployers.

    INSERT INTO ContactEmployers (ContactID, EmployerID)
    SELECT Contacts.ContactID, Employers.EmployerID
    FROM (Contacts INNER JOIN MasterTable
    ON (Contacts.Address=MasterTable.Address)
    AND (Contacts.LastName=MasterTable.LastName)
    AND (Contacts.FirstName=MasterTable.FirstName))
    INNER JOIN Employers ON MasterTable.Employer=Employers.Employer;

    The above does assume that the imported data is completely consistent, which might not be the case.  If you'd like a copy of the demo itself, which is made up of an Excel file from which the data is imported and the Access file which decomposes it using the above queries mail me at:

    kenwsheridan<at>yahoo<dot>co<dot>uk


    Ken Sheridan, Stafford, England
    Monday, May 9, 2011 9:16 PM