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:

[IMGNOTHUMB]http://bytes.com/attachment.php? attachmentid=78 70&d=141105236 5[/IMGNOTHUMB]

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:

[IMGNOTHUMB]http://bytes.com/attachment.php? attachmentid=78 71[/IMGNOTHUMB]

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

[imgnothumb]http://bytes.com/attachment.php? attachmentid=78 72[/imgnothumb]

[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_Asses sments?