Complex query

[Radhika]

I am trying to create a query that uses fields from one table : tbl_Records
The fields of interest are:
ID# (Text Field)
Name (Text Field)
Date (Text Field)
Stomal Stenosis (check box)
Granulation Donut (check box)
Stricture (check box)

I want to create a query that places a 'Yes' (in a new field called
complications)against the Last Date for each ID# if Stomal Stenosis,
Granulation Donut or Stricture were ever selected at 'Yes' accross any date
for each ID#.

For example
ID# Date Stomal Stenosis Granulation Donut Stricture
Complications
152697 01/01/2001 Yes
152697 03/04/2006 Yes
152697 09/02/2008 Yes
Yes
549687 05/08/2004 Yes
549687 02/09/2005
Yes

How can I go about doing this?

Thank you,
Radhika

 

[Allen Browne]

That's not going to work with the table the way it is.

Instead of a bunch of yes/no fields, you need to create a relational
structure with many *records* instead of many fields. See:
Don't use Yes/No fields to store preferences
at:
http://allenbrowne.com/casu-23.html

You will need:
- a table with one record for each ID# and name (whatever it is)
- a table with one record for each kind of thing you are testing for
- a junction table between these 2, with the date field.

You can then determine whether there is any testing issue for an ID# merely
by testing if there is any matching record in the junction table. You will
not store this indicator is a field.

The principles I'm outlining here are core concepts in data normalization.
If you want to do some reading about this most crucial database idea, here's
a starting point:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

As a minor point, avoid fields named Name and Date. These will cause you
grief. Here's a list of the names you need to avoid:
http://allenbrowne.com/AppIssueBadWord.html

 

[Radhika]

I am not authorized to change the way the records are stored in the table.

However, I have come close to my solution, but am having a problem.
Here is what I did.
1. I created a query that has one record for each thing I am testing for. It
says 'Yes' against each record if any options were selected.
2. I created a query that only has the last date for each record.
3. I linked query's 1 and 2 by ID# and dragged in the LastDate field, so now
I have the same date against the same ID#'s instead of multiple dates.
4. In query 3, I selected Count in design veiw for the field that says
'Yes'. So now my query looks something like this:

ID# LastDate CountNewField
152697 9/2/08 4
549687 2/905 2

So I am close. I tried to create a 5th query with a new field
'Complications' with the expression: IIf([ConutNewField]<>0,'Yes'). However,
It keeps giving me an error msg '#Error'. Is this because IIf functions do
not work with Count fields? How else can I have a field that says 'Yes'
against every ID# that has a number other than 0 associated with it?

Thank you,
Radhika

 

[Allen Browne]

It can be very frustrating when you're stuck with non-normalized data.

See if this approach solves your immediate problem:

SELECT [ID#], [Date],
IIf(Sum([Stomal Stenosis] + [Granulation Donut] + [Stricture]) <> 0, "Yes",
"No") AS HasComplication
FROM tbl_Records
GROUP BY [ID#], [Date]

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am not authorized to change the way the records are stored in the table.

However, I have come close to my solution, but am having a problem.
Here is what I did.
1. I created a query that has one record for each thing I am testing for.
It
says 'Yes' against each record if any options were selected.
2. I created a query that only has the last date for each record.
3. I linked query's 1 and 2 by ID# and dragged in the LastDate field, so
now
I have the same date against the same ID#'s instead of multiple dates.
4. In query 3, I selected Count in design veiw for the field that says
'Yes'. So now my query looks something like this:

ID# LastDate CountNewField
152697 9/2/08 4
549687 2/905 2

So I am close. I tried to create a 5th query with a new field
'Complications' with the expression: IIf([ConutNewField]<>0,'Yes').
However,
It keeps giving me an error msg '#Error'. Is this because IIf functions do
not work with Count fields? How else can I have a field that says 'Yes'
against every ID# that has a number other than 0 associated with it?

Thank you,
Radhika

That's not going to work with the table the way it is.

Instead of a bunch of yes/no fields, you need to create a relational
structure with many *records* instead of many fields. See:
Don't use Yes/No fields to store preferences
at:
http://allenbrowne.com/casu-23.html

You will need:
- a table with one record for each ID# and name (whatever it is)
- a table with one record for each kind of thing you are testing for
- a junction table between these 2, with the date field.

You can then determine whether there is any testing issue for an ID#
merely
by testing if there is any matching record in the junction table. You
will
not store this indicator is a field.

The principles I'm outlining here are core concepts in data
normalization.
If you want to do some reading about this most crucial database idea,
here's
a starting point:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

As a minor point, avoid fields named Name and Date. These will cause you
grief. Here's a list of the names you need to avoid:
http://allenbrowne.com/AppIssueBadWord.html

 

[Radhika]

Thank you!

It can be very frustrating when you're stuck with non-normalized data.

See if this approach solves your immediate problem:

SELECT [ID#], [Date],
IIf(Sum([Stomal Stenosis] + [Granulation Donut] + [Stricture]) <> 0, "Yes",
"No") AS HasComplication
FROM tbl_Records
GROUP BY [ID#], [Date]

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am not authorized to change the way the records are stored in the table.

However, I have come close to my solution, but am having a problem.
Here is what I did.
1. I created a query that has one record for each thing I am testing for.
It
says 'Yes' against each record if any options were selected.
2. I created a query that only has the last date for each record.
3. I linked query's 1 and 2 by ID# and dragged in the LastDate field, so
now
I have the same date against the same ID#'s instead of multiple dates.
4. In query 3, I selected Count in design veiw for the field that says
'Yes'. So now my query looks something like this:

ID# LastDate CountNewField
152697 9/2/08 4
549687 2/905 2

So I am close. I tried to create a 5th query with a new field
'Complications' with the expression: IIf([ConutNewField]<>0,'Yes').
However,
It keeps giving me an error msg '#Error'. Is this because IIf functions do
not work with Count fields? How else can I have a field that says 'Yes'
against every ID# that has a number other than 0 associated with it?

Thank you,
Radhika

That's not going to work with the table the way it is.

Instead of a bunch of yes/no fields, you need to create a relational
structure with many *records* instead of many fields. See:
Don't use Yes/No fields to store preferences
at:
http://allenbrowne.com/casu-23.html

You will need:
- a table with one record for each ID# and name (whatever it is)
- a table with one record for each kind of thing you are testing for
- a junction table between these 2, with the date field.

You can then determine whether there is any testing issue for an ID#
merely
by testing if there is any matching record in the junction table. You
will
not store this indicator is a field.

The principles I'm outlining here are core concepts in data
normalization.
If you want to do some reading about this most crucial database idea,
here's
a starting point:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

As a minor point, avoid fields named Name and Date. These will cause you
grief. Here's a list of the names you need to avoid:
http://allenbrowne.com/AppIssueBadWord.html

I am trying to create a query that uses fields from one table :
tbl_Records
The fields of interest are:
ID# (Text Field)
Name (Text Field)
Date (Text Field)
Stomal Stenosis (check box)
Granulation Donut (check box)
Stricture (check box)

I want to create a query that places a 'Yes' (in a new field called
complications)against the Last Date for each ID# if Stomal Stenosis,
Granulation Donut or Stricture were ever selected at 'Yes' accross any
date
for each ID#.

For example
ID# Date Stomal Stenosis Granulation Donut Stricture
Complications
152697 01/01/2001 Yes
152697 03/04/2006 Yes
152697 09/02/2008 Yes
Yes
549687 05/08/2004
Yes
549687 02/09/2005
Yes