Coding assistance required

[Guest]

Hi

Im currently doing a database as a favour to a friend who runs a fitness
centre and she wants a couple of things that i've never done before, i have
vague ideas of what to dobut could use some help

here we go

I have a member ID field (key), a surname field and a date of birth field,
she wants the member ID to consist of the surname and the date of birth,
example:

Surname: adams
DOB: 01/01/01
Membership_ID: adams010101

Im pretty clueless on this one, im not sure if its even possible

I also have 2 date tiem fields, one is supposed to be "todays date" and the
other is supposed to be "todays date + 1 year" how would i go about setting
that up

Lastly, when the day = friday she wants 2 queries to be run, one to find out
poeples birthdays in the next week and one to find out whos memberhsips are
up, again im clueless, can someone lend a hand please

 

[Guest]

Hi.

Are you trying to calculate the MemberID field and store it in a table?
That is considered a bad practice, and for good reasons. The proper method
is to just calculate it whenever necessary.

I'll assume the name of your table is TableName. I'll also assume the
MemberID field should be in the order of year, month, day, as your sample
value of 01/01/01 does not make this very clear. The following query will
show the Surname and DOB fields, along with the calculated MemberId values as
you requested:
SELECT TableName.Surname,
TableName.DOB,
[Surname] & Format(DOB,"yy") & Format(DOB,"mm") & Format(DOB,"dd")
AS MemberID
FROM TableName;

However, I would suggest using a four-digit year instead of a two-digit one.
Iin the first Format statement above, you can replace "yy" with "yyyy" to
obtain a four-digit year.


To add one year to a date, use the DateAdd function:
DateAdd("yyyy",1,DateField)


The following query will show those people whose birthdays occur in the next
seven days. So, when run on Friday, it will show those people whose
birthdays occur in the next week, the next week being the following Saturday
through the next Friday. It is somewhat clumsy (there's probably a better
way), but it works:
SELECT TableName.Surname,
TableName.DOB,
[Surname] & Format(DOB,"yy") & Format(DOB,"mm") & Format(DOB,"dd")
AS MemberID
FROM TableName
WHERE Format([DOB],"mmdd") in (
Format(DateAdd("d",1,Date()),"mmdd"),
Format(DateAdd("d",2,Date()),"mmdd"),
Format(DateAdd("d",3,Date()),"mmdd"),
Format(DateAdd("d",4,Date()),"mmdd"),
Format(DateAdd("d",5,Date()),"mmdd"),
Format(DateAdd("d",6,Date()),"mmdd"),
Format(DateAdd("d",7,Date()),"mmdd"));


Since you did not provide information as to how to determine expired
memberships, I cannot help you there.


I'll be unable to reply again until Wednesday, but post back if you have
problems, and most likely someone else will be able to help you.


-Michael