help please with creating a Query with different

[Helping People Ros]

Hello: I have a database of resources to use with clients with depression,
anxiety, stress etc. I have the symptons (depression anxiety etc) as yes / no
fields. I want to make a query where you can enter the sympton e.g.
depression and the query provides a list of only those resources where the
depression box is yes. I tried putting [enter sympton] in the field of the
query and 'yes' in the criteria but that lists all the resources. Can someone
tell me where I am going wrong. Thank you.

 

[XPS350]

Hello: I have a database of resources to use with clients with depression,
anxiety, stress etc. I have the symptons (depression anxiety etc) as yes / no
fields. I want to make a query where you can enter the sympton e.g.
depression and the query provides a list of only those resources where the
depression box is yes. I tried putting [enter sympton] in the field of the
query and 'yes' in the criteria but that lists all the resources. Can someone
tell me where I am going wrong. Thank you.

I think the problem here is that you have a field for each sympton. So
the criteria field will be different for each question
(Depression=True, Anxiety=True).

The best thing to do is te redesign your database. Make 3 tables:
- Resource
- Sympton
- ResourceForSympton (contains keys of Resource and Sympton)

In that way you are more flexible (adding an sympton doesn't require a
new field) and you can always use one and the same field to select
resources for a sympton (the sympton field in ResourceForSympton).


Groeten,

Peter
http://access.xps350.com

 

[Allen Browne]

'Helping People', the advice you received from XPS350 is really important to
follow. The way you've designed the tables is a very common mistake people
make when starting out with databases.

As a further example to help you grasp what to do, see:
Don't use Yes/No fields to store preferences
at:
http://allenbrowne.com/casu-23.html

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

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

Hello: I have a database of resources to use with clients with
depression,
anxiety, stress etc. I have the symptons (depression anxiety etc) as yes
/ no
fields. I want to make a query where you can enter the sympton e.g.
depression and the query provides a list of only those resources where
the
depression box is yes. I tried putting [enter sympton] in the field of
the
query and 'yes' in the criteria but that lists all the resources. Can
someone
tell me where I am going wrong. Thank you.

I think the problem here is that you have a field for each sympton. So
the criteria field will be different for each question
(Depression=True, Anxiety=True).

The best thing to do is te redesign your database. Make 3 tables:
- Resource
- Sympton
- ResourceForSympton (contains keys of Resource and Sympton)

In that way you are more flexible (adding an sympton doesn't require a
new field) and you can always use one and the same field to select
resources for a sympton (the sympton field in ResourceForSympton).


Groeten,

Peter
http://access.xps350.com