Relationship/Table Problem

[jenvandiver]

Hi, any help with this is appreciated.

I have a table with paragraphs of language, each paragraph having its own ID. This table is the record source for a form which allows the user to choose all the paragraphs that apply to their system. My problem is how to link the paragraphs that are chosen to the right system.

Once the user chooses the paragraphs that apply, I've been running a query on the paragraphs table to find the ones marked "True", but I'm not sure where to add a field for the System ID in the paragraph table or how to set up a new table that would link them.

For every system, the user needs to access the paragraphs table to make their choices. Can/should I automatically create a new paragraph table for each system?

I really appreciate any help or guidance on this!

 

[FoFa]

I would have a System table that contained your systems, then a SysParaXref table that cross references the paragraphs to the systems. In theroy you could even have a seq number as part of your cross reference table to make the paragraphs appear in a certain order also. You would have one entry in your SysParaXref table for each System/Paragraph pair (or one to many).

 

[jenvandiver]

Thanks. Just to make sure I understand...

I need three tables: tblSystems, tblParagraphs, and a tblSysPar that includes only two fields: System ID and Paragraph ID?

I have roughly 50 paragraphs, and at least that many systems. Some systems will be entered into the database new, so including them automatically into tblSysPar once they're entered is a factor.

Assuming I figured that out, would I then use tblSysPar as recordsource for the form where users choose the paragraphs they want?

Sorry for being so simple. I'm truly a newbie on this board.

 

[FoFa]

Yes those 3 tables. I would use unbound form, I would have them select a system, go to your form and display the system information, and have a multiselection combobox/listbox to select the paragraphs, then use VBA to loop the selections and insert the records into the cross ref. table as one way.
OR
Have a form where they select the system as before, display a form where they can insert a select paragraph, and when they do it appears in a list box.
OR
Use the 2 list box method where you have all the paragrpahs on the left, and ADD/REMOVE buttons in the middle and have them move the ones they want to the right hand box.
OR
Have a list of the paragraphs with a check box next to each one, have them check the ones they want.
OR
Hell, just have them keyin the paragraphs (just kidding).

 

[jenvandiver]

Thanks - I appreciate the guidance, especially the list of options. I have considered the last option several times myself. Would you be interested in moving to Missouri? I need someone with advanced level programming skills!

 

[FoFa]

AND LEAVE TEXAS!?!?!
You can't be serious?!?

But we are here for further assistance.

 

[jenvandiver]

Well if you were willing to come I wouldn't stop you!

Thanks again for all your help... I'm sure I'll need more as time goes by.