How to display select fields from a group of records 1

[tech84]

I'm pretty new to Access, and don't really know anything about SQL, so please bear with me. What I have is a table with fields for "group", "shift", "question1", "question2", etc., all the way to "question35". The "group" is either Manager, Team Member, or Other, and "shift" refers to dayshift or nightshift. The questions are from a survey that the employees fill out in which the answer is a number 1 - 4 (1 being "almost never", and 4 being "almost always&quot. What I'm trying to do is to create a form with a text box where I can type in a number, 1 - 35, representing a question. Then I would like a query to display all of the records, but only the field for the question number I input into the text box on the form. I will then divide the output into groups and get the average answer from each group, but I already know how to do that.

For example, let's say I have a form, frmSnglAnswer, with a text box, txtAnsNumber. A user would type a number, say "24", into the txtAnsNumber text box and click a command button. Then a query would run, and create a report which displays all records, but only "group", "shift", and "question24". Of course, I would format the output appropriately.

Does this make any sense? I'm afraid I'm so lost that I can't even ask the question intelligently! Thank you for any help you can provide!

Mike

 

[MakeItSo]

Hi Mike!

Your table is not set up well, read below the code. But for the moment, the following should work:
Display the properties of your form. Click once on your textbox and select the events tab of your properties form.
On the OnChange row, click on the three dots and choose Code.
Then paste this into the code section

Sub YourTextbox_Change()

Dim sSQL as String

If Len(Nz(Me![Name of Your Textbox])) Then
sSQL=DoCmd.RunSQL("SELECT [question" & Me![Name of Your Textbox] & "] FROM Your table name&quot
Me!Your question-display field=s´SQL
End If

End Sub

You should normalize your table, else you will get problems, when trying to query all fields of your table (Query too complex error).
Create a second table e.g. tblQuestions. Give it an ID field and a Question ID field, both key fields. Set the Question ID field to "Indexed (with duplicates)".
Open the "Relationships", add both tables and drag your ID field from table 1 to your questions table, Set the relationship property to "With referential Integrity, further Update detail field and further Delete". These terms might be called a little differently, I have a German Access...
You then have a "One to Many" relationship, which means that you can have Many records in your questions table related to one record in table 1.

Do not hesitate to contact me, if the above mentioned seems too complex, you can find my e-mail address below...
;-)
Kind regards,
Andy

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[tech84]

Yes, now that you mention it, I see that my table is not normalized. I'll have to work on that. Your suggested tables make sense to me, but I'm not sure how to enter data into two different tables via one form, and have the data all linked together. I guess that's part of the relationship deal, eh? Perhaps I should take a look at that sample Northwind db?

Also, in reference to your suggested code above, what would be the record source for the form with the text box... the table with the questions or a query?

Thanks so much for your awesome help! I love your web site! "...those tiny little life forms..." Ha!

Mike

 

[tech84]

Okay, I'm really stuck on this normalization thing. Sorry to be so basic. Each record has to have 35 questions, or fields, to answer, in addition to the "group" and "shift" fields. I don't understand how I can put the data from 35 questions (fields) into one field (QuestionID) in a separate table (tblQuestions). A table with 37 fields does seem crazy, but I can't figure out how to get around the 35 question boggle. I'm really stumped here. I created the table/fields/relationship Andy describes above, but when I entered a new record, the tblQuestions table was empty. I don't understand what it's supposed to do.

Thanks,


Mike

 

[MakeItSo]

Hi Mike,

although I meanwhile am not sure at all, if the code above will work..., the table would be the record source for your form.

Concerning the two-table-one-form problem: Once you have established the relation, you can add a sub-form to your form.
Therefore you
- create a new form based on your questions table
- open your main form in design view
- add a sub-form to it
- choose your new questions form as source
- in the "link from .. to" enter your link criteria (e.g. [response-ID])

That should well do it.

P.S: You should be able to easily and speedily change your table set-ups without loosing data, if save a backup of your table-contents in an excel workbook or use one as a buffer for transferring table data to another table...

Andy

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[MakeItSo]

If your db isn't too big after compactin and zipping, you could mail it to me (mailto:andygalambos@arcor.de or mailto:andreas.galambos@bowneglobal.de) and I can try to normalize it, or do it like this:

first, you must swap rows to columns (since your question fields are in a row, but will become records i.e. columns in your questions table. This could be easiest done in excel: just select and copy all question fields in your table and paste them into an excel sheet and swap rows w. colummns.
The paste the column containing your questions into the [question] field of your questions table and assign the same response-id to each 35 questions...

It's some typing and copy/paste work, but it'll be worth it... ;-)

Andy

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[tech84]

Hi Andy,

Thanks for all your help! You definitely deserve a star!

Mike