Query for 4 types of variables in one table 1

[gabber4858]

I have a table that I am trying to account for 4 different ways a wire connection can be made. The wires need to be "cross connected" in different locations to complete the circuit. I currently have a combo box to select the Start type, and End type of the connection and combow boxes to get the appropriate ID from the related tables. I am new to SQL queries and am looking for advice on how to set up my query.

My connection types are Jack to Pair, Pair to Port, Pair to Pair, and Jack to Port. My table looks like this:

ID start_type start_id end_type end_id
1 Jack 1 Pair 10
2 Pair 20 Pair 30
3 Pair 40 Port 50
4 Jack 2 Pair 80
5 Pair 90 Port 120

So... I want to be able to see the connection start to finish, or where there are "breaks".

I have gotten this far, but I am starting to get confused on the rest of the statement:

SELECT Jacks.JackNumber, Pairs.PairNumber
FROM Jacks, Pairs, CrossConnects
WHERE (((CrossConnects.StartType)='Jack') And ((CrossConnects.EndType)='Pair')
And ((Jacks.ID)=CrossConnects.StartID)
And ((CrossConnects.EndID)=Pairs.ID))
ORDER BY JackNumber;

I am not against scrapping this design out for a more efficient way, and any help is appreciated. Thank you in advance!!!

 

[SkipVought]

hi,

So... I want to be able to see the connection start to finish, or where there are "breaks".

How is a "break" defined?

Also, you have THREE tables: Jacks, Pairs, CrossConnects.

What is the structure of each?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[gabber4858]

The "breaks" are where there is no cross connect to complete the circuit. There are multiple locations where the cross connect will connect a Jack to a Pair in say..Closet 3, the other end of the Pair is in Closet 1, and a cross connect connects that Pair to a Port. I want to be able to see where all of the complete circuits are (where there is a Jack on one end, and ultimately a Port on the far end). I also want to be able to find where I need to make another cross connect to complete the circuit. I forgot to mention that I have a few more tables, Site and Closet.

Sites:

site_id site_name
1 N. Campus


Closets:

closet_id closet_name site_id
1 Closet1 1
2 Closet2 1
3 Closet3 1


Jacks:

jack_id jack_name closet_id
1 Jack1 5
34 Jack34 20


Pairs:

pair_id pair_name start_closet_id end_closet
1 1 1 5
100 25 5 20
. . . .
854 854 1 20

Ports:

port_id port_name closet_id
1 1-24 1

I guess my questions are can I keep stacking "AND" statements, can I do a loop statment, or do I need to learn more about VB? Or is their something even better than I don't know about? Thanks SkipVought for looking at this!!!

 

[MajP]

I am not sure if I fully understand, but if I am working with "chains/circuits" I use a self referencing table. What I

really only need to know is the item ID and its parent ID (The thing it connects to).

If the equipment is only used once then I can make this in one table. If the equipment is repeated in multiple chains then I

may need a seperate Junction table.

So lets assume it is only used once then my table is simply something like (of course you could have many other fields that

are unique to a piece of equipment such as cost, color, size...)


tblEquipment
equipmentID
typeID
parentID

ID TypeID ParentID
1 Jack
2 Pair 1
3 Port 2
4 Jack
5 Pair 4
6 Pair 5
7 Port 6
8 Pair 5
9 Pair 8
10 Port 9

The above data would show the circuits (1-2-3) and a branched circuit

4 - 5 - 6
|
|- 8 - 9

You can build self referencing nested queries, but that is really complicated. However, you can build easy recursive vb

function calls and use them in queries. If that is of interest I can demo that.

With this structure that great thing is that you can use a tree view to show your data.

http://tinypic.com/r/jkzh52/4

Although the treeview code is complicated, it is all encapsulated into a class module. It only requires the user to write one line of code to create this or any treeview. The only requirement is to get the query correct which is always exactly the same.
Here is the demo

http://www.4shared.com/document/8pyyoWt-/Circuit.html

 

[gabber4858]

I love this approach! You are correct, I can only use the equipment once a connection is made. I want it so that the only way you use the equipment again is if you remove the existing connection.

I downloaded your example, but it I get an error stating it can't find the project library or class. Anyways, I am going to try this new approach and redesign my table.

So now you leave me with more questions.... Is it possible for you to tell me how to fix the missing class error? Honestly, it will take me awhile to even begin to understand your VB programming, but worth it! If not, can I make a report that shows this in a Flat Layout? Good stuff for this weekend!

 

[MajP]

The tree view is an ActiveX control. It is part of
MSCOMTCTL.OCX.
Normally you go to the vbe and select
Tools-References and then browse in Windows/System32 and find the .OCX. Add it as a reference.

If you do not have it

http://www.microsoft.com/downloads/...98-51D0-41C1-BB14-64662F5F62FE&displaylang=en

Once you get the table structure the way you want, can you post your tables? That will help.
I can then demo how to do a recursive function to return you circuits in a "flat layout" as well.

The code is encapsulated within a class module, so you really do not need to understand what is under the hood. You really only need to understand the methods and properties that the class exposes. Once you get your table structure I can talk you through building the query. Creating the tree view then only requires a line of code.

 

[gabber4858]

I added my database to:

http://www.4shared.com/document/1Ss2JMPJ/_2__Circuit2.html

I have to admit... I am pretty frustrated at this point. Ignore the queries and the forms, I am still trying to figure some stuff out.

MajP, please tell me if I need to redo my tables (especialy Pairs) to make this work. Right now, I have it set up like:

pair_id
pair_name
closet_id_1 (Parent)
closet_id_2 (Other Parent)

Like I said... I am starting to over think, and confuse myself. Now my question is, (mainly after failing to create an Inner Join for Xconnects) is how to get the information based on criteria. I have been tring off and on today to get my Joins to look at both columns based on criteria, but get some funky results. My Xconnects table now looks like:

xconn_id
site_id
closet_id
start_type
start_id
end_type
end_id

I run into issues when the start_type and end_type have the same type in seperate rows. I get my query to almost work, but I can't figure out how to search both columns if "Jack" is in end_type OR start_type. Looking forward to see how to fix this.

I now realize I have more things to work out than my initial question. I'll repost a more logical question once I regain some sanity

 

[MajP]

You have a couple of ways you could do this, but regardless of how you do it I would ensure that every (jack, port, pair) has a unique idea that unique amongst all items. There is a reason for this because you bring them all back together into a big query where items from 3 tables get treated as "connectors".

1)Approach 1. Leave them in seperate tables but use a text field for each id. Also give the field a unique name not "id": port_ID, Jack_ID, and pair_ID. You may have to use some code to create the IDs.

A. So for the jack table I made my ids
J100,J101...J117
If I added a new one either manually or with code it would be J118
For the pairs they are
PA1, PA2,...PA101.
(If I had the time I would have made them PA1001 to PA1101 so that they could sort normally)
For the Ports I used
PO10...PO16

B. A modification to this approach would be to use a "natural key" instead of coming up with these artificial keys. If you had unique names for the pairs (or if you can come up with unique names for the pairs) a better solution would be to use the unique names as the keys for jacks, pairs, and ports. This would really make things easier to see and more intuitive. Then you would have these PKs for jacks:

21-1
21-2
21-3
21-4
21-5
21-6
21-7
...
and ports
01A0201
01A0202
02B0101
02B0105
...

Then in your tables you can see that 21-1 connects to 01A0201 without the need for a more complicated query.

Unfortunately Pairs do no not have unique names.

C. Now the trick is to bring everything together into a union query. So that all items appear in one table.

SELECT
 "Pair" as item_type,
 pair_id as item_id, 
 pair_name as item_name,
 closet_id_1,
 closet_id_2
FROM 
 pairs;
UNION
SELECT
 "Port" as item_type,
 port_id, 
 port_name,
 closet_id as closet_id_1,
 null as closet_id_2
FROM 
 ports
UNION SELECT 
 "Jack" as item_type,
 Jacks.jack_id as item_id, 
 Jacks.jack_name as item_name,
 closet_id as closet_id_1,
 null as closet_id_2
FROM 
 Jacks;

Now this query can be joined to your connection table. Although, I am suggesting a different approach then you, this concept would be the answer to the approach you where trying. In your table xConnects table you would connect Start_id to this query, and End_Id to another instance of this query to pull in all of the details.

2) Approach 2 would be to put all items into one single table. This simplifies a lot of things. It is not perfect because there are a few different fields between ports, jacks, and pairs. But it may make things much easier. In this case I could use an autonumber for a primary key because I can ensure that everything would have a unique id. My single table looks like

tblConnectors
item_id (PK and could be a simple autonumber)
item_type ("Jack", "Port", or "Pair")
item_subType (for ports you could have "Analog" or "Digital")
closet_1_ID (this is just the closet id for jacks and ports and the closet 1 ID for pairs)
closet_2_ID (this is null for jacks and ports and the closet 2 ID for pairs)

The union query above, is basically creates the table above without but just starting with seperate tables.

3) My connection table is simply
xconnID (a key representing a connection)
item_id ( a foreign key to a port, jack, or pair)
start_id (a key that shows which item the item_id is connected to)

Here is an example:
xconn_id item_id start_id
1 J100
6 PA10 J100
7 J114
8 PA77 J114
9 PA50 PA77

This shows that J100-PA10
and J114-PA77-PA50

Do not need any other information. Site, closet information would come from the query related through the item_id.

4) If the single table concept would work, then you can even do away with the connection table. You would just add the field
start_ID to the tblConnectors.

This demo use the Union query approach. Even if the tree view does not work take a look at the union query and then a look at the connection data query.

http://www.4shared.com/document/TN4djRmT/MajPCircuit3.html

Bottom line. There are several possible approaches, but whatever way you go somehow you need to bring all the connectors into a single "table" with unique IDs. Either a union query or start with everything in a union query. This is the same if you go with your approach of showing start and ends of connections, or as I do and just show each item and where it connects.

So it is your choice for the starting point.
1)Put jacks, ports, and pairs into a single table.
2)Keep jacks, ports, and pairs in own table with keys unique across the tables.

 

[MajP]

Under the Bottom line:
It should say "Either a union query to bring all connectors together or start with all connectors in a single table.

 

[gabber4858]

MajP,

Thank you for your patience! Your example will keep me occupied for awhile. Making the ID unique to all tables makes sense. This was causing me so much grief in my original design. I am trying to emulate what I do in real life, and database design is by no means a simple task! I will keep trying this out until I get it to make sense in my head. Thanks again!

 

[MajP]

I would recommend the following for the keys. There are several faqs on how to

increment your own keys. But this is what I would use, because you have to manipulate

the keys.

for port_id I would use PO1000 to PO9999
for jack_id I would use JA1000 to JA9999
for pair_id I would use PA1000 to PA9999

Because I may have to do string manipulation it is nice to be consistent with 2 letters

for all keys. The reason I start with 1000 and not 0000 is so I can sort the keys if

that is needed. If you instead would do PA1, PA10, PA20, PA100, PA3 then the sort

order becomes (because they are strings):
PA1, PA10, PA100, PA20, PA3

Also after looking at it, I would keep your seperate tables and add information to a

connection table. My tblXconnect has only three fields

xconn_id item_id start_id
1 JA1001
6 PA1001 JA1001
7 JA1014
8 PA1077 JA1014
9 PA1050 PA1077
10 PA1051 PA1050
11 PA1052 PA1051
12 PO1000 PA1052
13 JA1002
14 PA1002 JA1002
15 PA1003 PA1002
16 PA1004 PA1003
17 PO1001 PA1004

Where each item that is connected to another item gets added to the table. The start_id

is what the item connects to.

With the above table structure. I can display the circuits as follows.
[

http://tinypic.com/r/2lnyano/4

]
I included the Site,Closet, and connector name.
Again this requires only one query and a single line of code

Although you do not have to use the treeview, it demonstrates that with this simple

data structure you can create hierrarchial information. If it can be displayed in a

tree you can display it other ways as well with some work.

The treeview has some additional benefits.
You can use drag and drop to create/edit circuits. (requires some additional code to

modify the tables).
You can click on an item in the tree and pick something to connect to the item.
Can you get the treeview to work?

 

[MajP]

here is the image:

 

[MajP]

Here is the update

http://www.4shared.com/document/kMGSC-1H/MajPCircuit4.html

This is how I think I would do it.

Here is what I would then add
1)Form/s to enter new Jacks, ports, and pairs. This would need the code to make the primary keys.
2)Some means of adding an item to the tblXconnects. If I was using the treeview I would have a button to start a new circuit which would allow me to pick a connector and add it to the table and in turn show in the tree. Probably requires an input query. I would also finish the node click procedure. When you click on the node it would prompt if you want to add a new connection to the current node. You would get a form to choose the connector and then it would add it to the table and show in the treeview.
3) develop some vb functions to return circuits. You then can use these in queries to show similar circuit data shown in the tree view.

If you get the tree to work you can demo the drag and drop. If you really like bells and whistles you can even add images to the treeview. You can have a small jack, port, and pair icon show in the tree next to the item.

Once you figure out how to make one tree view query, they are all identical. So you could also have queries for each site. Then have a control to pick a site and load the tree with the query for that site.