Don't know where to begin....

[AC5FF]

I have a table of overdue notices that is updated daily and a report is run based on today's entries into that table. Everything works fantastic, but I want to make this process better - and eliminate a little work for both myself and my customers.

Basically, the entries are all automatic from another process outside of my database. All that is entered is EntryDate, Acct#, Amount. The problem arises that if I get a report today on account #803, and the same account appears on tomorrows report, then an email is sent to the account holder twice. Three/Four/Five times in a row if that account continues to be a problem.

I need to keep all my entries into this table; so for my example I would want to continue to enter account #803's information every day, but if a particular account is on today's run, I want my query to ignore it if there have been any previous entries for the past 5 days.

I hope that makes sense; I have it straight in my head what I want to do, but putting it into words just seems confusing right now. Let me know if you have questions and I'll do the best I can to answer them.

I am thinking I will need at least one extra query, maybe two; but it would sure be nice to do this with some type of function based on the date/account number... I just have not been able to come up with a good game plan to even start and try to get this to work....

 

[plog]

Can you provide sample data from your table (include table and field names) and then what you want the query to output based on that sample data?

 

[jzwp22]

To start, create a query that finds those accounts for which you have received overdue notices that have occurred today or within the four preceeding days

SELECT [acct#]
FROM overduetable
WHERE datediff("d",entrydate,date())<=5

BTW, it is best not to have spaces or special characters in your table or field names, so I would suggest changing the fieldname acct# to acctno.

Now since an account may have several notices over those five days we really only want to see each account one time in the query results. To do this we need to select only the unique or distinct accounts, so we need to add the DISTINCT key word to the SELECT clause of the above query

query name: qryAccountNumbersWithinPast5Days
SELECT DISTINCT [acct#]
FROM overduetable
WHERE datediff("d",entrydate,date())<=5


Now that we have the account numbers that we don't want to include, we need to tell your main query to ignore those account numbers. To do that we need to create another new query that uses your main query and qryAccountNumbersWithinPast5Days.

So create a new query and add your main query and qryAccountNumbersWithinPast5Days. Make a left join from the account number field in your main query to the account number field of qryAccountNumbersWithinPast5Days. (To do this via the design grid view in Access, make the join and double click the join line and you will be presented with 3 options. Choose the option that starts with Include ALL records from your main query...). Add in the criteria WHERE qryAccountNumbersWithinPast5Days.acctno is Null

 

[AC5FF]

First off - thank you for the replies. Work has gotten away from me and I have not had a chance to get back to this project. And, I absolutely REFUSE to work on this in the evening from home. Last time I did that and tried to claim it on my time card I was severely chastised.

Pog - Sorry I didn't get data posted. I believe that I understand where jzsp is going with his comments. I am going to give that a try and see if I can make things work. If not, I will get back here and post some sample data.

Again - thanks for the replies and I will try and keep you posted on the outcome!

 

[AC5FF]

JZW;
It worked.. I'm still trying to get my brain wrapped around it all, but essentially it worked like a charm. Thank You. What is even better, is if I change the "IS NULL" to "IS NOT NULL" I can get the opposite recordset.

So I figured my next step in this process would be easy. I need to update a YES/NO field and a TEXT field that is basically based upon the "IS or IS NOT NULL" criteria.

Here is the query as written now:

SELECT [Excess RunTime List].ReportDate, [Excess RunTime List].[ACCT NUMBER], [Excess RunTime List].Sent
FROM [Excess RunTime List] LEFT JOIN [Extreme Use Data for Last 5days] ON [Excess RunTime List].[ACCT NUMBER] = [Extreme Use Data for Last 5days].[ACCT NUMBER]
WHERE ((([Excess RunTime List].ReportDate)=[Enter Report Date]) AND (([Extreme Use Data for Last 5days].[ACCT NUMBER]) Is Null))
ORDER BY [Excess RunTime List].ReportDate, [Excess RunTime List].[ACCT NUMBER];

Now I want to change this query from a SELECT query to an UPDATE query and update the "Sent" field with TRUE (or -1 from what I have read). Yet when I have done this, the field does not update. I thought - maybe I was doing something wrong with the "YES/NO" field criteria; so I changed it to update the notes field with a text string. Still no updates. I can get the right records to show, but cannot update the fields.

Here is the code I am using to update the "Yes/No" field:

UPDATE [Excess RunTime List] LEFT JOIN [Extreme Use Data for Last 5days] ON [Excess RunTime List].[ACCT NUMBER] = [Extreme Use Data for Last 5days].[ACCT NUMBER] SET [Excess RunTime List].Sent = -1
WHERE ((([Excess RunTime List].ReportDate)=[Enter Report Date]) AND (([Extreme Use Data for Last 5days].[ACCT NUMBER]) Is Null));

I have change the = -1 to 0, 1, and TRUE but all attempts have given me the same results.

Ideas???

 

[jzwp22]

Are you getting any error messages when you run the update query? Sometimes, an action query will not run because the recordset is not updateable. This can happen if you have certain join types in the query. Here is a site that goes into details.

Another way is to identify the primary key of the records you want to update and pass those to the update query

Update tablename SET field=xxx WHERE recordID IN (SELECT recordID FROM....)

Some people do not like using IN () because it can cause some issues. An alternate would be to use the EXISTS method. Here is a site that has some examples

 

[AC5FF]

No, no errors at all when running either query.

If I run this as a select query, all data shows as expected. If I run it as an update, all that shows is the yes/no field but it is always unchecked. Same thing goes if I try to run it but update a notes field. I get the correct number of records, but the notes field is all blank.

Weird. Yet I seem to remember seeing something similar years back - I just can't remember what it was.....

 

[jzwp22]

Could you post a copy of the database with any sensitive data removed/altered so that we can take a look?

 

[AC5FF]

hope this works.
The query "Extreme Use Data - Update Sent" is currently set up as a select query. I'm trying to write this query as an update query to update the sent column.

The other query (not here) will basically be identical; except for using "IS NOT NULL" for the joined fields and updating the NOTES column with a text string.

 

[AC5FF]

Okay ... So I am officially calling myself out as an idiot. (again)

I went back to another database and checked out an update query I wrote recently that would do about the same thing as this one... Update a yes/no field. The difference is that query updated everything, not a select # as this one. So I ran that update query and looked at the results in table view (as I had been doing with the above problem query) and none of the fields shown were checked.

Then it hit me... I have to RUN the query, not just VIEW the query to update the data!
Arrrgghhh

This should fix BOTH of my problems now. A few tweaks to the whole process and I should have things running as I want.

NOTE: I may be back to this thread however - because there is a 2nd part of this whole process that I haven't even began to touch. Shouldn't be THAT difficult -- but I may have to request help.. Expecially now that a sample is here....

 

[jzwp22]

In the attached database, I took your Extreme Use Data-Update Sent query and copied it to a new query and changed it to an update query. The query name for that new query is qryUpdate. I changed the name of your Extreme Use Data-Update Sent query to Extreme Use Data-Update Sent PREVIEW. If you run this preview query with a report date of 25-Jan-13, you will see 19 records. Close the query and then run qryUpdate for the same date. You should get the message 19 records are going to be updated. Now open the PREVIEW query again and the sent field should now be checked.

If you want to try it again, run the query qryUndoUpdate to remove the check.


Oops, I just saw your most recent post. I'm glad you got it figured out.