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

x
?
Solved

SQL Query to Create 3 Tables from One Table.

Posted on 2004-10-21
14
Medium Priority
?
240 Views
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:

TableA:
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

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

TableB
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    

TableD
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
___________________________________________________________________________

NOTE:

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.
0
Comment
Question by:billcute
[X]
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
14 Comments
 
LVL 11

Accepted Solution

by:
Jokra_the_Barbarian earned 300 total points
ID: 12377838
First, create your tables, PK is primary key and FK is foreign key:

tblAddress
  AddressID  (PK)
  BlockNo  
  LotNo  
  Address  

tblOrders
  OrderNo (PK)  
  AddressID (FK)
  OrderDate  
  OStatus  

tblPermits
  PermitNo  (PK)  
  AddressID (FK)
  PermitDate  
  PStatus

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

0
 
LVL 4

Author Comment

by:billcute
ID: 12379506
Jokra_the_Barbarian,

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

Assisted Solution

by:Jokra_the_Barbarian
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

tblPermits
  id        Autonumber
  PermitNo  (unique index)  
  AddressID (FK)
  PermitDate  
  PStatus

You would not have to insert values into the autonumbered fields for the SQL statements.
0
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.

 
LVL 4

Author Comment

by:billcute
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?
0
 
LVL 11

Expert Comment

by:Jokra_the_Barbarian
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.
0
 
LVL 4

Author Comment

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

Author Comment

by:billcute
ID: 12387943
I need to generate DISTINCT recordset for the Address.
0
 
LVL 4

Author Comment

by:billcute
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".
0
 
LVL 4

Author Comment

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

Expert Comment

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

jaffer
0
 
LVL 4

Author Comment

by:billcute
ID: 12452369
jaffer,

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.

NOTE:
(i).
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.
(ii).
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.
*****************
(3).
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.

0
 
LVL 12

Assisted Solution

by:pique_tech
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
0
 
LVL 4

Author Comment

by:billcute
ID: 12477843
I think my major problem might be that I had just found that the database has DUPLICATES.
Note:
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.


Strategy:

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.

Case3
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.
Thanks.
0
 
LVL 4

Author Comment

by:billcute
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.
Bill
0

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