Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


SQL Query to Create 3 Tables from One Table.

Posted on 2004-10-21
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 11

Accepted Solution

Jokra_the_Barbarian earned 300 total points
ID: 12377838
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


Author Comment

ID: 12379506

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

Assisted Solution

Jokra_the_Barbarian earned 300 total points
ID: 12381474
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.
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.


Author Comment

ID: 12386822
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

ID: 12387219
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.

Author Comment

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

Author Comment

ID: 12387943
I need to generate DISTINCT recordset for the Address.

Author Comment

ID: 12391897
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".

Author Comment

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

Expert Comment

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


Author Comment

ID: 12452369

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

pique_tech earned 100 total points
ID: 12476906
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

Author Comment

ID: 12477843
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.

Author Comment

ID: 12564095
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.

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

597 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