Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Posts
    40

    Unanswered: Can a query field inherit a value from another field?

    Is there a way to . . . in a query that has multiple tables, force a field from one table to inherit the value from a field in another other table (without enforcing referential integrity)?

    Example: A user will input a value into Field 1 from Table 1. I want Field 1 from Table 2 to inherit that value (and store it) without user intervention.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    So... you want to enforce referential integrity, just without enforcing referential integrity?

    Why?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Apr 2004
    Posts
    40
    Exactly. Enforcing referential integrity is only useful when a user updates a field in BOTH tables. Lets say a user opens a form that uses the query (with referential integrity). They know nothing about tables, queries, and relationships. They create a record, completing a few fields that happen to be stored in Table 1. They plan to come back later to complete the fields that are stored in Table 2. In this case a record will be created in Table one, but none in Table 2. This will happen a lot in my database.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    lost

    why are you trying to store the same data in more than one table?

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Apr 2004
    Posts
    40
    In order to build a relationship between tables, both tables must have one field with common data. I'm trying to achieve this with minimal user interaction.

    In anticipation of the question, "Why are you using multiple tables?" - Scalability.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    that's certainly a good excuse!!

    but...

    what harm is done if the many side has no entry? imagine:

    tblCompany:
    idCompany
    ...and more blah about the company

    tblEmployees:
    idEmployee
    idCompany
    ...and more blah about the employee

    worst case: no employees show up for the company ...which is pretty reasonable if the user didn't enter any employees. it's not a referential integrity violation.

    if you can't tolerate zero employees, after you save the new tblCompany entry, lock the user on a form that refuses to unload if the count of tblEmployees = 0 WHERE idCompany = yourNewIDcompany

    i don't think it makes sense to create an empty employee in the hope that the user will come back and find that empty record and fill in the blanks after lunch. sounds like a recipe for many empty employee records.

    izy

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Apr 2004
    Posts
    40
    Thanks for the thoughts, Izy. What I'm attempting to do is a requirement for this database. Users must have the latitude to enter part of a record now and part later. Sometimes only partial information is known when the record is created.

    I thought of a work-around. I'm actually using three tables in the query. Tables one and two are synchronized using Ref Integrity--no problems. The relationship between tables two and tree is the troublesome one. What I've done is take a field from table 2 that is required and move it to table 3. That way the user will definitely hit all three tables on the first visit, and referential integrity can do its job. Not ideal, but it works.

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Ref Integrity

    In the Main Form - have your autonumber field somewhere.

    On the subform - have the linked field somewhere. Set the "Default Value" on that field to (i.e. =Forms!MainForm!RecID). RecID is the autonumber field on the Main form.

    Hope that helps if I read what you're trying to do correctly.

Posting Permissions

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