Struggling creating a version of this Excel sheet in Access form

[Hargo]

Hi


I am trying to create a form in Access that does the same as the spreadsheet shown in the attached doc

My initial problem is that I don't know how to get all the Item Numbers into a cross tab query - each item is a field in a table

From what I can see cross tab queries have a maximum of three fields that can be used as row headings whereas I have 19!!

A simple query would show the data transposed with Quarters as rows and the Items as columns but I was hoping Access could do this as it is shown in the attachment

Is it possible and if so what's the technique?

I would also want the data to change depending upon the Patient ID Field

Any suggestions gratefully received


Thanks

[twinnyfo]

Hargo,

My first and best piece of advice would be to Normalize your data (see here Database Normalization). Your Items should all be in the same field, rather than separate fields.

Once you do this, you will find that creating your Cross Tab Query will be a snap!

Building the charts and graphs will be another story, but the same priniciple will apply in Access that exists for Excel. You can probably use the same Cross tab Query.

I hope this gets you pointed in the right direction. We'll stand by for further assistance as needed.

[Hargo]

Thanks twinnyfo

I suspected that might be the issue but cannot grasp how I would record quarterly scores for each of the items for each of the patients

The items are individual assessments which are scored out of five leading to an overall score

Each patient has an assessment upon admission and then quarterly after that

It's obviously pertinent that I couldn't think of a primary key for this table

I read the normalization stuff (Again) and whilst I get the gist, I still cannot envisage how to link the 'one-field' Items table to the patient. I assume a 3rd table (Perhaps called assessments) would be needed but still cannot see in my mind how everything links to one field in Items - what would be the primary key of Items for instance?

Think my basic skills have hit a wall!! haha

[twinnyfo]

Hargo,

The basics of what your table would loo like when populated is that rather than having a "wide" table, you have a "tall" one.

Based on the data you've provided, your Table would have the following fields:

Field          Type
===================
AssessmentKey  Long, AutoNumber, Primary Key
PatientID      Text (or Long, FK to Patients Table; e.g. the "right way" to do it)
ItemType       Integer, FK to Table tblItemTypes (listing: 1 = Clinical, 2 = Security)
ItemID         Long, FK to your list of items (AGR, SH, etc.)
AssessmentDate Date (or text if you only save as Q1, Q2, etc.)
Assessment     Integer

Then, your table would look like this:

1  SG0041  Clinical  AGR  Q1  3
2  SG0041  Clinical  AGR  Q2  3
3  SG0041  Clinical  AGR  Q3  2
4  SG0041  Clinical  AGR  Q4  1
5  SG0041  Clinical  SH   Q1  3
6  SG0041  Clinical  SH   Q2  3
7  SG0041  Clinical  SH   Q3  2
... etc.

If this were the case, a Cross tab Query would take Patient ID, Item Type and ItemID as Row Headings and AssessmentDate (Qtr) as a Column Heading and use the Assessment as the Value.

I do not know the origin of the Baseline value, otherwise I would also have included that value.

Hope this hepps!

[Hargo]

Thanks very much for your help twinnyfo

Only thing still puzzling me is:

ItemType & ItemID both FK - is the tblItemTypes not the 'List of Items'? Does that mean the two fields need to be a composite key in tblItemTypes?

[twinnyfo]

Hargo,

Yes, tblItems should have ItemID, ItemType and Item. Then when you build the CrossTab, join the Items Table with your Assessments Table and Include ItemType--this is probably the "best" way to normalize these tables. Then your assessments table would no longer need the ItenType field, but just the index to the Items Table.

Hope that made sense. But, it is a good catch!

[Hargo]

Thanks, I think I'm with you

Final questions

- Is AssessmentKey essential as the data would be meaningless (Must the table have a Primary Key if it has a Foreign Key to Patient and A Foreign Key to Items?)

- With regard to relationships - can a FK have a 1 - many relationship to a PK?

Is that correct - One Assessment can have many Items?

[twinnyfo]

The Primary Key, in this case (IMO), is not absolutely necessary. It does not seem to be a table for which you would need to refer to in such a way. I just have a habit of building in a PK for most of my tables.

I know you have some work ahead of you with this project, but don't hesitate to let us know how you are doing with it and know that we are also willing to help with other troubleshooting as needed.

[Hargo]

Thanks twinnyfo, you've been a great help

I'm just looking at the relationships now and I don't seem to be able to do what I think I should!!

The only options appear to be:

1 Item has Many Assessments
I item has 1 Assessment

I think I want 1 Assessment has many Items (i.e. 19)

(I may have edited my last post after you had opened it, sorry)

[twinnyfo]

Yes, one assessment has many items. You have the right idea.

[Hargo]

But it doesn't seem possible

Access seems to only allow the PK to have one and the FK many

Unless I'm being a divvy!!

[twinnyfo]

Here is a picture of the relationships that you should have:

Once you have those figured out, a sample cross tab query such as this:

TRANSFORM Sum(tblAssessments.AssessmentValue) 
    AS SumOfAssessmentValue
    SELECT tblPatients.PatientID, 
        tblPatients.[Patient Name],
        tblItemTypes.ItemType,
        tblItems.ItemName
    FROM tblItemTypes
        INNER JOIN (tblItems
            INNER JOIN (tblPatients 
                INNER JOIN tblAssessments
                    ON tblPatients.PatientKey = tblAssessments.Patient)
                ON tblItems.ItemKey = tblAssessments.ItemName)
            ON tblItemTypes.ItemTypeKey = tblItems.ItemType
    GROUP BY tblPatients.PatientID,
        tblPatients.[Patient Name],
        tblItemTypes.ItemType,
        tblItems.ItemName,
        tblItemTypes.ItemTypeKey,
        tblItems.ItemKey
    ORDER BY tblItemTypes.ItemTypeKey,
        tblItems.ItemKey
    PIVOT IIf(Month([AssessmentDate]) Between 1 And 3, "Q1",IIf(Month([AssessmentDate]) Between 4 And 6, "Q2",IIf(Month([AssessmentDate]) Between 7 And 9, "Q3", "Q4")));

Will yield the following results:

One of the keys is line 22 above, in that it converts the date into a Quarter.

I hope this is what you are looking for.....

[Hargo]

Thanks again twinnyfo

Here are my relationships -I don't have 4th table due to ItemType being potentially least significant piece of data - I wouldn't call it redundant because there may be a need to call it but it surely it doesn't warrant its own table just for that?

If it's essential for creating relationships I'll obvisouly build it in

Regards

Rob

[twinnyfo]

Your tables, as you have them designed, should still work for this exercise.

[zmbd]

What is, or is there, a relationship between tbl_HoNOS_Items and tbl_HoNOS_Assessments?

[twinnyfo]

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.

[Hargo]

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?

[Hargo]

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!!!

[twinnyfo]

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.ItemTypeKey is a Long Integer, Autonumber; tblItems.ItemType is also a Long Integer. The Field Name is merely "ItemType". I could have just as easily used "ItemTypeKey", 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.ItemTypeKey".

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 "accidentally" 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!

[Hargo]

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

[twinnyfo]

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.

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.

[zmbd]

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

[Hargo]

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"?

[twinnyfo]

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.

[Hargo]

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)

[twinnyfo]

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

[Hargo]

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

[twinnyfo]

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_Assessments 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_Assessments. In design view of your Table, you can delete the subdatasheet reference to tbl_HoNOS_Assessments, or change it to another table. In general, this will not affect how your DB functions.

Hope all this makes sense.

[Hargo]

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:

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)

[twinnyfo]

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:

Field            Description
============================
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.

[Hargo]

Hi twinnyfo

I have been trying to solve my crosstab query issue and I believe [Surname] is not recognized as valid because I do not have appropriate FROM / INNER JOIN code lines referencing the relationship between tbl_Patient_Information and tbl_Admissions OR an ON line referencing the linked fields (In this case NHS Number)

I would adopt a trial and error approach to add such lines if I had confidence that my underlying structure/relationships was sound

In the meantime I think I'll read an SQL reference manual to see if that helps

With regard to new thread, I'm still waiting on user feedback to ensure I / we are commencing with full knowledge of what is required

[Hargo]

Thanks, you have been MORE than helpful

I have added an ItemKey Field (Autonumber) and am confident new items would not be added to this assessment - can the ItemKey not be used for sorting in this instance? Not sure sorting even needed as table defaults to that order anyway, although obviously I don't know what you have planned for the scoring form yet

I have learned the hard way about NOT using spaces!!!

[twinnyfo]

Good morning, Hargo,

I've just been looking at your Surname issue. The real issue is that the Field "Patient ID" is coming from tabl_Admissions: Post #27, Attachment "Relationships".

tbl_Admissions should have an "AdmissionsID", not PatientID. For all intents and purposes, I find that NHSNumber is the real PatientID. tbl_HoNOS_Assessments should have an FK to tbl_Patient_Information.NHSNumber.

Concerning three of your other Tables (something for you to think about), do tbl_Cluster, tbl_Pathway and tbl_25_Hours refer to a specific Patient or to a specific Admission? There is a difference. If these tables refer to each time the patient is admitted, then these tabels are correctly related (I think they are based on how I think you have things set up). Again, because they have a PatientID, it makes one think that they are related to the patient and not the admission. Since the Admissions table has the PatientID, then each time we have an admission, and those three tables gather information, we are able to know who the patient is (I hope this makes sense).

I am working the Cross tabl for the tables appropriately related. and will post as soon as I can.

[twinnyfo]

Hargo,

Please see attached. Note the fixed Field Names.

Below is the SQL:

TRANSFORM Sum(tbl_HoNOS_Assessments.AssessmentRating) AS Rating 
SELECT tbl_Patient_Information.NHSNumber, 
    tbl_Patient_Information.Surname, 
    tbl_Patient_Information.Forename, 
    tbl_HoNOS_Item_Types.ItemType, 
    tbl_HoNOS_Items.ItemName 
FROM tbl_Patient_Information 
    INNER JOIN (tbl_HoNOS_Item_Types 
        INNER JOIN (tbl_HoNOS_Items 
            INNER JOIN tbl_HoNOS_Assessments 
                ON tbl_HoNOS_Items.ItemKey = tbl_HoNOS_Assessments.ItemName) 
            ON tbl_HoNOS_Item_Types.ItemTypeKey = tbl_HoNOS_Items.ItemType) 
        ON tbl_Patient_Information.PatientKey = tbl_HoNOS_Assessments.Patient 
GROUP BY tbl_Patient_Information.NHSNumber, 
    tbl_Patient_Information.Surname, 
    tbl_Patient_Information.Forename, 
    tbl_HoNOS_Item_Types.ItemType, 
    tbl_HoNOS_Items.ItemName, 
    tbl_HoNOS_Item_Types.ItemTypeKey, 
    tbl_HoNOS_Items.SortOrder 
ORDER BY tbl_HoNOS_Item_Types.ItemTypeKey, 
    tbl_HoNOS_Items.SortOrder
PIVOT IIf(Month([AssessmentDate]) Between 1 And 3,
    "Q1",
    IIf(Month([AssessmentDate]) Between 4 And 6,
        "Q2",
        IIf(Month([AssessmentDate]) Between 7 And 9,
            "Q3",
            "Q4")
        )
    );

This renders the Chart you desire.

[Hargo]

Thanks for showing query design view as well twinnyfo, it really helps.

I think this is where my relationships need to be clarified

I note you have used the NHS Number from tbl_Patient_Information and then linked this to tbl_HoNOS_Assessments using PatientKey(PK)


In my structure I have:

tbl_Patient_Information which uses [NHS Number] as PK since every individual in the UK has their own unique NHS Number issued to them

(This is a long integer so I thought it would make a good PK)

tbl_Admissions uses [NHS Number] as a foreign key so any patient can be admitted multiple times. Upon admittance patients are issued a unique Hospital ID which the hospital call "Patient ID" - As this is unique I thought it would make a good PK for tbl_Admissions.

I want/need HoNOS Assessments to be related to tbl_Admissions rather than tbl_Patient_Information as the assessment is relevant only to that Admission - if a patient is ever admitted again a new baseline and set of assessments would be recorded and the previous ones of little use.

The only data i relate to tbl_Patient-Information is tbl_Incidents since the hospital WOULD LIKE TO KNOW if a patient has proved to be violent during a previous admission

As I mentioned earlier I suspect the way tbl_Patient-Information and Tbl_Admissions are related may be the reason Surname wasn't being recognized

Does my explanation make sense AND justify why I need tbl_Admissions AND tbl_Patient_Information in my cross tab query?

[twinnyfo]

Hargo,

Your explanation was BRILLIANT in helping me understand your relationships. That being said, let me quickly make some updates and show you what I come up with.

I think, all in all, you have a good grasp of the concepts we are dealing with, and--because I am unfamiliar with how you use your data--we've had a few small miscommunications. But, none that will prove fatal to the process.

[twinnyfo]

Hargo,

I think we finally got it!

I fyou look at the relationships, the Assessments Table is not related properly to the Admissions Table. You will note that the Admissions Table has "NHSNumber", but it is related to the Patient Table via PatientKey. This is intentional, as the query I used for the Row Source draws on the PatientKEy AND the NHSNumber. The Bound column is the patientKey (which is hidden) and the NHS Number is displayed. We could just as easily have used the patient Name or any other piece of information from the patients Table. But, it is bound to the Key.

Thus, the SQL now looks like this:

TRANSFORM Sum(tbl_HoNOS_Assessments.AssessmentRating) AS Rating 
SELECT tbl_Patient_Information.NHSNumber, 
    tbl_Patient_Information.Surname, 
    tbl_Patient_Information.Forename, 
    tbl_HoNOS_Item_Types.ItemType, 
    tbl_HoNOS_Items.ItemName 
FROM tbl_HoNOS_Item_Types INNER JOIN 
    (tbl_HoNOS_Items INNER JOIN 
        (tbl_Patient_Information INNER JOIN 
            (tbl_Admissions INNER JOIN 
                tbl_HoNOS_Assessments ON 
                    tbl_Admissions.PatientID = tbl_HoNOS_Assessments.Patient) ON 
                tbl_Patient_Information.PatientKey = tbl_Admissions.NHSNumber) ON 
            tbl_HoNOS_Items.ItemKey = tbl_HoNOS_Assessments.ItemName) ON 
        tbl_HoNOS_Item_Types.ItemTypeKey = tbl_HoNOS_Items.ItemType 
GROUP BY tbl_Patient_Information.NHSNumber, 
    tbl_Patient_Information.Surname, 
    tbl_Patient_Information.Forename, 
    tbl_HoNOS_Item_Types.ItemType, 
    tbl_HoNOS_Items.ItemName, 
    tbl_HoNOS_Item_Types.ItemTypeKey, 
    tbl_HoNOS_Items.SortOrder 
ORDER BY tbl_HoNOS_Item_Types.ItemTypeKey, 
    tbl_HoNOS_Items.SortOrder 
PIVOT IIf(Month([AssessmentDate]) Between 1 And 3,
    "Q1",
    IIf(Month([AssessmentDate]) Between 4 And 6,
        "Q2",
        IIf(Month([AssessmentDate]) Between 7 And 9,
            "Q3",
            "Q4")
        )
    );

Whew!

[Hargo]

Thanks twinnyfo

My only question is why does tbl_Patient_Information need a PatientKey (PK) when NHS Number is already an unique long integer? Why can't NHS Number in tbl_Patient_Information be the PK and NHS Number in tbl_admissions be the FK? What am I missing? Isn't the PatientKey redundant data?

Hargo

NB Still awaiting an answer so we can commence a new thread

[twinnyfo]

The PK could be the NHS Number if you wish. It is not required that it have it's own unique identifier. This is purely your choice. But, here is the theory:

Think about your patients. One of the things that identified the patient is the NHS Number, along with their name, address, etc.

Now, think about records. A record captures information about the patient. This information includes the NHS Number and the name, etc. The PK, merely identifies "the record" that holds all that information.

Now, based on how your DB is set up, you will probably not have any problems with this. I have a set up in which I have a bunch of military officers (who are identified in the US by their Social Security Number [SSN]). I could use the SSN as a unique identifier for my officers meeting a promotion board. However, sometimes I have officers meeting two types of boards at the same time. That would put two of the same SSNs in the same table (oops!).

So, I put a PK in that table that merely identifies the Record. However, when I do this, based on that index, I can also determine which board the officer is meeting, etc., etc., etc.

Again, I don't think you will ever run into that problem in this particular case. It is just a good practice to have a separate, distinct, unique identifier for each record. Otherwise, one might argue that I have a table with names, and I could just set many fields as PKs, becaues there is only one "Sherlock" "Holmes" living at "22B Baker" "Street" "London". While that is an absurb extension, the logic does hold true.

To emphasize, we only make recommendations. If you so choose to completely redesign your DB and want it to spit out wooden nickels, we will help you work through that. :-)

Either way, if you design your cross tab query in the query design view, you should have no problems.

[Hargo]

I will give it a whirl with NHS Number as the PK - I think we learn more from our mistakes in any case, so won't be for nothing if it doesn't work and NHS Number does fulfill the PK field functions IMO

One final point regarding the crosstab query - the baseline measure can/will have the same date as [Admission Date] in tbl_Admissions - is it straightforward adding this condition to the PIVOT IIF statement?

I'm thinking:

PIVOT IIf(Month([Assessment Date]) = tbl_Admisisons.[Admission Date], "Baseline", 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")));

Hospital Year Starts April so Q1 = Months 4 - 6

I'm concerned that I am comparing a Month() date with a standard Short date in my code above so would appreciate your thoughts

I will be posting new thread before I leave for the day (next 30 minutes) but am conscious I have taken enough of your time for one day so please don't rush your reply as I won't be in till 08:00 GMT


Regards


Hargo

[twinnyfo]

Hargo,

I will give it a whirl with NHS Number as the PK - I think we learn more from our mistakes in any case, so won't be for nothing if it doesn't work and NHS Number does fulfill the PK field functions IMO

I got my cross tab to work like this. And, yes, NHSNumber does fulfill the functions of a PK.

Concerning the Dates, as long as you set the data type and date format identically in both tables, you should be fine comparing apples to apples. You also make an assumption that the First Assessment (baseline) is conducted in the same month as the admission. What if a patient is admitted on the last day of the month, but the assessment does not occur until the next day? Two possible fixes are 1) to simpy "pre-date" the assessment to the Admission Date, so if the two dates are equal, it is the Baseline; or 2) add another field to the Assessments table indicating a baseline.

Just some thoughts on this....

We have been considering Quarters, and as it relates to baselines, we could have a patient that is admitted in the 3rd Quarter. Then, after their third assessment (upon admission, three months later, then three months after that), the chart created by the Cross Tab would have the following headings: Baseline, Q1, Q4 (in that order). Would you rather have headings that are simply dates? This way they would be sequential. Or, we can change the format of the Quarters, such that they read "2014 Baseline", "2014 Q4", "2015 Q1"). Just some thoughts to make it clear for those reviewing the data.......

[Hargo]

Hi twinnyfo

I was concerned that I was comparing 'cooking' apples with 'eating' apples

The Baseline Assessment will be entered as the Admission date, so is it advisable/possible to 'remove' the month element from the initial clause?

IIF([Assessment Date] = [Admission Date], "Baseline", IIF(Month([Assessment Date]) Between 1 And 3, "Q4" etc. etc.

but add it back in to subsequent parts of the clause as above?

I am concerned that 2 conditions will ALWAYS be true for the baseline assessment - i.e. it will meet the matching of dates to get the "Baseline" BUT also be in a month to get "Q1", "Q2" etc.

With regard to your 'Quarters' scenario, I don't understand why the chart headings would read Baseline, Q1, Q4 if they were admitted in Q3? The Q3 reading would be the baseline, 3 months later the reading would be a Q4 one and then Q1 three months later - Chart should show Baseline, Q4, Q1 etc which to my mind is perfectly acceptable - I do like your idea of years though. The average admission duration is 2 years so showing 8 readings would be an improvement upon existing spreadsheet's 4 reading limit.

Thanks again for all your guidance on this

[twinnyfo]

Hargo,

With regard to your 'Quarters' scenario, I don't understand why the chart headings would read Baseline, Q1, Q4 if they were admitted in Q3?

A cross tab query lists it columns in order, alphabetically. So, if we have three fields returned (three columns) named "Baseline" (the initial assessment), "Q4" (the 'second' assessment) and "Q1" (the 'third' assessment), the Query will list these columns in alphabetical order from left to right.

However, your post also got me thinking about presentation of this data, in either a Form or Report....

As you know, Forms and Reports use a Record Source from which they display their data. Each control on those Forms/Reports must have a corresponding Control Source (a "Field" from a Table of Query. I've worked with Cross Tab queries before, using them as a Record Source, and it can get tricky--because sometimes you will not have data that results in one of hte Cross Tab columns being returned. This will cause an error on your Form/Report.

Additionally, as you mention, a typical stay lasts 2 years, resulting in 8 readings. Will there ever be more? Will there ever be fewer? Along these lines, then, we must consider how to generate a list of data that satisfies the requirements of a Record Source for a Form//Report--My sincerest apologies for overlooking this in the first place.

Along the same lines, should your result data return cloumns with the following field names: "Baseline", "Q1" (the first assessment, three months after admission, but irrespective of the actual date), "Q2", "Q3", ..., "Q8" (which is actually at the 2-year point)? Then, we would always return those nine fields--even if they were blank.

Do you see where I am going with this? Reports and Forms don't like non-existent fields, and trying to create a truly dynamic report (one which resets the control sources for its controls based on the data in its record source) is a bit beyond my capabilities, and I'm not sure this is the direction you need to go.

I've got some time today, and I'll take a look at the mock-up I have of your DB and see if I can figure anything out. Concerning the input of the data (your other, new thread), that will not change, and I will re-post my thoughts on that, since for some reason the mods deleted your original thread and my response.

Let me know your thoughts on the above.

[Hargo]

Hi twinnyfo

I now understand the first part about sequence in the presentation due to Cross Tab listing constraints

I had always assumed the query would be used as record source for Form or Report (not sure which) but now I'm not so sure it's absolutely essential (though it would be great).

Here are my thoughts:

2 Years is an average but though I suspect the standard deviation would be small I will check

I don't think empty columns is a problem - I doubt it would be worth running the report/form unless 3-4 quarters were complete so it would 'always' be half full 'when seen' anyway

How would changing the names to "1st" "2nd" etc. affect sequencing?

Not sure why original thread deleted either (multiple questions was mentioned but I cannot remember all of the thread) but I have made some progress which I will detail in that thread later - thank you

[twinnyfo]

Our "desire" is to create a query that returns standard fields names--every time, whether there is data or not--so that it can be used as a record source. I also noticed that you wanted a chart--which will also require a standardized record source.

This is possible. I just have to look at the data.

[Hargo]

Not sure what you mean by look at the data, there is no data as yet - not in my DB anyway

Does it help at all if I speak to users about changing from "Qtrs" to something like "1st Assessment" etc. so sequence will always be 1st, 2nd etc regardless of date - might need to look at using datediff and comparing it with 90, 180, 270 days etc

What do you think?

[twinnyfo]

"Looking at the data" -- I have dummy data (and I hope you have some in your db, too). I need to figure out how to get it to display properly.

Yes, if the assessments were simply 1st, 2nd, (whatever the convention) it would make things easier. W could generate a list of assessments, based on date, and list them sequentially.

I think using DateDiff(), which is a good idea, might run into problems if the assessments are not perfectly spaced. Thus, getting a list of dates and using those dates as a basis for our ordering.

More to follow....

[Hargo]

I enter occasional dummy data to meet referential integrity rules and to see how results come out but that's it

I was thinking something along the lines of

IF([Assessment Date] = [Admission date], "Baseline",IF([Assessment Date] - [Admission Date] < 100, "1st Assessment",IF([Assessment Date] - [Admission Date] < 190, "2nd Assessment" etc. etc.

I would need to get these numbers agreed but what do you think?

[twinnyfo]

The initial concept is good. However, envisioning up to 8 assessments and this becomes unwieldy--as accurate and effective as it may be.

My concept is to generate a list of assessment dates, evaluating the dates on the Admission date (which would give us the Baseline) and then numbering sequentially from there. This would allow for all possibilities, to include assessments inside/outside of the "perfect world" parameters.

Then, once we generate the list of dates/assessments, we join that query with all the data in the Assessments Table. It sounds confusing and convoluted right now, but it will make sense when you see, if I can get a visual for you....

[twinnyfo]

Take a look at this:

Is this something that would work? To get there, it is a little involved, but I think you can handle the overall concept.

The Query above looked at a patient with five assessments. The first is listed as the baseline, the assessment three months later as "1st", etc.

The one shortfall, with this query is that the actual assessment dates are not shown, but that can easily be solved by adding them in additional columns. If such a record set would be useful for you, let me know and I can describe how to get there.