Struggling creating a version of this Excel sheet in Access form

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • twinnyfo
    Recognized Expert Moderator Specialist
    • Nov 2011
    • 3653

    #16
    Z,

    Thanks for fixing the image on my previous post....

    Agreed, there should (must) be a relationship between Assessments and Items. With that, the Cross Tab should operate perfectly.
    Last edited by zmbd; Sep 18 '14, 07:30 PM. Reason: [{YMW(^_^) I can hold off on those bifocals yet (º_º)}]

    Comment

    • Hargo
      New Member
      • Sep 2014
      • 88

      #17
      I WANT a:

      1 Assessment(FK ItemID) TO Many Items(PK ItemID) relationship

      BUT

      Only seem able to have 1 Item TO Many Assessments


      I think I'll try your arrangement twinnyfo as your Crosstab query result looks perfect - thanks


      Thinking forward, I'm a bit concerned about data entry

      My existing form (for non-normalized table) was fairly straightforward and speedy to complete (see attached)

      With this new structure (which I haven't fully grasped admittedly) I am envisioning the user having to add 1 Item at a time but having to select the item, item type, Quarter etc every time - quite a lengthy process

      Thoughts?
      Attached Files

      Comment

      • Hargo
        New Member
        • Sep 2014
        • 88

        #18
        Sorry twinnyfo, I'm really NOT grasping your relationships!!

        For instance:


        The join between tblItemTypes AND tblItems goes from the ItemTypeKey (PK) to the ItemType


        I assumed the ItemTypeKey was an AutoNumber so the data types are different thus preventing a join - what is your ItemTypeKey?

        Why does the join not go from the ItemType to the ItemType?


        Also does the tblItemTYpes only have two records in it, Clinical & Security?

        I'm such a novice!!!
        Last edited by Hargo; Sep 19 '14, 08:58 AM. Reason: Error spotted

        Comment

        • twinnyfo
          Recognized Expert Moderator Specialist
          • Nov 2011
          • 3653

          #19
          Hargo,

          Concerning Post #17, you do NOT want 1 Assessment to Many Items, as then you would have to have an Assessment Field in tblItems. Assessments are not a required bit of information for each Item. However, each Assessment must have Items in it for it to be an assessment. The 1 Item to Many Assessments means that each unique ItemKey can appear in tblAssessments many times (thus one assessment has many items included in it--as you wish, but I think the concept is not quite making sense to you, which is fine, we all have to learn to understand DB design over time).

          Also
          With this new structure (which I haven't fully grasped admittedly) I am envisioning the user having to add 1 Item at a time but having to select the item, item type, Quarter etc every time - quite a lengthy process
          When a new patient is added (or when you want to create a new record of your 19 items) you can have your VBA module behind the form create those 19 entries, with the designated quarter. Just because the table is designed in this particular way does not mean that we have to wear out our fingers during data entry. Then, during data entry, instead of having a single form, you would have a continuous form, filtered by the patient and current date/quarter--however you plan to capture the date/quarter. This is more involved that the original thread, but it is very possible and would make sense in the long run. I would even advise that using an option group for each record would be beneficial, as the user could then simply click one of six buttons (labeled 0-5) to select the value for each of the 19 items--much faster than tabbing and typing in my opinion.

          Concerning Post #18:
          Sorry twinnyfo, I'm really NOT grasping your relationships!!

          For instance:

          The join between tblItemTypes AND tblItems goes from the ItemTypeKey (PK) to the ItemType

          I assumed the ItemTypeKey was an AutoNumber so the data types are different thus preventing a join - what is your ItemTypeKey?
          In my tables, tblItemTypes.It emTypeKey is a Long Integer, Autonumber; tblItems.ItemTy pe is also a Long Integer. The Field Name is merely "ItemType". I could have just as easily used "ItemTypeKe y", or I could have called it "Gorilla" or "Ectoplasm" . The name of the field is immaterial--My preference (which does not have to be yours) is to use "ItemType", even though it is referring to "tblItemTypes.I temTypeKey".

          You ask:
          Also does the tblItemTypes only have two records in it, Clinical & Security?
          Yes--Absolutely! This is the foundation of good DB normalization. You don't want people to "accidental ly" add a value that you don't want.

          You say:
          I'm such a novice!!!
          I was a novice many years ago, too. However, I continue to learn new things on this forum all the time. You have to start somewhere, and we are glad to help you along in your journey. I hope the assistance and insights you gain on this forum help you to become a valuable asset to your employer!

          Comment

          • Hargo
            New Member
            • Sep 2014
            • 88

            #20
            Thanks very much for taking the time to explain twinnyfo

            I think I grasp the first point about the 1 to many and I appreciate the ItemKey / Item Type as Integer now as well as the last point about normalization

            BUT I have no idea how the form will collect all the data despite your detailed explanation!! Much to learn

            I have looked at your cross tab query and like the fact that Quarters are selected based upon the month (although I need to check with the users whether quarter is standard or whether it refers to individual patients' 3 monthly stay!!

            If it's the latter, is the same outcome achievable? Plus would it be possible to add to the IIF Statement a line for 'Baseline' based on the date? I'm thinking: If Assessment date is within so many days of Admission date?

            Regards

            Hargo
            Last edited by zmbd; Sep 20 '14, 01:41 AM. Reason: [z{heads-up - We may split this thread at this point depending on the need (^_^)}]

            Comment

            • twinnyfo
              Recognized Expert Moderator Specialist
              • Nov 2011
              • 3653

              #21
              Hargo,

              If you are willing to stick around, we may be able to accomplish much here.

              To the Site Mods, please allow us a bit of leeway here, as we might be drifting a bit off course of the original thread, but I do think it is all pertinent to Hargo converting his Excel-based Charts into an Access-based Chart.

              =============== ========

              Take a look at the attached image.[IMGNOTHUMB]http://bytes.com/attachment.php? attachmentid=78 75&stc=1&d=1411 136939[/IMGNOTHUMB]
              This form is used to Score records for Promotion. According to theory, if this was just a Continuous Form, I would enter the SRID (or even select it from a drop down list) and then select the Officer eligible for Promotion. Additionally, since the record is scored by several senior officers, I would have to also select the person scoring the Record and then enter the Score.

              However, unseen in the image is the table behind it. When we are scoring records, we append records to the Scoring Table. We include all the Officers eligible, and All of the Scorers. Since the SRID is associated with the Eligible, we merely use the Index for the Officer to get the SRID. Then, I have a field for the Score the Record receives from that Scorer. The nine buttons you see are all part of an option group which assigns a score based on which button is clicked.

              Now, when I filter the records by Scorer ABCD, I get a list of all the Eligible Officers (sorted alphabetically) . I enter the scores for these records as I receive them from the Scorer (they complete their scores on paper after reveiwing the Officer's records). I just click on the score and scroll down through the list. When I am done, I select another Scorer and another set of records pops up--the same officers, but filtered by a different scorer.

              This is basically what you will be doing with your Assessments Table. When you want to add an assessment, you would (for example, because I don't know exactly how you want to implement this) select the Patient, then Click on "Add Assessment". This would run an Append Query, based on the Patient and the Current Date. This Append Query would use the UNJOINED Items Table (which will always result in 19 records) and Append the PatientID, AssessmentDate and ItemID. The Assessment Value should default to Zero, unless you prefer a Null value, as Zero and Null mean two completely different things. Zero means an assessment of 0, whereas Null might indicate "Not Assessed". Determine what these values should mean beforehand.

              I have looked at your cross tab query and like the fact that Quarters are selected based upon the month (although I need to check with the users whether quarter is standard or whether it refers to individual patients' 3 monthly stay!!

              If it's the latter, is the same outcome achievable? Plus would it be possible to add to the IIF Statement a line for 'Baseline' based on the date? I'm thinking: If Assessment date is within so many days of Admission date?
              We still have to determine where teh "Baseline" comes from.

              All these are details you should determine before hard-wiring too much into your queries and Forms. As you continue building and designing Databases, you will learn that every minute determining exactly what the customer wants is worth every hour of coding required to fulfill that requirement.

              If you begin building based on faulty assumptions, the rework required is work-time (and sanity) lost forever.

              I hope this post gave you a little more to work with. I am happy to walk you through the Option Group building process--it's actually very easy once you've done it a few times.
              Attached Files

              Comment

              • zmbd
                Recognized Expert Moderator Expert
                • Mar 2012
                • 5501

                #22
                ...please, any deeper into the post conversion data entry needs to be in a new thread. Links to this thread will provide the needed context.

                Comment

                • Hargo
                  New Member
                  • Sep 2014
                  • 88

                  #23
                  Thanks again Twinnyfo, I think I grasp the concept but haven't a clue how to create your solution. Based upon the above mod comment should I create a new thread with some more pertinent info?

                  Perhaps a title of "Creating a Scoring/Assessment form in Access"?

                  Comment

                  • twinnyfo
                    Recognized Expert Moderator Specialist
                    • Nov 2011
                    • 3653

                    #24
                    Hargo,

                    A new thread may be the best way to go. I'll be glad to work through the exercise with you. I'll be "more free" tomorrow, as I am ultra busy at work today.

                    Comment

                    • Hargo
                      New Member
                      • Sep 2014
                      • 88

                      #25
                      Just a quick question regarding the cross tab query SQL twinnyfo

                      Line 10 - The Inner Join for tblAssesments doesn't have an opening bracket like the other two Inner Joins - is there a reason this one doesn't need it?

                      Just trying to edit your SQL for my object names at the mo and I thought it worth an ask is all



                      I'll post something new later then and just get back to me when you can, no rush, you are helping me after all (I posted this before i saw your post, sorry)

                      Comment

                      • twinnyfo
                        Recognized Expert Moderator Specialist
                        • Nov 2011
                        • 3653

                        #26
                        My SQL in post #12 is directly out of the Query Builder. If your relationships are identical, you should only have to change Table Names (plus allowing for any naming convention issues you may have (like spaces in your field names).

                        Comment

                        • Hargo
                          New Member
                          • Sep 2014
                          • 88

                          #27
                          Tried to make all edits necessary to allow for naming conventions but I think there are other issues with tables & fields & relationships.

                          I have attached my relationships and an issue I just don't get based on them -

                          I really don't know why tbl_HoNOS_Items has a sub datasheet - I assume it's because of the relationships but don't grasp why each Item would have it's own table of Ratings, Dates and Patients

                          When you're less busy could you have a look please, as I think anything I do from here on is based on shaky ground!!


                          I think grasping this is central to continuing

                          Thanks again for your time
                          Attached Files

                          Comment

                          • twinnyfo
                            Recognized Expert Moderator Specialist
                            • Nov 2011
                            • 3653

                            #28
                            Hargo,

                            1. Please post your Cross Tab Query Code

                            2. In general, tbl_HoNOS_Items seems to be set up OK, except Item Key does not appear to be a Long Integer, Autonumber. It appears to be text (unless I am missing something). Your corresponding Field in tbl_HoNOS_Asses sments should also be a Long Integer (but not Autonumber), as it is the foreign key to this Table. You may still have a Field which contains the text (Item X), if you desire, but it is really unnecessary, as the Item Code/Name (ADL, etc.) covers that. Item Key should be the "index" for that Table, and be a numerical value, so that other tables can look at the Index for that Table and refer to any of the other fields related to that index.

                            As I look at your data, at least as it is presented in the original Excel Sheet, it appears that you want to always present the data in the same order? If this is the case, then you may want to keep that separate field for sorting records in a particular way. I have some tables use such a method.

                            Concerning the subdatasheet, I would not be concerned about it at all. The Subdatasheet is merely displaying all the times that the item number is displayed in tbl_HoNOS_Asses sments. In design view of your Table, you can delete the subdatasheet reference to tbl_HoNOS_Asses sments, or change it to another table. In general, this will not affect how your DB functions.

                            Hope all this makes sense.

                            Comment

                            • Hargo
                              New Member
                              • Sep 2014
                              • 88

                              #29
                              Thanks again twinnyfo

                              The Item Numbers (1-19) are used in the spreadsheet as well as the Item Names - as they are all unique I assumed I could use them as the "index" (is this not the case?) - Also, there appears to be a problem sorting using this field because due to the data type being text the sort order goes; 1,10,11,12 etc 19, 2, 3 etc. I guess having an autonumber would solve this as that field would sort 1 - 19 would it not?

                              Before I delete the subdatasheet reference can I clarify that the sub data sheet would collect ALL Item 1 etc. scores for every patient and thus be unwieldy/unnecessary?

                              I will post in new thread for the Scoring form shortly, but if I zip up the database tonight and attach it could you have a look for other fundamental floors in my design please? (Don't have ZIP at the place I am working, they use RAR)

                              Cheers

                              Hargo


                              Here's the cross tab query:

                              Code:
                              TRANSFORM Sum(tbl_HoNOS_Assessments.[Assessment Rating]) 
                                  AS SumOfAssessment_Rating
                                  SELECT tbl_Admissions.[Patient ID], 
                              tbl_Patient_Information.Surname, tbl_Patient_Information.[Forename(s)], tbl_HoNOS_Item_Types.[Item Type], 
                              tbl_HoNOS_Items.[Item Name]
                                  FROM tbl_HoNOS_Item_Types
                                      INNER JOIN (tbl_HoNOS_Items
                                          INNER JOIN (tbl_Admissions 
                                              INNER JOIN tbl_HoNOS_Assessments
                                                  ON tbl_Admissions.[Patient ID] = tbl_HoNOS_Assessments.[Patient ID])
                                              ON tbl_HoNOS_Items.[Item Key] = tbl_HoNOS_Assessments.[Item Name])
                                          ON tbl_HoNOS_Item_Types.[Item Type Key] = tbl_HoNOS_Items.[Item Type]
                                  GROUP BY tbl_Admissions.[Patient ID],
                                      tbl_Patient_Information.[Surname], tbl_Patient_Information.[Forename(s)],
                                      tbl_HoNOS_Item_Types.[Item Type],
                                      tbl_HoNOS_Items.[Item Name],
                                      tbl_HoNOS_Item_Types.[Item Type Key],
                                      tbl_HoNOS_Items.[Item Key]
                                  ORDER BY tbl_HoNOS_Item_Types.[Item Type Key],
                                      tbl_HoNOS_Items.[Item Key]
                                  PIVOT IIf(Month([Assessment Date]) Between 1 And 3, "Q4",IIf(Month([Assessment Date]) Between 4 And 6, "Q1",IIf(Month([Assessment Date]) Between 7 And 9, "Q2", "Q3")));
                              Issues I can remember popping up include TRANSFORM statement syntax error and Surname field not recognized (I suspect this may be to do with my relationships hence the zip request)

                              Comment

                              • twinnyfo
                                Recognized Expert Moderator Specialist
                                • Nov 2011
                                • 3653

                                #30
                                Hargo,

                                The Item Numbers (1-19) are used in the spreadsheet as well as the Item Names - as they are all unique I assumed I could use them as the "index" (is this not the case?) - Also, there appears to be a problem sorting using this field because due to the data type being text the sort order goes; 1,10,11,12 etc 19, 2, 3 etc. I guess having an autonumber would solve this as that field would sort 1 - 19 would it not?
                                This is why, if the items should always be in the same order, that I recommend using an additional field used only for sOrting. The reason I say this is that you may eventually come across new assessments, which, by the AutoNumber, would come at the end of your list, but you want them sorted at the top of the list. So, your tbl_HoNOS_Items would look like this:

                                Code:
                                [B]Field[/B]            [B]Description[/B]
                                ============================
                                ItemKey          Long, AutoNumber, PK
                                SortOrder        Integer (1-19)
                                ItemNumber       Text ("Item 1", "Item 2", etc.)
                                ItemName         Text ("ADL", "LIV", etc.)
                                ItemType         Integer, FK to tbl_HoNOS_Item_Types
                                ItemDescription  Text
                                Again, I cannot emphasize enough about NOT using spaces in your field names. You will learn that in the long run this makes maintaining and coding your DB much easier, especially if you start using VBA to run recordsets. All things can still be done without getting rid of the spaces, but everything is just easier to do.

                                Before I delete the subdatasheet reference can I clarify that the sub data sheet would collect ALL Item 1 etc. scores for every patient and thus be unwieldy/unnecessary?
                                In answer to your question, yes, that is what will be displayed. Again, deleting it or not is completely immaterial. It won't affect how your DB operates, only how your Table is displayed in datasheet view.

                                Unfortunately, I cannot access ZIP files from this site at work.....

                                I'll try to duplicate the Tables you have as best I can to see what I can find.

                                Hope this helps.

                                Comment

                                Working...