Results 1 to 5 of 5

Thread: lookup field

  1. #1
    Join Date
    Nov 2004
    Posts
    4

    Unanswered: lookup field

    i am trying to create a field that displays the contents of (one of a number of different fields) depending on the (contents of a further field)

    i can do this in excel using the following formula:

    =INDEX(C14:E14, MATCH(G14,$C$1:$E$1,0))

    where C14:E14 = (one of a number of different fields)
    G14 = (contents of a further field)
    $C$1:$E$1 = (range of field names from which G14 chooses)

    does anyone know how i can do this in an access query? i'm sure it must be simple but i can't figure it out

    many thanks in advance
    stephen

  2. #2
    Join Date
    Nov 2004
    Posts
    4

    more detail - can anyone help?!?

    thought i'd add some info to put my problem into context:

    i need a lookup query to allocate the number of hours needed for one of four possible jobs at any of an infinite number of locations

    the hours required for each job are specified in fields (job1 etc.) the job is chosen in another field (job type) but i need a further field to display the correct hours required for the selected job

    i have a query with the following headings:

    location, job1, job2, job3, job4, job type (1-4)

    i'm trying to create a field that displays the number of hours for each location depending on the job type

    would much appreciate any help - sorry if it seems a very basic task!

    thank you in advance

    stephen

  3. #3
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Hi
    I'm not quite sure I understand what you are asking.

    location, job1, job2, job3, job4, job type (1-4)

    i'm trying to create a field that displays the number of hours for each location depending on the job type
    Do you mean you are trying to create a value under the correct jobx heading depending on the value of job type ? So three of the values will be blank and one will have a value ?

    It would help if you explained your table structure a little. If I had to guess, I would design the tables as follows:

    Table: Jobs
    Location JobNumber JobType
    Bedford____0001_______4
    Luton______0002_______3
    London____0003_______4
    Swansea___0004_______2

    Table: JobRates
    JobType____Hours
    1__________3
    2__________2.5
    3__________4
    4__________6

    There would be a relationship between JobType in the two tables. The query you want is then simply created from a cross-tab query, something like this:

    Code:
    TRANSFORM Sum(Rates.Hours) AS SumOfHours
    SELECT Jobs.Location
    FROM Rates INNER JOIN Jobs ON Rates.JobType = Jobs.JobType
    GROUP BY Jobs.Location
    PIVOT Jobs.JobType;
    The output would look like:

    Location___1____2____3____4
    Bedford____0____0____0____6
    Luton______0____0____4____0
    London_____0____0____0____6
    Swansea___0____2.5___0____0


    HTH
    Chris

  4. #4
    Join Date
    Nov 2004
    Posts
    4
    thank you very much for help, the trouble is, each location has different set of job rates...

    location,___job1,____job2,_____job3,____job4,_____ job_type,_____hours

    Bedford,____1_________1________1__________2_______ job2_______1
    Luton,______1_________1________2__________2_______ job1_______1
    London,_____0_________0________0__________1_______ job3_______0
    Swansea,____2_________2________2__________3_______ job2_______2

  5. #5
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Then I would recommend your rates table should look like this:

    Table: Rates
    Location_____JobType____Hours
    Luton_________1__________3
    Luton_________2__________2.5
    Luton_________3__________4
    Luton_________4__________6
    London________1__________5
    London________2__________8
    London________3__________9
    London________4__________10
    etc

    Then your relationship is made by two fields Location and JobType.

    Maybe I'm missing the point here. If you could describe the structure of your current tables maybe I can help further.

    Chris

Posting Permissions

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