Temp or Permanent Table? (1 Viewer)

[silversun]

Hi all.
Table 1 has list of devices with serial/model number and the date of assignment. Table 2 has a list of complaints. User can have a device assigned with at least one complaint or more brought to the shop for repairs/analysis. Table 3 lists the tests they need to run per complaint. Obviously, some of the tests are common in almost all of the complaints and some of them are needed for fewer number of complaints.
User needs to have a table that holds all Tests per device and its status. User will update the status of each test once he has ran the test. (Test Passed or Finished or something like that). The goal is to have all tests uniquely listed per device.
I thought I am going to need a temp table to list all Tests per device and then remove duplicates from Tests column (per device) before I append my data into another table to store for ever.
I try to show how does it look like by using attached figures. Please let me know if my text or figures are not clear enough.

Am I going to need a temp table or its better to avoid it? Either way I need your suggestions before I can proceed.
Thank you

 

[plog]

I don't see what a temp table accomplishes. Why would you want to throw away data?

Further I think you need another table that sits between Devices and Complaints--ShopVisits. Perhaps that is what you are calling "assignments" but I am uncertain.

A device can have multiple shop visits
A shop visit can deal with multiple complaints
A complaint can have multiple tests.

 

[jdraw]

Can you describe the business and put this into context "to the shop for repairs/analysis."
Does User mean technician or service person?
Does complaint mean issue/problem experienced by the "device" owner?
Why tests per device as compared to tests per assessed issue/problem?

Often you would have a list of symptoms and probable issue(s) along with tests to confirm/reject?

 

[MajP]

At a minimum you need a junction table.

TblDevices_Complaints_Tests

DeviceID_FK (foreign key to the device table)
  ComplaintID_FK (foreign key to the complaint table)
  TestID_FK (foreign key to the test table)

In that table a record of
1 2 6
1 2 11
1 6 1
1 6 6
Means that device 1 had complaint 2 and ran tests 6 and 11
Device 1 had complaint 6 and ran tests 1 and 6

If I understand you correctly you want to see each unique test per device. In the above test 6 gets run for complaint 2 and for complaint 6. Using a group by query you can show the unique tests by grouping on Device and Tests.

 

[Pat Hartman]

I agree with the others. Do not throw away the early tests. When you add a new test, you can update any previous tests with a "superseded" flag. That will solve your reporting problem at the end because you can select only the latest versions of the tests by selecting only rows with Superseded = False.

Do this in the form's AfterUpdate event by selecting tests of the current record's type that occurred prior to the date/time of the current test and mark them as superseded = True

 

[silversun]

Hi all. Thanks for al your responds.
I try my best to clarify.

Each device will be assigned one time only. After all tests are done they will archive the device for ever.
Yes, a device can have multiple complaints.
A USER is the person who tests the devices and prepares a report for each device.
Each complaint has a list of different tests to run. Some tests are common in some complaints.

For example temperature test (Test 1) is needed for complaint 1, 2 and 3. When user is working on the device that has complaints 1, 2 and 3 he will run the temperature test only once because there is no point of running the same test three times on the same device.

This DB intended to be used by person who is running the tests. He will open a form that shows complaint(s) in front of each device serial number along with a list of the tests (uniquely). Then he starts running the tests based on the given list and checks the boxes to say that test is done and passed. All devices he tests and analyzes are returned from the field so that they come to the shop only once (will never go back to the field). The tests are being used to identify any issues and use those info in the future designs.

Having said all above it should be clear why I can remove redundancies of tests and doesn't hurt our goal in any aspect. Tests are done in order and only once. There will not be running the same test in the future on the same device.

In my figure it shows a device (Device 1) assigned with three different complaints (1, 2 & 3). When user starts testing the Device 1 he only runs tests 1, 2, 3, 5, 6 and 7 to cover all complaints. They are done only once. If Device 1 had only two complaints 1 and 2 then user would have to run tests 1, 2, 3, 5 and 6. I am trying to show when device arrives has three complaints and after I simplify the table(s) I will have unique tests in front of each device serial number (simplify from upper section of the figure to lower half).

Please let me know if it is still not clear.

 

[jdraw]

For clarity, and to satisfy my own curiosity, can you tell us who made/makes whatever complaint(s)? What sort of devices are involved?
I worked in a lab and almost all testing involved replicate specimens. That is, there may be 5,10 or 15 samples forming a batch or multiple batches. The objective was to have certainty/confidence in whatever observation/result was obtained.

"The tests are being used to identify any issues and use those info in the future designs."

 

[silversun]

For clarity, and to satisfy my own curiosity, can you tell us who made/makes whatever complaint(s)? What sort of devices are involved?
I worked in a lab and almost all testing involved replicate specimens. That is, there may be 5,10 or 15 samples forming a batch or multiple batches. The objective was to have certainty/confidence in whatever observation/result was obtained.

"The tests are being used to identify any issues and use those info in the future designs."

I can't share too much info because of the company policy. Medical Device is implanted for some period of time and then when they explant it, send it to the lab for analysis (with the complaint(s)). Complaints are such as early battery depletion or no output or no communication with outside world or similar things. The objective here is to know why some issues were arise or confirm that there was no issues seen in the lab when testing.
Is that good enough?

 

[jdraw]

Yes, thanks.
Here's an overview based on what I gleaned from the thread.

 

[silversun]

Yes, thanks.
Here's an overview based on what I gleaned from the thread.

View attachment 101109

I am not quiet sure about your chart because I am not familiar with this type of chart but it looks OK mostly.
In addition I want to say we have a device that hospital says "there was no output and we had to explant it" when returning the device. The complaint here is "No Output", the issue/fault that caused device to be returned. Thus, we take the device and start our work by running a set of tests to analyze or verify the issue(s). In fact we test the device against the complaints.
I am not sure if my English writing is good enough to deliver what is in my mind.

 

[MajP]

If I understand correctly, each complaint has a set number of required tests.

tblComplaints_tests
(ComplaintID_FK, TestID_FK)
1 2
1 3
1 7
2 1
2 4
2 8
3 7
3 6

Then I assume there is a table showing devices and their complaints
tblDevice_Complaints
(DeviceID_FK, ComplaintID_FK)
A 1
A 2
A 3

Then that is all I need. I join tblDevice_Complaints to tblComplaints_tests by complaintID_FK. Then group by device, Test. The result would be
A 1
A 2
A 3
A 4
A 6
A 7
A 8
The set of unique required tests for that device, without duplication.

 

[jdraw]

My "chart" was a simple diagram attempting to take the pieces you have mentioned in the thread and to show how they "seemed " to be related. The info in the blue boxes was a statement of what the nearby boxes and lines represented (as I understand things). I think MajP's posts reflect things in database terms. We do not know your experience with database/Access and are trying to get an understanding of the "issue/opportunity" you are facing.

To describe things in my "chart"

You receive a Device that has 1 or more Complaints
You have a list of all Complaints that may apply to this specific device or device category
The DeviceComplaint box identifies the Complaints for this Specific device
There are a number of Tests that your facility can do
The ComplaintTest box identifies which Tests are used with this Complaint
The ComplaintTestDeviceComplaint box identifies the Tests to be run against this specific device
The Result box indicates all Results of tests run against each Device. Results are signed and dated and held in archive.
The archived results can be interrogated to return various statistics, such as:
-number of complaints with device or device category and test results
-commonality of complaints confirmed/not confirmed by testing
-etc.

 

[silversun]

If I understand correctly, each complaint has a set number of required tests.

tblComplaints_tests
(ComplaintID_FK, TestID_FK)
1 2
1 3
1 7
2 1
2 4
2 8
3 7
3 6

Then I assume there is a table showing devices and their complaints
tblDevice_Complaints
(DeviceID_FK, ComplaintID_FK)
A 1
A 2
A 3

Then that is all I need. I join tblDevice_Complaints to tblComplaints_tests by complaintID_FK. Then group by device, Test. The result would be
A 1
A 2
A 3
A 4
A 6
A 7
A 8
The set of unique required tests for that device, without duplication.

That's correct. We are in the same page.

 

[silversun]

My "chart" was a simple diagram attempting to take the pieces you have mentioned in the thread and to show how they "seemed " to be related. The info in the blue boxes was a statement of what the nearby boxes and lines represented (as I understand things). I think MajP's posts reflect things in database terms. We do not know your experience with database/Access and are trying to get an understanding of the "issue/opportunity" you are facing.

To describe things in my "chart"

You receive a Device that has 1 or more Complaints
You have a list of all Complaints that may apply to this specific device or device category
The DeviceComplaint box identifies the Complaints for this Specific device
There are a number of Tests that your facility can do
The ComplaintTest box identifies which Tests are used with this Complaint
The ComplaintTestDeviceComplaint box identifies the Tests to be run against this specific device
The Result box indicates all Results of tests run against each Device. Results are signed and dated and held in archive.
The archived results can be interrogated to return various statistics, such as:
-number of complaints with device or device category and test results
-commonality of complaints confirmed/not confirmed by testing
-etc.

That's correct. We are in the same page.

 

[Isaac]

Hi all.
Table 1 has list of devices with serial/model number and the date of assignment. Table 2 has a list of complaints. User can have a device assigned with at least one complaint or more brought to the shop for repairs/analysis. Table 3 lists the tests they need to run per complaint. Obviously, some of the tests are common in almost all of the complaints and some of them are needed for fewer number of complaints.
User needs to have a table that holds all Tests per device and its status. User will update the status of each test once he has ran the test. (Test Passed or Finished or something like that). The goal is to have all tests uniquely listed per device.
I thought I am going to need a temp table to list all Tests per device and then remove duplicates from Tests column (per device) before I append my data into another table to store for ever.
I try to show how does it look like by using attached figures. Please let me know if my text or figures are not clear enough.

Am I going to need a temp table or its better to avoid it? Either way I need your suggestions before I can proceed.
Thank you

Neither::: use a permanent, refillable, Staging table.

 

[silversun]

Hi all.

After spending a lot of times on your suggestions I was not able to solve the issue. Here I have my DB attached. Maybe it helps you to understand me better.
I have a table that fills up by user form, frm_projects.

As mentioned before each project has at least one complaint or multiple complaints.
For each complaint user will run multiple tests. Some of the tests can be common in different complaints (please see t_tests)
Once a new record was saved in t_projects user could come back to check on each project, complaints, tests and status of each test whether it was done or not don yet. Every time he runs a test then he changes the status of the test from "Not done yet" to "Done".

The way I've setup my form-subform I can save each project in a table, t_projects, and its complaints in another table, t_fileComp.

Now I need to save a list of all unique tests per project in third table called t_fileTest automatically. The default status obviously will be "Not done yet".

Please help me to create a query or function or use SQL statements that can perform my desired task.

Please let me know if I am not clear.

Thanks

 

[plog]

As mentioned before each project has at least one complaint or multiple complaints.

Using your relationships.jpg, that is not true of your structure. The problem is t_file Comp. What's its purpose?

With it one project can go to many complaints AND one complaint can go to many projects. From what you've written that is incorrect. My understanding is that a complaint only goes to one project? Right? t_fileComp doesn't need to exist.

Nor does this t_fileTest table you propose. You don't move data around a database, you structure it properly and all the data is related. That means you can obtain what you need by queries to bring the appropriate data together.

 

[silversun]

The problem is t_file Comp. What's its purpose?

It holds complaint(s) per project. Each project can have one or multiple complaints.

 

[plog]

You don't need t_FileComp to do that. Remove it and directly link t_projects to t_complaints by putting file_id in t_complaints.

The only reason to have t_FileComp in the manner you have it is if a complaint can belong to multiple projects.

 

[silversun]

My understanding is that a complaint only goes to one project? Right?

You don't need t_FileComp to do that. Remove it and directly link t_projects to t_complaints by putting file_id in t_complaints.

The only reason to have t_FileComp in the manner you have it is if a complaint can belong to multiple projects.

Lets say you send me project (file) A with complaints 4, 7, 10 and 12 (for example). The day after you will send me project B with complaints 2, 3 and 4. That means each project have multiple complaints and even some of the complaints are common in other projects (which is most of the time).