Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Feb 2006
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    key fields and relationships (MS access 2003)

    I am working on a delivery versus invoice database where I have a delivery table and an invoice table. The delivery number is the key field in both tables and they are linked as relationships. I then have a query that is looking at both of these tables and checking to make sure that when an invoice comes in there is a related delivery number in the delivery table. This all works ok except when there is a match and someone then wants to change the delivery number in the invoice table, i.e. a typo on the delivery number of the invoice table that just happens to match an existing delivery number in the delivery table. When the correction is made in the invoice table it changes the delivery number in the delivery table as well. Is there any way to retain this link for comparison purposes but keep the invoices table modification from also modifying the delivery table?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: key fields and relationships (MS access 2003)

    Is the delivery number in the delivery table changed because Cascading Updates has been set for the relationship between the invoice table and delivery table?
    If so, you can turn off Cascading Updates.
    If not, how does the delivery number get changed? Through code?

  3. #3
    New Lounger
    Join Date
    Feb 2006
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: key fields and relationships (MS access 2003)

    I can

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: key fields and relationships (MS access 2003)

    I don't think we can tell what's happening without seeing the database. If you wish, you can post a stripped down copy of your database. See <post#=401925>post 401925</post#> for instructions.

  5. #5
    New Lounger
    Join Date
    Feb 2006
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: key fields and relationships (MS access 2003)

    I identified what seem to be the cause of the problem but now I need a fix for the fix! Access forces the relationship between the delivery table, delivery number field and the invoice table, delivery number field to be a one to one relationship. This seems to be what causes the problem link. If I change the invoice table, delivery number field to no longer be a key field and set it as not indexed or indexed (duplicates ok) Access changes the relationship back to a one to many and my problem link goes away. If I change the invoice table, delivery number field to Indexed (No Duplicates) Access forces the link back to a one to one and my problem link comes back

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: key fields and relationships (MS access 2003)

    If you really need a one-to-one relationship between invoices and deliveries, why do you have two different tables? You could move the delivery data into the invoices table (or vice versa).

  7. #7
    New Lounger
    Join Date
    Feb 2006
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: key fields and relationships (MS access 2003)

    From a purely Access perspective the two fields look like a one to one relationship but they are not. At least not they way I am looking at it. The delivery table is populated from data directly off of a delivery ticked from the truck line. The delivery number is a unique number in the table since no two delivery tickets should ever have the same number and if duplicates show up in the database is would be due to an receiving person double entering a ticket (error), or a trucking line error. The Invoice table is populated via an invoice from the supplier which references multiple deliveries by specific delivery number. Again the delivery number should be unique (no duplicates) in the Invoice table. The Invoice table is ultimately evaluated against the Delivery table to make sure that all Deliveries are invoiced without duplicates and that the quantities and dollar amounts for each table are in agreement. The Delivery number and information in the Delivery table should ultimately agree with the Delivery number and information in the Invoice table but they are derived from different sources

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: key fields and relationships (MS access 2003)

    Let's see if we can straighten this out. You wrote
    <hr>... an invoice from the supplier which references multiple deliveries ...<hr>
    I would interpret that to mean that one invoice number can be associated with several delivery numbers, i.e. you have a one-to-many relationship. If that is correct, the relationship between the tables should not be on the delivery number field (there should be no such field in the invoices table), but on the invoice number field (you should add this field to the deliveries table, with either no index or a non-unique index). If I am completely off, can you explain the above quote?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •