Update a query based on results from another query using count function

[DrPat]

I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these freqencies (among other things).

I have 3 queries which count how many patients come in 5, 4 and 3 times/week.

In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".

I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.

(OK...confession time, I'm not using SQL view, I'm using the query design view)

In the "update to:" row, I use the Build function and locate the count I'm looking for.

Problem: when I run the query I get the error: Operation must use an updateable query.

Any help would be appreciated

 

[plog]

Update queries are usually a sign of an improper structure and this is true in your case. You need a Patient database which assigns a unique ID to each patient and then a visit table to track what days a patient has a visit. Then you use a query to determine how many times each person came in during a week. Visits per week isn't a value you store, its a value you calculate.

 

[DrPat]

Thanks Plog. Let me clarify a bit.
This portion of the database is not to track visits MADE, rather visits ANTICIPATED (It prints a Monday morning report). The number of anticipated visits are calculated on autoexec and the number of 3's, 4's and 5's are calculated using the count function in the 3 queries mentioned above.

What I will try is to create a separate table just for the number of anticipated visits for the week and have my report use that table as well.

In the meanwhile, any other suggestions would be appreciated.

 

[jdraw]

Please post a jpg (screen print) of your tables and relationships.

There is a free data model here that may give you some ideas.
http://www.databaseanswers.org/data_models/doctors_practice/index.htm