Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Coding assistance required

Discussion in 'Information Technology' started by DarranG, Jul 28, 2006.

  1. DarranG

    DarranG
    Expand Collapse
    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
     
  2. Loading...

    Similar Threads Forum Date
    SciTech Kids "using coding skills to hack" Friends on Games Breaking News Feb 10, 2013
    Opinion Mayan tablet decoding rules out 2012 apocalypse Breaking News Dec 1, 2011
    Grammer / Vyakarn Request For Assistance With Marking Tests Language, Arts & Culture May 22, 2013
    Translation Assistance Questions and Answers Sep 9, 2012
    UK UK Police Seek Assistance: Terrifying moments - Sikh Protesters Storm Community Centre, Trash It Breaking News Oct 13, 2011

  3. Michael H

    Michael H
    Expand Collapse
    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



    "DarranG" wrote:

    > 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
     

Share This Page