update recordset that isn't updateable...

G

Guest

I've read the Help topic about what isn't updateable and queries with more
than one table usually falls into that category. But I am trying to do that
and need help with the code.

I have a table of NoShows (we log when a client doesn't show up for an
appt). I want to send out a letter to clients who has between 2 and 5
noshows within a floating 60 day period. Below is the query, qryNSCount,
that gives me the clientID:

SELECT DISTINCT AUX_CLIENT_ID
FROM tblNoShow
WHERE DUE_TIME Between DateAdd("d",-60,Date()) AND DATE()
GROUP BY AUX_CLIENT_ID
HAVING AUX_CLIENT_ID>100000 AND Count(AUX_CLIENT_ID)>1 And
Count(AUX_CLIENT_ID)<6
ORDER BY AUX_CLIENT_ID;

Now that I have the clientIDs, when someone goes to print the letter, I want
Access to timestamp each noshow record within that 60 days period so we know
that the client has been notified of those noshows.

Unfortunately, if I combine the Count query with the table tblNoshow, I
can't update:

SELECT tblNoShow.AUX_CLIENT_ID, LetterPrintDate, LetterPrintBy, DUE_TIME
FROM tblNoShow INNER JOIN qryNSCount ON tblNoShow.AUX_CLIENT_ID =
qryNSCount.AUX_CLIENT_ID WHERE LetterPrintDate Is Null AND
Format([DUE_TIME],'Short Date') Between Date()-60 And Date()-2

Is it possible to split up the two queries and do a Loop function so I can
timestamp the letterprintdate and letterprintby?

I just don't know how to code it.
 
R

Roger Carlson

If you use your "count" query as a Subquery, you should be able to update it
the main query. Something like this:

SELECT tblNoShow.AUX_CLIENT_ID, LetterPrintDate, LetterPrintBy, DUE_TIME
FROM tblNoShow
WHERE tblNoShow.AUX_CLIENT_ID IN
(SELECT DISTINCT AUX_CLIENT_ID
FROM tblNoShow
WHERE DUE_TIME Between DateAdd("d",-60,Date()) AND DATE()
GROUP BY AUX_CLIENT_ID
HAVING AUX_CLIENT_ID>100000 AND Count(AUX_CLIENT_ID)>1 And
Count(AUX_CLIENT_ID)<6
ORDER BY AUX_CLIENT_ID)
AND
LetterPrintDate Is Null AND
Format([DUE_TIME],'Short Date') Between Date()-60 And Date()-2;

For an example of something similar, look at my sample called
"MaxQueryProblem.mdb" on my website: www.rogersaccesslibrary.com. Look at
"Correct Query 2".

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
G

Guest

oh ok...I was trying to do a subquery, but couldn't get the right syntax.
Thanks, I'll try it and see if it works.

Roger Carlson said:
If you use your "count" query as a Subquery, you should be able to update it
the main query. Something like this:

SELECT tblNoShow.AUX_CLIENT_ID, LetterPrintDate, LetterPrintBy, DUE_TIME
FROM tblNoShow
WHERE tblNoShow.AUX_CLIENT_ID IN
(SELECT DISTINCT AUX_CLIENT_ID
FROM tblNoShow
WHERE DUE_TIME Between DateAdd("d",-60,Date()) AND DATE()
GROUP BY AUX_CLIENT_ID
HAVING AUX_CLIENT_ID>100000 AND Count(AUX_CLIENT_ID)>1 And
Count(AUX_CLIENT_ID)<6
ORDER BY AUX_CLIENT_ID)
AND
LetterPrintDate Is Null AND
Format([DUE_TIME],'Short Date') Between Date()-60 And Date()-2;

For an example of something similar, look at my sample called
"MaxQueryProblem.mdb" on my website: www.rogersaccesslibrary.com. Look at
"Correct Query 2".

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


ngan said:
I've read the Help topic about what isn't updateable and queries with more
than one table usually falls into that category. But I am trying to do that
and need help with the code.

I have a table of NoShows (we log when a client doesn't show up for an
appt). I want to send out a letter to clients who has between 2 and 5
noshows within a floating 60 day period. Below is the query, qryNSCount,
that gives me the clientID:

SELECT DISTINCT AUX_CLIENT_ID
FROM tblNoShow
WHERE DUE_TIME Between DateAdd("d",-60,Date()) AND DATE()
GROUP BY AUX_CLIENT_ID
HAVING AUX_CLIENT_ID>100000 AND Count(AUX_CLIENT_ID)>1 And
Count(AUX_CLIENT_ID)<6
ORDER BY AUX_CLIENT_ID;

Now that I have the clientIDs, when someone goes to print the letter, I want
Access to timestamp each noshow record within that 60 days period so we know
that the client has been notified of those noshows.

Unfortunately, if I combine the Count query with the table tblNoshow, I
can't update:

SELECT tblNoShow.AUX_CLIENT_ID, LetterPrintDate, LetterPrintBy, DUE_TIME
FROM tblNoShow INNER JOIN qryNSCount ON tblNoShow.AUX_CLIENT_ID =
qryNSCount.AUX_CLIENT_ID WHERE LetterPrintDate Is Null AND
Format([DUE_TIME],'Short Date') Between Date()-60 And Date()-2

Is it possible to split up the two queries and do a Loop function so I can
timestamp the letterprintdate and letterprintby?

I just don't know how to code it.
Click to expand...
Click to expand...
 
G

Guest

1. You work in Muskegon? I grew up in Grand Rapids (went to City High
School) and went to U-M in A^2.

2. The query took about 2.5 mins to update the data. Is it because of the
subquery? Any way to reduce the time? We have to run this update query
every day. There could be around 200 records to update daily and this would
be run on a slower machine so it could take longer.

Roger Carlson said:
If you use your "count" query as a Subquery, you should be able to update it
the main query. Something like this:

SELECT tblNoShow.AUX_CLIENT_ID, LetterPrintDate, LetterPrintBy, DUE_TIME
FROM tblNoShow
WHERE tblNoShow.AUX_CLIENT_ID IN
(SELECT DISTINCT AUX_CLIENT_ID
FROM tblNoShow
WHERE DUE_TIME Between DateAdd("d",-60,Date()) AND DATE()
GROUP BY AUX_CLIENT_ID
HAVING AUX_CLIENT_ID>100000 AND Count(AUX_CLIENT_ID)>1 And
Count(AUX_CLIENT_ID)<6
ORDER BY AUX_CLIENT_ID)
AND
LetterPrintDate Is Null AND
Format([DUE_TIME],'Short Date') Between Date()-60 And Date()-2;

For an example of something similar, look at my sample called
"MaxQueryProblem.mdb" on my website: www.rogersaccesslibrary.com. Look at
"Correct Query 2".

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


ngan said:
I've read the Help topic about what isn't updateable and queries with more
than one table usually falls into that category. But I am trying to do that
and need help with the code.

I have a table of NoShows (we log when a client doesn't show up for an
appt). I want to send out a letter to clients who has between 2 and 5
noshows within a floating 60 day period. Below is the query, qryNSCount,
that gives me the clientID:

SELECT DISTINCT AUX_CLIENT_ID
FROM tblNoShow
WHERE DUE_TIME Between DateAdd("d",-60,Date()) AND DATE()
GROUP BY AUX_CLIENT_ID
HAVING AUX_CLIENT_ID>100000 AND Count(AUX_CLIENT_ID)>1 And
Count(AUX_CLIENT_ID)<6
ORDER BY AUX_CLIENT_ID;

Now that I have the clientIDs, when someone goes to print the letter, I want
Access to timestamp each noshow record within that 60 days period so we know
that the client has been notified of those noshows.

Unfortunately, if I combine the Count query with the table tblNoshow, I
can't update:

SELECT tblNoShow.AUX_CLIENT_ID, LetterPrintDate, LetterPrintBy, DUE_TIME
FROM tblNoShow INNER JOIN qryNSCount ON tblNoShow.AUX_CLIENT_ID =
qryNSCount.AUX_CLIENT_ID WHERE LetterPrintDate Is Null AND
Format([DUE_TIME],'Short Date') Between Date()-60 And Date()-2

Is it possible to split up the two queries and do a Loop function so I can
timestamp the letterprintdate and letterprintby?

I just don't know how to code it.
Click to expand...
Click to expand...
 
R

Roger Carlson

1) Live in Muskegon, work in G.R.

2) Subqueries are slow by there very nature. This query has to be run for
each and every record in the main query. You could fiddle with it some.
You might be able to move some of the conditions in Having Clause into the
Where Clause. That might speed it up, but proably not enough.

Another option is to convert your "count" query into a Make-Table query (eg
NoShowIDs).
SELECT DISTINCT AUX_CLIENT_ID INTO NoShowIDs
FROM tblNoShow
WHERE DUE_TIME Between DateAdd("d",-60,Date()) AND DATE()
GROUP BY AUX_CLIENT_ID
HAVING AUX_CLIENT_ID>100000 AND Count(AUX_CLIENT_ID)>1 And
Count(AUX_CLIENT_ID)<6
ORDER BY AUX_CLIENT_ID

Then instead of having your "count query" as a subquery, you'd have
something like this:

SELECT tblNoShow.AUX_CLIENT_ID, LetterPrintDate, LetterPrintBy, DUE_TIME
FROM tblNoShow
WHERE tblNoShow.AUX_CLIENT_ID IN
(SELECT AUX_CLIENT_ID FROM NoShowIDs)
AND
LetterPrintDate Is Null AND
Format([DUE_TIME],'Short Date') Between Date()-60 And Date()-2;

Now, the down side of this is you HAVE to run the first query before running
the second query or you will come up with incorrect data. Therefore, you
should put the queries in a macro or better yet, a code module. I don't
like to suggest this, but sometimes, compromises have to be made for
performance reasons.


--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


ngan said:
1. You work in Muskegon? I grew up in Grand Rapids (went to City High
School) and went to U-M in A^2.

2. The query took about 2.5 mins to update the data. Is it because of the
subquery? Any way to reduce the time? We have to run this update query
every day. There could be around 200 records to update daily and this would
be run on a slower machine so it could take longer.

Roger Carlson said:
If you use your "count" query as a Subquery, you should be able to update it
the main query. Something like this:

SELECT tblNoShow.AUX_CLIENT_ID, LetterPrintDate, LetterPrintBy, DUE_TIME
FROM tblNoShow
WHERE tblNoShow.AUX_CLIENT_ID IN
(SELECT DISTINCT AUX_CLIENT_ID
FROM tblNoShow
WHERE DUE_TIME Between DateAdd("d",-60,Date()) AND DATE()
GROUP BY AUX_CLIENT_ID
HAVING AUX_CLIENT_ID>100000 AND Count(AUX_CLIENT_ID)>1 And
Count(AUX_CLIENT_ID)<6
ORDER BY AUX_CLIENT_ID)
AND
LetterPrintDate Is Null AND
Format([DUE_TIME],'Short Date') Between Date()-60 And Date()-2;

For an example of something similar, look at my sample called
"MaxQueryProblem.mdb" on my website: www.rogersaccesslibrary.com. Look at
"Correct Query 2".

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


ngan said:
I've read the Help topic about what isn't updateable and queries with more
than one table usually falls into that category. But I am trying to
Click to expand...
do
that
and need help with the code.

I have a table of NoShows (we log when a client doesn't show up for an
appt). I want to send out a letter to clients who has between 2 and 5
noshows within a floating 60 day period. Below is the query, qryNSCount,
that gives me the clientID:

SELECT DISTINCT AUX_CLIENT_ID
FROM tblNoShow
WHERE DUE_TIME Between DateAdd("d",-60,Date()) AND DATE()
GROUP BY AUX_CLIENT_ID
HAVING AUX_CLIENT_ID>100000 AND Count(AUX_CLIENT_ID)>1 And
Count(AUX_CLIENT_ID)<6
ORDER BY AUX_CLIENT_ID;

Now that I have the clientIDs, when someone goes to print the letter,
Click to expand...
I
want
Access to timestamp each noshow record within that 60 days period so
Click to expand...
we
know
that the client has been notified of those noshows.

Unfortunately, if I combine the Count query with the table tblNoshow, I
can't update:

SELECT tblNoShow.AUX_CLIENT_ID, LetterPrintDate, LetterPrintBy, DUE_TIME
FROM tblNoShow INNER JOIN qryNSCount ON tblNoShow.AUX_CLIENT_ID =
qryNSCount.AUX_CLIENT_ID WHERE LetterPrintDate Is Null AND
Format([DUE_TIME],'Short Date') Between Date()-60 And Date()-2

Is it possible to split up the two queries and do a Loop function so I can
timestamp the letterprintdate and letterprintby?

I just don't know how to code it.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

1. Kewl. Where do you work (if you want to say)?

2. I did have the update query in a db.execute code. I'll test out the
make table to see if it goes faster. If I do that, shouldn't it be a delete
and then append code? If I do a make table, it may prompt me to override the
table name.

Also, as the tblNoShow gets more and more records, will the query time
increase because the query has to filter through more records?

Thanks!
Ngan
 
R

Roger Carlson

1) Spectrum Health (Butterworth/Blodgett merger)

2) db.execute shouldn't prompt you, but a delete/append combination should
work too.

Yes, as the table gets bigger, the query will take longer. That's
axiomatic. However, the real log-jam is running the aggregate subquery for
each record. A simple Select Query as a subquery should run much faster.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


ngan said:
1. Kewl. Where do you work (if you want to say)?

2. I did have the update query in a db.execute code. I'll test out the
make table to see if it goes faster. If I do that, shouldn't it be a delete
and then append code? If I do a make table, it may prompt me to override the
table name.

Also, as the tblNoShow gets more and more records, will the query time
increase because the query has to filter through more records?

Thanks!
Ngan

Roger Carlson said:
1) Live in Muskegon, work in G.R.

2) Subqueries are slow by there very nature. This query has to be run for
each and every record in the main query. You could fiddle with it some.
You might be able to move some of the conditions in Having Clause into the
Where Clause. That might speed it up, but proably not enough.

Another option is to convert your "count" query into a Make-Table query (eg
NoShowIDs).
SELECT DISTINCT AUX_CLIENT_ID INTO NoShowIDs
FROM tblNoShow
WHERE DUE_TIME Between DateAdd("d",-60,Date()) AND DATE()
GROUP BY AUX_CLIENT_ID
HAVING AUX_CLIENT_ID>100000 AND Count(AUX_CLIENT_ID)>1 And
Count(AUX_CLIENT_ID)<6
ORDER BY AUX_CLIENT_ID

Then instead of having your "count query" as a subquery, you'd have
something like this:

SELECT tblNoShow.AUX_CLIENT_ID, LetterPrintDate, LetterPrintBy, DUE_TIME
FROM tblNoShow
WHERE tblNoShow.AUX_CLIENT_ID IN
(SELECT AUX_CLIENT_ID FROM NoShowIDs)
AND
LetterPrintDate Is Null AND
Format([DUE_TIME],'Short Date') Between Date()-60 And Date()-2;

Now, the down side of this is you HAVE to run the first query before running
the second query or you will come up with incorrect data. Therefore, you
should put the queries in a macro or better yet, a code module. I don't
like to suggest this, but sometimes, compromises have to be made for
performance reasons.


--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
Click to expand...
Click to expand...
 
G

Guest

2. When I did the db.execute for the make table, it gives me the msgbox "The
table already exists". So I went ahead and did the delete/append combo.
This time, it took less a second to do. Amazing. Yea, I don't like to have
to do the make table, but guess you have to compromise somewhere.

Thanks again!

Roger Carlson said:
1) Spectrum Health (Butterworth/Blodgett merger)

2) db.execute shouldn't prompt you, but a delete/append combination should
work too.

Yes, as the table gets bigger, the query will take longer. That's
axiomatic. However, the real log-jam is running the aggregate subquery for
each record. A simple Select Query as a subquery should run much faster.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


ngan said:
1. Kewl. Where do you work (if you want to say)?

2. I did have the update query in a db.execute code. I'll test out the
make table to see if it goes faster. If I do that, shouldn't it be a delete
and then append code? If I do a make table, it may prompt me to override the
table name.

Also, as the tblNoShow gets more and more records, will the query time
increase because the query has to filter through more records?

Thanks!
Ngan

Roger Carlson said:
1) Live in Muskegon, work in G.R.

2) Subqueries are slow by there very nature. This query has to be run for
each and every record in the main query. You could fiddle with it some.
You might be able to move some of the conditions in Having Clause into the
Where Clause. That might speed it up, but proably not enough.

Another option is to convert your "count" query into a Make-Table query (eg
NoShowIDs).
SELECT DISTINCT AUX_CLIENT_ID INTO NoShowIDs
FROM tblNoShow
WHERE DUE_TIME Between DateAdd("d",-60,Date()) AND DATE()
GROUP BY AUX_CLIENT_ID
HAVING AUX_CLIENT_ID>100000 AND Count(AUX_CLIENT_ID)>1 And
Count(AUX_CLIENT_ID)<6
ORDER BY AUX_CLIENT_ID

Then instead of having your "count query" as a subquery, you'd have
something like this:

SELECT tblNoShow.AUX_CLIENT_ID, LetterPrintDate, LetterPrintBy, DUE_TIME
FROM tblNoShow
WHERE tblNoShow.AUX_CLIENT_ID IN
(SELECT AUX_CLIENT_ID FROM NoShowIDs)
AND
LetterPrintDate Is Null AND
Format([DUE_TIME],'Short Date') Between Date()-60 And Date()-2;

Now, the down side of this is you HAVE to run the first query before running
the second query or you will come up with incorrect data. Therefore, you
should put the queries in a macro or better yet, a code module. I don't
like to suggest this, but sometimes, compromises have to be made for
performance reasons.


--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

followup question on updating un-updateable queries 1
Updateable querie 2
Recordset not updateable 3
Recordset not updateable 2
Recordset is not updateable. 3
Non-updateable query issue 1
Pass-Through query used to update an access table 2
Recordset not Updateable 2

Top