Question Desperate help!

[thekey]

Hello users

I need desperate help with a project design issue. I am creating a database to log data from a survey. In this survey for one of the fields there are 15 possible procedures which can take place. There can be more than one selected. What I need to achieve is to be able to design a form where the user can select a procedure from a drop down list (That is easy enough which I have done).

My problem is two fold, firstly in certain scenarios there maybe more than one procedure, how do I go about giving the user the option to add another row for a procedure without creating 15 rows i.e procedure 1, procedure 2, procedure 3 etc. in the form/table.

Secondly if the person has a second, third etc. procedure how do I make it add a second field so the user can enter the date of the procedure.

Please help! Thanks in advance.

 

[James Dudden]

Hi,
You need to create a new table and form (probably tabular is best) called procedures that will be something like this:

ProcedureID
ProcedureDescription
ProcedureDate

In the forms table that you are currently working on add the field ProcedureID and then link to the procedures table and enforce the relationship in the relationship window.

Next you need to place the Procedures form as a subform on to your current form and link on the ID

Hope that helps

Regards
JD

 

[thekey]

Hi James

Thank you very much for your reply. I have found your advice very useful. I just wanted to clarify something. In my database so far I have a table with the fields recordID, firstname, surname etc. I also have a second table with the procedures listed which is what I was using as a lookup.

If I understood you correctly do I create a third table with procedureID, proceduredesc and procedure date and then add the procedureID to my existing procedure table with the list of procedures?

In the relationships window do I then link procedureID in the newly created table to the one which lists the procedures and do I add record ID to the newly created table and join it with my first table with the individual's details?

Just to clarify my dilemma:

The database analyses data from a survey. Each individual may or may not have a procedure and if the individual does, they may have more than one. There are 15 possible procedures and if an individual has one I need to be able to ensure the user puts the date of the procedure.

Sorry if this is long winded!

 

[The_Doc_Man]

What was suggested to you is called a "Junction Table." It's purpose is, briefly, to enumerate points of overlap between two other tables. In this case, you want a list of procedures that apply to each response.

When there is a date or other data specific to the COMBINATION of survey and procedure, it goes in the JUNCTION table, not the survey or procedure tables. Sort of the Access equivalent to the Biblical admonishment, "Render unto Caesar that which art Caesar's..."