Help Needed - Parameter Query from Multiple fields

[Ty-Uk]

Hi - i'm sure this is very simple but i can't fugure it out so would appreciate some help.

i would like to create a parameter query that searches through 3 seperate fields - ie the 3 fields are boat park 1, boat park 2 and boat park 3.....when prompted i would like to enter a boatpark number and automatically search the 3 boat parks for that number.

many thanks in advance for your help

Ty

 

[rainman89]

First off, i dont think you should have 3 seperate fields for boat park.. you should have one field called boat park and have the 3 listed in there. That will make it easier to search for what you are looking for.

What does your table structure look like?

 

[redneckgeek]

To avoid having to enter the parameter 3 times (once for each field),
use a subquery to get the parameter:

SELECT TABLE1.* 
FROM TABLE1, (SELECT TOP 1 [ENTER NUMBER] AS SEARCHID FROM any_table) SRCH
WHERE BOAT_PARK1 = SRCH.SEARCHID 
OR BOAT_PARK2 = SRCH.SEARCHID 
OR BOAT_PARK3 = SRCH.SEARCHID

 

[maxmangion]

as rainman suggested it will be better if you explain your table structure before you move on, as it seems clear from the fields you mentioned that your table(s) are not normalized.

 

[Ty-Uk]

First off, i dont think you should have 3 seperate fields for boat park.. you should have one field called boat park and have the 3 listed in there. That will make it easier to search for what you are looking for.

What does your table structure look like?

Unfortunatley it's a database i've inherited and can't really alter it much (as it's the sailing clubs) the reason it has 3 sperate fields is just to make it easier to grab the data for a mail merge, as each field has different pricing.

as i'm a relative novice i dont really understand what you mean when you say "whats you table structure look like?" sorry for sounding thick

 

[rainman89]

well table structure would mean...

your table name then all the fields that you have in your tables.

 

[Ty-Uk]

well table structure would mean...

your table name then all the fields that you have in your tables.

table name is Membership

Fields are Mem Number (primary key)
Name
Address
Phone
1 park number
1 boat number
1 boat fee
2 park number
2 boat number
2 boat fee
3 park number
3 boat number
3 boat fee

there are about another 15 but they are only to do with billing and family info

 

[rainman89]

grr.... this is really not normalized... inheriting databases sometimes sucks!!

you should have separate tables for park and boat.... maybe even fee...

sure you cant remodel the system? itll make it easier on you

 

[Ty-Uk]

grr.... this is really not normalized... inheriting databases sometimes sucks!!

you should have separate tables for park and boat.... maybe even fee...

sure you cant remodel the system? itll make it easier on you

yes it's becoming apparant i should start a new one and do it properly - so that's what i'll do instead of butchering this one, many thanks for your help !!!

 

[rainman89]

Great idea!!

Post back if you need help!

Good luck