Updating status 1

[carfj1]

Pardon my stupidity...

I am having some novice trouble with two tables:

Clients table:
ID, Name,..., Status (Active or suspended)
The key is the ID which is an autonumber.

Payments Table:
ID, Date, Membership,NextPaymentDay,Amount
The key is ID, Date, Membership

I need to update the Status of the client to active with an action query if NextPaymentDay<Date(). To do this I made a query to get the max date for every client.

Having this query joined with the client table on an update query using ID for the join creates a not updatable query.

I would appreciate if someone can guide me in the right direction, cause I may have to make a temporal table with status data and then udate the status on the client table.

Thanks

PD, I crated referential integrity on the relationships with cascade updates...(fyi)

 

[scriverb]

You can spend countless hours trying to figure this one out. Sometimes it has to do with keys both primary and non or it can be a matter of one side or the other of the action query not being updateable which yours isn't. You see you created a interim query with GroupBy and Max data. This is not updateable. As a result even though you are not updating that side of the relationship in your action query ACCESS just chokes on it and won't run because it is cautious and confused.

Solution: Change your interim query to a make-table query and then change your action Update query to link your main table to this temporary table. It always works and is a lot easier than trying to figure this querky problem out.

DoCmd.Setwarnings False
DoCmd.OpenQuery &quot;qryMakeTable&quot;
DoCmd.OpenQuery &quot;qryUpdateQuery&quot;
DoCmd.Setwarnings False

Let me know if this solves your problem. Bob Scriver

 

[carfj1]

Bob,

Thanks for stopping me from looking on the wrong direction, I do have spent some hours looking at the JOIN thing.

I was asked to improve the database so that no temporary table had to be created. The main reason is that this table will be created more than 100 times in a day and we feel this might make the database unstable or with a higer risk of corruption.

Any way if there is no other way... I should stay with current configuration, same as you are suggesting.

Regards,
Carlos

 

[scriverb]

You can add this to your process. Deleting the temporary table after you are done with. This does not get rid of the temporary space that the .mdb file has acquired to create it but your IT staff should be performing Compacting processes on a regular basis anyway to eliminate the blank space in the .mdb file. More than likely your tables that are being updated are on the backend anyway and the temporary tables will be created on your PC's front-end application. Stability here should not be a problem as you can always get a new copy and be back in business quickly.


DoCmd.Setwarnings False
DoCmd.OpenQuery &quot;qryMakeTable&quot;
DoCmd.OpenQuery &quot;qryUpdateQuery&quot;
DoCmd.DeleteObject acTable, &quot;tblTempTable&quot;
DoCmd.Setwarnings False

Let me know if this will help.
Bob Scriver

 

[carfj1]

Bob,

Actually this database is a StandAlone DB that checks all Clients status when it is loaded and after a payment is done. Scince there is some movement arround this process I was wondering if there was some other route to achive this update.

I will stay on your guideline.
Thanks a lot,

Carlos

 

[scriverb]

Even if it is a stand-alone database it is standard practice to split your database into a front-end and back-end database. The front-end has all of the static tables, queries, forms, macros, and modules. The back-end database has all of the tables that contain your updateable records. By splitting them you are allowing more than one user access to the data as you can distribute many copies of the front-end to individual PC's with the back-end on a common server location. The front-end then has LINKED tables that it references. These would be the tables being added and updated to on a regular basis.

The front-end is where this temp table would be created let's say 100 times a day and creating, deleting, and re-creating it should not be a problem. I have databases that do this on a regular basis.

Even if you are going to have a single user use this database as a stand alone the tables should be split in this manner. This way programmers can develop changes to the application and when ready distribute to the PC without over-writing the data in the tables. If they were located within the front-end a new version would wipe out your recently updated data.

I hope I have not confused you on this problem. There are ways to solve your problem but as i said before we would have to look at your indexes and whether you have a one to many relationship in these queries. You see if there are more than one record linked on the right side of the relationship then ACCESS doesn't know which one to update. This will cause the error that you referred to earlier in this posting.

Bob Scriver