 | |
Newbie question about formula based values
comp.databases.ms-access comp.databases.ms-access
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: Newbie question about formula based values -
11-25-2007
, 02:56 PM
|
"lyle fairfield" <lylefair (AT) yahoo (DOT) ca> wrote in message news:EAh2j.15510$9F1.10921 (AT) read1 (DOT) cgocable.net... "Neil" <nospam (AT) nospam (DOT) net> wrote in news:amg2j.871$Vq.43 (AT) nlpi061 (DOT) nbdc.sbc.com: "lyle" <lyle.fairfield (AT) gmail (DOT) com> wrote in message news:72efa194-0a71-42de-a549-79a724509fb2 (AT) p69g2000hsa (DOT) googlegroups.com. .. On Nov 24, 1:14 pm, "Mintyman" <minty... (AT) ntlworld (DOT) com> wrote: HI, I'm not that experienced in Access so am turning to the experts for some basic help. I have the following fields in a table : Name Type Level Number Start Date Date End Date Date Is it possible to have some code that will automatically change the value of 'level' from 2 to 1 if today's date is greater than 'End Date'? Effectively, I want the customer's access level to drop once their contract has run out. At the moment I have to keep an eye on when customer contracts are due to end and manually update the value in the 'level' field from 2 to 1. Can anyone show me how to do this? Thanks! Probably you should determine the customer's access level on the basis of [End Date]<VBA.Date(). Maintaining a "level" field that simply mimics the results of a calculation is unnecessary, and is generally considered to be poor design. Hi, Lyle. I tend to disagree with you on this. I think having a "level" field is a good idea. There may be other reasons besides expiration date for determining the level. Granted, all those criteria could be included in a dynamic level calculation. But having a separate field would allow ad-hoc adjustments to someone's level on a case-by-case basis. So I prefer the separate field approach. Another benefit of the separate field approach is that it allows sorting on filtering on that value, which isn't possible with a calculated value. Now here you might say that any sorting or filtering should be provided programmatically as part of the application. But depending on the size of the business and the need, they might not have the time or the resources to program everything that might be needed with the database. So being able to do sorting and filtering using the bulit-in Access tools is a big plus, IMO. So, those are my thoughts. Neil 1. As the level field is time-dependent would it not always have to be recalculated before/during use? If so, why not just use the calculation? 2. You think this would be beyond the casual user SELECT Orders.* FROM Orders WHERE ShippedDate > RequiredDate ORDER BY DateDiff("D", RequiredDate, ShippedDate) but updating the level field would not? -- lyle fairfield I will arise and go now, For always night and day I hear lake water lapping With low sounds by the shore; While I stand on the roadway Or on the pavements gray, I hear it in the deep heart's core. - Yeats "Mintyman" <mintyman (AT) ntlworld (DOT) com> wrote |
Quote:
|
Hi guys, Thanks for the input here. I'll give you a bit more context so you know what i'm trying to achieve. I have 2 levels of contract for customers : Standard and Premium. Everyone by default will get a Standard contract (level 1). If people want to upgrade, they can move to premium (level 2). Upon doing so, they will choose a length of time their contract will run for : 3,6,12, 24 months. Once their premium contract has run out, I would like it to automatically revert back to a Standard contract (level 1). I'm using the 'level' as a variable in an ASP website to determine access to extra functionality e.g. % If varLevel = 2 then % Show extra content/functionality for premium customers % Else % Show basic content/functionality for standard customers % End If % I hope this makes sense! |
for the reasons I stated, and for what you wrote above re. using the value
in a separate system, I think you'd be better off with a standalone field
that's updated. That's my POV, and others might disagree with me. But that's
how I would do it.
Neil
#12
| |||
| |||
|
|
You can use Windows Scheduler (in Control Panel) to run the query whenever you want. The way you'd do it is as follows: 1) Create your query. 2) Create a macro. In this macro have a single item that calls RunCode. Have it call a routine you create. 3) In the routine that you referenced in the macro, do the following: Currentdb.Execute "MyQuery", dbfailonerror Docmd.Quit That will run your macro and then close the application when it's done. 4) In Windows scheduler, create a new scheduled task based on Microsoft Access. Go into the task and edit it, telling it to open your database. At the end of that line, put /x macroname. Example: c:\program files\office\msaccess.exe c:\somedir\mydatabase.mdb /x mymacroname Note that you have to specify the path to msaccess.exe if you want to use the supplemental arguments. Is that clear? If not, let me know. Neil "Mintyman" <mintyman (AT) ntlworld (DOT) com> wrote in message news:6Ni2j.5612$B97.4576 (AT) newsfe7-win (DOT) ntli.net... Hi Neil, Will this query only run when I run it in Access? I would like these values to be updated on a constant basis without me having to do anything. I wasn't sure if Access could do this or whether i'm getting confused with SQL stored proedures. An alternative would be to place the code on a webpage on my site that will be accessed on a daily basis. That way, the query could be triggered every time a visitor loads that specific page. "Neil" <nospam (AT) nospam (DOT) net> wrote in message news:dq_1j.1362$AR7.112 (AT) nlpi070 (DOT) nbdc.sbc.com... You can create a query; don't add any tables; click the SQL button; and then add the following: Update MyTable Set Level=1 Where EndDate<Date() If you want to ONLY update if the level was 2 (e.g., not 3), then you'd do: Update MyTable Set Level=1 Where EndDate<Date() AND Level=2 Save your query, and then, when you open it from the database window, it will update your records. (Be sure to substitute the actual name of your table for MyTable.) "Mintyman" <mintyman (AT) ntlworld (DOT) com> wrote in message news:NRZ1j.12827$Ew3.9757 (AT) newsfe7-gui (DOT) ntli.net... HI, I'm not that experienced in Access so am turning to the experts for some basic help. I have the following fields in a table : Name Type Level Number Start Date Date End Date Date Is it possible to have some code that will automatically change the value of 'level' from 2 to 1 if today's date is greater than 'End Date'? Effectively, I want the customer's access level to drop once their contract has run out. At the moment I have to keep an eye on when customer contracts are due to end and manually update the value in the 'level' field from 2 to 1. Can anyone show me how to do this? Thanks! |
#13
| |||
| |||
|
|
Sounds perfect Neil.....only problem is that I don't have access to the PC. The database is located on a hosting account on a remote server (where my website is hosted). So, I can't get access to the Windows Scheduler. I was hoping there would be a way for Access to run these updates automatically. |
if Access is closed, it can't. What you'd do is set a timer to check the
time every minute or every ten minutes or whatever. If the time equals or is
past the time to run the code, then execute the query. Don't need the macro
or anything. And, of course, remove the Docmd.Quit line. :-)
Of course, if the query hangs or if Access crashes or is someone restarts
the machine, then you're screwed (unless you can put the MDB in the Startup
folder).
FMS has a utility that runs as a Windows service, so that's a more solid
approach, and can automatically restart the machine if it crashes.. I
haven't used this utility myself, so I can't give you any more information
on it. But you can go to http://www.fmsinc.com/products/Agent/index.html to
get more info on their "Agent" product.
Of course, that assumes that you're able to install the utility on that
machine.
If you want to execute the code from your web site, you can do that as well.
If your web site can execute SQL code against an Access database, then just
do that. That would be very simple. If not, then you can possibly open
Access through Automation and execute the SQL that way. But accessing
Access through a web site is a bit out of my area of knowledge.
Quote:
|
I agree this query only needs to be run once a day. However, the only way I know of calling this update would be to put a trigger in the ASP code on a designated page that I know will be visited at least once a day - the homepage soudns ideal. However, I don't want it to trigger every time the page is requested. That would be a waste of overheads. The only way I could do it would be to enclose it in an IF statement that would only allow the code to run during a given period in the day e.g. run the update ASP code between 11am and 11.20am. Then all I need to do is hope that I receive a vistior within that time period. I know it's not elegant but it should work......unless you can think of a better idea? |
then you can modify the query to only update that visitor's record.
Second, you would create a table in your Access database which tracks when
the record was last updated for that visitor. When you execute the code, you
store the current date (without the time component). Then, whenever the user
logs in, you execute your code, but check the date it was last run, and, if
it was run today, then skip.
Of course, there'd still be the overhead of opening the Access app each
time. So a better approach would be to store the date it was last updated
for that user in your web database. Do the same thing, check last date
against current date, and then run the SQL against the Access app only if
the dates don't match. That should do it for you. (Still have the overhead
of looking up the last date; but what can you do?)
If the above is not correct regarding needing to update the visitor's
record, but, instead you want to update all records, then you can do this
same process of checking the date it was last run, only there would be a
single value (for all users) instead of one for each user. That would work.
But it seems a bit hokey to me to rely on someone clicking on your web page
in a given day to run a process. Better would be if you could put some sort
of automation in place.
HTH,
Neil
Quote:
| "Neil" <nospam (AT) nospam (DOT) net> wrote in message news:5fl2j.24521$JD.4523 (AT) newssvr21 (DOT) news.prodigy.net... You can use Windows Scheduler (in Control Panel) to run the query whenever you want. The way you'd do it is as follows: 1) Create your query. 2) Create a macro. In this macro have a single item that calls RunCode. Have it call a routine you create. 3) In the routine that you referenced in the macro, do the following: Currentdb.Execute "MyQuery", dbfailonerror Docmd.Quit That will run your macro and then close the application when it's done. 4) In Windows scheduler, create a new scheduled task based on Microsoft Access. Go into the task and edit it, telling it to open your database. At the end of that line, put /x macroname. Example: c:\program files\office\msaccess.exe c:\somedir\mydatabase.mdb /x mymacroname Note that you have to specify the path to msaccess.exe if you want to use the supplemental arguments. Is that clear? If not, let me know. Neil "Mintyman" <mintyman (AT) ntlworld (DOT) com> wrote in message news:6Ni2j.5612$B97.4576 (AT) newsfe7-win (DOT) ntli.net... Hi Neil, Will this query only run when I run it in Access? I would like these values to be updated on a constant basis without me having to do anything. I wasn't sure if Access could do this or whether i'm getting confused with SQL stored proedures. An alternative would be to place the code on a webpage on my site that will be accessed on a daily basis. That way, the query could be triggered every time a visitor loads that specific page. "Neil" <nospam (AT) nospam (DOT) net> wrote in message news:dq_1j.1362$AR7.112 (AT) nlpi070 (DOT) nbdc.sbc.com... You can create a query; don't add any tables; click the SQL button; and then add the following: Update MyTable Set Level=1 Where EndDate<Date() If you want to ONLY update if the level was 2 (e.g., not 3), then you'd do: Update MyTable Set Level=1 Where EndDate<Date() AND Level=2 Save your query, and then, when you open it from the database window, it will update your records. (Be sure to substitute the actual name of your table for MyTable.) "Mintyman" <mintyman (AT) ntlworld (DOT) com> wrote in message news:NRZ1j.12827$Ew3.9757 (AT) newsfe7-gui (DOT) ntli.net... HI, I'm not that experienced in Access so am turning to the experts for some basic help. I have the following fields in a table : Name Type Level Number Start Date Date End Date Date Is it possible to have some code that will automatically change the value of 'level' from 2 to 1 if today's date is greater than 'End Date'? Effectively, I want the customer's access level to drop once their contract has run out. At the moment I have to keep an eye on when customer contracts are due to end and manually update the value in the 'level' field from 2 to 1. Can anyone show me how to do this? Thanks! |
#14
| |||
| |||
|
|
Sounds perfect Neil.....only problem is that I don't have access to the PC. The database is located on a hosting account on a remote server (where my website is hosted). So, I can't get access to the Windows Scheduler. I was hoping there would be a way for Access to run these updates automatically. I agree this query only needs to be run once a day. However, the only way I know of calling this update would be to put a trigger in the ASP code on a designated page that I know will be visited at least once a day - the homepage soudns ideal. However, I don't want it to trigger every time the page is requested. That would be a waste of overheads. The only way I could do it would be to enclose it in an IF statement that would only allow the code to run during a given period in the day e.g. run the update ASP code between 11am and 11.20am. Then all I need to do is hope that I receive a vistior within that time period. I know it's not elegant but it should work......unless you can think of a better idea? |
just hosting it on the same server as your web site? If it's being used as a
back end for your web site, then my original reasons for advocating a
standalone Level field are null and void. If that's the case, the go with
Lyle's suggestion and use a calculated value to determine the level when
needed and avoid the updating altogether. I was basing my suggestion on the
idea that users would be interacting directly with the Level field, and
would benefit from having a standalone field. But if that's not the case,
then forget the standalone field altogether.
Neil
#15
| |||
| |||
|
|
"Mintyman" <mintyman (AT) ntlworld (DOT) com> wrote in message news:fieaeq$a3i$1$8300dec7 (AT) news (DOT) demon.co.uk... Sounds perfect Neil.....only problem is that I don't have access to the PC. The database is located on a hosting account on a remote server (where my website is hosted). So, I can't get access to the Windows Scheduler. I was hoping there would be a way for Access to run these updates automatically. I agree this query only needs to be run once a day. However, the only way I know of calling this update would be to put a trigger in the ASP code on a designated page that I know will be visited at least once a day - the homepage soudns ideal. However, I don't want it to trigger every time the page is requested. That would be a waste of overheads. The only way I could do it would be to enclose it in an IF statement that would only allow the code to run during a given period in the day e.g. run the update ASP code between 11am and 11.20am. Then all I need to do is hope that I receive a vistior within that time period. I know it's not elegant but it should work......unless you can think of a better idea? Also, are you using this MDB as a back end for your web site, or are you just hosting it on the same server as your web site? If it's being used as a back end for your web site, then my original reasons for advocating a standalone Level field are null and void. If that's the case, the go with Lyle's suggestion and use a calculated value to determine the level when needed and avoid the updating altogether. I was basing my suggestion on the idea that users would be interacting directly with the Level field, and would benefit from having a standalone field. But if that's not the case, then forget the standalone field altogether. Neil |
#16
| |||
| |||
|
|
Users will be interacting directly with the 'Level' field so i'm keen to keep it separate. Thank you (and Lyle) for your input on this  )"Neil" <nospam (AT) nospam (DOT) net> wrote in message news:9Jy2j.27033$lD6.18098 (AT) newssvr27 (DOT) news.prodigy.net... "Mintyman" <mintyman (AT) ntlworld (DOT) com> wrote in message news:fieaeq$a3i$1$8300dec7 (AT) news (DOT) demon.co.uk... Sounds perfect Neil.....only problem is that I don't have access to the PC. The database is located on a hosting account on a remote server (where my website is hosted). So, I can't get access to the Windows Scheduler. I was hoping there would be a way for Access to run these updates automatically. I agree this query only needs to be run once a day. However, the only way I know of calling this update would be to put a trigger in the ASP code on a designated page that I know will be visited at least once a day - the homepage soudns ideal. However, I don't want it to trigger every time the page is requested. That would be a waste of overheads. The only way I could do it would be to enclose it in an IF statement that would only allow the code to run during a given period in the day e.g. run the update ASP code between 11am and 11.20am. Then all I need to do is hope that I receive a vistior within that time period. I know it's not elegant but it should work......unless you can think of a better idea? Also, are you using this MDB as a back end for your web site, or are you just hosting it on the same server as your web site? If it's being used as a back end for your web site, then my original reasons for advocating a standalone Level field are null and void. If that's the case, the go with Lyle's suggestion and use a calculated value to determine the level when needed and avoid the updating altogether. I was basing my suggestion on the idea that users would be interacting directly with the Level field, and would benefit from having a standalone field. But if that's not the case, then forget the standalone field altogether. Neil |
OK. Did you ever get the updating problem worked out?
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.