NewB query help....

[have_a_cup]

I must be missing the obvious here...so go easy on me...I need to
query 8 fields that have the same name w/ a numeric extension. I
don't want to create 8 queries unless I have to...The actual record
set contains constants in terms of data entered...here's my generic
example

Ref 1 Ref 2 Ref 3
NE AR EA
EA NE AP
AP NE NE

whats the best way to query all Ref fields for with the 'NE'?

thanx in advance

 

[Jason Lepack]

The best way is to Normalise your database.

I figure there's some identifier field (we'll call it ID) that you've
left out of this merry conglomeration, so your table looks like this:

ID, Ref1, Ref2, Ref3
1, NE, AR, EA
2, EA, NE, AP
3, AP, NE, NE

A better design would be

ID, RefID, Value
1, 1, NE
1, 2, AR
1, 3, EA
2, 1, EA
2, 2, NE
2, 3, AP
3, 1, AP
3, 2, NE
3, 3, NE

So now, when you want to see the ID and RefID with a value of NE:

SELECT
ID,
RefID
FROM
YourTable
WHERE
Value = 'NE'

But, if you must stick with your initial design:

SELECT
[Ref 1],
[Ref 2],
[Ref 3]
FROM
YourTable
WHERE
[Ref 1] = 'NE'
OR [Ref 2] = 'NE'
OR [Ref 3] = 'NE'

All the code I gave can be entered in SQL view of the Query Desgner
(View -> SQL View)

Oh, and if you don't redesign, it'll be funny when 6 months down the
road the client (or your boss...) wants to have a 4th ref.

Cheers,
Jason Lepack