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?
27 2788
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.
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
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.
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.
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!
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?
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.
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: - Field Type Notes
-
QNum Integer Determines which question number the user is answering
-
QValue Integer Determines "Value" of Answered Question (i.e. 1-5)
-
QText Text If there is a written response user wants to provide
-
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.
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.
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!
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.
Because of resolution issues, I can't read your attachment. Try to embedd in a word doc and I'll take a look.
Here is the Word version.
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
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...
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?
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....
@Z,
Thanks for the info on the image links! I was wondering what I was doing wrong......
:-)
Hi Tweenyfo, pleasee see the attached doc of the relationships.
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....
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. tblElementsYou 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).
Hi Tweenyfo, I am attaching the table's design view.
Great! See above concerning tblElements.
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.
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....
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.
I'll try to provide some options tomorrow morning. We'll try to come up with some easy solutions.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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 :
...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |