Make Products/Orders Query Updateable.

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I have a products table where the PK is ProductID. Also have the standard Orders
table and OrderDetails table. I created a query that joins the Orders table and
OrderDetails table. The query includes the OrderDate field from the Orders table
and the ProductID field from the OrderDetails table. I added a criteria to the
OrderDate field to limit the orders to the past year. Then I converted the query
to a totals query to get the total sold of each item during the past year.

How can I join the totals query to the product table in a new query to get an
updataeable query?

I want to show all products in the new query with the inventory, reorder point
and number sold during the past year and to be able to edit the inventory and/or
reorder point fields.

Thanks for all help!

Steve
 
A totals query cannot be made into an updateable query. If you want an
updateable query, you'll need to use a select query instead.
 
Ken,

Thank you for responding!

I don't need to edit the total sold field from totals query, just the inventory
and reorder point fields from the Products table. I would just like to display
the total sold field with the inventory and reorder point fields.

Steve
 
I want to show all products in the new query with the inventory,
reorder point
and number sold during the past year and to be able to edit the
inventory and/or
reorder point fields.

One way:
The reorder point would go into the Products table. Then you could do
a totals query on the Issued/Used items table. use an outer join and
then NZ to convert nulls to zeroes and then subtract... Oh heck wait
a minute. Allen Browne has this on his website. There's an inventory
control schema with the queries you need to make the thing work.
Check it out.
 
If you just want to display these other fields, then you can use the DMax,
DLookup, DSum or other domain function in the control source of those
textboxes instead of including them in the query as fields.

That should let you do what you want and you can then use a select query as
the form's recordsource.
 
Back
Top