Picking out only one number in a table that has four values

  • Thread starter Thread starter Lee
  • Start date Start date
L

Lee

I have a table that has 9 OutPut types numbered one to
nine (OutPutType1) Each output type has 4 values in each
0,1,2,3 I want to pick out only the outputstypes that has
a 3 and show them in a query.
 
Have you tried putting a 3 in the criteria line of the query (show all nine OutputTypes in the query, and put a 3 in each one's criteria line)... this will show all records with a 3 in that particular field.
 
-----Original Message-----
Have you tried putting a 3 in the criteria line of the
Click to expand...
query (show all nine OutputTypes in the query, and put a 3
in each one's criteria line)... this will show all records
with a 3 in that particular field.
.
Click to expand...

Yes, it returns all zeros
 
-----Original Message-----
Have you tried putting a 3 in the criteria line of the
Click to expand...
query (show all nine OutputTypes in the query, and put a 3
in each one's criteria line)... this will show all records
with a 3 in that particular field.
.
Click to expand...
this is the code that is in the Query.

SELECT BIGSAW.DATE, BIGSAW.OUTTYPE1, BIGSAW.OUTTYPE2,
BIGSAW.OUTTYPE3, BIGSAW.OUTTYPE4, BIGSAW.OUTTYPE5,
BIGSAW.OUTTYPE6, BIGSAW.OUTTYPE7, BIGSAW.OUTTYPE8,
BIGSAW.OUTTYPE9
FROM BIGSAW
WHERE (((BIGSAW.DATE) Between [Forms]![MainForm]![Start]
And [Forms]![MainForm]![End]));
 
I have a table that has 9 OutPut types numbered one to
nine (OutPutType1)
Click to expand...

In that case you have an improperly normalized table, which is why
you're having difficulty. If you have a one to many relationship to
outputtypes, you should have *two* tables - rather than one value for
each field use one value for each *record* in the many side table.
Each output type has 4 values in each
0,1,2,3 I want to pick out only the outputstypes that has
a 3 and show them in a query.
Click to expand...

Use a Query with 3 on the criteria line under each of the nine fields,
putting each criterion on a different line in the query grid. This
will use OR logic and return a record if any one of the fields
contains 3.
 
OUCH! You need to look at normalizing your table structure. See John Vinson's reply.

If you cannot, then you could use a query that looked like the one below:

SELECT BIGSAW.DATE, BIGSAW.OUTTYPE1, BIGSAW.OUTTYPE2,
BIGSAW.OUTTYPE3, BIGSAW.OUTTYPE4, BIGSAW.OUTTYPE5,
BIGSAW.OUTTYPE6, BIGSAW.OUTTYPE7, BIGSAW.OUTTYPE8,
BIGSAW.OUTTYPE9
FROM BIGSAW
WHERE BIGSAW.DATE Between [Forms]![MainForm]![Start]
And [Forms]![MainForm]![End] AND
(BIGSAW.OUTTYPE1 = 3 OR BIGSAW.OUTTYPE2 = 3 OR
BIGSAW.OUTTYPE3 = 3 OR BIGSAW.OUTTYPE4 = 3
OR BIGSAW.OUTTYPE5 = 3 OR
BIGSAW.OUTTYPE6 = 3 OR BIGSAW.OUTTYPE7 = 3
OR BIGSAW.OUTTYPE8 = 3 OR
BIGSAW.OUTTYPE9 = 3)


-----Original Message-----
Have you tried putting a 3 in the criteria line of the
Click to expand...
query (show all nine OutputTypes in the query, and put a 3
in each one's criteria line)... this will show all records
with a 3 in that particular field.
.
Click to expand...
this is the code that is in the Query.

SELECT BIGSAW.DATE, BIGSAW.OUTTYPE1, BIGSAW.OUTTYPE2,
BIGSAW.OUTTYPE3, BIGSAW.OUTTYPE4, BIGSAW.OUTTYPE5,
BIGSAW.OUTTYPE6, BIGSAW.OUTTYPE7, BIGSAW.OUTTYPE8,
BIGSAW.OUTTYPE9
FROM BIGSAW
WHERE (((BIGSAW.DATE) Between [Forms]![MainForm]![Start]
And [Forms]![MainForm]![End]));
Click to expand...
 
Back
Top