Access relationship question

[Arby]

Hi all,

I'm just putting a database together, and I'm having a relationship "issue". It's fairly simple right now, but I'm not sure if you can do what I am trying to do..

Basically I have, so far, 3 tables. Each one has a primary key "Serial #".

There's a "Systems" table, a "Parts" table, and a "cash flow" table. I have a link from

Systems - Cash Flow
Parts - Cash flow
Both links are set to enforce referential integrity (with cascade update/delete enabled as well).

What I want it to do is work so that I can add new values to either the Systems or Parts table, with their own unique "Serial #" code, and then be able to add the value to the cash flow table.

Currently, I can add items to the "Systems" table, and then add the corrosponding entry into CashFlow, and that's fine. If I try and input an invalid entry, it will tell me that the appropriate entry does not exist in "Systems".

However, I cannot add anything to "Parts", since it gives the error that the appropriate entry does not exist in CashFlow. Obviously, I need to make it in Parts first, and even if I wanted to make it in CashFlow first, it wouldn't let me (entry does not exist in "Systems").

I have tried fiddling with the relationships, but I get other problems like not being able to add items to "Systems", etc. If I try and drag "Parts" to "CashFlow" the other way (in the relationship), it tells me that I cannot do that because it violates the integrity rules.

I can see WHAT the problem is, and I know WHY it's doing it, I just don't know how to fix it.

Basically, I want the field "Serial #" in the table "Cash Flow" to be able to draw from the field "Serial #" in either Parts or Systems, but reject anything else (in other words, incorrect serial numbers, since the item shouldn't be in the cash flow table until it has been registered as an item in either "Parts" or "Systems").

Any ideas?

 

[lightray]

I would suggest taking off the cascade update, and definately the cascade delete. You don't want to hit delete record to find it deletes all the relative records in the database. If you are using [Serial #] as a field name not a good idea, use something like SerialNo (don''t use spaces either, not good practice) What is the Database designed to do?

 

[Arby]

I've removed those options, and changed the name to "SerialNo", but I still have the same problem.

I have already filled the database with some data; "Systems" and "CashFlow" have some entries with identical "SerialNo" fields. I can create a relationship by dragging the "SerialNo" of "Systems" to the "SerialNo" of "CashFlow".

However, I cannot create a relationship by dragging the "SerialNo" of "Parts" to the "SerialNo" of "CashFlow" (I get an error). Dragging the other way will, assumably, make it so that I have to enter the value in "CashFlow" before I can add it to "Parts". But then, I might get the error that the related field is not found in "Systems".

I want it to work so that I can have values in "Systems" and "Parts" with different Serial numbers, and can add a value to "CashFlow" which has a "SerialNo" identical to either a value in "Systems" or "Parts" (But it should give an error if there isn't a related value in either).

Basically, I run a small computer resale type home business. I want to have a table to log the systems (specifications and etc), parts (Just the serial number and a description - for components), and link them to a CashFlow sheet where I can log how much I paid for them, how much they sold for, it they needed any add-onparts and the cost, profit/loss, etc.
Becauase only myself will be using the database, I *could* get away without the relationship - I'd just have to remember to fill in the cash-flow sheet every time I add something. But I'm just trying to make it a bit easier by automatically creating a new entry in CashFlow when I add something new to either of the other tables.

I've attatched a sample database. It contains the same fields and everything else as the real one, just different actual data.

 

[rburna904]

First off in the indexs : CashFlow change Serial # to idxSerialNo

Now onto the relationships :

Based on what I am seeing you would typicly do the following:

User enter information into Systems or OtherBits (I assume that is suppose to be Parts)

So you would have a One to Many relationship from systems to cashflow and a one to many relationship from otherbits to cashflow

Once you do this it will let you add records to any of the tables as you would expect.

 

[boblarson]

First of all - Lightray: Cascade Updates/Deletes are not bad. In fact, they can be one of your best friends. You want to choose the correct time to use them, however. If you have a record, such as a part number with a serial number and you need to track all items associated with that serial number and you have data associated with that part that exists soley because that part exists, you could want to enable the Cascade updates/deletes as if you delete that part, you could end up with "orphan" records in the other tables. In other words records that don't have a required corresponding entry in the parts table.

Now, that is not likely the reason for Arby's problem. What is the problem is an erroneous thought process around relationships. Relationships will let you define how the data fits together, but defining a relationship does not mean that you can add data into one table and it automatically will add a corresponding record into an associated table. You must add data into the primary table first and then information associated with that record into the other table(s).

Arby: I've attached a revised version (with cascade updates/deletes enabled). The problem that you had with your relationships is that you were setting your primary keys wrong and it's also best to not use TEXT fields as keys as they can't be indexed.

 

[Arby]

Ah, thanks for the advice!

rburna904 - Well, there will never be more than any one item with a Serial number - the serial number is unique among every System or Part. The other thing is, I cannot change whether the relationship type is One-to-One or One-to-Many

boblarson - Thank you for the help! I have looked at the database that you provided, but the Part field doesn't need to be connected to the System one - the parts aren't for systems, but individual items that people might want to buy. What I'm really looking for is for each item (A system or a part) to have a unique "SerialNo", which can then link to the CashFlow table so that I can add an associated entry in the CashFlow table for that item (System or Part).

Thank you both for the advice, though!
Is what I'm trying to do possible?

 

[boblarson]

Arby:

Try this one out.

Also,
you wrote:

I cannot change whether the relationship type is One-to-One or One-to-Many

Access determines that for you based on your table structure and relationships.

 

[The_Doc_Man]

Arby, FYI:

Relationships are one-to-one if there is a UNIQUE index on the key used for linking on both sides of the relationships. They are one-to-many if one side has a (DUPLICATES ALLOWED) index on the key in question. You don't change anything in the relationship. You change the nature of the index in the table. That is how 1/1 or 1/M gets set up.

 

[Arby]

boblarson - Ah, thank you. Unfortunately, I know that I could create seperate IDs and link Systems to CashFlow and Parts to CashFlow seperately, I was just hoping for a way to use one unique ID throughout the whole system, just changing the syntax of the ID. For example, a System would have the ID:
AS001
And a part might have the ID
AP001
And both of these would hopefully be linkable to the one ID in CashFlow.

The_Doc_Man - Ah, thanks for the advice! Regarding all the One-to-One Vs. One-to-Many things though - there will never be a duplicate anywhere on the database. Every System has it's own unique ID (starting "AS" for ArbitalSystems, and a number counting from 001 upwards) and every Part has it's own ID (starting "AP" for ArbitalPart, and a number counting up in the same way as Systems), and each individual entry will be linked to one entry in the CashFlow database.

I just want it to be set up so that I can add an item to either Systems or Parts, and can then add a related value in CashFlow (linked to the same ID). This should also give an error if the ID doesn't exist in either Systems or Parts.

Currently, if I try and make a relationship between Parts and CashFlow, it says that there is an error. Even if it worked, I think that if I tried to add a new value to CashFlow, it would say that the opposing table didn't have the required entry. For example, imagine that "Systems" has a value with a serial ID of "AS001" and "Parts" has a value with an ID of "AP001". If I tried to add a value into "CashFlow" with the ID "AS001" I would get an error that the required value does not exist in "Parts", and if I tried to add a value of "AP001" I would get the error that the required field does not exist in "Systems". In other words, it wouldn't work either way. I want it to be able to look at both "Systems" and "Parts", and check if there is an corrosponding value in either of them, not both.

 

[lightray]

Arby, FYI:

Relationships are one-to-one if there is a UNIQUE index on the key used for linking on both sides of the relationships. They are one-to-many if one side has a (DUPLICATES ALLOWED) index on the key in question. You don't change anything in the relationship. You change the nature of the index in the table. That is how 1/1 or 1/M gets set up.

This is a very informative post Doc_Man, and thanks Bob for your tips. I am expanding my knowledge in this area. (one each)