SQL Query to Create 3 Tables from One Table.

Posted on 2004-10-21
Last Modified: 2006-11-17
I have a long table (TableA), The "Address" field of TableA is duplicated.

I want to be able to break TableA into 3 smaller DISTINCT tables (TableB, C and D).

The 3 new tables will then be linked to one another in a child / Parent relationship in such a way that these 3 table resembles the original TableA (without mismatching the data).

See the configuration below:

AddressID  BlockNo  LotNo  Address  OrderNo   OrderDate  OStatus   PermitNo   PermitDate   PStatus
     1          1324       2       xyz          111         10/01/04   Active      3111        10/09/04    Approved
     2           525        14     zzz          112         10/02/04   Active      3112        10/04/04    Approved
     3          12          101    yyy          113         06/14/04   Expired                                    Disapproved
     4          1324       2       xyz          114        01/12/03    Expired     2119       07/04/03   Approved
     5          525         14     zzz          101         10/12/02   Expired     1101       12/02/02   Approved

AddressID  BlockNo  LotNo  Address  
     1          1324       2       xyz        
     2           525        14     zzz          
     3          12          101    yyy          
     4          1324       2       xyz          
     5          525         14     zzz          

AddressID   OrderNo   OrderDate  OStatus  
     1            111         10/01/04   Active      
     2            112         10/02/04   Active      
     3            113         06/14/04   Expired    
     4            114        01/12/03    Expired    
     5            101         10/12/02   Expired    

AddressID  PermitNo   PermitDate   PStatus
     1          3111        10/09/04      Approved
     2          3112        10/04/04      Approved
     3                                            Disapproved
     4          2119       07/04/03      Approved
     5          1101       12/02/02      Approved


You will notice from TableA that:
AddressID #1 and 4; 2 and 5 are repeated twice. The BlockNo, LotNo and Addresses are the same. This is "perfectly normal, it ONLY shows that the Addresses in question each applied for 2 different Permits at different dates.

THE COMBINATION of the BlockNo, LotNo and Address make up a whole address.

The OrderNo, OrderDate, OStatus, PermitNo, PermitDate, and PStatus are different and unique on their own.
Question by:billcute
    LVL 11

    Accepted Solution

    First, create your tables, PK is primary key and FK is foreign key:

      AddressID  (PK)

      OrderNo (PK)  
      AddressID (FK)

      PermitNo  (PK)  
      AddressID (FK)

    Then create your queries to insert into the tables:
        INSERT INTO tblAddress (AddressID, BlockNo, LotNo, Address)
        SELECT  AddressID, BlockNo, LotNo, Address FROM TableA

       INSERT INTO tblOrders (OrderNo, AddressID, OrderDate, OStatus)
        SELECT  OrderNo, AddressID, OrderDate, OStatus FROM TableA

       INSERT INTO tblPermits (PermitNo, AddressID, PermitDate, PStatus)
        SELECT  PermitNo, AddressID, PermitDate, PStatus FROM TableA

    LVL 4

    Author Comment


    In defining the 3 tables, PK / FK which one should be an "Autonumber" field or "Number" field?
    LVL 11

    Assisted Solution

    tblAddress:  AddressID  (PK) Autonumber

    I'm not sure if the orderNo and PermitNo can be auto generated. Looks like you should autogenerate the OrderNo, and maybe add an id field to the Permits table with a unique index (no duplicates) for PermitNo like so:

    tblOrders: OrderNo (PK) Autonumber

      id        Autonumber
      PermitNo  (unique index)  
      AddressID (FK)

    You would not have to insert values into the autonumbered fields for the SQL statements.
    LVL 4

    Author Comment

    Thanks for your info: By default, the OrderNos and ermitNos are generated by the Local Authorities. and it looks like this:

    OrderNo                               PermitNo
    SDQ-043-04                          311396
    SCQ-112-02                          296041

          For the address:
          BlockNo            LotNo                      Address
    (1). 10321;                1                         123-14 118th Street
    (2). 10321;                12                       123-16 118th Street
    (3). 10321;                 3                        123-18 118th Street
    (4).  1223;                  1                        220 Church Street.
    (5).    124                   24                      1025 12th Avenue
    (6). 11837                   3                        109-22 Park Avenue

    As you can see from above:

    BlockNo is duplicated
    LotNo    is Duplicared
    Address   is NOT duplicated

    The combination of BlockNo, LotNo make up a unique property identification in any given localty.

    Usually, in looking for a property address in a street, the BlockNo LotNo combined identify a specific property Address. Based on the above, A unique BlockNo and a Unique LotNo (combined) can be Autogenerated and perhaps made out as Natural keys.

    It is possible to have a table consisting of autogenerated BlockNo (from 1 - 100000)
    while the LotNo can range from (1 - 2999).

    I dont know how to Autogenerate numbers. Do you know how?
    What do you think?
    LVL 11

    Expert Comment

    1. Keep AddressID in the Address table as a Primary Key that is autogenerated.
    2. Your clustered index would be a combination of the BlockNo and LotNo. Unfortunately, autonumbers increment by one for each record, simple as that.
    You will have to write code in the form or a module that grabs the next available LotNo for a particular BlockNo. This could be accomplished with a function that you pass in BlockNo and it returns the next available LotNo. The same is true for the next available BlockNo. Most likely you would allow the user to type in a BlockNo or click a button to autogenerate the next BlockNo.

    I'm probably not explaining myself well, but to answer your question, autonumbers alone will not solve your problem.
    LVL 4

    Author Comment

    I have tested your SQL Query, it generated duplicates on all the fields. I need to generate DISTINCT recordset.
    LVL 4

    Author Comment

    I need to generate DISTINCT recordset for the Address.
    LVL 4

    Author Comment

    Jokra_the_Barbarian ,
    I know how to accomplish your#2 raised question. I just need to accomplish my desired objectives as stated in my poster. Right now, I have been able to obtain a distinct list of address with using "DISTINCT".
    LVL 4

    Author Comment

    I have not heard from you for a while. Is everything OK?
    LVL 27

    Expert Comment

    Could you please identify the Duplicates from your First Post, and how did you do that?

    LVL 4

    Author Comment


    Look closely at at TableA, B and C in my poster above: You will notice the followings:

    (1). BlockNo, LotNo and Address fields are "duplicated.

    Desired Goal for TableC:

    I want to create a unique, combination of NON-DUPLICATED fields for BlockNo, LotNo and Address fields. (This I have been able to achieve).

    (2). There are times when NOT all the fields are filled (see TableA item #3 - PermitNo, and PermitDate are NOT filled. The PermitStatus is filled.

    Creating TableD FROM tableA with Blanks (or Unfilled fields) can be difficult, moreso that I intend to join Tables B, C, and D together in a Child / Parent Relationship.
    PermitNo fields are Unique and NON-DUPLICATED fields but they are somtimes left blank if Permit Numbers have not been issued (may be pending approval).

    Disired Goal:
    I want to be able to create TableD FROM TableA WITHOUT Duplicates.
    Although Table A does not show duplicated Order numbers, but it is not unique. The orderNo can be used more than once if the Address request for more than one job. The Locaal authorities in thios case one OrderNo for the thre job for a single address.

    Desired Goal For Table B:
    I want to create a Unique and non duplicating OrderNo. This Order number can point to related address in Table C and related "JobNos" which are not referenced in this poster.
    Of course in a child/ parent relationship.

    The way, :Jokra_the_Barbarian's simulated tblAddress, tblOrders; and tblPermits with (PK) and (FK) are quite good but when I tried his SQL Statements for those tables , each table created came back with "Duplicated" Results.

    LVL 12

    Assisted Solution

    If Jokra's suggestion does what you want EXCEPT for the duplications, then all you have to do is modify his SQL statements to include the DISTINCT operator

        INSERT INTO tblAddress (AddressID, BlockNo, LotNo, Address)
        SELECT DISTINCT AddressID, BlockNo, LotNo, Address FROM TableA

       INSERT INTO tblOrders (OrderNo, AddressID, OrderDate, OStatus)
        SELECT DISTINCT  OrderNo, AddressID, OrderDate, OStatus FROM TableA

       INSERT INTO tblPermits (PermitNo, AddressID, PermitDate, PStatus)
        SELECT DISTINCT  PermitNo, AddressID, PermitDate, PStatus FROM TableA
    LVL 4

    Author Comment

    I think my major problem might be that I had just found that the database has DUPLICATES.
    I inherited the database from someelse who did not what she was doing and I am now trying to cleanup her mess.

    The Permits are supposed to be UNIQUE but it was not set in the Table Design. The data is about 20,000 records.
    I would have used the PermitNo only as filter but I want to be vety careful in handling it.

    And the only way to figure that out is by using a filter described below:

    So what I want to do is to remove the duplicates first.

    To do that, I have to filter the query by (cboConnType) - ConnID which has a list of items in the list as follows: We can use the ConnID [cboConnType. Column (0)] as a filter instaed of cboConnType.Column (1).

    ConnID         ConnType (Text)
        1               Tap
        2               Tap / Plug
        3               Plug
        4               Wetconnection
        5                Wetconnection / Plugs

    The above are list of items in the cboConnType. The data can therefore be identified by:

    (i). cboConnType                  (Filter #1)
    (ii). PermitNo                       (Filter #2)
    (iii). BlockNo                        (needed only to compare with data for certainty)
    (iv). LotNo                           (needed only to compare with data for certainty)
    (v). Address                         (needed only to compare with data for certainty)
    (vi). EmplyID                         (needed only to compare with data for certainty)

    You will notice that item # (i) and (vi) are missing from TableA, they are actually in the table but not shown in my question due to space problem.

    The reason why it is neccessary to use the cboConnType is due to the fact that an address can have several jobs connected to it. These jobs are identified under cboConnType

    BUT ONCE I get rid of the duplicate, I will set the PermitNo NOT TO ALLOW DUPLICATES.


    Case 1:
    Looking at TableA in my original poster question, please assist in composing an expression to pull compare the data identify the data and ensure that none of the category is repeated twice with a PermitNo.

    Case 2.
    If any of the cbotype is repeated more than once with the same PermitNo, then identify the data and isolate the record into a separate table tblduplicate.

    After isolating the duplicates in tblduplicate for me to examine, I will then need a query to delete the duplicates from the original table (TableA).

    Once I have rid the duplicates from the database, I can then proceeding to other things.

    I need getting rid of the duplicate first.
    LVL 4

    Author Comment

    I have decided to close this poster and split the points 75 / 25 btw  "Jokra_the_Barbarian" and  "pique_tech". I will post new questions very soon to address the unresolved issues. Thank you guys for your help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Course: MongoDB Object-Document Mapper for NodeJS

    NodeJS (JavaScript on the server) is awesome, but some developers get confused about NoSQL when it comes to working in Node with MongoDB (NoSQL database). Do you need a better explanation of how to use Node.js with MongoDB? The most popular choice is the Mongoose library.

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    913 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now