Multi-table, multi-criteria: avoid repeating records

[NicoleGW]

Hi everyone. Apologies if this has come up before, but the search terms I've tried here and on google keep turning up the wrong information.

At work I manage a large database with many tables. It stores data for participants in a research study. Each table stores the data for a different test, so one participant may have multiple records. Primary keys for these tables are defined by a combination of the participant and date of test fields. (Everything is dependent upon a table that stores the static info for participants, so the database is normalized.)

I want to be able to make a table that lists target participants and dates, and then create a query that looks at this table and pulls all the available data from various tables for those individuals that was recorded within one year of the target dates.

I've successfully made queries that meet these criteria while pulling data from only one table. The problem I'm having is that when I try to pull from multiple tables, each with it's own date field that needs to be used as a criterion, I end up excluding almost all the data, because most of the target participants do not have all the requested data within the target dates.

I've tried being inclusive with my criteria (using ORs), but then I end up with tons of data that I don't want and I need to filter through it, which defeats the purpose of the query.

Any advice on handling this issue, or do I basically just need to create a separate query for each table?

I'm sorry if this is too vague, but it's illegal for me to upload any of my own dataset. I could probably come up with an example if it's helpful, though.

Thanks!

 

[jzwp22]

With your current structure it sounds like you might need a UNION query. A UNION query should work as long as each individual query returns the same # & types of fields. You have to do a UNION query in the SQL view. In terms of your table structure, the tests should all be in one table. You can add a field that would distinguish one type of test from another.

 

[NicoleGW]

Unfortunately, the different tests produce different data, so they can't all be consolidated into matching fields or one table.

Here's a simple two test example:

Test A
1. Participant Code
2. Date of Test
3. Do you like red? (Y/N)
4. Do you like green? (Y/N)
5. Do you like yellow? (Y/N)

Test B
1. Participant Code
2. Date of Test
3. How old are you?
4. How old is your mother?
5. How old is your father?
6. How many siblings do you have?

What would be great would be if I could feed the query, say, a participant Jane, and a date 4/02/2008 and get all the data from the requested fields on one line for Jane, so long as the individual dates of the tasks were within one year of the target date.

Thanks for your response!

 

[jzwp22]

Since the number of fields vary, you cannot use a UNION query. You could possibly use a cross-tab query to display the results as you describe but again you would have a query for each test.

BTW, there are ways to design a questionnaire/survey database that would accommodate different question/response types

Here is a quick table structure. In the question table you would designate to which test (your Test A, Test B etc) the question belongs. If a question can be used in multiple tests then you would set up a junction table (tblTestQues)

tblQuestions
pkQuestionID primary key, autonumber
txtQuestion
fkResponseTypeID foreign key-->tblResponseTypes

tblResponseTypes (describes the response type for a question, Y/N, short answer etc)
pkResponseTypeID primary key, autonumber
txtResponseType




tblParticipantResponse
pkPartResponseID primary key, autonumber
fkParticipantID foreign key-->tblParticipants
dteResponse (the test date)

tblTestType (your Test A, Test B etc.)
pkTestTypeID primary key, autonumber
txtTestType

tblTestQues (a junction table that links the questions to the specific test assuming that a question may appear in multiple tests)
pkTestQuesID
fkQuestionID foreign key-->tblQuestions
fkTestTypeID foreign key-->tblTestType

tblResponses
pkResponseID primary key, autonumber
fkPartResponseID foreign key-->tblParticipantResponse (links response to particpant & date of response)
fkTestQuesID foreign key-->tblTestQues (links to test type and question)
txtResponse (you could use a text field here no matter whether the response is a short answer or a Yes/No type answer)

 

[NicoleGW]

Sorry it took me a few days to respond. Really busy at work.

jzwp22, thanks so much for taking the time to type out that structure. I've played around with it, and it's really neat to reconceptualize the database like that.

Unfortunately, the database I'm working with was already in use when I got here, and I'm not going to be able to alter its basic structure at this point. There are dozens of tables and forms and over 10 MB of data in all different formats (mostly numeric, but also short answer and yes/no). I think I was probably misleading with my examples, because most of our tasks aren't surveys. There are a lot of biophysics tasks and that sort of thing.

So I'm still hunting for a way to improve my queries, if possible. One thing I forgot to mention before is that there is one table that tracks all the instances of tasks that have been completed. So if I include that table in my query, apply my criteria to its fields, and then make use of the pre-existing relationships of the individual task tables to that one big log table, my query gives me somewhat better results.

Is it possible, though, to get fields from multiple tables to all line up on one entry of a query if the entries in the different tables don't share a unique ID?

Thanks again!

 

[jzwp22]

I figured that you might not be able to change your current structure, but it sounds like it might eventually be necessary based on the information you have provided thus far.

As to your question, I'm not sure I quite understand what you are asking. If the tables are related you can bring in information from both tables using a query, but they would not be contained all within the same row/record in the query. Now if one of the tables has multiple related records and there is some way to group them using a totals query such that you end up with one summary record, then yes you can return 1 row when you link the table with the totals query. For example, lets say you have a table of salespeople and another table that holds their individual sales (each record in the sales table has a primary key, a foreign key relating back to the sales person (salespersonID), a sale date and a sale amount). You can create a totals query that sums the sales for each salesperson. You can then create a new query using the salesperson table and the totals query (linked via the salespersonID). Your query would return 1 row/record for each salesperson.

 

[NicoleGW]

Okay, I think I have it figured out. I used SQL view to create a bunch of nested queries running in the background, and it looks to the user like there's just one query that pulls all the data into one table. I'm still getting a few repeat rows, but that's just because certain tables had more than one entry that met the criteria. Stricter date limits will prevent this.

I'm sure I'm going to do a bad job explaining this, but basically this is what happens:

Each task that I want data for has it's own little query (these are the inner queries) that pulls all of the Unique Log IDs for that task matching the target Participants and Dates. These Log IDs are all stored in one table, regardless of task type. Unless you go into SQL view, you don't see these queries. I'm going to make them customizable using parameters, so that other users just need to run the main query.

The main query (outer query in SQL view) selects all fields from the target tasks for entries where the Log ID (foreign key) matches the Log IDs that were selected for that task by the inner queries.

So basically it's the same thing as creating a bunch of individual queries and then one big query that refers to them, only a lot tidier and less likely to confuse users.

Someday I'll figure out how to make it fully customizable, so that users can determine how many tasks they want, but for now I'm just going to set it so they need to enter five tasks. If they want more than that, I'll tell them that they'll need to use the special GoStabYourselfInTheFace query.

Thanks again for your help!

 

[jzwp22]

Glad to hear you got it worked out. Good luck on the project. I know users can be demanding, but without users, programmers would not have jobs.