Updating form from two joined tables

  • Thread starter Thread starter Alex Martinez
  • Start date Start date
A

Alex Martinez

Hello,

I am trying to join two separate tables together and update the table in my
form; unfortunately I can't seem to update the table, I can join them
without a problem.



Table 1 is called Audit Inventory

PolicyNumber (text - field size 9)

Insured (text - field size 50)

AuditComments (Memo field)

Completed (date field)



Table two is called Invoice

PolicyNumber (text - field size 9)

Complete (date field)

user_id (text field size 7)

MonthEnd (date field)





SQL statement (record source of the form)

SELECT [Audit Inventory].PolicyNumber, [Audit Inventory].Insured, [Audit
Inventory].AuditComments, [Audit Inventory].Completed, Invoice.PolicyNumber,
Invoice.Complete, Invoice.user_id, Invoice.MonthEnd

FROM [Audit Inventory] LEFT JOIN Invoice ON [Audit Inventory].PolicyNumber =
Invoice.PolicyNumber;







In my form I have the following fields [Audit Inventory].PolicyNumber,
[Audit Inventory].Insured, [Audit Inventory].AuditComments, [Audit
Inventory].Completed, Invoice.user_id, Invoice.MonthEnd. The Invoice table
is an Excel file that gets upload in the database everyday. Not a problem
easy to upload. What I want is if the PolicyNumber in the Invoice table
match with the PolicyNumber of the Audit Inventory table I want the user to
able to add notes to the [Audit Inventory].AuditComments field. Also if the
[Audit Inventory].Completed (date) is Null I want the Invoice.Complete
(date) to replace the null. Is this possible? Thank you in advance and
Happy New in advance.
 
Alex,

Is the PolicyNumber field in either (or both) of the tables set as a
Primary Key field, or at least as a unique index? What is the nature of
the relationship between the tables? Can the Invoice table contain more
than one record for any given PolicyNumber?
 
Hi Steve,

Thanks for inquiring, regarding the PolicyNumber they are not unique or are
a primary key field in both tables. The Audit Inventory table is a detail
table regarding a policy - In short Policy Number, Insured Name and when
certain audits has been completed (the completed date is manually inputted
by the user). The Invoice table is nothing more then a table containing the
Policy Number and the completed date of audits that has been completed.
What I want to do is save the user time in entering the completed date using
the Invoice table in my form. At the present time the invoice table will not
have another record associated with the PolicyNumber, but may down the road
in which I will later resolve. Thanks and Happy New Year.
Regards,


Steve Schapel said:
Alex,

Is the PolicyNumber field in either (or both) of the tables set as a
Primary Key field, or at least as a unique index? What is the nature of
the relationship between the tables? Can the Invoice table contain more
than one record for any given PolicyNumber?

--
Steve Schapel, Microsoft Access MVP


Alex said:
Hello,

I am trying to join two separate tables together and update the table in
my form; unfortunately I can't seem to update the table, I can join them
without a problem.



Table 1 is called Audit Inventory

PolicyNumber (text - field size 9)

Insured (text - field size 50)

AuditComments (Memo field)

Completed (date field)



Table two is called Invoice

PolicyNumber (text - field size 9)

Complete (date field)

user_id (text field size 7)

MonthEnd (date field)





SQL statement (record source of the form)

SELECT [Audit Inventory].PolicyNumber, [Audit Inventory].Insured, [Audit
Inventory].AuditComments, [Audit Inventory].Completed,
Invoice.PolicyNumber, Invoice.Complete, Invoice.user_id, Invoice.MonthEnd

FROM [Audit Inventory] LEFT JOIN Invoice ON [Audit
Inventory].PolicyNumber = Invoice.PolicyNumber;







In my form I have the following fields [Audit Inventory].PolicyNumber,
[Audit Inventory].Insured, [Audit Inventory].AuditComments, [Audit
Inventory].Completed, Invoice.user_id, Invoice.MonthEnd. The Invoice
table is an Excel file that gets upload in the database everyday. Not a
problem easy to upload. What I want is if the PolicyNumber in the
Invoice table match with the PolicyNumber of the Audit Inventory table I
want the user to able to add notes to the [Audit Inventory].AuditComments
field. Also if the [Audit Inventory].Completed (date) is Null I want the
Invoice.Complete (date) to replace the null. Is this possible? Thank
you in advance and Happy New in advance.
Click to expand...
Click to expand...
 
Alex,

The field on which the tables are joined in the query (in this case
PolicyNumber) will need to be unique in order for the query (and hence
the data in the form) to be updateable.
 
Thanks Steve


Steve Schapel said:
Alex,

The field on which the tables are joined in the query (in this case
PolicyNumber) will need to be unique in order for the query (and hence the
data in the form) to be updateable.
Click to expand...
 
Back
Top