Relationship not working as it should

[Clems]

Hi everyone.
Firstly, apologies for asking this question. I'm a noob when it comes to access but I'm learning fast.



What I'm trying to do is make my life a bit easier for myself (paperwork wise) when I do work for a customer. I've created 3 databases that I want to link together using a relationship but I don't seem to be able to get it 100% working (I'm nearly there but I'm not sure what I'm doing wrong).

The 1st database contains my customer details, the 2nd contains Works Order details and the 3rd contains Invoice details.

What I'm trying to achieve is to have a customer DB where any customer can have any number of works orders but each works order can only have 1 invoice attached to it.

Now, when I link the first and second DB together, great! It all works perfectly as I would expect it to (my customer can have multiple works orders against their name). The problem arises when I try to attach the 3rd (invoice) DB to the 2nd (works orders) DB. It seems to work ok when I add the data in plain / table view (or mode?) but when I want to view or add data using a form, the details are not there.

I eventually got the 3 databases to kind of work using a form with sub-forms but I'm not sure if this is causing the issue or not. It works perfectly if I only have 2 tabs on the form (customer details and works orders) but as soon as I introduce the Invoice tab it doesn't seem to be "attaching" the invoice data to the correct works order (and it seems to be getting confused with the relationship as it keeps on pointing to the 1st (customer) DB when trying to set this up in a form). I even thought that this was the issue and I needed to link the 3rd DB to the 1st DB with the correct ID fields but that still didn't work.

So for example, customerA has a works order number of 123. That works Order Number should relate to an invoice number of 456.
What's happening is customer A has a works order number of 123 (as expected). When I add invoice 456 to works order 123 it appears to work fine (as expected). I then change works order to the next works order in the table (124) but invoice 456 is attached to it even though it belongs to works order 123.

What seems to be happening is that the invoice number is somehow following me to whatever works order I'm looking at. I'm not sure if the relationships part is wrong (I just can't seem to get my head around it properly) or I'm missing something.

I keep on reading that you can have a 4th database to keep track of all the key fields and then use queries to display the data. Perhaps this is the way to go but it seems a bit long winded doing this for something so simple. I'm sure I'm doing something wrong but not sure what. I've even thought of duplicating (there, I've said it!) data to get to my desired result but that kinda defeats the whole point in me doing this.

I've included a screenshot of the relationship in the hope that it will help you understand my issue better as I'm a bit crap at explaining things.



Thanks all for any help or advice and sorry if I've confused the matter with my rambling on.

[rpeare]

First those are tables, not databases.

Second you say:

What I'm trying to achieve is to have a customer DB where any customer can have any number of works orders but each works order can only have 1 invoice attached to it.

So you have ONE customer that can have MANY work orders (one to many relationship which is what you have)
EACH work order can only have ONE invoice (one to one relationship) so the MANY side of your current relationship (the invoice table) should be changed to 1.

From the sound of your description, your relationships are not the problem. It's how you have your forms/subforms set up (your data entry faulty not your table relationship).

If you do, in fact have a 1 to 1 relationship between WORKORDER and INVOICE why aren't those items on the same table? does every workorder result in an invoice or can you have a work order that never becomes an invoice?

If it's the latter you will need a form with a subform, and a sub-subform within the subform to get all of your data linked correctly (if you are using BOUND controls as opposed to unbound controls).

[Clems]

Hi and thanks for getting back to me on this.
Yes, I did actually mean to say tables but for some reason I said databases (think I got myself confused there).

Anyway...

So you have ONE customer that can have MANY work orders (one to many relationship which is what you have)
EACH work order can only have ONE invoice (one to one relationship) so the MANY side of your current relationship (the invoice table) should be changed to 1.

Thanks for this, I'll sort that out now.

If you do, in fact have a 1 to 1 relationship between WORKORDER and INVOICE why aren't those items on the same table? does every workorder result in an invoice or can you have a work order that never becomes an invoice?

Yes, you are correct. I guess I could have the works order and invoice data on the same table but I wanted to keep them separate from each other otherwise I may as well just have the whole lot in one table and make life very easy for myself (but then again I would be duplicating a lot which is what I don't want). You are also correct about every works order resulting in an invoice. You will never be able to have a works order that doesn't result in an invoice hence only one invoice per works order.

If it's the latter you will need a form with a subform, and a sub-subform within the subform to get all of your data linked correctly (if you are using BOUND controls as opposed to unbound controls).

Ok, I don't quite understand the logic behind this. Why do I need a sub-subform within the subform? If I can link the 1st table with the 2nd table by just using a tabbed subform why do I need to introduce another subform into the equation in order for me to link the 2nd and 3rd table together (or is that just the way it works)?

I'm just trying to understand a bit better about how it all links together and it will make more sense to me then.

If I had the works order and invoice data on one table (like you said), would it work if I split the works order and invoice fields into separate tabs on a subform or do you think this would cause more confusion? If it would work is it just a simple case of just separating the fields or would I need some coding or a query to tie them together?

To be honest, I'm not that bothered in how the data is stored as long as there are no duplications. I just need the fields for each "section" (customer, works order, invoice) to be separate on the form to keep things nice and tidy.

Thanks for the help so far, I really appreciate it

[rpeare]

There are a bunch of ways to handle your data entry that I can think of, but by far the simplest is to make your workorders/invoices a single table since you have indicated you will not have one without the other. Then you can avoid subforms altogether. Do not get lost in my use of form/subform/sub subform. Think about the easiest possible solution that is going to give you the data you want while still maintaining proper normalization of your database.

[Clems]

Okay, so it's looking like I may need to have a re-design of my forms. I guess I can use a drop down field to select and parse the customer data and then fill in the rest myself.

Thanks for your help