Updates to a query Using a query

  • Thread starter Thread starter Doug MacDonald
  • Start date Start date
D

Doug MacDonald

I have 3 tables: tblItem, tblQuality and tblOrder.
tblItem fields: ItemID and ItemName.
tblQuality fields: QualityID, QualityType, ItemID.
tblOrder fields: OrderID, ItemID, Quantity
I can create a 1 to 1 query (qryItemQual) with tblItem and
tblQuality and I am able to update and add records within
the query.
I want to create another 1 to many query using qryItemQual
(1) and the table tblOrder (many). I want to be able to
add records through this query but it is not updateable.
It shows the records that are currently in the three
tables but doesn't allow me to update or add records.
The tables and the queries are all joined by ItemID.
Does Access allow me to use the queries and tables as
described above?
 
I have 3 tables: tblItem, tblQuality and tblOrder.
tblItem fields: ItemID and ItemName.
tblQuality fields: QualityID, QualityType, ItemID.
tblOrder fields: OrderID, ItemID, Quantity
I can create a 1 to 1 query (qryItemQual) with tblItem and
tblQuality and I am able to update and add records within
the query.
I want to create another 1 to many query using qryItemQual
(1) and the table tblOrder (many). I want to be able to
add records through this query but it is not updateable.
It shows the records that are currently in the three
tables but doesn't allow me to update or add records.
The tables and the queries are all joined by ItemID.
Does Access allow me to use the queries and tables as
described above?
Click to expand...

Hi Doug,

As a general rule, you want to edit data in forms anyway.
Use your queries for sources of forms or reports.

What you want to do will be a snap using
a form bound to the first query,

with a subform bound to tblOrder.

For each ItemID in the main form,
you can show/add/edit/delete records
in tblOrder subform.

For a good example, open up the "Orders"
form in the sample db "NorthWind"
(typically in C:\Program Files\Microsoft Office\Office\Samples)

Forms/subforms are powerful stuff!

Good luck,

Gary Walter
 
Gary,

Thanks so much. I will give that a try.

I also discovered the following Help file after posting
the message. Typing "updatable queries" in the Access
Help Wizard and then selecting "When can I update data
from a query" describes several instances when queries can
or cannot be updatable.

One of the Nutshell Access books pointed that Help file
out. I have found the Nutshell books on Access to be
excellent references.

Doug
 
Back
Top