1. Registered User
Join Date
Nov 2004
Posts
4

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

stephen

2. Registered User
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!

stephen

3. Registered User
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. Registered User
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. Registered User
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