Question Adding date calculation to table

[nick941741]

I have an events data base, that has the main tables of

Clients
Events
Coms - Shows any communcations, phone calls in & out etc.

There is also a members table, which feeds my clients table. There are only 2 types of members available. 1 Option has no expiry date, so don't know if its better to just set this to a date way off in the future or just leave it blank.

The 2nd member type will always expire on the following January no matter when the member joins, apart from if they join in January then that will be the following January.

Hope that makes sense.

Member joins in Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec 09 - Membership will expire in Jan 2010

I need a calulation so that if the member type = "member" then it completes the date to be the following January

Is this possible?

Thanks

Nick

Edit: This is using Access 2003 - I have some basic skills in acces and a little VB

 

[namliam]

There is also a members table, which feeds my clients table. There are only 2 types of members available. 1 Option has no expiry date, so don't know if its better to just set this to a date way off in the future or just leave it blank.

If it doesnt have an expiry date, dont make one up... leave it blank.

I need a calulation so that if the member type = "member" then it completes the date to be the following January

Yes its possible, however.... Anything that is easy to calculate, should not be stored anywhere...

First of Jan Next year
Dateserial (Year(YOurDate) +1, 1,1)

 

[nick941741]

Hey, thanks for that. I am interested in hearing why you wouldn't want to store this anywhere though, what would you do instead?

Thanks

Nick

 

[The_Doc_Man]

You don't store ANYTHING that can be recomputed in a query because as a general principle, most serious databases don't work from tables. Most reports don't work from tables. You do better working from a query that, where needed, computes what needs to be computed before you build the report, form, or datasheet for a select query.

As to why? Has to do with two factors.

1. (Less relevant on modern computers) - Storing something that can easily be recomputed makes the stored record longer. Requires more space. Slows down data retrieval because the length of the record governs how many records fit in a single buffer and that in turn governs how long it takes you to read the disk for all buffers. Computing a value in a query is far faster. Computers do 1 billion operations per second these days, but still cannot expect disk reads faster than about 100 per second. (And that's BLAZING fast.) So compute in preference to storing.

2. (Always relevant on any computers) - If you store something that you could have computed and then some time later have to go back to update that field in that record, you have to go update the related field. You just doubled the amount of maintenance work required for the DB in question. Or tripled, quadupled, etc. Minimize your update work to the single place where that update is relevant and use queries for everything else.

 

[namliam]

You don't store ANYTHING that can be recomputed in a query

ANYTHING is a big word, but as a general 99.99999999999999999999999% proof yes...

It is one of the basic principles of database design "normalization" if you dont know about that than go google it and learn the 1st, 2nd and 3rd normal form atleast if not 4 and 5...

 

[nick941741]

Thanks for the replies guys, it makes sense now.

It was basically so I can make the membership expire on that date, or not expire if they are the other type of member.

I will just run this as a report instead when its required and calcualte it then.

Looks like I need to go back and review some of my other fields too!

Oops