Temp or Permanent Table? (1 Viewer)

[silversun]

a complaint can belong to multiple projects.

Exactly. Once I received your projects then I have to work on each according to the complaints. I use this DB to summarize received projects, their complaints, and the test I need to run or have ran.
I will run the tests according to each complaint. Some tests are common in different complaints (also some complaints are common in different projects) so that I will not have to run those repeatedly. Only one test covers my respond to complaints that require that test per project. Please see the very first images I have attached in this post.

 

[plog]

I get it now, this does sound like a case for using an action query to move data to a table.

1 question--do you want to tie the test results back to the project or to the complaints? Both are possible, just wondering what the lowest level of relationship you will need.

 

[silversun]

do you want to tie the test results back to the project or to the complaints?

Yes. I do. That's why I have a table for tests per project and a table for complaints per project. This way I can check each project, complaint on each project and the tests that are done when I run a report in the future.

 

[plog]

That wasn't a clear answer. I am trying to determine the lowest level that you want to tie the test results back to.

Do you want to tie the test back to the specific complaints of a project? Or do you want to just tie the test results back to the project?

 

[silversun]

That wasn't a clear answer. I am trying to determine the lowest level that you want to tie the test results back to.

Do you want to tie the test back to the specific complaints of a project? Or do you want to just tie the test results back to the project?

Sorry. I was confused.
No. I don't need to tie the tests back to the specific complaint but they need to be tied to the project.

 

[plog]

That makes it easier. Attached is a database to show how I think you should accomplish this.

I renamed a few tables to better reflect their purpose, added a Test results table, updated the Relationship tool and set up a query to populate Test Results. That query will have to be modified so it doesn't run for all records, just for the new ones. You will also need to add some sort of event to run that query (or some form of it) when a user adds new projects/complaints.

Hopefully it makes sense but let me know if you have any questions.

 

[silversun]

That makes it easier. Attached is a database to show how I think you should accomplish this.

I renamed a few tables to better reflect their purpose, added a Test results table, updated the Relationship tool and set up a query to populate Test Results. That query will have to be modified so it doesn't run for all records, just for the new ones. You will also need to add some sort of event to run that query (or some form of it) when a user adds new projects/complaints.

Hopefully it makes sense but let me know if you have any questions.

Thanks for your time.
I am going to check it tonight and ask you questions tomorrow if there was any. I am taking OFF from my work now.

 

[plog]

I see another issue that might require another table. You need a table between t_ComplaintTypes and t_TestTypes.

The existing t_TestTypes has duplicate values, e.g. 8 'Battery Current Test' records. t_TestTypes should hold only 1 of those and then the table that goes between t_ComplaintTypes and t_TestTypes would have 8 records to determine which Complaint types have which tests.

 

[silversun]

The existing t_TestTypes has duplicate values

You're right but I used the same concept as when you have two tables, for instance, "Car Makes" and "Car Models". In table "Car Models" you will have to have the make of that model in front of each model. This also helps to have nested combo boxes in data entry form.
Anyhow, this was the best I knew but I am going to try your suggestion to see if it helps avoiding any redundancies or makes things simpler and easier.
Thank you for your time.

 

[silversun]

query to populate Test Results

I am trying to understand your changes and make sense of them in my mind before I try the query. I am sure I will need to run that query or a similar one ultimately.
Thanks again

 

[plog]

Attached is a new database.

I added that table between TestTypes and ComplaintTests. I moved the data to it appropriately. And I revised the append query so that it will only add the missing items to TestResults. This means you could run it everytime a change is made and it will find and only only the missing tests.