choose which records link to a new addition (reverse of already defined)

[DatabaseIntern]

Hello, I am quite new to access but have been spending a lot of time reading up on it and surfing the forums over the last week and have managed to come up with what I think is a decent database structure. I essentially have two tables, Modules and Microcontrollers- each module can be used in various microcontrollers so I have set up a "UsedIn" relationship that links each module to the ID of the microcontrollers it is used in. currenttly I have a form that allows me to select applicable microcontrollers for individual modules, but I was wondering if there was a way I could create a new microcontroller and select which modules it uses (like in reverse of the way already defined). I cannot seem to get this to work (I have tried querys to no avail). I am new to VBA but can figure it out if need be (i have other programming background so its pretty intuitive). Any suggestions on how I should go about this? I have posted lots of photos to try and make the database structure as clear as possible - sorry some of the data is blocked out as I cannot post it on a public forum. I know it looks kinda messy.



So Short Version -
I want to add a new Microcontroller and choose which modules link to it. Currenttly I link modules to microcontrollers (this is functionality I want as well, it is required). Can I do both?









Thanks in advance - these forums have already been a lifesaver. Id be happy to clarify any questions, as I know how confusing it can be to understand these posts sometimes.

[June7]

You are using multi-value field in Module table to select related Microcontrollers. So I say no, can't (at least not easily at first glance) do what you want. Maybe with some fancy VBA code.

I refuse to use multi-value fields.

[DatabaseIntern]

Hmm, I see - well at least now I know that. Would you reccomend a different database architecture? what would you recommend in place of the multi-value fields? I'm eager to learn better databasing technique as a major reason for me working on this project is to learn how to use access/VBA. If there is a better way to go about this project I am eager to hear it (even if it means significant revision/restarting)

[June7]

A child table that has a record for each Microcontroller for each Module is normalized structure.

Non-normalized is a field for each microcontroller (Micro1, Micro2, etc). How many microcontrollers can a module have?

[DatabaseIntern]

Hmm, I see - when I intially started this project it was in non-normalized structure with a field for each microcontroller (previously this data was stored 2-dimensionally in Excel), but I thought that what I did was normalized, is it not? each module can be used in any number of microncontrollers (average is about 10, although hypothetically one module could be used in every microcontroller), and each microcontroller can consist of multiple modules. the modules are like individual legos, and the microcontrollers are like finished designs, you may use the same part in different designs, and designs use more than one part. New modules and Microcontrollers are added quite frequently though, so I need to be able to have a form that easily adds/edits both.

[June7]

Multi-value field is normalized structure but can be difficult to work with. http://office.microsoft.com/en-us/ac...33722.aspx#BM7

By description in your last post, seems the data storage is backwards to me. Consider:

tblModules

tblMicrocontrollers

tblMicroModules
MicroID
ModID

This is many-to-many relationship. Use form/subform arrangement for data entry.

[DatabaseIntern]

Hmm, Thanks for the link, it is pretty useful, and after reading up I think you might be right, multivalued fields are difficult to work with and may not be my best approach. I am a little bit confused about your suggested layout though, most likely because I have never actually implemented a subform. are you suggesting 3 different tables, one with the microcontroller names, one with the module names and data, and another one with IDs pointing to them? If so what advantages does that give? would it allow me to look at the database from both module and microcontroller primary views? I honestly thought that I was doing the standard way since I just used a Lookup and it automatically gave me these multivalue fields. As for data entry My supervisor is very adamant about being able to input all the info on one page with minimal scrolling, and they plan to somewhat misuse them as a way to view/edit information quickly as well.

Thanks for all your help, I'm learning some good stuff here. I figure its best to learn it right so i have a usefull skill for the rest of my career, so i really appreciate it.

[June7]

Review
http://forums.aspfree.com/microsoft-...es-208217.html
http://office.microsoft.com/en-us/ac...010098674.aspx

Yes, 3 tables.

Form/subform setups could be:
Main form bound to tblMicrocontrollers and subform bound to tblMicroModules
Main form bound to tblModules and subform bound to tblMicroModules

[DatabaseIntern]

Thanks, this is really useful, I'm gonna remake my Database with 3 tables and see if some kinks iron out. Thanks for your help, ill let you know if it works out