Struggling creating a version of this Excel sheet in Access form

[twinnyfo]

Can it not be entered manually and an index? My reason for not adding an additional index for this table is that I have an OpenArgs which populates the NHSNumber control in frmAdmissions from frmPatient_Info rmation and I didn't want to lose it

Whenever you are using an index (or PK from another table) you want to eliminate all possibility for fat-fingering the value.

Again, based on your preferences, you wanted to keep the NHS number as your PK. That's fine and it will "work". However, even though you have it set as a Long Integer, it is always numerical characters and you have a proper inputmask/format set for that field, whenever you introduce spaces into a numerical value, you introduce potential problems with how the DB looks at the data. In most cases, there are never any problems--and since we are at the end of the road here, in terms of time, there is no point in changing anything right now.

At the same time, this is also why I cringe at the idea of having a user-entered SG number--as you saw, the possibility of duplicate entries now exists. Again, no point in changing right now, you just need to make sure that users now how important it is to be accurate with their SG numbers.

BTW, not sure how you would lose the NHSNumber?

In a well-designed Admission Form, you would have a main form that selects the Patient (by NHSNumber). This populates the form with all the information associated with the patient, to make sure it is the correct person. Then, you click "Admit Patient" and a subform, based on tblAdmissions appears. Since it is related to tblPatientInfor mation, and the Master/Child relationship between the main form and subform is based on NHSNumber, that value is automatically included in your update of hte Admission information. No need for Open Args or anything else like that. I, myself, was personally amazed at how incredibly simple such a setup really was--I almost don't have to think about building my forms in this way....

Again, just some good stuff to know for the future.

[Hargo]

All good stuff to know, even if I only get it basically from what we have done earlier

Whenever you are using an index (or PK from another table) you want to eliminate all possibility for fat-fingering the value.

I assumed using the Open Args would remove this problem - The NHS Number MUST be entered manually once in frm_patient_Inf ormation but that's it

Had I known what I now know I would probably have Used an Index and a lookup as with PatientID but alas time is running out

As NHSNumber not involved in HoNOS Assessments / Queries is this likely to be stopping the Ranking work or have you spotted another flaw in my tables/queries that is to blame?

[twinnyfo]

At this point, without having my grubby little fingers on your actual data and able to play with it, I have no clue why your data does not produce the results we want it to. Especially the rankings coming out at "0". The only thing I can figure is that the DCount is not finding at results--ever.

Still looking at your query to diagnose.....

[twinnyfo]

Here's a really whacked idea. Change your qry_AssessmentD ates to the following:

SELECT [tbl_HoNOS_Assessments].[PatientID]+1-1 AS PatientID, tbl_HoNOS_Assessments.AssessmentDate
FROM tbl_HoNOS_Assessments
GROUP BY [tbl_HoNOS_Assessments].[PatientID]+1-1, tbl_HoNOS_Assessments.AssessmentDate
ORDER BY [tbl_HoNOS_Assessments].[PatientID]+1-1, tbl_HoNOS_Assessments.AssessmentDate;

[IMGNOTHUMB]http://bytes.com/attachment.php? attachmentid=79 36[/IMGNOTHUMB]

If your query is correct, you will get the index, and not the referenced SGNumber. Otherwise, you will get a type mismatch error.

Let me know what happens.

[Hargo]

I got the index I think

Here's screen shot attached for you to check


Does this mean my query is right?

[Hargo]

Here's the impact upon the Order Query if it helps

[twinnyfo]

Run your qry_Assessment_ Dates, so you can see all your values.

Now, in the Immediate Window, type the following:

?DCount("[PatientID]", "qry_Assessment_Dates", "[PatientID] = " & [Enter One of your Patient Indexes (1-10)] & " AND AssessmentDate < #" & [Enter one of the Assessment Dates that YOu know exists] & "#")

Do this several times--you should know what you want the values to be, because you can see them in front of you.

Tell me if you ever get a correct response.

If not, run this in hte Immediate Window:

?DCount("[PatientID]", "qry_Assessment_Dates")

You should get 10, if not--something very wrong.

If 10, then:

?DCount("[PatientID]", "qry_Assessment_Dates", "[PatientID] = " & [Enter One of your Patient Indexes (1-10)])

See how I am working the troubleshooting here? We're trying to find out at which point the Query is breaking down.

The simple fact that you are getting 0's for patients 1 and 2 tells me something somewhere is not right. But, the only Patient for which we are getting the proper number is 10.

How many total assessments do you have in tblAssessments (groups of 19)? According to qry_Assessment_ Dates, you should have 190 total records in the Assessments Table, Correct?

[Hargo]

I do have 190 records in HoNOS Assessments, Yes


Problem with script appears to be with the use of Parameter which I think is being viewed as a field name (? guessing here)

I removed the parameters and ran the script with each of the Patient Indexes and it returned all the right answers

I then thought I'd see what the script does with different dates in HoNOS (Attached) - interestingly more of them are now ranked appropriately - not sure what that actually tells us, so

Over to you Obi Wan :)

[Hargo]

Been adding and deleting HoNOS all morning and I think I have a discovery!! I think it's a US v UK date system issue

I have entered all the admission dates as UK dates i.e. 5/2/14 = 5th February rather than May 2nd so baseline readings are picking these dates

When it comes to the Follow up, I noticed as they all go in with today's date that 3rd October is showing as 10/3/14 where I would use 3/10/14

Bit of a long shot and doesn't explain why ALL baselines for each patient were not ranked = 0 but it's a start perhaps?

I have attached a visual for you to contemplate :-)

On a separate issue, can you think of any reason why fSub would randomly choose to appear or not when clicking baseline yet always appear when clicking FollowUP? (Thought shifting the date element of the script had solved this!!)

Now this is crazy, but the only time that the ranking system (AND fSub!!!!) work is when the date is NOT able to be understood in both US and UK systems 28/2/14, 30/4/14, 19/5/14 & 26/6/14

Dates that can be read in both systems e.g. 10/3/14, 3/4/14, 6/5/14 etc all have ranking problems AND fSub didn't appear!!! Conspiracy theory time haha

[twinnyfo]

Hargo,

To avoid confusion in my Date entry fields, I use the following input mask and format:

Input Mask:  00\->L<LL\-00;0;_
Format:      dd-mmm-yy

That way there is no confusion (US or UK--OR with the Database). I was hypothesizing whether it might have something to do with the date format....

You UK folks drive on the wrong side of the road AND write your dates backwards!

:-) (just pokin' fun).

This might bring us closer to solution....

[Hargo]

All my date fields in tables are formatted dd/mm/yy so not sure how tbl-HoNOS_Assessmen ts is taking today's date 3/10/14 (which is visible in the Assessment Date control in frmAddAssessmen t) and turning it into 10/3/14 (Yankee style!!)


Is it just the tables that need amending then?

[Hargo]

Format and input mask on all tables

Just added another set of 19 using frmAddAssessmen t and the dates have all come up as 10/3/14 again in tbl_HoNOS_Asses sments!! What is overriding the formatting of this field?!!?!?

[twinnyfo]

the text box must have the same formatting....

[twinnyfo]

I just noticed, you said it was the Table that had the wrong format.....

It could have something to do with your Windows Regional Settings, but it sounds like Windows is defaulting to UK-style...

[Hargo]

If Windows was defaulting to UK style why would tbl_HoNOS_Asses sments have a US style date for today's date - is it significant it's just on the FollowUp (i.e. today's date taken from your code?)