SQL Query to Create 3 Tables from One Table.

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.
Who is Participating?
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

billcuteAuthor Commented:

In defining the 3 tables, PK / FK which one should be an "Autonumber" field or "Number" field?
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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

billcuteAuthor Commented:
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?
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.
billcuteAuthor Commented:
I have tested your SQL Query, it generated duplicates on all the fields. I need to generate DISTINCT recordset.
billcuteAuthor Commented:
I need to generate DISTINCT recordset for the Address.
billcuteAuthor Commented:
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".
billcuteAuthor Commented:
I have not heard from you for a while. Is everything OK?
Could you please identify the Duplicates from your First Post, and how did you do that?

billcuteAuthor Commented:

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.

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
billcuteAuthor Commented:
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.
billcuteAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.