Need help with this query -thx 1

[DanKay1]

RateTable
Sex Age Dur1 Dur2 Dur3 Dur4
M 30 .5 .6 .7 .8
F 31 .3 .4 .5 .4
M 28 .4 .5 .6 .7

ClientTable
Name Sex Age Duration
John M 28 2

I need to figure out how to obtain Rate for John based on Sex, Age, and Duration, which should = .5
Is there a query or VBA code for it with out changing format of RateTable?

Thanks in advance.

 

[smedvid]

I would suggest normalizing the rate table to list duration values indivually. What id you add 20 more Durations? 20 more fields? The better approach is to have a table such as following:

Sex, Age, Dur_Code, Dur_Rate
M 30 1 .5
M 30 2 .6
M 30 3 .7
M 30 4 .8

In lieu of changing the structure, you can extract information from the table by dynamically constructing the SQL as follows.

'Pseudo Code...
Dim lcSQL as string
Dim ThisDB as DAO.Database
Dim rs as DAO.RecordSet

'Create a Record Set to Loopthrough.
lcSQL = "Select * from ClientTable"
Set rs = ...lcSQL...
Do While rs.EOF() = False
lcDuration = rs.Duration

....
lcSQL = "Select RateTable.Dur" & lcDuration & " "
lcSQL = lcSQL & "From ClientTable, RateTable "
lcSQL = lcSQL & "Where ClientTable.Sex = RateTable.Sex AND"
lcSQL = lcSQL & "ClientTable.Age = RateTable.Age
...

Create Record Set or Run Query... Or Insert to Table?

rs.movenext

Loop



Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[DanKay1]

is it possible to do something like this in access:

SELECT ClientTable.name, ClientTable.sex, ClientTable.age, (CASE ClientTable.Duration
WHEN 1 THEN RateTable.Dur1
WHEN 2 THEN RateTable.Dur2
WHEN 3 THEN RateTable.Dur3
WHEN 4 THEN RateTable.Dur4
ELSE -1) rate
FRON ClientTable, RateTable
WHERE ClientTable.sex = RateTable.sex
AND ClientTable.age = RateTable.age

 

[smedvid]

Acutally, I think you can embed that via IIF()

U can try somethink the following...

Select ...
IIF(ClientTable.Duration = 1, RateTable.Dur1,
IIF(ClientTable.Duration = 2, RateTable.Dur2,
IIF(ClientTable.Duration = 3, RateTable.Dur3,
IIF(ClientTable.Duration = 4, RateTable.Dur2,-999)))) as rate
...



Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[PHV]

You may consider normalizing your RateTable with a saved query, say qryRateNormalized:
SELECT Sex, Age, 1 As Duration, Dur1 As Rate FROM RateTable
UNION SELECT Sex, Age, 2, Dur2 FROM RateTable
UNION SELECT Sex, Age, 3, Dur3 FROM RateTable
UNION SELECT Sex, Age, 4, Dur4 FROM RateTable
;
Then your desired query may look like this:
SELECT C.Name, C.Sex, C.Age, R.Rate
FROM ClientTable C INNER JOIN qryRateNormalized R
ON (C.Sex=R.Sex) AND (C.Age=R.Age) AND (C.Duration=R.Duration)
;
Or like this:
SELECT C.Name, C.Sex, C.Age, Nz(R.Rate, -1) As Rate
FROM ClientTable C LEFT JOIN qryRateNormalized R
ON (C.Sex=R.Sex) AND (C.Age=R.Age) AND (C.Duration=R.Duration)
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244