Querying categories

[John]

I want to make a query to categorize my data for a report. Let's say I have
YesNo fields A,B, and C. On the basis of the combination of the values of
those fields I would like to give the different combinations one temporary
status value (one for each combination) in a new field. Example of the data:
ID A B C
1 Y N N
2 Y Y N
3 Y Y Y
A query should result in:
ID Status
1 Pending
2 Not OK
3 OK
Can this easily be done in one query? I can't seem to figure it out.
Thank you.
John

 

[Jeanette Cunningham]

John,
you can do this with 2 queries.
table Categories
PK CatID
A
B
C

table Status
StatusTot StatusDescr
1 Pending
2 Not OK
3 OK

1st query
SELECT Categories.CatID, IIf([A]=-1,1,0)+IIf(=-1,1,0)+IIf([C]=-1,1,0) AS
CatCount
FROM Categories;
Save As qCountCat

2nd query
create a query using qCountCat and Categories, joining CatCount from
Categories with CatTot from Status
SELECT qCountCat.CatID, Status.Status
FROM qCountCat INNER JOIN Status ON qCountCat.CatCount = Status.StatusTot;

Jeanette Cunningham

 

[Paola]

Esta es una prueba

 

[Paola]

Es solo una prueba

 

[Jeanette Cunningham]

John,
create a table that will look up the status like this:
--StatusTot long integer
--StatusDescr text
--name the table Status

StatusTot StatusDescr
1 Pending
2 Not OK
3 OK


Create 2 queries like this:
I put your original data in a table called Categories with a field called
CatID
I created the 1st query like this:
SELECT Categories.CatID, IIf([A]=-1,1,0)+IIf(=-1,1,0)+IIf([C]=-1,1,0) AS
CatCount
FROM Categories;
Save the query as qCountCat

Create the 2nd query like this:
Add both qCountCat and the table called Status to the query grid
Join the field CatCount to the field StatusTot
Put fields CatID and StatusDescr in the query.
The sql looks like this:
SELECT qCountCat.CatID, Status.StatusDescr
FROM qCountCat INNER JOIN Status ON qCountCat.CatCount = Status.StatusTot;

Jeanette Cunningham

 

[Jeanette Cunningham]

After I posted, I realised there is a simpler way.
Access stores Yes/No values as -1 for Yes and 0 for No.
Use this to simplify the query.

Create the lookup table like this.
StatusTot StatusDescr
-1 Pending
-2 Not OK
-3 OK
0 Not OK

Create the 1st query like this:
SELECT Categories.CatID, [A]++[C] AS CatCount
FROM Categories;
Save as qCatCount

Create the second query like this:
SELECT qCountCat.CatID, NewStatus.StatusDescr
FROM qCountCat INNER JOIN NewStatus ON qCountCat.CatCount =
NewStatus.StatusTot;

Jeanette Cunningham

 

[John]

Thanks!
john

After I posted, I realised there is a simpler way.
Access stores Yes/No values as -1 for Yes and 0 for No.
Use this to simplify the query.

Create the lookup table like this.
StatusTot StatusDescr
-1 Pending
-2 Not OK
-3 OK
0 Not OK

Create the 1st query like this:
SELECT Categories.CatID, [A]++[C] AS CatCount
FROM Categories;
Save as qCatCount

Create the second query like this:
SELECT qCountCat.CatID, NewStatus.StatusDescr
FROM qCountCat INNER JOIN NewStatus ON qCountCat.CatCount =
NewStatus.StatusTot;

Jeanette Cunningham

I want to make a query to categorize my data for a report. Let's say I
have
YesNo fields A,B, and C. On the basis of the combination of the values of
those fields I would like to give the different combinations one temporary
status value (one for each combination) in a new field. Example of the
data:
ID A B C
1 Y N N
2 Y Y N
3 Y Y Y
A query should result in:
ID Status
1 Pending
2 Not OK
3 OK
Can this easily be done in one query? I can't seem to figure it out.
Thank you.
John

 

[James A. Fortune]

I want to make a query to categorize my data for a report. Let's say I have
YesNo fields A,B, and C. On the basis of the combination of the values of
those fields I would like to give the different combinations one temporary
status value (one for each combination) in a new field. Example of the data:
ID A B C
1 Y N N
2 Y Y N
3 Y Y Y
A query should result in:
ID Status
1 Pending
2 Not OK
3 OK
Can this easily be done in one query? I can't seem to figure it out.
Thank you.
John

Create a new table to contain the status values assigned for each of the
eight combinations:

tblStatus
SID AutoNumber
A Y/N
B Y/N
C Y/N
Status Text
SID A B C Status
1 N N Y Not Allowed
2 N Y N Not Allowed
3 N Y Y Not Allowed
4 Y N N Pending
5 Y N Y Not Allowed
6 Y Y N Not OK
7 Y Y Y OK
8 N N N Not Allowed

Then you should be able to create an inner join on all three fields with
your first table (I'm calling it tblReportData):

qryShowStatus:
SELECT ID, tblReportData.A, tblReportData.B, tblReportData.C, Status
FROM tblReportData INNER JOIN tblStatus ON (tblReportData.C =
tblStatus.C) AND (tblReportData.B = tblStatus.B) AND (tblReportData.A =
tblStatus.A);

!qryShowStatus:
ID A B C Status
1 Y N N Pending
2 Y Y N Not OK
3 Y Y Y OK

Personally, I believe that each situation should be considered on its
own as to whether a calculation or a separate table is better. SQL
theorists tend to like a separate "all possibilities" table for all
situations.

Another issue is that for large tables indexes on Y/N fields might not
work as well as on other field types.

I am currently facing a similar status issue. ISO specifies that
certain information should be obtained about companies that do any of
your outsourcing. For example, "Are they ISO Certified?", "Are they a
preferred outsourcer?", "Are they D6-51991 Compliant?", "Are they a sole
source supplier?", etc. Much of this information is stored in Y/N
fields. Some of those fields taken together, along with performance
metrics, can determine an outsourcer status. If the outsourcer status
can be calculated from the Y/N fields, then it should be a calculated
field. If the customer wants to be able to select an outsourcer status
and have Access change the Y/N fields appropriately then a transition
has to be planned because existing forms assume that the user is going
to choose the Y/N values and that the status will be calculated from those.

James A. Fortune
(e-mail address removed)

 

[John]

Thanks. In fact I can use both Jeanettes approach as yours in this matter.
For some outcome I can only use yours.
Thanks again, John

 

[Monik Meyer]

Le 9/02/08 18:50, dans (e-mail address removed), « Paola »

 

[Monik Meyer]

Le 9/02/08 18:50, dans (e-mail address removed), « Paola »

 

[Monik Meyer]

Le 9/02/08 18:50, dans (e-mail address removed), « Paola »

 

[Monik Meyer]

Le 9/02/08 18:50, dans (e-mail address removed), « Paola »