Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2006
    Posts
    103

    Unanswered: SQL query problem

    is there a way to do a query for eg.

    table A: has fields

    Ref_ID headcount StartMonth EndMonth
    Ref001 10 Jan Jun
    Ref002 11 Jan Mar
    Ref002 9 Apr Sep

    if i want to make a query to have a same table structure, but only the StartMonth and EndMonth, i want it to have a number instead of Jan, eg. Jan=1, Feb=2, Mar=3 etc...

    the reason is i need to sum up all the headcount for example startMonth is Jan to Jun,

    so i can use the new query in the whereclause i can put startmonth>=1 AND endmonth<=6

    Thanks.

  2. #2
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Are the 'StartMonth' and 'EndMonth' fields stored as strings (i.e. text)?

    You could covert them as follows:
    Code:
    Switch([strMonth]="JAN",1,[strMonth]="FEB",2,[strMonth]="MAR",3,[strMonth]="APR",4,[strMonth]="MAY",5,[strMonth]="JUN",6,[strMonth]="JUL",7,[strMonth]="AUG",8,[strMonth]="SEP",9,[strMonth]="OCT",10,[strMonth]="NOV",11,[strMonth]="DEC",12)
    OR
    Code:
      Month(DateValue([strMonth] & " 1, 2000"))
    Usually, I have a table that stores values Months.

    tblMonth
    Code:
    MonthID    strMonthShort    strMonthLong
    1          Jan              January
    2          Feb              February
    3          Mar              March
    4          Apr              April
    ...
    12         Dec              December
    I use this as a lookup field in other tables to store the month. I'll have a combobox that uses tblMonth as its recordsource bound to a field that stores a 'month'.

    Another alternative is to store a full date, that way you have the year and month in one field. You can code your data entry form to add a date that is the first or last day of the the month, based on a month and year entered/selected by the user.
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  3. #3
    Join Date
    Jun 2006
    Posts
    103
    yes...is text 50 characters

    the switch code works just fine, thats the thing i need, now my vba code just need to sum up those which is start month=1 and end month=1 for Jan data. and the rest.

    thanks cosmos

  4. #4
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    mkggoh,

    Glad to be of help.
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •