update same field with criteria

[Angi]

I have a table (Z_rptTable) that stores totals from a whole slew of other
tables. Every month I need to update the MoTot field in the Z table with all
the numbers in the other tables.

I'm currently using a single update query for each one, which works but is a
pain! When I try to combine them I get an error saying "Duplicate output
destination". My question is: Is there a way to use one query to update the
same field in a table using criteria or is the way I'm doing it the only way to
do it?

TIA

Here's the SQL code that doesn't work:

UPDATE [Z_Monthly PRVU Report], CCH_PRO_RVU_FY05 SET [Z_Monthly PRVU
Report].TOT_Num =
([CCH_PRO_RVU_FY05]![CCH_ER_CT])+([CCH_PRO_RVU_FY05]![CCH_ER_FLUORO]),
[Z_Monthly PRVU Report].TOT_Num =
([CCH_PRO_RVU_FY05]![CCH_ER_MAMMO])+([CCH_PRO_RVU_FY05]![CCH_ER_MRI])
WHERE (([Z_Monthly PRVU Report]![Place]="cch_er") AND ([Z_Monthly PRVU
Report]![Place]="cch_in") AND ((CCH_PRO_RVU_FY05.MONTH)="july04"));

 

[John Vinson]

I have a table (Z_rptTable) that stores totals from a whole slew of other
tables. Every month I need to update the MoTot field in the Z table with all
the numbers in the other tables.

I'm currently using a single update query for each one, which works but is a
pain! When I try to combine them I get an error saying "Duplicate output
destination". My question is: Is there a way to use one query to update the
same field in a table using criteria or is the way I'm doing it the only way to
do it?

TIA

Here's the SQL code that doesn't work:

UPDATE [Z_Monthly PRVU Report], CCH_PRO_RVU_FY05 SET [Z_Monthly PRVU
Report].TOT_Num =
([CCH_PRO_RVU_FY05]![CCH_ER_CT])+([CCH_PRO_RVU_FY05]![CCH_ER_FLUORO]),
[Z_Monthly PRVU Report].TOT_Num =
([CCH_PRO_RVU_FY05]![CCH_ER_MAMMO])+([CCH_PRO_RVU_FY05]![CCH_ER_MRI])
WHERE (([Z_Monthly PRVU Report]![Place]="cch_er") AND ([Z_Monthly PRVU
Report]![Place]="cch_in") AND ((CCH_PRO_RVU_FY05.MONTH)="july04"));

Well, it doesn't make any sense to me. What result would you want
stored in Tot_Num if

([CCH_PRO_RVU_FY05]![CCH_ER_CT])+([CCH_PRO_RVU_FY05]![CCH_ER_FLUORO])

and

([CCH_PRO_RVU_FY05]![CCH_ER_MAMMO])+([CCH_PRO_RVU_FY05]![CCH_ER_MRI])

contain *different* values, as one would presume that they would?
You're telling Access that you want to set this single field to two
different values; you can't put two values into one field.

A few suggestions:

- Use . rather than ! to delimit table or query names from fieldnames.
! is a delimiter for Objects (such as Forms and Controls), not for
fields.
- Consider basing your Report directly on a Totals query rather than
storing the query results in a table and then basing the report on
that table. The table is just adding complexity to your task.
- If you're trying to join [Z_Monthly PRVU Report] to
[CCH_PRO_RVU_FYO5], use a Join line in the query grid. Right now every
single row in eachtable is linked to every single row in the other
table.
- Consider storing date data in a Date/Time field rather than a text
field containing "july04". This string will not sort correctly (it
will sort before June and after August for instance) and is hard to
search.
- Don't use reserved names such as MONTH for fieldnames; Access will
confuse it with the Month() date/time function.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps