Make Products/Orders Query Updateable.

[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

[Ken Snell]

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 Snell
<MS ACCESS MVP>

"Steve" <ss****@bellsouth.net> wrote in message
news:cU*****************@newsread1.news.atl.earthl ink.net...

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

[Steve]

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
"Ken Snell" <kt***********@notcomcast.realnet> wrote in message
news:un**************@TK2MSFTNGP12.phx.gbl...

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 Snell
<MS ACCESS MVP>

"Steve" <ss****@bellsouth.net> wrote in message
news:cU*****************@newsread1.news.atl.earthl ink.net...

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

[Pieter Linden]

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.

[Ken Snell]

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.

--
Ken Snell
<MS ACCESS MVP>

"Steve" <ss****@bellsouth.net> wrote in message
news:y7****************@newsread2.news.atl.earthli nk.net...

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
"Ken Snell" <kt***********@notcomcast.realnet> wrote in message
news:un**************@TK2MSFTNGP12.phx.gbl...

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 Snell
<MS ACCESS MVP>

"Steve" <ss****@bellsouth.net> wrote in message
news:cU*****************@newsread1.news.atl.earthl ink.net...

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