Parameter Query in Access 2000 - Criteria in multiple fields

[hounddog1]

I have a parameter query laid out like this:

Fname Lname Addr1 Cit Sta Zip C1 C2 C3 C4 C5 C6 C7 C8 C9

The "C" columns represent codes for mailing purposes. For example, 1 is Pastors, 2 is Organist, 3 is Pianists, etc. I need the Parameter Query to prompt for input. I'd rather have a single expression to "Ask" for the input only one time rather than nine different prompts asking for "Enter Code1, Enter Code2, etc. Additionally, there are approx 100 different Codes/categories. These are numeric values but any of the "C" fields could contain any of the 100 or so, numeric values. I need to be able to do a mailing to all "1's" or all "2's", etc. The Parameter Query needs to prompt for the "Enter Code #" and look for the selected input in any of the nine "C" fields. Does this make sense? Any help would be appreciated.

 

[KenReay]

Hi

Make a form with textbox controls to accept user input for paramters, use syntax like Forms!MyFormName!MyC1 as criteria in your query

You have made life difficult for yourself with your table design, by breaking normalisation rules, life would ahve been easier if you had split you table into two related tables tblNames, tblCategory, linked on NameId, something like

tblNames
lngNameId PK (Autonumber)
Fname
Lname
Addr1
Cit
Sta
Zip

tblCategory
lngNameId (Long) ) PK
strCategory )

Note PK is Prime Key




Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[hounddog1]

Yeah, I realize this is not optimum. This data was in an old DOS database and new tables were built from that. I appreciate your help.

 

[lespaul]

you could write a query that normalizes your data and use that as the source for your search.

Leslie

 

[hounddog1]

Hey lespaul,
Could you be more specific? I think I know what you mean but I'm not sure...

 

[lespaul]

could you give some sample data from the table? That makes it easier to help design the query.

leslie

 

[PHV]

And what about something like this in your WHERE clause ?
... And (
C1=[Enter Code #] Or C2=[Enter Code #] Or C3=[Enter Code #]
... Or C9=[Enter Code #]) ...

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

 

[hounddog1]

Should I email it to you or lay it out here?

 

[lespaul]

just lay it out here!

PHV, I think that's what hounddog1 is currently doing, there are nine prompts, he just wants one.

leslie

 

[PHV]

lespaul, my sql example should prompt only one time for "Enter Code #".

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

 

[hounddog1]

Here it is...

FieldNames: Data: Data:

Lastname Smith Jones
FirstName Frank Bill
MI R. L.
Title Rev. Rev.
Salutation Frank Bill
Position Pastor Interim Pastor
Address1 2911 Old Mill Road 211 Park Street
Address2 P.O. Box 1320
City_State Columbia, SC Irmo, SC
Zip 29210 29260
Phone_H 486-9530 772-1550
Phone_W 740-9511 772-1550
Birthday 7/2/1952
Anniv
Aux_Date
C1 1 2
C2 24 24
C3 59 444
C4 444 67
C5 67 76
C6 100 5
C7 39
C8 12
C9
Newslettr N
Church Bellville Baptist Colonial Baptist
ChurchNo 30 42
PositNo 10 15
Spouse
SP_Bdate
Field22(NoteField) Bill is main cont
Cell 415-7523
Fax
Email Bill7995@isp.com
-------------------------
(I laid it out this way to make it simpler..hope it's clear)

There is a table containing the "C" codes and corresponding descriptions. There are approximately 100 different items.
On my form I have a combo box (9 of them) that points to the list for easy selection and consistency. Again, this was the best I could come up with based on what I had to work with.
Here is a sampling:

Field1 Field2
1 Pastor
2 Interim Pastor
3 Minister of Music
4 Minister of Education
5 Minister of Youth
6 Executive Committee
7 Associational Officers
8 Sunday School Director
9 Pianist
10 Treasurer
11 Clerk
12 Preschool Director

 

[lespaul]

first off, did you try PHV's suggestion? Does it only prompt once?

secondly, is there a primary key in this table?

leslie

 

[hounddog1]

There's no primary key and I'm trying PHV's suggestion right now. Did the data layout make sense?

 

[hounddog1]

Hey PHV,
Your suggestion did the trick. Works Great! Thanks for your help! Also thanks to lespaul. (Is this derived from the Guitar, Les Paul?)

 

[lespaul]

no, i'm leslie and my husband is paul!

you're not the first to ask though!

leslie