querying across different fields linked 1-to-1 (1 Viewer)

[mincher]

I have been involved in a nightmare project for a while; databasing a very poorly designed questionairre...

The database consists of many large tables linked in a 1-to-1 way. It works but i would have it done it better...

However the problem is now querying across different fields with the problem is that there are different numbers of records in each field corresponding to if a person answered the question or not. Is there a way of asking: For those who answered yes in one field, how many of these answered yes to another question (given that the groups might not be identical)?

 

[KenHigg]

Q1. Do all the tables share a common field, say a questionaire number?
Q2. Is there one table that can be used as the primary table that contains basic questionaire data?
Q3. Just how many records do some of the 'Large' tables contain?

 

[mincher]

Q1. Do all the tables share a common field, say a questionaire number?


Thanks, Ken. Yes, they do.

Q2. Is there one table that can be used as the primary table that contains basic questionaire data?

Yes

Q3. Just how many records do some of the 'Large' tables contain?

Not that many - around 170.

Minch

 

[KenHigg]

Seems you could do a query where you bring all the fields over and then use that query as a source for your other queries so that you wouldn't have to re-create the relationships evertime you need the data.

Exp:
1. In the query grid, bring over the primary table.
2. Bring down all the fields
3. Bring over the next table and tie the two together using the common field using a one to many
4. Bring down all those fields
5. Repeat step 3 & 4 for the remaining tables, always tying back to the primary table.

Once this works save it as kind of a master query and use it in your other queries that do the filtering, sorting, etc.

Make sense?

 

[mincher]

Ken,

Worked absoultely fine though no need to to recreate the relationship as they were tied in a 1-to-1 relationship already.

My big lesson is never accept long questionaiires with large tables - it creates merry hell working out what you are trying to query!

Thanks

Paul

 

[KenHigg]

Cool - glad it worked. I'm not sure why there are so many tables. Seems the db needs normalized -