Cannot create this query ?

[LOUISBUHAGIAR54]

BarthelIndex is a table containing a number of numeric fields, a date field and two text fields. The date field contains the date on which an assessment of dependency is made. One text fields contains an id number of an elderly resident, and the other text field contains the name of the assessor. The number fields contain a number from 0 to 3 depending on the level of ability of various functions of daily living.

Each assessment of dependency needs to be made regularly every 100 days.

BarthelQuery is a query which basically has a field which sums the values in the numeric fields to give an index of dependency.

A second table is called "Particulars" and has the name, surname and idcardno of the elderly resident. In this table, field [idcardno] is the primary field.

I would like to have a query based on BarthelQuery and table Particulars so that i can use this to create a report. The report will show the name, surname, idcardno, date of last assessment, assessor, and BarthelIndex of all those residents due to have an assessment as the 100 days from the last assessment have elapsed.

I know I will need to have a totals query, but I am having problems and the method to use appears confusing. Can I please have some help in this ?


LouisB

 

[plog]

I'm having a hard time visualizing your data--could you provide some sample data along with what you want your report to show based on that sample data?

Most likely this is going to involve a sub-query to determine each person's last visit. Then, you would take that and use it in a another query using BartheIndex to find all the data for that vist.

 

[LOUISBUHAGIAR54]

Is it possible to upload the structure of the table "BarthelIndex" and the design of the report in question ? If this is possible then I would like some assistance to do this ?

I have tried to do this and agree with you that this is the correct way to solve this problem.

LouisB

 

[plog]

You should be able to upload your database, use the 'Advanced' posting option when replying to this and underneath will be options for uploading a file.

 

[LOUISBUHAGIAR54]

I have tried uploading the whole database before. It is too big and wont upload. In addition for confidentiality's sake it is not advisable to do so. Is it possible to upload just the tables and the design of the report ?

Many thanks again.


LouisB

 

[plog]

You could strip out all the objects other than the ones you need. Or simply post some sample data in this format:

TableName
FirstName, LastName, DOB
John, Smith, 1/1/1985
Laura, Jones, 2/2/1986

 

[LOUISBUHAGIAR54]

You could strip out all the objects other than the ones you need. Or simply post some sample data in this format:

TableName
FirstName, LastName, DOB
John, Smith, 1/1/1985
Laura, Jones, 2/2/1986

Basically I have a query called BarthelQuery based on table BarthelIndex. I would like to make a subquery based on BarthelQuery which will be a totals query. I want to group on idcardno. This will group on the idcard of residents. Then I would like to choose the maximum on the date field so that I focus on the last date of assessement. The unique number, and primary field of BarthelIndex is the ID autonumber. I would like on the totals query to pin point on the ID number of the record with the maximum date so that then I will pick up various fields from that particular record.What I would need guidance on is how to pin-point on the ID number of the record with the maximum date for that particular resident. Many thanks.LouisB

 

[plog]

I assume since you quoted my request for sample data in your response to it, that you read my request. However, your last post simply restated what you initially post was about without the requested sample data. Allow me to iterate my initial request for sample data:

I'm having a hard time visualizing your data--could you provide some sample data along with what you want your report to show based on that sample data?

Most likely this is going to involve a sub-query to determine each person's last visit. Then, you would take that and use it in a another query using BartheIndex to find all the data for that vist.

 

[LOUISBUHAGIAR54]

Dear plog,




Thanks for your interest.


BarthelIndex is a table with the following structure:


Field Name Date Type

Idcardno Text
Bowels Number (0,1,2)
Bladder Number (0,1,2)
Grooming Number (0,1)
Toilet_Use Number (0,1,2)
Feeding Number (0,1,2)
Transfers Number (0,1,2,3)
Mobility Number (0,1,2,3)
Dressing Number (0,1,2)
Stairs Number (0,1,2)
Bathing Number (0,1,2)
Meta Date/Time
ID Autonumber
Assessor Text
WeightKg Number

The primary field is the record ID which is an autonumber. Meta is the date when an assessment is made. Fields 2-11 describe an ‘activity of daily living’ ADL and are graded as shown; a lower grade equates with more dependency.

Particulars is another table with many fields describing patient characteristics. The following is part of the table structure that will concern us for our purpose:

Field Name Date Type

First_Name Text
Second_Name Text
Idcard_No Text
Date_of_Discharge Date/Time

The primary field is Idcard_No.

BarthelQuery is a query which is totally based on table BarthelIndex and contains a field called ‘Index’. This field summates the numeric values in fields 2-11. This sums up to a global measure of dependancy for that particular resident on that particular date. The maximum number possible in this field is therefore 20 and the least grade possible is 0. .

An assessment is made every 100 days, and on every new admission to the residential home. I would like a query which gives a list with the following fields;

First_Name, Second_Name, Idcard_No, last_date of assessement, Index, Assessor, for those residents whose last assessment was carried out more than 100 days before the current date. This list will only include residents who are at the retirement home and therefore whose Date_of_Dischage is Null. This list I would also like to include new admissions who do not yet have any previous assessment.

I presume that one will have to have a totals query based on BarthelQuery. What I have done is a totals query which groups on idcardNo. If then I choose Max on meta I will then need to have the specific ID (autonumber) for that particular record with the latest [meta]; and I do not know how to do that.

Otherwise I can group on idcardNo and choose to Max on ID (autonumber). This approximates to the latest date as entries are done sequentialy: however this not exactly what is accurate. Then I can link this subquery again on BartelQuery linking on the ID field in order to be able to collect [meta], [Assessor], and [Index] for that particular, latest date of assessment.

In the same query I would then link table Particulars to the totals query on IdcardNo. and put Null as criteria for Date_of_Discharge. The type of join here is such that new residents who do not have entries in table BarthelIndex will appear along those whose last assessment is more than 100 days expired. This is what I envisage should be done. However I am having difficulties.


Many thanks for your patience and for any assistance forthcoming.


LouisB