By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,666 Members | 1,784 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,666 IT Pros & Developers. It's quick & easy.

Make Products/Orders Query Updateable.

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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


Nov 12 '05 #3

P: n/a
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.
Nov 12 '05 #4

P: n/a
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



Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.