Questionaire database

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • zmbd
    Recognized Expert Moderator Expert
    • Mar 2012
    • 5501

    #16
    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>D atabaseTools>Re lationships) 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...

    Comment

    • Kaylen83
      New Member
      • Sep 2014
      • 15

      #17
      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?

      Comment

      • twinnyfo
        Recognized Expert Moderator Specialist
        • Nov 2011
        • 3653

        #18
        Kaylen,

        Could you post a pic of your relationships, and perhaps a couple of your tables in design view (particularly tblElementScore s). 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 tblElementScore s. 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....

        Comment

        • twinnyfo
          Recognized Expert Moderator Specialist
          • Nov 2011
          • 3653

          #19
          @Z,

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

          :-)

          Comment

          • Kaylen83
            New Member
            • Sep 2014
            • 15

            #20
            Relationships

            Hi Tweenyfo, pleasee see the attached doc of the relationships.
            Attached Files

            Comment

            • twinnyfo
              Recognized Expert Moderator Specialist
              • Nov 2011
              • 3653

              #21
              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....

              Comment

              • twinnyfo
                Recognized Expert Moderator Specialist
                • Nov 2011
                • 3653

                #22
                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).

                Comment

                • Kaylen83
                  New Member
                  • Sep 2014
                  • 15

                  #23
                  Relationships and table design views

                  Hi Tweenyfo, I am attaching the table's design view.
                  Attached Files

                  Comment

                  • twinnyfo
                    Recognized Expert Moderator Specialist
                    • Nov 2011
                    • 3653

                    #24
                    Great! See above concerning tblElements.

                    Comment

                    • Kaylen83
                      New Member
                      • Sep 2014
                      • 15

                      #25
                      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(El ements) 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(tblCon tacts) and the audits(tblAudit s) done for them. The audit table(tblAudits ) is linked to the tblElementScore s by the AuditID.

                      Comment

                      • twinnyfo
                        Recognized Expert Moderator Specialist
                        • Nov 2011
                        • 3653

                        #26
                        Kaylen,

                        Have you thought about adding a Yes/No field to tblElementScore s? 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....

                        Comment

                        • Kaylen83
                          New Member
                          • Sep 2014
                          • 15

                          #27
                          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.

                          Comment

                          • twinnyfo
                            Recognized Expert Moderator Specialist
                            • Nov 2011
                            • 3653

                            #28
                            I'll try to provide some options tomorrow morning. We'll try to come up with some easy solutions.

                            Comment

                            Working...