How do I use DCOUNT in an update query?

[Guest]

I have successfully used the Totals row to count records; however, my
ultimate goal is to use these counts to update a table, so I need to use
DCOUNT(). My guess is that I don't understand the syntax and that is why I
am running into problems.

Here's the situation:
I have a Shop Order Table with the following fields:
Shop Order #: this is a unique value and the primary key
Quantity required: this is the number of parts required to fill the order
Quantity made: this the number of parts completed and the field I
ultimately want to update by counting the number of parts listed in another
table with that Shop Order #.
Closed: is a boulean field which is true once the number made and the
number required are equal the inventory has been adjusted. The update query
I use to change this field value is working!

I have a table called Modules with the following fields:
Shop Order #: joined to the shop order number in the above table
Module ID: unique number for each part we make.
One shop order may have several module ID's if more than one part needs to
made to fill the order. I can either count the number of times the Shop
Order # appears in this table or the number of unique modules for each shop
order. I do not know which is easier.

The query needs to produce something that looks like this:
Shop Order # ModuleCount
1 3
2 1
3 2
4 0
So that I can use this data to update the Quantity Made field in the Shop
Order Table.

Thank you,
Irene

 

[Guest]

I was able to get DCOUNT() to work in a select query. I have now converted
the select query to an update query:

UPDATE [Shop Order Table] INNER JOIN [Potted Modules Q] ON [Shop Order
Table].[Shop Order #] = [Potted Modules Q].[Shop Order #] SET [Shop Order
Table].[Quantity Made] = DCount("[shop order #]","Potted Modules Q","[shop
order #]=" & [Potted Modules Q].[Shop Order #])/2;

Now I am getting an "Operation must use an updateable query" error.

I have changed the query properties "unique records" to "yes", as I saw
recommended in another post on this topic, and I still get the same error
message.

 

[John Vinson]

I was able to get DCOUNT() to work in a select query. I have now converted
the select query to an update query:

UPDATE [Shop Order Table] INNER JOIN [Potted Modules Q] ON [Shop Order
Table].[Shop Order #] = [Potted Modules Q].[Shop Order #] SET [Shop Order
Table].[Quantity Made] = DCount("[shop order #]","Potted Modules Q","[shop
order #]=" & [Potted Modules Q].[Shop Order #])/2;

Now I am getting an "Operation must use an updateable query" error.

I have changed the query properties "unique records" to "yes", as I saw
recommended in another post on this topic, and I still get the same error
message.

The first question is - should you store this count in your table AT
ALL? I would say No, unless you have some very good reason to do so!

Why? Because it's redundant. You will have a number stored in your
table; but if you add or delete any records in the [Potted Modules Q],
that stored number WILL BE WRONG, with no indication *why* it's wrong
or even that it's wrong.

Can you not instead simply calculate the count in a Query, and base
your forms, reports, additional queries, etc. directly on the Query?

The direct answer to your question is that in order to be updateable,
the above query must meet several restrictions. Among them are that
[Shop Order #] must be the Primary Key of [Shop Order Table] (or must
have a unique Index); and the query [Potted Modules Q] must not
contain any Sum, Count, Group By or other Totals operations. It may
not be necessary to join [POTTED MODULES Q] into the Update query at
all - you're not referencing any fields from it other than in the
DCount, which will work independently.

John W. Vinson[MVP]

 

[Guest]

John,

Thank you very much for your suggestions. The problem that I was having had
to do with having the unnecessary query linked in the update query. Once I
removed [Potted Module Q], everything worked.

The actualy working update query is below:

UPDATE DISTINCTROW [Shop Order Table] SET [Shop Order Table].[Quantity Made]
= DCount("[Shop Order #]","Potted Modules Q","[Shop Order #]=" & [Shop Order
#])/2
WHERE (([Closed]=False));

My objective in doing it this way, is that I want Access to search for the
number of parts produced to fill a shop order. This query will be run first
to update the Shop Order Table. Then another query will be run to update the
table again and Close shop orders that have been shipped.

Thanks again.

I was able to get DCOUNT() to work in a select query. I have now converted
the select query to an update query:

UPDATE [Shop Order Table] INNER JOIN [Potted Modules Q] ON [Shop Order
Table].[Shop Order #] = [Potted Modules Q].[Shop Order #] SET [Shop Order
Table].[Quantity Made] = DCount("[shop order #]","Potted Modules Q","[shop
order #]=" & [Potted Modules Q].[Shop Order #])/2;

Now I am getting an "Operation must use an updateable query" error.

I have changed the query properties "unique records" to "yes", as I saw
recommended in another post on this topic, and I still get the same error
message.

The first question is - should you store this count in your table AT
ALL? I would say No, unless you have some very good reason to do so!

Why? Because it's redundant. You will have a number stored in your
table; but if you add or delete any records in the [Potted Modules Q],
that stored number WILL BE WRONG, with no indication *why* it's wrong
or even that it's wrong.

Can you not instead simply calculate the count in a Query, and base
your forms, reports, additional queries, etc. directly on the Query?

The direct answer to your question is that in order to be updateable,
the above query must meet several restrictions. Among them are that
[Shop Order #] must be the Primary Key of [Shop Order Table] (or must
have a unique Index); and the query [Potted Modules Q] must not
contain any Sum, Count, Group By or other Totals operations. It may
not be necessary to join [POTTED MODULES Q] into the Update query at
all - you're not referencing any fields from it other than in the
DCount, which will work independently.

John W. Vinson[MVP]

 

[John Vinson]

My objective in doing it this way, is that I want Access to search for the
number of parts produced to fill a shop order. This query will be run first
to update the Shop Order Table. Then another query will be run to update the
table again and Close shop orders that have been shipped.

Again...

It is *NOT* necessary to store the count in a Table in order to do
this.

If you want to search for the number of parts produced, simply use a
Totals query to count the number of parts produced.

This totals query can be used as the basis of a Form, of a Report, as
the basis for another query; you can put criteria on the calculated
field (e.g. a criterion of <100 would find all orders where fewer than
100 parts had been produced); and - most importantly - you can be
confident that the number *is correct*. If you store the value using
an Update query, *then you CANNOT be confident that the number hasn't
changed since the update query was run*.

John W. Vinson[MVP]

 

[Guest]

Thanks. I will see if this will work for me.

Again...

It is *NOT* necessary to store the count in a Table in order to do
this.

If you want to search for the number of parts produced, simply use a
Totals query to count the number of parts produced.

This totals query can be used as the basis of a Form, of a Report, as
the basis for another query; you can put criteria on the calculated
field (e.g. a criterion of <100 would find all orders where fewer than
100 parts had been produced); and - most importantly - you can be
confident that the number *is correct*. If you store the value using
an Update query, *then you CANNOT be confident that the number hasn't
changed since the update query was run*.

John W. Vinson[MVP]