Another question on selecting multiple values

[Guest]

I've looked over all of the responses to people asking how to select multiple items. But nothing makes complete sense to me. I don't know much coding, but can play with it and usually figure out what I need if given a template...but these table form parent relationship stuff is just completely losing me. So I will give my exact situation and hopefully someone will be able to help me with good details.

We have 1 table linked to 1 form...basically it's a paper form created in Access and enters all of its information on to 1 table. We will eventually have multiple forms, each with their own form and table all linked by their client number (primary key).
One question we are asking the person to select all Pubic Assistance they receive: None-1, Food Stamps-2, Housing Assistance-3, General Assistance-4, Badgercare-5, and Temporary Assistance to Needy Families (TANF)-6.
If more than 1 is selected, we need to upload to the government as such 1,2,4 if those are the 3 that are chosen.
Right now it is set up as an option group, it allows you to select one and then I can go into the table and enter in the other numbers in the format that is required...but this is very time consuming and hard to explain to the program assistant doing data entry.
I tried to do individual check boxes, but I can't check just one. If I try to check one they all check or none of them check. This field IS NOT set up as a yes/no value. But rather as a text value and I used a look up value to set the number values for the options.
If this requires creating additional tables, I have no idea where to start on setting them up and getting them to link properly. Any help would be greatly appreciated!

Thanks
Christina

 

[Rick Brandt]

I've looked over all of the responses to people asking how to select

multiple items. But nothing makes complete sense to me. I don't know much
coding, but can play with it and usually figure out what I need if given a
template...but these table form parent relationship stuff is just
completely losing me. So I will give my exact situation and hopefully
someone will be able to help me with good details.

We have 1 table linked to 1 form...basically it's a paper form created in

Access and enters all of its information on to 1 table. We will eventually
have multiple forms, each with their own form and table all linked by their
client number (primary key).

One question we are asking the person to select all Pubic Assistance they

receive: None-1, Food Stamps-2, Housing Assistance-3, General Assistance-4,
Badgercare-5, and Temporary Assistance to Needy Families (TANF)-6.

If more than 1 is selected, we need to upload to the government as such

1,2,4 if those are the 3 that are chosen.

Right now it is set up as an option group, it allows you to select one

and then I can go into the table and enter in the other numbers in the
format that is required...but this is very time consuming and hard to
explain to the program assistant doing data entry.

I tried to do individual check boxes, but I can't check just one. If I

try to check one they all check or none of them check. This field IS NOT
set up as a yes/no value. But rather as a text value and I used a look up
value to set the number values for the options.

If this requires creating additional tables, I have no idea where to

start on setting them up and getting them to link properly. Any help would
be greatly appreciated!

Yes, you should have an additional table. Your data is arranged much like
a sales order. I'm sure you've seen forms set up for these. There is a
main table and form for the sales order header and there is another table
and subform for entering in the line-items on the order. This arrangement
allows for a given "Order" to have any number of line-items associated with
it each one entered as a separate record in the second "related" table.

Your requirements are the same. You have a main record where you need to
record multiple related records to indicate the multiple types of Public
Assistance possible. Putting these in a single field separated with commas
might seem like a reasonable way to deal with this, but it is improper
design and it will cause you grief later on if you try to pursue that
strategy.

Just remove that field from your existing table and create a new table with
that field and another field to hold the foreign key that will link it to
the existing main table. The two fields combined could be the primary key
for the new table.

Create a small continuous view form against the new table and then add that
form to your existing form as a sub-form. Make sure that the MasterLink
and ChildLink properties of the sub-form control include the linking field
names. This new MainForm/SubForm combination should now allow you to enter
as many Public Assistance entries as you want for each record in your main
table. You can use a ComboBox in the sub-form to provide the choices.

 

[Guest]

Thank you for taking time to respond. And I understand that it's probably an improper way to set up a database, by having multiple values separated by commas. But I have no choice, it's required by the Federal Government to have a field that uploads to their database like that.

 

[John Spencer (MVP)]

Just because you need to output it that way does not mean you need to input it
that way or store it that way. Use the additional table(s) and then use a
concatenate records function.

One method of concatenating the same field from multiple records can be found at

http://www.mvps.org/access/modules/mdl0004.htm