Sorting by multiple critera

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello all

here is my current dilemma I have the following table with information the
first column that is unique, I then have 20 fields that are either yes or no
to each column entry. How can I create a query that will extract the unique
entry and only the fields that are yes, I do not wish to display the no
fields.
 
Could you provide a sample of your records and how you would expect them to
display in a query? Are you more concerned about their appearance in a
report than a query?
 
Hello all

here is my current dilemma I have the following table with information the
first column that is unique, I then have 20 fields that are either yes or no
to each column entry. How can I create a query that will extract the unique
entry and only the fields that are yes, I do not wish to display the no
fields.
Click to expand...

This may be VERY difficult. Queries assume that the recordset has the
same "shape" for each record. You can't have a Query which has eleven
fields in one record, six fields in the next, and so on.

Could you post a sample of the desired appearance of your results?


John W. Vinson[MVP]
 
Here goes the field that is unique is called location
The other fields represent objects that might exist or not exist at the
location

Example

Location field 1 field2 field3 field4 field5 .....fieldxx
a yes no yes no no etc
b yes yes no yes yes
c no no no no yes

I wish to create a query where I enter a specific location and only return
the fields that have a yes value and not to display the no fields

Enter a field1 and field3 appear
Enter c only field 5 apperar
 
Create a normalizing union query like:
SELECT Location, Field1 as TheValue, 1 as TheField
FROM tblTooManyYesNos
WHERE Field1 = -1
UNION ALL
SELECT Location, Field2, 2
FROM tblTooManyYesNos
WHERE Field2 = -1
-- etc --
;

You can then query for the location and only see the yes field names.
 
Back
Top