Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2002
    Posts
    27

    Unanswered: Query help - don't know where to start!

    Dear all,

    I have a table structure that is out of my control that I need to query. It is a table that holds information about staff activity in the morning, afternoon and evening of each day of the week. Fields as follows:

    UserID
    Date (the monday of the week in question)
    MonMorn
    MonArvo
    MonEven
    TueMorn
    TueArvo
    TueEven
    .
    .
    .
    FridayMorn
    FridayArvo
    FridayEven

    Each of these fields can have several results in them including:
    OT
    Admin
    Train
    Sick
    Hols

    I want to run a query (or queries) that ends up giving me a table that has a line for each UserID with
    UserID, TOTALDAYS, OT, Admin, Train, Sick, Hols where each of the types is a total for a time period (usually a month) and TOTALDAYS is the sum of the rest....

    Any help would be much appreciated... I don't really know where to start.

    Bentley

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "I have a table structure that is out of my control"

    it's frankly awful

    "Each of these fields can have several results in them"

    can you give examples?


    rudy

  3. #3
    Join Date
    Jul 2002
    Posts
    27
    "Each of these fields can have several results in them"

    "can you give examples?"

    Only options for each (and every) field is as follows:
    OT
    Admin
    Train
    Sick
    Hols
    DGE
    Other

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so, an example of MonMorn would be

    'Admin,Train,Train,DGE,Train'

    what i'm looking for is actual examples

    also, what is the datatype of those fields?

  5. #5
    Join Date
    Jul 2002
    Posts
    27
    table structure etc is as follows:

    I will list a tuple (in a column below) and two potential values for each


    Weekstart : 01/01/2003 : 08/01/2003 (date field)
    MonMorn : OnTerr :Admin (text field)
    MonArvo : OnTerr :Training
    MonEven : DGE :NULL
    .
    .
    .
    .
    .
    etc etc

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nope, i'm lost

    can you show me the DDL, the CREATE TABLE statement

    then run SELECT * FROM TABLE and show what that produces

Posting Permissions

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