Creating query which selects only 'no' fields

[Guest]

I have a table (tblAlert) which has a large amount of yes/no fields as an
answer to whether different people have responded to an alert that has been
sent out. The table also includes a review date. I want to create a query for
the basis of a report which requires two things:

1. Only become active once the review date has passed - which I have done by
placing the following in the criteria box - Now>[Review Date].

2. I only want to include fields that have a "No" answer.

Thank you

 

[Roger Carlson]

This is impossible the way you have set up your table. A query cannot
return different fields for each record. You have to specify a single field
list for *all* records.

You have this problem because of your multiple yes/no fields. If instead,
you had a separate table with the Alert text in one field and the Answer
stored in another, you could easily query this.

I'm only guessing, but I'd say you are looking at a Many-to-Many
relationship between People and Alerts. As such, you should have a People
table, an Alert table, and an linking table (People/Alert). Something like
this:

People
=====
PeopleID (pk)
Name
<other info>

Alerts
====
AlertID (pk)
AlertText

PeopleAlert
========
PeopleID (cpk)
AlertID (cpk)
Answer

But as I said, without knowing more about your business rules, I'm just
guessing.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Thanks Roger,

I have taken your advice and set up an alert reply table named
'tblAlertReply'. What I want to happen is:

1. Each alert has the same people responding. Each respondent has their own
ID, a few other fields such as name, department etc. and a yes/no field named
'Replied' which will be checked by the user when a reply is received. I have
linked the tables through the AlertID and I am under the impression this
would require a one-to-many relationship as one alert has many reponses.

2. I have created a form named frmAlert with the record source being the
table containing the information with regards to the alert named 'tblAlert'.
I have then created a sub-form within this form with the record source being
'tblAlertReply'. I want this form to show the alert and then the sub-form
will show in datasheet mode who has replied. But when I change the alert all
the respondents change. I understand why as the a;ert responses are linked to
the alerts through the 'alertID' but I wondered if there was a possible way
of having the same respondents to each alert in this way.

3. I also want to write the query as mentioned before.

Sorry if this is a bit all over the place

Thanks

This is impossible the way you have set up your table. A query cannot
return different fields for each record. You have to specify a single field
list for *all* records.

You have this problem because of your multiple yes/no fields. If instead,
you had a separate table with the Alert text in one field and the Answer
stored in another, you could easily query this.

I'm only guessing, but I'd say you are looking at a Many-to-Many
relationship between People and Alerts. As such, you should have a People
table, an Alert table, and an linking table (People/Alert). Something like
this:

People
=====
PeopleID (pk)
Name
<other info>

Alerts
====
AlertID (pk)
AlertText

PeopleAlert
========
PeopleID (cpk)
AlertID (cpk)
Answer

But as I said, without knowing more about your business rules, I'm just
guessing.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have a table (tblAlert) which has a large amount of yes/no fields as an
answer to whether different people have responded to an alert that has
been
sent out. The table also includes a review date. I want to create a query
for
the basis of a report which requires two things:

1. Only become active once the review date has passed - which I have done
by
placing the following in the criteria box - Now>[Review Date].

2. I only want to include fields that have a "No" answer.

Thank you

 

[Roger Carlson]

I'm sorry, but I'm not sure what the questions is here. Can you clarify?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thanks Roger,

I have taken your advice and set up an alert reply table named
'tblAlertReply'. What I want to happen is:

1. Each alert has the same people responding. Each respondent has their
own
ID, a few other fields such as name, department etc. and a yes/no field
named
'Replied' which will be checked by the user when a reply is received. I
have
linked the tables through the AlertID and I am under the impression this
would require a one-to-many relationship as one alert has many reponses.

2. I have created a form named frmAlert with the record source being the
table containing the information with regards to the alert named
'tblAlert'.
I have then created a sub-form within this form with the record source
being
'tblAlertReply'. I want this form to show the alert and then the sub-form
will show in datasheet mode who has replied. But when I change the alert
all
the respondents change. I understand why as the a;ert responses are linked
to
the alerts through the 'alertID' but I wondered if there was a possible
way
of having the same respondents to each alert in this way.

3. I also want to write the query as mentioned before.

Sorry if this is a bit all over the place

Thanks

This is impossible the way you have set up your table. A query cannot
return different fields for each record. You have to specify a single
field
list for *all* records.

You have this problem because of your multiple yes/no fields. If instead,
you had a separate table with the Alert text in one field and the Answer
stored in another, you could easily query this.

I'm only guessing, but I'd say you are looking at a Many-to-Many
relationship between People and Alerts. As such, you should have a
People
table, an Alert table, and an linking table (People/Alert). Something
like
this:

People
=====
PeopleID (pk)
Name
<other info>

Alerts
====
AlertID (pk)
AlertText

PeopleAlert
========
PeopleID (cpk)
AlertID (cpk)
Answer

But as I said, without knowing more about your business rules, I'm just
guessing.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have a table (tblAlert) which has a large amount of yes/no fields as
an
answer to whether different people have responded to an alert that has
been
sent out. The table also includes a review date. I want to create a
query
for
the basis of a report which requires two things:

1. Only become active once the review date has passed - which I have
done
by
placing the following in the criteria box - Now>[Review Date].

2. I only want to include fields that have a "No" answer.

Thank you

 

[Guest]

Ok,

I have a table that contains event information - 'tblEvents'. If the user
believes the event to be significant enough, they can send an alert and that
information is stored in a further table - tblAlert - is linked to the event.
Each alert requires a number of people to respond to it depending on the
department that alert is from. For these respondents I have created another
table - tblAlertReply - which contains the information on the people who need
to reply including a yes/no field - named 'replied' - which the user checks
when a reply is received. The respondents are the same for each alert and
department. For example I have three types of alert and each type of alert
has a different set of respondents. So Alert Type 1 may have 4 people to
respond, alert type 2 may have 6 people and so on but they are the same
people for each alert. The tables are related in the following way:

tblEvents ---> tblAlerts (1toM) tblAlerts ---> tblAlertReply (1toM)

I want to do two things:

Create a form and sub-form which shows the alert information and the
replies. I have created these but the respondents change depending on the
record. For example Alert 1 has the 4 necessary respondents (in the sub form)
but as soon as I move on to Alert 2, the sub form becomes blank and I have to
type in the respondents every time I create a new alert. I want to be able to
keep the respondents in the sub form and when a new alert occurs I will need
the yes/no field clearing so I can say whether that particular respondent has
replied to that particular alert.

Secondly I want to create a report which runs from a query which will show
only the people who have not responded to the alert.

Sorry if this is very complicated, I think I have explained it at a much
more complicated level than it is.

Create a form which

 

[Roger Carlson]

All right. If you want the database to know something, you have to store
it. Therefore, you need a Respondents table. Now, each Alert can have one
or more Respondent and each Respondent can respond to one or more Alerts.
This is the definition of a Many-to-Many relationship, so you have to create
a linking (intersection) table. Something like this:

tblAlerts ---> tblAlertRespondents <----tblRespondents

But if you think about it, it is the *combination* of an alert and a
respondent that has a reply, so you can think of your existing tblAlertReply
table as the linking table:

tblAlerts ---> tblAlertReply <----tblRespondents

Your tables would be something like this:

tblAlerts
--------
AlertID
AlertType
<other>

tblAlertReply
-------------
AlertReplyID
AlertID (cfk)
AlertType (cfk)
RespondentID (cfk)
Reply

tblRespondents
---------------
RespondentID
RespondentName
AlertType
<other>

You would create your subform by Joining tblAlertReply and tblRespondent
with an OUTER JOIN so all of the appropriate respondents would appear.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Ok,

I have a table that contains event information - 'tblEvents'. If the user
believes the event to be significant enough, they can send an alert and
that
information is stored in a further table - tblAlert - is linked to the
event.
Each alert requires a number of people to respond to it depending on the
department that alert is from. For these respondents I have created
another
table - tblAlertReply - which contains the information on the people who
need
to reply including a yes/no field - named 'replied' - which the user
checks
when a reply is received. The respondents are the same for each alert and
department. For example I have three types of alert and each type of alert
has a different set of respondents. So Alert Type 1 may have 4 people to
respond, alert type 2 may have 6 people and so on but they are the same
people for each alert. The tables are related in the following way:

tblEvents ---> tblAlerts (1toM) tblAlerts ---> tblAlertReply (1toM)

I want to do two things:

Create a form and sub-form which shows the alert information and the
replies. I have created these but the respondents change depending on the
record. For example Alert 1 has the 4 necessary respondents (in the sub
form)
but as soon as I move on to Alert 2, the sub form becomes blank and I have
to
type in the respondents every time I create a new alert. I want to be able
to
keep the respondents in the sub form and when a new alert occurs I will
need
the yes/no field clearing so I can say whether that particular respondent
has
replied to that particular alert.

Secondly I want to create a report which runs from a query which will show
only the people who have not responded to the alert.

Sorry if this is very complicated, I think I have explained it at a much
more complicated level than it is.

Create a form which

I have a table (tblAlert) which has a large amount of yes/no fields as an
answer to whether different people have responded to an alert that has
been
sent out. The table also includes a review date. I want to create a query
for
the basis of a report which requires two things:

1. Only become active once the review date has passed - which I have done
by
placing the following in the criteria box - Now>[Review Date].

2. I only want to include fields that have a "No" answer.

Thank you