Need to create a form for completing a survey

[Munkyjay]

Hi there

I've been tasked with a work project to make a new survey database. The database will be used to complete surveys reviewing other peoples' work. The assessments are very basic and all of the questions have the same selection of answers to choose from. There are over 100 questions, and they are divided into several sections. Each section then has a comments field.

So when a person completing this assessment starts - the idea is that they select an offender on whom the assessment is being done (I work in offender management). Then once that offender has been chosen - key data on that offender is automatically filled in on the form. The person completing the form would then go through and fill in all the questions for that offender. This review is called QUIPS.

I have designed the tables and relationships. However, I can't seem to get my head around how I can make the forms work in the way I would like them to. I can choose an offender from the list on the main form, but then when I've tried to make the questions appear - nothing comes up. Every offender should have the full list of questions appear.

I plan to make the list of offenders limited to those who have not got fully completed assessments - so you can't go in and change one which is already completed.

I have tried looking for help in the forum here, but could not find a suitable topic for help with this.

All help will be VERY much appreciated!! Thanks in advance and Happy New Year!

 

[Simon_MT]

The Subfrom doesn't exist but I think that is intentional.

There are two parts to this form, the Offender and the Assessment or QUIPS. I assume that there is nothing to stop viewing completed questionaires just no entry is allowed.

If this project is reviewing other people's work then there is an assumption that the offender is already known yet QUIPS holds offender details? Doesn't the offender already have a Unique Reference, this would be better than just using the offenders name which could be subject to typos?

Table Field Names - don't use quotation markes in field names and you are not supposed to use spaces either. There is so much textual information on the solitary Form and it needs to be tidied up. I use Arial and some color on the labels just to break the form up a little, I don't do grey unless I really have to.

It is also a bad idea to put lookups within your tables, I don't why MS did this as some forum members say this is a bad, bad idea. You can still use combi-boxes on forms.

To answer your question I would have a Search Dialogue Form with OffenderID, Surname, First Name, Date of Birth as your starting point.

Simon

 

[Munkyjay]

Hi Simon

Thanks for looking at this for me.

You're correct in thinking that people will also have the option of viewing completed assessments. I will set this up on another form, so I have one for data entry and one for reviewing completed assessments.

The offender details will be imported using a macro I have set up in the database. The offenders have a unique reference called PNC. However, an offender may have more than one of their reviews assessed. So the unique reference I am using is unique to the offender and the review that is being assessed. This means that the offenders review can only ever be assessed once.

The form I have set up to do a little testing allows the user to list all the offenders and DOB etc. The part I'm getting stuck on is having that as the main form and then having a sub-form which lists all the questions. When I've tried it, the subform is blank and only shows me data when I've entered it manually in the tables. How can I pick an offender and then have a list of all the questions too? Regardless of whether they are blank or not.

Thanks again.

 

[Simon_MT]

The problem with the submitted database is that there is no subform so it is a little difficult to see what you are trying to acheive.

The UniqueID in QUIPS should relate to QUIPS not the Offender. There should also be a table for Offenders using the PNC Reference. QUIPS should only have the PNC Reference the rest of the Offender Details should be on the Offenders Table. A QUIPS Query would then join to the Offenders Table.

Simon

 

[Munkyjay]

Hi Simon

I've just added a QUIPS Main page with a sub-form to give you an idea of what I'm after. In its current state, the subform does not link to the main page. If I choose to link the two, the questions all disappear - because they have not been completed yet.

I want the form to look something like this and show the blank questions so the survey can be completed on a particular offender. Also, if I then choose another offender, it will show the completed questions for that person instead. I hope that makes sense!

The reason I've kept the QUIPS data and the Offender data in the same table is because there is very little information unique to the offender. 99 times ouf of a hundred, the details for an offender will be the same. However, on rare occasions, the information like the DOB/PNC might differ due to a typo and this means that assessments have been completed on the two different versions as if they were different people. These are merged later on, but I need this database to review those assessments.

Thanks again.

 

[Simon_MT]

The main problem is there is so much information on the main form there is hardly any room for the questionaire.

Simon

 

[Munkyjay]

If I can get the questions to appear for each offender, then the plan is to split the questions up and divide them between tabs.

Could you see what I'm trying to achieve?

 

[Simon_MT]

Yes, I totally understand and that is exactly the right approach. I'm in the middle of tabbing the Sections of the Questionaire. The Quips Reference is so long, I'm thinking that we should create an Numeric Field as well the Reference.

Once the tabs are complete I'll set-up a way to populate the table. I send something up in the next day or two.

Simon

 

[Munkyjay]

Wow! Thanks!! I was starting to worry as in another similar post in this forum, someone said that it wouldn't be possible.

The reference number I used is part of the information I pull through from Excel. I only use it to make sure that the entries are unique - it does not need to appear on the form or anything. I just left it all there so I could see what I was doing.

I thought this was going to be plain sailing after I had done my tables...serves me right I guess.

 

[Simon_MT]

I come from a background of being a non-techie, so I had to learn Access backwards. I knew what I wanted to acheive but couldn't get Access to do it.

Tables are the easy bit. You will need to split the database once you have developed it. Each User having their own version of the forms reports queries etc. and the Backend for data.

Simon

 

[Munkyjay]

I'm in the same boat! I can picture the form in my mind and how it works, but it just seems to be beyond my reach at the moment

When I ran a query to display all the questions, it works fine - just not on the form. I just noticed your other post about updating the table. Is that what you've added to this database?

So the offender is chosen, then this append query is run to add the questions to the responses table and then subform refreshed to show it all....?

 

[Munkyjay]

I just tried this idea of yours about using the append query to fill in the combined-key for the responses table (using ID from QUIPS and ID from Questions). This has now let me see all the questions for every offender!

This means that for just three offenders, the responses table already has 438 records now. How many records should a table really be holding to run smoothly? The information stored is pretty minimal really.

I've never had training or experience of splitting a database. I can see now why you suggested that - due to how big this table will get. Will it mean that I need to export the responses data into another table to archive? And if I did that, I'd need some criteria to select what goes over and what doesn't...?

 

[Simon_MT]

MunkyJay,

You should be able to populate thousands of records. The Question file is small as it is essentially an index.

I have created a v3 for you. Naming Conventions have been used although I have no been totally consistent. There is a lot more that can be done but this should be a go starting point. Your update routine would need to re-done and probably a few more combi-boxes where appropriate.

Hopefully I have taken out all the Lookups in the tables.

I have split the Name into First and Last so that it is searchable, with wildcards. I have populated the first two records with the third for you to test.

Simon

 

[Dairy Farmer]

Just an option that may help you. Are you, by any chance, on a domain server? If so, Sharepoint has great ways of doing surveys.

 

[Munkyjay]

Apologies for the delay, I've not been in the office all week. This is amazing work Simon! I'm actually leaving on holiday tomorrow morning, but had a very quick peek at this just now - the form is brilliant! What was your secret to getting the subform show up in that way?

 

[Simon_MT]

There are 12 controls for each section hidden on the form S1-S12 with the Section Value. This are used for the Parent-Child relationships. The Subform for the Management Team and Reviewers is simply there to save space. There could be more Lookups for on the subform but you can look at that later.

The Name has been split because it is easier to search on First Name and Surname and you will have to split these out when importing the information.

I have Access 2007 but I'm guessing you maybe using Access 2003 so the look maybe slightly different.

If you need any further help when you get back from holidays let us know.

Simon

 

[Munkyjay]

Hi Simon

Happy New Year! I've had a look at the form, and I can see the hidden controls. I still can't quite work out how you managed to split the subform up into tabs/sections....?

 

[Simon_MT]

Within the Questions there is SectionID so as long as the SectionID is the underlying query you can split out each section. By first creating a Form with the Questions and once S1-12 controls are created then it is a matter of adding a Tab Control and 12 Subforms to the blackened part of the Tab Control and adding (Add Page) tabs as you go along. Then the tedious (just a dig as MS) part is removing the subForm title and repositioning and resizing each subform so it is consistent.

Simon

 

[Simon_MT]

There is no other thing the Team / Reviewer Selection might be wrong, I couldn't remember who was excluded from the process, I was having a blonde moment.

Simon

 

[Munkyjay]

Hi again!

I thought I had already tried this before, and when I was changing from one tab to another in the design view, the subforms were overlapping one another. So they were not showing different subforms for each tab. Maybe I was doing this wrong.

I hope you don't mind the questions! It's just that I have to look after this database after it is being used, and I want to make sure that I understand the thought and design process behind it all.

Thanks!