Saving multiple records to one field

[NickM]

Hi all

It's been a while since I've used Access and therefore I'm a bit rusty.

I am trying to set up a questionnaire Db.

Essentially I will have a picking table with 100 questions in and I wish to create a form where the user can select up to 30 of them which then saves to the receiver table along with other details (ie co name and contact etc).

Now, is it possible to save and create the 30 records at once in the receiver table (if not on close, then on a button click).

ie
Name - Co - Q picker no

Joe - - - JB - - 5
Joe - - - JB - - 37
Joe - - - JB - - 1


I really want to avoid having to set up 30 different fields within the receiving table.

I hope I've managed to make myself clear

Thanks for your help in advance

 

[Norie]

Why not add one extra field that indicates the question?

 

[NickM]

Hi Norie

I didn't mention (duh!) but another field in the receiver table was going to be question number.

The fundamental question was can 30 fields on a form save to one field in a table (obviously all the same data type), essentially creating multiple records as opposed to having 30 form fields writing to 30 table fields.



Thanks

 

[NickM]

Hi Norie

I didn't mention (duh!) but another field in the receiver table was going to be question number.

The fundamental question was can 30 fields on a form save to one field in a table simulatneously (obviously all the same data type), essentially creating multiple records as opposed to having 30 form fields writing to 30 table fields.



Thanks

 

[Andrew Fergus]

I don't understand the problem 100% but is this another case of a many-to-many relationship? i.e. one receiver can have multiple questions, and one question can be assigned to multiple receivers? I say 'another' because there have been a couple of questions on many-to-many relationship problems recently e.g. see here.
Andrew

 

[NickM]

Hey Spongebob


Basically I dont think it's a many to many relationship as I'm not talking about a relationship. I'm talking about writing data to a table from a form.

I need to record in a table
Client
QuestionFromPicker (Combo box field from question database identifying specific question to add)
QuestionNoOnForm (order in which they appear in report)

I need to use the first instance but was hoping that if I created a selection form I could have 30 x combos on it. On a click each would create a record in the 'written' table.

ie (Table fields in brackets)

Combo1 = Record1 (Client, Question1, QuestionFromPicker)
Combo2 = Record2 (Client, Question2, QuestionFromPicker)
Combo3 = Record3 (Client, Question3, QuestionFromPicker)

Next questionnaire created would log as

Combo1 = Record4 (Client, Question1, QuestionFromPicker)
Combo2 = Record5 (Client, Question2, QuestionFromPicker)
Combo3 = Record6 (Client, Question3, QuestionFromPicker)

Essentially I have 2 differing types of question( one set is multiple choice and the other is a free text space) therefore I need the records to be logged as above to enable me to seperate the two sets (identifiable by the QuestionFromPicker number). Also there will not always be 30 questions needed

Now I can record this info if I have a form feeding the info one by one, but thats a tedious way to do it.

The other solution I was offered is to have a separate table field for each question choice, meaning that I would have the

but then that means I wont be able to easily identify the two sets of questions. That solution would leave the 'written' table fields being

Client
Question1FromPicker
Question2FromPicker
Question3FromPicker
etc

This would then mean data being logged as (Table fields in brackets)

Client = Record1 (Client)
Combo1 = Record1 (Question1)
Combo2 = Record1 (Question2)
Combo3 = Record1 (Question3)

Next questionnaire created would log as

Client = Record2 (Client)
Combo1 = Record2 (Question1)
Combo2 = Record2 (Question2)
Combo3 = Record2 (Question3)

Does that make sense?

Again the ultimate question is can I write 30 sequential records simultaneously to a table from 1 form?

 

[Norie]

Does that make sense?

Not really.

Can you give an example of your data and what you want?

 

[NickM]

Right let me try again
========
I have a picker table that contains 100 questions of two differing question types

I need to create a form to enable picking up to 30 questions, therefore 30 combo boxes on one form looking at the picker table.

The form then writes to the (up to) 30 selections to the results table along with Client
=========
I.e. Joe Bloggs Ltd wants questions 2,4,8,13,11 produced in that order .

My form would contain 30 combo boxes on one form.(client name input on this form too, question order relates to whichever combo box contains selected info).


So my form would be filled in as follows:

Client = Joe Bloggs Ltd
Combo 1 = 2
Combo 2 = 4
Combo 3 = 8
Combo 4 = 13
Combo 5 = 11
Combos 6 - 30 = Blank

Then write all to the table (on a click?? creating 5 records in one go)

My results table contains fields
Client/Question Number/ Question picked

so the record set would look like

Record1 = Joe Bloggs/1/2
Record2 = Joe Bloggs/2/4
Record3 = Joe Bloggs/3/8
Record4 = Joe Bloggs/4/13
Record5 = Joe Bloggs/5/11



Can I set my form to write these records from the 30 boxes simultaneously or is the only way to do this to set up a form with 1 combo box for the picker number and another to input order and do this repeatedly until Ive logged all questions required


I.e.

Client = Joe Bloggs
Combo1 = 2
Order = 1

Write to table

Client = Joe Bloggs
Combo1 = 4
Order = 2

Write to table

Client = Joe Bloggs
Combo1 = 8
Order = 3

Write to table

Client = Joe Bloggs
Combo1 = 13
Order = 4

Write to table

Client = Joe Bloggs
Combo1 = 11
Order = 5

Write to table

Once I have collected this info I need to produce a report sorting the questions of differing types in two sub reports, then print for clients to complete.

I will then create a differnt Input form for collating the info when we get all the forms back.

Any clearer??

Thanks

 

[Andrew Fergus]

Assuming your 50 questions are fixed and any client can have any combination of the 50 questions, then you have a many to many relationship between your clients and questions. The reason I mention this is that without the right data structures, you are going to grapple with your data - as you have found out.

As a minimum you will need 3 tables, a client table, a question table (you should already have those first 2 tables) and a survey table. The survey table should look like this :

tbl_Survey
Sequence (autonumber - used to keep the sort order / sequence of the questions)
Client_id_link (link to your client table)
Question_id_link (link to your questions table)
Response (up to you how you use this field, something like yes/no or a text box etc depending on your question and what answer you want to capture later on)
plus any other survey specific data (not client data)

Your data entry form will use this survey table - you select the client in the main part of the form and select the questions (in the correct sequence) in the subform part of the form. I believe the question number on the 'report' could be derived using dcount - although there is nothing stopping you from adding another field into the survey table for the question number. But coming back to your original question about saving multiple records at once, Access doesn't work that way - the method I have described would result in Access saving each question you select in the subform as soon as you go to select the next question (by tabbing or moving onto the next record), albeit in a vertical layout within the subform. It's no more tedious than tabbing or moving onto the next record with a horizontal layout.

HTH, Andrew.