I've always been bad with relationships. Ask my ex! Please help!!

[danbo]

Good day to all. I am working on a DB that was started from a template. After learning what I could in the short amount of time I've had I found that it is not "normalized" and needs some help. I'm now starting from scratch and have had a lot of help from some good people . The last who tried walking me through this but I'm stuck and I think he's frustrated. Please see below.



This is for a small telephone company. I need to separtate data as depicted in the images attached. Basics are this:

Each customer has equipment and each piece of equipment has a cable pair.

Can someone look at my relationships and tell me what I can do to make this work?

#1 As it is I get an error when I try to create a form using the current query. I can't add data to the form unless it's a form with two subforms. Is this the correct method for my layout?

#2 When I use a form with two subforms the data is collected properly in their respective fields but I can't figure out the correct relationships to add so that I can run a query with multiple tables. (comes back blank)

#3 Because I can't check via a query I can't tell if this is going to "join" my data properly.

Any help would be rewarded with lot and lots of thank yous!!

[June7]

Cable pair is associated with Equipment, Equipment associated with Customer. I don't see need for customer ID in CablePair.

Can each customer have more than one equipment? Each equipment will have only one cable pair?

[danbo]

Cable pair is associated with Equipment, Equipment associated with Customer. I don't see need for customer ID in CablePair.

Can each customer have more than one equipment? Each equipment will have only one cable pair?

Yes. For the use of this DB we are using one of each. So how do I associate the cable pair?
Customers to equipment by customer I'd
Cable pair to customer by customer ID? I kind of need the cable to be associated with the equipment in case the customer disconnects.
So, customer to equipment by customer ID
equipment to cable by equipment? Is that correct?

[June7]

Took another look at the data structure. Not even sure the Cable Pair table is needed.

Will each equipment have only one cable pair?
Can each cable pair be associated with only one equipment?

Since the only data in Cable Pair is CablePair (a number), and if answer to above both yes, why not just have this field in Equipment?

If Cable Pair is just a lookup table for a model identifier and multiple equipment can have the same model, then the table is needed.

[danbo]

Took another look at the data structure. Not even sure the Cable Pair table is needed.

Will each equipment have only one cable pair?
Can each cable pair be associated with only one equipment?

Since the only data in Cable Pair is CablePair (a number), and if answer to above both yes, why not just have this field in Equipment?

If Cable Pair is just a lookup table for a model identifier and multiple equipment can have the same model, then the table is needed.

Hmmm, that's a thought. I'm not sure how to do that. If for instance a customer has cable pair 22 going to their house and it is connected to equipment rst3-1-1-3 and it would always stay like that it may work. But what if the equipment is changed or the cable pair is changed then I would lose the existing data right?
Another problem:
A customer at 123 main street is on equipment rst1-1-1, cable pair 8. He moves out and the company abandons the cable at that address but uses the equipment for another pair at another house. I still want the record to reflect that the cable is still at the original house.
Likewise, a customer is on cable 4, equipment let03-03-03 and their cable goes bad and needs to be switched to a new pair?

Giving the above, what would you suggest?

Thanks for the help too by the way!!

[June7]

Okay, cable and equipment and customer can be any combination. If cable and/or equipment is not with a customer, why would item need to be associated with cable or equipment? Aren't they essentially returned to stock and available for reassignment? If the association is critical, think I am seeing need for a 3-way junction table. Don't think I've ever encountered this situation.

Do you care about history of assignments?

[danbo]

Yup, they have to be maintained. Reason is this. Without keeping track of it we will lose it. Imagine a company that has 8000 pieces of product scattered around a 18 sq. mile area. Without documenting the location we wouldn't know where they are. Worse even, say we know that cable pair 1094 is available but don't know what road it's on or the address that it is going to or where it could be re routed to. Also we have about 4000 pieces of equipment, (outlets to plug into at the office) by knowing what equipment is hooked up to a specific cable we know where it can feed. Thing is, either of them can change at any given time. Rst1-1-1 feeding pair 44 at 123 main could very well look like rst1-1-1 cable 64 667 south street. So it's important to know each item, it's current assignment and condition.

Sometimes cable is stolen from an old customer too.
123 main moves out. A year later we need the cable to feed another house who's cable has gone bad.
1. We need to record the bad cable so it's not reused
2. We need to record the old equipment at the old house so it can be re used
3. We need to record the new cable pair and equipment at the new house
4. We need to maintain customer information too. If that customer moves out but his cable and equipment is still there we will be able to set up a new customer on his/her old equipment and pair.

I think that's about it for scenarios. So..... What do you think now?
Not bad for a first database problem huh?
I should have picked a truck inventory database. Would have been much easier. Ugh

[June7]

Sounds like you need locations in a table separate from customer info. Link equipment and cable to the location record. Have a field in Customer table for Location ID.

I understand keeping track of equipment and keeping track of cable, I don't understand what you mean by tracking them together. Cable and equipment are either at customer location or in stock. You can determine which cable is with which equipment through the location link. If a search for a cable or equipment through the location link returns nothing that should tell you the item is not at a location.

Also sounds like you need a Condition field in the Cable Pair table.

[danbo]

Sounds like you need locations in a table separate from customer info. Link equipment and cable to the location record. Have a field in Customer table for Location ID.

I understand keeping track of equipment and keeping track of cable, I don't understand what you mean by tracking them together. Cable and equipment are either at customer location or in stock. You can determine which cable is with which equipment through the location link. If a search for a cable or equipment through the location link returns nothing that should tell you the item is not at a location.

Also sounds like you need a Condition field in the Cable Pair table.

Good morning June. Hope you had a great Holiday week.

The equipment is separate from the cable and is actually in our office here. Not every piece of equipment has cable and not all cable is hooked to a piece of equipment.

A customer can only have one address, cable pair and equipment

Equipment can only have one cable pair but it can be any cable pair and that pair can change at any time. I need to track the location of all cable

Cable pairs can only go to one address but that address can be changed at any time.

I've been hard at this all week and get close to what I need but then something always breaks. My last attempt was to make tables

Customer | CustomerID |Autonumber
Address | CustomerID | Number
CablePair | CustomerID | Number
equipment| CustomerID | Number

I tried linking them all with the customerID and something new happened. First, it wouldn't run the query to gather the information from the select tables and then when it did (don't remember what I did to make it work) I wasn't able to create a form. An error returned saying the information wasn't in the recordset.

This was only happening on the cablepair information.

My plan was to allow orphan data so address, cablepair and equipment data was still there even if a customer was gone.
I created a form that would pull the data from customers and then delete only the customer data, leaving the rest behind. This seemed to work okay but still the query wouldn't run. allowing me to view all of the data together.
I'm at a loss here.

[dblife]

Is there any reason why the structure should not be as attached?
The equipment location log could be queried for the last entry for each customer to see what equipment and cable pair they have.
The attached db is A2003 shows what I mean.

[danbo]

Okay, that's almost working and very well too. The only thing I am having a problem with now is I have to limit the equipment description to a list that already exists and rather than having the same equipment listed twice I have to have the data actually replaced. I don't know if that makes sense.

Currently I have a list of equipment
Rst1-1-1
Rst1-1-2
Rst1-1-3
Rst1-1-4

I am using a combo box to select from the list above. However, when I select rst1-1-1 I get the following results.
Rst1-1-1
Rst1-1-2
Rst1-1-3
Rst1-1-4
Rst1-1-1 with customer ID
*** This happens when I create a new customer ****

Rather than using the current record it is creating a new one.
Solution??

Thanks for your imput. Very helpful so far!!!!

[June7]

Why would there be customer ID in the combobox? If the combobox should offer a list of all equipment then the RowSource should be from the Equipment table, not the junction table.

If you want the combobox to show only equipment not already assigned to a customer, that will require a RowSource that is a filtered SQL with join of Equipment to the junction table.

Show the combobox RowSource statement for analysis.

[danbo]

It's not actually in the combobox. That was depicting what is seen on the equipment table after a customer is created. The rst's that are listed above the one with the customerID are all not being used. I was hoping to be able to select one and have it come off of that list or update with the customer information.

[June7]

I am confused. If Equipment can be with only one customer, how can it be listed twice? This 3-way junction is allowing duplicate selection of each data (also allows duplicate combinations). Do you want this? This provides historical info on all equipment usage but will make a restricted combobox row source more difficult.

[danbo]

ya I found that out as soon as I got it all together. this isn't working. GRRRRRRRRRRR I need to go back to school!
Now I'm trying to pull the data from a query that looks for the equipment without an associated phone number. this seems to be working but it's still adding more equipment with duplicate values rather than using what is already there.

SELECT [Available RST].[RST / LET], [Available RST].CRV FROM [Available RST] ORDER BY [Available RST].[RST / LET];

"Available RST" is the query RST / LET is the control, CRV is control
it finds the data but isn't using it.