Assigning matching IDs from one table to another / MultiSelect Listbox

[TPH_AF]

Hi all,


I've been struggling with this problem for a few days now...I'm not that experienced at Access, so hopefully I'll be able to explain it properly for everybody.


A while ago I was tasked with creating a compliance testing database for my company's internal processes. For example: Payments, Hiring, Project Management, etc. Right now it's all done through Excel and is structured like this:


Compliance Test 01:




-----|Sample1|Sample2|Sample3|etc...
Item1|GradeA |GradeB |GradeC |etc...
Item2|GradeB |GradeA |GradeC |etc...
Item3|GradeC |GradeC |GradeA |etc...
etc...


In short, a Test has many Items (based on company regulations, like "Has the project been approved by all superiors?"). For each Item you choose a few Samples ("Project A", "Project B" and so on), and for each Sample the user attributes a Grade ("In Compliance", "Not In Compliance", "N/A", etc).


I've uploaded a barebones version of the DB so you guys can see what I mean.


In the Tests mainform, the user fills in the Test details (Tab #1), chooses which available Items he wants to include in his test (Tab #2), details the Samples which will be tested (Tab #3) and finally assigns a Grade to each Item/Sample combination (Tab #4).


Here's what I can't figure out (please let me know if I should create a separate topic for each question):


1) In the subform sfrm_Items_CTs_List (Tab #2): Is there I way I could assign multiple Item IDs to the same Compliance Test, using the listboxes provided? I wanted the user to assign multiple items at once, leaving out maybe one or two that aren't applicable to a specific Test. Assigning the Items one by one would be too cumbersome.


2) Most importantly, in the subform sfrmSamples_CT (Tabs #3 and #4): In the first two examples I filled everything in "under the hood", but in reality I haven't the foggiest idea of how to automatically assign the Item-Test / Item-Sample / Sample-Grade combinations to each other, in order to have it recognize these relationships. This is pretty much the heart of the database.


Taking a look at the way the Relationships are set up, I think that maybe one way to do it, would be for the DB to automatically check every time a user inputs a new Sample in Tab#3, to see if [tbl_Samples].[lngCT_ID] matches the [CT_ID] of any Items in the [tbl_LINK_Tests_Items]. If so, it'd fill in the matching Item/Sample ID combo in the [tbl_LINK_Items_Samples] table. Would this be correct? If so, how could I set this up in script form?


As always I appreciate any help, sorry for the huge post but I wanted to make sure I explained everything correctly. Please let me know if I can clarify anything for you guys.

Edit: I went ahead and attached a 2003 version of the database as the first upload used Access 2010.

[TPH_AF]

Alright, I think I've solved part of the first problem by following an example here - http://www.baldyweb.com/MultiselectAppend.htm. I've attached an updated version of the database.


On Tab # 2, the user is now able to assign the available Items to the current Test by clicking the "Confirm Items" (cmdAddItems) button. This can be tested in the sample "State Branch - Purchasing" Test. The Items selected in Tab#2 will now show up (after reloading the form) on Tab # 4. Great!


Is there any way I could:


a) Reflect the changes in real time by refreshing the subform on Tab # 4 as soon as the Items are added?
b) Transfer (remove, essentially) unwanted items to the "Removed Items" list, by clicking the ">" and "<" buttons?


I'm still working on a way to automatically link the Samples on Tab#3 to all the new Items on each Test...

[June7]

The form is so wide, the New Test button is off screen. Suggest you make the form more compact.

1. If you want to allow dynamic selection of items to associate with tests, need a junction table for that. Is that tbl_LINK_Tests_Items? Instead of having user select all that are applicable, you have them deselect what is not. Well then, need code to cycle through the list boxes and save appropriate record(s) in the junction table. Sounds complicated.

2. After looking at your table relationships, gave up trying to understand this.
You have circular relationships. This could be issue. http://www.codeproject.com/Articles/...atabase-Design

EDIT: Above is based on the first posting, didn't see the second until after.

[TPH_AF]

Hi June, thanks for the feedback

The form is so wide, the New Test button is off screen. Suggest you make the form more compact.

Yes, it's based on the screen resolutions we have at work...I've added some scroll bars just in case, but I'll look into making it more compact like you've suggested.

1. If you want to allow dynamic selection of items to associate with tests, need a junction table for that. Is that tbl_LINK_Tests_Items? Instead of having user select all that are applicable, you have them deselect what is not. Well then, need code to cycle through the list boxes and save appropriate record(s) in the junction table. Sounds complicated.

Exactly, though I think I've figured it out as you can see in the second example I've posted. That is exactly right, [tbl_LINK_Tests_Items] joins the many-to-many relationship between the Tests and Items.

2. After looking at your table relationships, gave up trying to understand this.
You have circular relationships. This could be issue. http://www.codeproject.com/Articles/...atabase-Design

EDIT: Above is based on the first posting, didn't see the second until after.

I agree it is complicated, but I really couldn't find a better way of doing it given the "three-dimensional" nature of what's needed. Every Test is linked to its' Items, the Items have to be linked to their Tests and Samples, and finally I have to be able to identify the Samples right back to their respective Tests. So mainly I have to worry about the three main tables: Tests, Items and Samples. Everything else is auxiliary to those, or junction tables to handle the relationships.

[June7]

Shouldn't Items link to Samples through Tests?

Just don't know your business process. How does data flow?

[TPH_AF]

Shouldn't Items link to Samples through Tests?

Just don't know your business process. How does data flow?

Thanks for another quick reply June7.

If the Items/Tests were not a many-to-many relationship it would be exactly as you suggest. Except in this case [tbl_LINK_Tests_Items] is in the way (see attached image).

But the Samples also have to be linked to the Items with the [tbl_LINK_Items_Samples] because after I assign a Grade to each Sample (and each grade has a score), I will calculate the % of compliance of each Item individually.

Here's an example of how it would work in practice:

a) The user creates a Test01 in [tbl_ComplianceTests];

b) The user creates Item1 and Item2 in [tbl_Items];

c) Item1 and Item2 are assigned via a form to Test01. (I've done this bit in Tab#2)
So now [tbl_LINK_Tests_Items] has the Test01/Item1 and Test01/Item2 combo.

d) User creates Sample1, Sample2 and Sample3 in [tbl_Samples], which are assigned to Test01 via [tbl_Samples].lngCT_ID (This is Tab#3)

e) Now is the tricky bit. As the user creates the Samples in the form, I need them to be automatically matched to all the Items over in [tbl_LINK_Tests_Items] that also have the same lngCT_ID. *Then* those ItemIDs are stored in [tbl_LINK_Items_Samples] with their corresponding SamplesIDs. So Item1 gets matched with Sample1, Sample2 and Sample3 *and* Item2 with the same samples.

[June7]

This makes my head hurt. You need someone who's better at analysing data relationships. I still think the circular references would be eliminated in a proper design. Perhaps you need to save compound primary keys as compound foreign keys (something I loathe) or some junction table needs a unique ID field that can be saved as a foreign key. I just can't get a grip on the relationships to follow through on this.

[TPH_AF]

Hi June7, sorry for all the confusion...if I could have found a way to make it simpler I would have...it makes my head hurt too, believe me!

I've done some small changes on the attached DB, and also inserted all the IDs on the form to try to clarify things a little bit more.

If you go into the "CT - Projects 2013" and check the Samples in Tab#3, you can see they match perfectly with the ones at Tab#4.

Now if you go into "CT - Projects 2014" and do the same thing, you can see that the Samples assigned to the Items in Tab#4 don't match up with the ones in Tab#3 at all. This is because the DB is checking only if the Samples' ItemIDs match with the ItemIDs in the Test. But it also needs to make sure that the TestID, the Item's TestID *and* the Sample's TestID match, or it just won't work!


I've attached an image as well, showing what I mean by this.

Thanks very much for your patience!

[TPH_AF]

Just for future reference, the issues have been solved!
http://forums.techguy.org/business-a...one-table.html