Struggling creating a version of this Excel sheet in Access form

[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_Inf ormation 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 "Admissions ID", not PatientID. For all intents and purposes, I find that NHSNumber is the real PatientID. tbl_HoNOS_Asses sments should have an FK to tbl_Patient_Inf ormation.NHSNum ber.

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_Inf ormation and then linked this to tbl_HoNOS_Asses sments using PatientKey(PK)


In my structure I have:

tbl_Patient_Inf ormation 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_Inf ormation 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_Inf ormation 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 miscommunicatio ns. 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_Inf ormation need a PatientKey (PK) when NHS Number is already an unique long integer? Why can't NHS Number in tbl_Patient_Inf ormation 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.