473,323 Members | 1,550 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Questionaire database

What is the best way to display a blank list of questionaire for every new record. And the person to answer only the applicable questions when an Applicable Yes/No field is checked. I am thinking then to apprend those answers to the historical table where all the answers are kept? Any advice on how to get this structure done?
Sep 26 '14 #1
27 2788
twinnyfo
3,653 Expert Mod 2GB
Kaylen83,

Conceptually, you would build an unbound form that includes text boxes (or response fields or check boxes--whatever types of data you are gathering) and based on the value of the Y/N check box (you don't mention how that check box is set), disable some of the controls. Then when the user has complete the questionnaire, they click a submit button which appends the record to the desired table.

There are a lot of moving parts included in what you are asking. This can be done, but you will have to provide us considerably more information before we can provide specifics.

Please review this thread: How to Ask Good Questions and provide us more details about your request.
Sep 26 '14 #2
Hi Tinny, thank you for the guidance. How do you apprend the answers the the table if the controls are unbound fields that are not part of another table?

@twinnyfo
Sep 26 '14 #3
twinnyfo
3,653 Expert Mod 2GB
You would create an INSERT query, based on the controls on your form. This could be quite complex (depending on how many responses there are).

Of course, if you wanted a bound form, that is the easiest way to update a table, then you could control whether a record is saved or not in other ways.

To be honest, it is a little unclear what you want to do (exactly) and why you want to do it that way....

But, I'm willing to work through a solution with you with better understanding of what you want to do.
Sep 26 '14 #4
Hi Twinnfo, I thrilled you are willing to help me with this, I have been spending a month building an audit database that record all the scores for elements so I can compile reports from the scores. Now I have come to a standstill.
Everything seems to be working correctly until when I published the tables to Sharepoint and linked them, now my select query doesn't allow data entry. Before the Sharepoint linking, I have a query that allows me to add the elements and their scores while pulling related data from other tables. That is how I was able to enter from the query the elements and scores for each audit. I am not sure if this is the rights structure anymore after I geard of how append query might be the way to do the job. So this my goal, I want to have a form listing all the elements and scores for every audit to be entered. Along with each element, there is a check box for NA, not applicable, and if checked, that element will not be scored. Since the elements and scores are short, I have them in continous form. As for old data, I have put in some of the old audits for report analyzing and would still want them to be pulled based in their audit ID. Please please if you can help me get this database to work correctly, I would be so appreciative. The ultimate solution is to be able to have the tables linked through SHarepoint and users to work on entering audit scores using access front end. Let me know if you need any printscreen.
Sep 26 '14 #5
twinnyfo
3,653 Expert Mod 2GB
You just said a very ugly "four-letter word": SharePoint.

From what I understand you can only link an Access DB to SharePOint via one of the SharePoint Lists. I have very limited knowledge and understanding of SharePoint lists, as it pertains to Access, but the key to remember is that all your data in SharePoint must be what the Databse is looking for.

AND, also from my limited knowledge, SharePoint does not allow for MS Access to update the SharePoint List (I may be wrong on this, so I appreciate other experts jumping in on this one) unless the specific permissions are set for that user. And, your queries will behave differently in a List than with a Table.

The biggest problem that I see with SharePoint is that a SharePoint List is NOT a Table.

Please keep in mind that your description in Post #5 is significantly more involved and not very descriptive of a "Questionnaire Database", but deals more specifically with linking said database to SharePoint (that ugly four-letter word).

In general, I think what you are trying to do is doable. However, I would have to learn along with you.

You said the DB-only version worked fine?

Also, is your goal to have users enter their data from SharePoint or from the DB or both? This is important, because I have built surveys on Sharepoint before, and they can be both fun, challenging and tricky.

To success!
Sep 29 '14 #6
Hi Twinnfo,
Actualyl I am not building a questionnaire database, I'm just referring to it that way because I wanted a blank audit form to show up when you click the create new audit button.
My DB is working fine with the query updating the tables before linking to Sharepoint. I am trying to the best way for my users to use the database, I tried splitting the database and put it on the network drive, however, the reponse time between the front end and the back end is very slow. That was why I turned to Sharepoint, which turns out to be much faster in connecting between the front and the back. If you have other ideas to share my database with mltiple users, please let me know.

Still my problem with the working standabalone database, I am still not able to see all the elements listed when creating a new audit, I have to manually select the each applicable element before I can score then. This could be tedious and a user may miss a question or two. So my goal is to get those questions listed all out and the users can select Not Applicable (NA) to disable to score for that question. Then after all is scored, there is a submit audit that apprend all the non null scores and elements to the tblElementScores table. Does this sound like the right way to go?
Sep 29 '14 #7
Twinnyfo,see the link to the printscreen of some of my tables and form.



Don't know if the link is workin on this site, I wanted to attach a printscreen.
Sep 29 '14 #8
twinnyfo
3,653 Expert Mod 2GB
Your link just sent me to this same thread.

My "best" recommendation for sharing is using MS Access on a shared network. How large is your DB? It is odd that the lag time would be so great, unless the DB is huge.

Also, there are very effective ways to share a DB on a network (search on Bytes for a good thread--I'll try to find one, too, as this is how I learned to do my own).

On our network, SP is about 100 times slower than our shared network, so I am surprised that it was actually an increase in performance.....

Share with us a little more on your Audit/Questionnaire DB:

Your Table: Does it look somewhat like this:

Expand|Select|Wrap|Line Numbers
  1. Field  Type     Notes
  2. QNum   Integer  Determines which question number the user is answering
  3. QValue Integer  Determines "Value" of Answered Question (i.e. 1-5)
  4. QText  Text     If there is a written response user wants to provide
  5. QNA    Yes/No   Indicates if the user will answer this question.  If true, ignore the response
If all questions have the same possible responses, then this is a good set up for your database, otherwise your table becomes too "wide" to manage effectively and causes many problems when adding or deleting questions.

Let me know about this; we'll work through this step by step. BUT, I think your main concern, as you have mentioned, is getting a list of blank responses for a new user. The user only has access to those responses, and as they enter their responses, they are recorded in the DB. Plesae correct me if this is not the case.
Sep 29 '14 #9
Hi Twinnyfo, thank you for aggreeing to help me through this mess. My databse is not big at all, only 2MB or less so with the splitting, it is less for each end. I don't know why the connection is slow, even if we wanted to open a Word document on the shared network drive, it takes some time to load.

My tblElementScores table does look like the one you examplified. And yes, the users select the questions to be answered on the form which is created from a query that insert the scores and questions answered to the the main table of Scores.
How do we attach a document on this site? I would like to show you some printscreens.
Sep 29 '14 #10
twinnyfo
3,653 Expert Mod 2GB
When you respond, click on the "advanced" button at the bottom of the screen.

Click on "Manage Attachments"

Click "Browse" and select a file (embedding a .PNG into a Word Doc works best, because I can open those at work).

Click "Upload"

Once the file is uploaded, click "Close this Window"

Then, below your response, you will see a list of the files you have set to upload.

If you have an image file that meets the dimension requirements, you can use the "IMGNOTHUMB - /IMGNOTHUMB" tags (placed within brackets, like the Code Tags:

which may or may not work on your system.....

Hope to keep heppin' a whole heap!
Attached Images
File Type: jpg Goofy Head Scratch 2.jpg (24.7 KB, 10554 views)
Sep 29 '14 #11
Oh I totally didnt see the manage attachments at the end of the Advance reply window! Sorry, i am new to this forum. :) Here is my prinscreen in PNG.
Attached Images
File Type: jpg Audit DB progress.jpg (84.5 KB, 325 views)
Sep 29 '14 #12
twinnyfo
3,653 Expert Mod 2GB
Because of resolution issues, I can't read your attachment. Try to embedd in a word doc and I'll take a look.
Sep 29 '14 #13
Here is the Word version.
Attached Files
File Type: docx Doc1.docx (1.28 MB, 345 views)
Sep 29 '14 #14
zmbd
5,501 Expert Mod 4TB
Just FYI
When you go to the Advanced Editor to insert the image.
You can use [IMG]...[/IMG] for just a thumbnail view, or the [IMGNOTHUMB]...[/IMGNOTHUMB] to place the image in-line... the trick is the url!

I'll take TwinnyFo's image from Post #11

So we upload the image, and find the url for the image. It would seem to make sense to do this:
[IMGNOTHUMB]http://bytes.com/attachment.php?attachmentid=7905&stc=1&d=141200400 5[/IMGNOTHUMB]

and we get this:


Broken-link icon (,_,)

When we really want to see his shinny mugshot



To accomplish this, what we need to do is delete the very last part following the "attachmentID=####" from the ampersand on, so delete the "&stc=1&d=1412004005" leaving just:
[IMGNOTHUMB]http://bytes.com/attachment.php?attachmentid=7905[/IMGNOTHUMB]

I'll resest the urge to place that link back into the parser....
-Z
Sep 29 '14 #15
zmbd
5,501 Expert Mod 4TB
AND, also from my limited knowledge, SharePoint does not allow for MS Access to update the SharePoint List (I may be wrong on this, so I appreciate other experts jumping in on this one) unless the specific permissions are set for that user. And, your queries will behave differently in a List than with a Table.
This is correct.
I can easily link to Sharepoint lists; however, to be able to even read the list, the Sharepoint admin must allow my user profile read rights to that part of the site. The same is true if I would like to write data back to the site.

IMHO: Sharepoint is broken for database useage mainly for two reasons:
  • It does not support the VBA commands. Only things that you can do from a MS-Access-MacroCommand (and even then very limited) can be called from your forms.
  • Sharepoint does not support the relational links (AccessRibbon>DatabaseTools>Relationships) You must use the nasty little lookup-field at the TABLE LEVEL to establish the workaround and these are sometimes a bit fickle as to if they will work or not work correctly with the Sharepoint site.
- FE/BE-Splits: Sometimes the slowness is because each user does not have their own copy of the FE on their client. Also tables that either seldom or do not change (say a table with the names of the weekdays, or USA State names and abreviations) should be pushed to the FE to be used at the client end.
- Another reason for the slowness can be if you have what I call a server-side installation of office... which was not the intent of MS Office.

timers going off and I need to get them going...
Sep 29 '14 #16
zmdb, you are right about Sharepoint, it doesnt support data integrity. I am the owner of the sharepointsite, so I should dbe able to read and write to the SP. When I notice how much faster the front end works o when linked to SHrepoint versus the netwrok drive, that is why I was lending toward Sharepoint.
As for the network drive, I tested it out myself by accessing the databse front end on my desktop while the back end on the network drive. Every single loading lags. The split databases are now less than 1MB each. Of course I would love to keep the tables safe and users to be able to add data to the databse simulatanously.
So two questions:

1. How do i get the list of questions to show up for each new audit?

2.What would be the best way for me to share this databse so users can use it and data are centralized?
Sep 29 '14 #17
twinnyfo
3,653 Expert Mod 2GB
Kaylen,

Could you post a pic of your relationships, and perhaps a couple of your tables in design view (particularly tblElementScores). Also, I understand redacting for privacy purposes, but I think so much was redacted that nothing really makes much sense.

I would also need to better understand the field element ID in tblElementScores. It's difficult to know exactly what that field is pointing at.

I also notice a "tblTempScores". Occasionally I use a "temp" table, but I really don't like to, because Access can bloat easily if data is truly "temporary" in a table (appending and deleting records causes Access to continually reserve more space that ultimately is unused.

This may take a while for me to understand your DB, but we'll get there....
Sep 29 '14 #18
twinnyfo
3,653 Expert Mod 2GB
@Z,

Thanks for the info on the image links! I was wondering what I was doing wrong......

:-)
Sep 29 '14 #19
Hi Tweenyfo, pleasee see the attached doc of the relationships.
Attached Files
File Type: docx relationships.docx (68.0 KB, 244 views)
Sep 29 '14 #20
twinnyfo
3,653 Expert Mod 2GB
What would be the best way for me to share this databse so users can use it and data are centralized?
Again, the "best" way is BE on the network, FE on each user's PC (the FE would be copies down every time the user opened the DB, this way the most recent updates would be available.

I have tricks to do this, but let's get you working first....
Sep 29 '14 #21
twinnyfo
3,653 Expert Mod 2GB
Some quick thoughts on your relationships:

All Tables
I highly recommend removing any spaces from Field Names. Although your DB will continue to funtion, the more involved you get with using VBA, the more convenient it will be to remove hte spaces. Simply deleting the Space or using an underscore is acceptable.

tblElements
You should avoid using "#" in your Field Names. This can cause problems when referencing that field.
Standard ID has a relationship with tblStandards.[Standard ID]. It should be related to the Index of tblStandards.
tblGroups
Is there a reason the relationship to tblContacts is not one-to-many (liek it is with tblAudits? You could have several contacts for each Group, yes?

tblContactTypes
For clarification, tblContactTypes does not appear to list any "types" of contact, but only the Contact name? It is related to the Contact Title? I am just a bit confused.

Looking at things from a "macro" perspective, it looks like your tables, at first site are pretty well normalized (exceptions listed above). It will take some time to understand what's going on, but I think I am getting closer.

I know one aspect you are looking for in particular is to create a set of elements (that hte user will not have to select each element code) but simply enter the score? Based on what I see, this should not be too difficult, I just need to konw more about your data (see post #18).
Sep 29 '14 #22
Hi Tweenyfo, I am attaching the table's design view.
Attached Files
File Type: docx Table Design Views.docx (53.3 KB, 295 views)
Sep 29 '14 #23
twinnyfo
3,653 Expert Mod 2GB
Great! See above concerning tblElements.
Sep 29 '14 #24
Hi Twinnyfo, answering your questions. Yes one group can have many contacts, and it is a one to many relationship from tblContacts to tblGroups. But the tblContactType is different, it is for the different titles, and only one contact with that title per group. So the contact types contain titles such as Director, Adminstrator, Representative. And each group can only have one contact for each of those titles.
As for the elemment table. I can take out the # sign, that field is used for sorting since new elements can be added thoughout the year.

To clarify, an brand new audit have all these major Standards that need to be scored. And each major standard have multiple sub-standards, which I called Elements, tblElements. The substandards(Elements) are the ones to be scores from 0% to 100%. I want a form that lists all Standards and all the elements scorable for a new audit. NOt all elements are applicable so I think an N/A field would be a good idea to disable that element being scored if checked Yes.

Each group has contacts(tblContacts) and the audits(tblAudits) done for them. The audit table(tblAudits) is linked to the tblElementScores by the AuditID.
Sep 29 '14 #25
twinnyfo
3,653 Expert Mod 2GB
Kaylen,

Have you thought about adding a Yes/No field to tblElementScores? This would indicate whether the element is scored or not (your "N/A" field). There are really two ways to think about this:

On the one hand, if there is a field like this, then whenever you are compiling scores, all "N/A" records are ignored. This would be relatively easy to do. The field would default to "False" (you could name the field "Ignore" or something similar), but any changes to true, during other queries, would be ignored.

On the other hand, if there is not a field like this, but the scoring is managed via the Form, then, you can have some additional options: First, if there is no score (Null), then the score is not counted. Second, if you wanted to force the non-counting issue, you could control the VBA behind the form/subform such that all "N/A" records are then deleted, thus eliminating the need for filtering any score results. In the first case, you run the risk of someone entering a score by accident--even with the N/A check box. IN the second case, you run the risk of accidentally deleting a record that someone actually wanted to score.

There are challenges with all methods, but understanding how you want to take on those challenges gives us a more direct approach during the design phase.

BTW, aside from any SharePoint stuff, I think what you are trying to do is "relatively" simple and straightforward. We'll get there....
Sep 30 '14 #26
Hi Twinnyfo, I have an NA field on the elements score table. But now I am having the issue of getting it to disble a record when checked. The conditonal formt works fine on the continuous form, but after i ran an update query to clear out the scores and checkboxes, the conditional format no longer working.
Sep 30 '14 #27
twinnyfo
3,653 Expert Mod 2GB
I'll try to provide some options tomorrow morning. We'll try to come up with some easy solutions.
Sep 30 '14 #28

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: L. Healy | last post by:
Hello All, I am pretty new to php, and I think this group is the right place to ask about this problem - please don't flame me if it isn't! I have an online questionnaire. The idea is that...
0
by: Cherrish Vaidiyan | last post by:
sir, The following are the steps that i followed in setting up standby database on Red hat Linux 9. i am using Oracle 9i. i have followed the steps in this site : ...
6
by: Marvin Libson | last post by:
Hi All: I am running DB2 UDB V7.2 with FP11. Platform is Windows 2000. I have created a java UDF and trigger. When I update my database I get the following error: SQL1224N A database...
8
by: Kamlesh | last post by:
Hi, How do I know the physical database path of a database. When I goto the DB2INSTANCE users's directory (/home/db2inst1), I see following folders: /db2inst1/NODE0000/SQL00001...
3
by: Tom | last post by:
WindowsXP, AccessXP I need to create a continuous form for entering responses to a questionaire like so: Question Response The responses can be Yes/No, Text or a selection from a...
11
by: admin | last post by:
Hi all, First time poster here... I'm a webmaster and I'd like to add a simple script to my website which will allow users to fill in a brief multiple choice questionaire, and then provide a...
10
by: Will Honea | last post by:
I have a data set which I need to analyze but I am having a problem figuring out a structure for the database - or whether there are better ways of attacking the problem. The base data set is a...
0
by: ShowyardDeveloper | last post by:
I am new to developing web sites, and need some help. How do I: When a customer is going through my questionaire they can pick how many varities of plants they want (say they want 5 different...
0
by: Jack | last post by:
Training Classes for Oracle10g, 9i, 8i Certification training in Oracle10g and 9i: DBA, Developer, Discoverer. training conducted at your location worldwide. Courseware licensing also available....
1
by: carlosk | last post by:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim connection As New SqlClient.SqlConnection Dim command As New...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.