What happened to the Messenger Plus! forums on msghelp.net?
Shoutbox » MsgHelp Archive » Skype & Technology » Tech Talk » database help

database help
Author: Message:
spokes
Full Member
***

Avatar
I <3 Rollerblading

Posts: 423
Reputation: 10
31 / Male / Flag
Joined: May 2004
O.P. database help
ok, im using access and in one table i have these 2 fields

LastUpdated and DateRegistered

now, ive made a 3rd field DaysLeftToRenew, the basic idea is that membership has to be renewed once a year, how can i display how many days that member has left to renew their membership in this field?

thanks

[Image: sig15ws.png]
02-15-2006 05:33 PM
Profile E-Mail PM Web Find Quote Report
Adeptus
Senior Member
****


Posts: 732
Reputation: 40
Joined: Oct 2005
RE: database help
Hi,

You don't need an additional column in your database table for this -- you can compute it at any time, from what's already there. Use SQL functions datediff(), dateadd() and getdate().  The query might look something like this:

select DateRegistered, LastUpdated, datediff('d', getdate(), dateadd('yyyy', 1, LastUpdated)) as DaysLeftToRenew from MyTable

The above would evaluate to the difference (in days) between the current date and LastUpdated + one year, which sounds like what you want. 

This post was edited on 02-17-2006 at 02:59 PM by Adeptus.
02-17-2006 02:42 PM
Profile E-Mail PM Find Quote Report
RaceProUK
Elite Member
*****

Avatar

Posts: 6073
Reputation: 57
37 / Male / Flag
Joined: Oct 2003
RE: database help
SQL is a surprisingly powerful language, with many useful functions that allow you to use derived fields. If you must have an actual field though, then just set up a cron job to daily update that field.
[Image: spartaafk.png]
02-17-2006 02:57 PM
Profile PM Web Find Quote Report
Adeptus
Senior Member
****


Posts: 732
Reputation: 40
Joined: Oct 2005
RE: database help
quote:
If you must have an actual field though, then just set up a cron job to daily update that field.

If he wants to make it look like a table with a field, he can always create a view including the computed column...  :P
02-17-2006 03:02 PM
Profile E-Mail PM Find Quote Report
RaceProUK
Elite Member
*****

Avatar

Posts: 6073
Reputation: 57
37 / Male / Flag
Joined: Oct 2003
RE: database help
CREATE OR REPLACE VIEW MembershipDetails AS
SELECT LastUpdated, DateRegistered, datediff('d', getdate(), dateadd('yyyy', 1, LastUpdated)) as DaysLeftToRenew
FROM MemberList;

Should do the trick ;)

This post was edited on 02-19-2006 at 10:47 PM by RaceProUK.
[Image: spartaafk.png]
02-19-2006 10:47 PM
Profile PM Web Find Quote Report
« Next Oldest Return to Top Next Newest »


Threaded Mode | Linear Mode
View a Printable Version
Send this Thread to a Friend
Subscribe | Add to Favorites
Rate This Thread:

Forum Jump:

Forum Rules:
You cannot post new threads
You cannot post replies
You cannot post attachments
You can edit your posts
HTML is Off
myCode is On
Smilies are On
[img] Code is On