Form with record source from two tables

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • is49460
    New Member
    • Jan 2007
    • 25

    #1

    Form with record source from two tables

    Any help would be very appreciated!!!

    I have two tables: Main Data and Archived Data.
    Each table has the same fields (about 50 or so fields).
    Main data contains records that are updated every week.
    Archived data contains historical data that once was in "main data".
    Each record can be distinguished by 3 different fields (There is no PK!):Invoice No, Amount, Date.
    Some records can be in both tables, some can have the same invoice number (that's why I identify unique record by three different fields). Some can be in the same table with the same Invoice number and so on...

    I need to create a form with the following functionality:
    1. User enters invoice number.
    2. If such record exist and is unique in only one table, then display that record.
    if no record found - nothing is displayed
    if multiple records exist (either within the same table or different ones) some kind of option should be displayed to specify (or verify) the amount and date to make sure the desired record is displayed.
    3. User should be able to modify displayed fields.

    The problem I'm having with such design is how to determine how to pull out record from correct table. The design I thought of did NOT seem to be reliable.
    Any suggestions will be appreciated and let me know if more info needed about table or data...
    Thanks a lot
  • ADezii
    Recognized Expert Expert
    • Apr 2006
    • 8834

    #2
    Originally posted by is49460
    Any help would be very appreciated!!!

    I have two tables: Main Data and Archived Data.
    Each table has the same fields (about 50 or so fields).
    Main data contains records that are updated every week.
    Archived data contains historical data that once was in "main data".
    Each record can be distinguished by 3 different fields (There is no PK!):Invoice No, Amount, Date.
    Some records can be in both tables, some can have the same invoice number (that's why I identify unique record by three different fields). Some can be in the same table with the same Invoice number and so on...

    I need to create a form with the following functionality:
    1. User enters invoice number.
    2. If such record exist and is unique in only one table, then display that record.
    if no record found - nothing is displayed
    if multiple records exist (either within the same table or different ones) some kind of option should be displayed to specify (or verify) the amount and date to make sure the desired record is displayed.
    3. User should be able to modify displayed fields.

    The problem I'm having with such design is how to determine how to pull out record from correct table. The design I thought of did NOT seem to be reliable.
    Any suggestions will be appreciated and let me know if more info needed about table or data...
    Thanks a lot
    If each record can be distinguished by 3 different fields, namely, [Invoice No], [Amount], and [Date] and no Primary Key exists on either Table, I see absolutely no reason why you would not want to create a Composite Primary Key on these Fields. It would eliminate data duplication, as well as simplify the required coding to accomplish what you desire. Is there any particular reason why you do not wish to do this?

    Comment

    • is49460
      New Member
      • Jan 2007
      • 25

      #3
      Originally posted by ADezii
      If each record can be distinguished by 3 different fields, namely, [Invoice No], [Amount], and [Date] and no Primary Key exists on either Table, I see absolutely no reason why you would not want to create a Composite Primary Key on these Fields. It would eliminate data duplication, as well as simplify the required coding to accomplish what you desire. Is there any particular reason why you do not wish to do this?

      Data in "Main Data" table gets overwriten every week. Seems to me that if I create a composite PK it be rather useless because for example:

      week1:
      trans ID | Composite PK
      A 1
      B 2
      C 3

      I would add those 3 transaction into "Archived Data" table that would be like "Main Data" at current instance.

      Week2:
      trans ID | Composite PK
      A 1
      B 2
      D 3

      For me to store those transactions into "Archived Data" I would still have to check if they already exist by [Invoce No], Date, Amount. Because new transaction D got assigned to composite PK 3 that was previously assigned to transaction C.
      Of course I dont like the design without PK myself. Perhaps I could use it and I'm just missing something... If you think that composite PK would make the task easier please suggest.
      Thanks for replying.

      Comment

      • NeoPa
        Recognized Expert Moderator MVP
        • Oct 2006
        • 32669

        #4
        You could consider a UNION query for showing the records but they would not be updatable so that could be a stumbling block.

        Comment

        • is49460
          New Member
          • Jan 2007
          • 25

          #5
          Originally posted by NeoPa
          You could consider a UNION query for showing the records but they would not be updatable so that could be a stumbling block.
          I thought of UNION query which would be the best solution but the user must be able to modify data...

          Comment

          Working...