DLookup?

[Cadburys]

Hi

I have 2 tables - tblSuburbs

Suburb (text primary key)
Zone (number)

tblZones

Zone (number primary Key)
Pax1 (number)
Pax2 (number)
Pax3 (number)
Pax4 (number)
Pax5 (number)
Pax6 (number)

So the datasheet looks like this

Zone Pax1 Pax2 Pax3
1 10 30 40
2 20 40 60

What I would like to do is on a form have the following:

Suburb as a combo box
Pax as a combo box

Fee = find zone of suburb selected and lookup in zone table as the Zone
number / if Pax1 selected then get value

eg. if zone suburb in zone 3 selected and Pax = Pax3 then value will be 60.

Can DLookup check both the value of the Zone field and the headings to then
determine the Pax?

Thank you so much for your help.

Regards
Cadburys

 

[John W. Vinson]

Hi

I have 2 tables - tblSuburbs

Suburb (text primary key)
Zone (number)

tblZones

Zone (number primary Key)
Pax1 (number)
Pax2 (number)
Pax3 (number)
Pax4 (number)
Pax5 (number)
Pax6 (number)

So the datasheet looks like this

Zone Pax1 Pax2 Pax3
1 10 30 40
2 20 40 60

What I would like to do is on a form have the following:

Suburb as a combo box
Pax as a combo box

Fee = find zone of suburb selected and lookup in zone table as the Zone
number / if Pax1 selected then get value

eg. if zone suburb in zone 3 selected and Pax = Pax3 then value will be 60.

Can DLookup check both the value of the Zone field and the headings to then
determine the Pax?

Not easily, because your table structure IS WRONG. You're "committing
spreadsheet" - storing repeating fields Pax1 through Pax6, essentially storing
data in a fieldname.

If you have a one to many relationship between a Zone and a Pax (whatever a
Pax is, may peace be with you!) you should really have a table with fields
Zone, PaxNo (values 1 to 6), and PaxValue (10 or 40 or 60 or whatever).