Sorting Data using Queries

[burkepl]

I have two tables, one contains all of the circuits and then each individual circuit shows all of the related ductbank in the next column. The other table is the opposite, it contains all of the ductbank in the first column and the second column contains all of the ductbank that it goes throught.

I want to be able to enter a circuit and then return the ductbank it goes into. Then show every circuit that is in each of those ductbanks. Then show every duct that each of those circuits are going to.

.............................CircuitA...................
DuctbankA...................DuctbankB..............DuctbankC
CircuitB...CircuitC......CircuitD..CircuitE.........CircuitF

A top down type of diagram is what I'm aiming for, but any way to filter the data is good. I believe I can do it linking queries together, but macros and vba would be an option; however, I am not very familiar with those tools. Any help would be appreciated. Thanks.

 

[lameid]

It sounds like you want to join the tables on DuctBank (the one with the circuit to the one with that is the first column in the second table).

Use your query in a report and group by the circuit. Then see how close it is to what you want.

 

[fneily]

First, you state "The other table is the opposite, it contains all of the ductbank in the first column and the second column contains all of the ductbank that it goes throught." I'll assume you mean "all of the circuits that it goes through."

Do you know about normalization?
You may want to read:
Fundamentals of Relational Database Design

http://r937.com/relational.html

You may be new to Access. Normalization of tables is usually the first step in designing a database.

One circuit contains many ductbanks, and one ductbank can run through many circuits. This is a classic many-to-many relationship which relational databases, such as Access, don't like. So you have to make two one-to-many relationships by creating a junction table. So your tables are:
tblCircuit
CircuitID Primary Key
other circuit info fields

tblDuctbank
DuctBID Primary Key
other DuctBank infor fields

tblConnection
ConnectID Primary Key
CircuitID Common field(foreign key)
DuctBID Common field(foreign key)
Any COMMON fields.

As you can see, tblConnection will create what you want quite easily.

Now if you decide not to correct your database design, well, good luck.

 

[burkepl]

Thanks, I am new to access. I'll look into the normalization but the many to many relationship is exactly what I am talking about. I'll let you know when I come to a roadblock.

 

[burkepl]

Alright, so I've got my CircuitID and DuctbankID and their corresponding tables set up. I'm not sure how to create the tblConnection with the common fields.

example where RWY is the ductbank primary key in the Duct to Circuit table.

RWY CIRCUIT 1 CIRCUIT 2
4DB19B4 4APCK2017002 4APCK2016002
4DB19B5 4APCK2019001 4APCK2020002
4DB21G3 4APCK2020002 4APCK2016002

example where Circuits is the circuit primary key in the Circuit to Duct table. Thanks

Circuits Ductbank 1 Ductbank 2 Ductbank 3
4CHBE1308001 4DB13A2 4DB15B2 4DB16A3
4CHBE1308002 4DB13A2 4DB16B2 4DB17A3
4CHBE1643001 4DB13A2 4DB16B2 4DB16A3

 

[lespaul]

Ok, having fields named
Ductbank 1 Ductbank 2 Ductbank 3
CIRCUIT 1 CIRCUIT 2

breaks the rules of normalization...so your tables AREN'T in the correct form as recommended above. Did you read the fundamentals document linked above?

You have CIRCUIT 4APCK2017002, what else do you need to know about the CIRCUIT? You have DUCTBANK 4DB13A2 what else is there to know about the DUCTBANK?

So for EACH CIRCUIT you will have ONE record in TblCircuit...for EACH DUCTBANK you will have ONE record in tblDUCTS....then you have a table that correlate DUCTS to CIRCUITS.

What is a RWY? How is that related to a circuit?

I would expect that here:
[tt]
Circuits Ductbank 1 Ductbank 2 Ductbank 3
4CHBE1308001 4DB13A2 4DB15B2 4DB16A3
4CHBE1308002 4DB13A2 4DB16B2 4DB17A3
4CHBE1643001 4DB13A2 4DB16B2 4DB16A3[/tt]
that the information under Circuits would match a circuit you listed above (4APCK2017002,4APCK2016002) and there aren't any matches.....(if you are going to post sample data, which helps TREMENDOUSLY, then try to use the same samples all the way through so we can SEE how it really all works together).....

Your last table (connections) will need to have BOTH a SINGLE Circuit and SINGLE Ductbank in a SINGLE record....

HTH



Leslie

Have you met Hardy Heron?

 

[burkepl]

Ok, here's some corresponding data.

Circuits Ductbank 1 Ductbank 2
4APCE1012003 4DB21A4 4DB19I6
4APCE1012004 4DB21B4 4DB19G7

Ductbanks CIRCUIT 1
4DB19I6 4APCE1012003
4DB19G7 4APCE1012004

So for this example, there's multiple ductbanks in the circuit, but only one circuit in the ductbank. I also changed the name from RWY (which is raceway) to Ductbank. I read through the fundamentals document, but still not sure about normalization. For most ductbanks, theres more circuits(up to 52) and for most circuits theres up to four ductbanks. Thanks.

 

[lespaul]

Ok, so you have these tables set up like this:

[tt]
tblCircuits
CircuitID CircuitName {any other information about the circuit?}
1 4APCE1012003
2 4APCE1012004

tblDuctbanks
DuctID DuctName {any other information about the duct?}
1 4DB19I6
2 4DB19G7

tblDuctCircuits
DCID CircuitID DuctID
1 1 1
2 2 2
[/tt]

so DuctCircuit where DCID = 1 has circuit 4APCE1012003 in DUCT 4DB19I6; where DCID = 2 has circuit 4APCE1012004 in DUCT 4DB19G7.

each circuit has ONE record in tblCircuit. Each DUCT has ONE record in tblDuct. tblDuctCircuits tells you the "junction" of the two.

Leslie

 

[burkepl]

As it is, each circuit has one record in tblCircuit and each duct has one record in tblDuct. The other information contained in the other fields is the information that I'm trying to correspond to. It would be very labor intensive to organize each case of one circuit and one ductbank, unless there's a way for access to do it. I have about 200 ductbanks and 450 circuits. I'm trying to figure out the routing for each circuit, and then all of the circuits that are related to the first one because it's in a similiar ductbank.

 

[fneily]

To further expand on LesPaul's example using your data.
You have:
Circuits Ductbank 1 Ductbank 2
4APCE1012003 4DB21A4 4DB19I6
4APCE1012004 4DB21B4 4DB19G7

This would look like this:
tblCircuits
CircuitID CircuitName {other circuit information)
Circuit1 4APCE1012003
Circuit2 4APCE1012004

tblDuctBanks
DuctID DuctName {any other information about the duct?}
Duct1 4DB21A4
Duct2 4DB21B4
Duct3 4DB19I6
Duct4 4DB19G7

tblDuctCircuits
DCID CircuitID DuctID other common fields
DC1 Circuit1 Duct1
DC2 Circuit1 Duct3
DC3 Circuit2 Duct2
DC4 Circuit2 Duct4
DC5 Circuit3 Duct1
etc.

So you can now see all the DuctBanks for Circuit1.
You can get all the circuits related to Duct1.

It's not that "intensive".

 

[burkepl]

I started organizing the data like in the last post, this is an example of what I did.

DCID Circuit ID Ductbank ID
10 Circuit 1 Ductbank 284
11 Circuit 1 Ductbank 204
12 Circuit 1 Ductbank 130
13 Circuit 1 Ductbank 62

I had to manually go through and enter each circuit 1 under each ductbank ID, is there a way to automatically do this considering that there are hundreds of circuits? Thanks

 

[lespaul]

I guess that depends on exactly what you already have entered....your OP states that you have two tables that already have information...if you provide some sample data from those two tables we may be able to help you come up with a query to populate your new tables.



Leslie

Have you met Hardy Heron?

 

[burkepl]

tblDuctbank

Ductbank ID Ductbank CIRCUIT 1 CIRCUIT 2
Ductbank 5 4DB08C1 4PPAK2008013 4PPAK2008015
Ductbank 6 4DB08C2 4PPAK2008021 4PPAK2008026


tblCircuits
Circuit ID DBCircuitsLeft Ductbank 1 Ductbank 2
Circuit 240 4PPAK2008031 4DB08C1 4DB09A1
Circuit 241 4PPAK2008032 4DB08C1 4DB09A1


tblTogether

DCID Circuit ID Ductbank ID
32 Circuit 240 Ductbank 17
24 Circuit 240 Ductbank 5
34 Circuit 241 Ductbank 17
26 Circuit 241 Ductbank 5
33 Circuit 255 Ductbank 17
25 Circuit 255 Ductbank 5

 

[lameid]

burkepl,

That looks like what you have now...

What does the data look like in the tables you started with (the ones that have all the data in them)?

 

[burkepl]

The information is still contained in those tables (tblDuctbank and tblCircuits). The only important information contained in each table is for tblCircuits the ductbanks numbers and for tblDuctbank the circuit numbers.

+Ductbank ID Ductbank CIRCUIT 1 CIRCUIT 2
Ductbank 5 4DB08C1 4PPAK2008013 4PPAK2008015

-DCID Circuit ID
24 Circuit 240
25 Circuit 255
26 Circuit 241
27 Circuit 239
28 Circuit 225
29 Circuit 224
30 Circuit 223
31 Circuit 216

Under the ductbank ID is a + button you can click that displays the above information. The same is true for the Circuits table. I entered all of this by hand.

 

[lameid]

I think fneily's last post has the right structure.

So long as the CircuitName column in tblcircuit has a unique index on it, you should be able to append to it from your existing table. Append to tblcircuit from your existing table first using field Circuit 1 and then circuit 2. You should expect to see that a number will not be appended due to

Do the same with the duct table (make DuctName unique in tblDuctBanks) and append to it from your Ductbank 1 and Ductbank 2 fields.

From here, you can join from the new tables to the original table on the name fields but never both of the same type. You can use this to append to the tblDuctCircuits table. You will of course have to have several queries joining on each set of fields to append all the records.

I hope this makes sense.