Query too complex or bringing back too few items

[swilkinson]

Hello, I am quite new to Access but I am only trying to do a simple task- or
so I thought! I have many tables that contain info about how data is being
entered, all linked by an ID. I cannot however do a simple query to bring up
the info I want because the query is too complex. I have made a few smaller
queries which I then planned to run a larger query on, but this ends up with
either only two items coming up when there should be about 30. Or the
database puts repeats of many of the items in and so the query is too
complex!?
Any ideas would be greatly appreciated!! Many Thanks

 

[scubadiver]

The records you get returned will depend on what the joins are between the
tables.

What tables do you have and what are you trying to achieve?

 

[swilkinson]

Hi,
Thank you for your reply.

There are many tables, all with lots of different information in and I'm
looking to pull up just a few fields from each. All the tables can be linked
by a person ID number and the items that I would like to pull up is a name of
a person that filled in the questionnaire, this can change per questionnaire
and per person ID, this field could also be blank.

When I created the smaller queries I did have the problem of repeated person
ID which made the file too big and I got the query too complex problem. I
solved this by realising that there were some multiple entries for each
person ID, which were then filtered by current or superseded. I then used
this in my query and the problem was solved.
Now when I am trying to pull up all these queries together, to show who was
completing the questionnaires for each person I'm having lots of problems.

I have tried linking all the smaller queries (in design view) to just one
which I know will contain all the people (i.e. all person IDs) in all the
three possible ways. Some combinations bring up just two person IDs which is
too few and the other way brings up multiples and so overloads access.

I hope this makes more sense. Many thanks

 

[scubadiver]

IMO, this doesn't sound like a very well design DB. For a single survey you
only need three tables:

respondents
questions
responses

 

[swilkinson]

Unfortunately it wasn't me that designed this database, and I can't change
the design- but I am trying to use it!
Thanks

 

[scubadiver]

ok, I will let you off then!

You haven't been too clear what information you are actually trying to find
out. Also, how many tables do you have and how are they related to person ID?
Is it like

table1 - table2 - table 3 - table4 - table5

or is it like

table1
|
table4 - table5 - table2
|
table3