Having a problem with a totals query

[LOUISBUHAGIAR54]

Dear All,

I am having a problem with creating a totals query. I have a table called 'Particulars' which containes a number of fields that contains details on patients. This includes their names and date of discharge from hospital. I would like to join this with a query called BarthelQuery. The latter contains details from another table called Barthel. This contains details of patient dependancy levels and a date when the assessment of the dependency levels was carried out. Assessments are carried out a three montly intervals. BarthelQuery works out an index of dependency based on values in the table Barthel.

I want to create a second query, which i think should be a totals query. The latter is needed to extract the LATEST index for each individual patient. I am having problems doing this as when i extract the latest date using Max value on the date field the dependency index shown is not the same index on that date.

Can someone please shed some light on this problem ?

 

[bob fitz]

Do you have a relationship between the two tables?
It sounds to me a though there should a One to Many relationship using the primary key of 'Particulars' as the foreign key in Barthel.

 

[LOUISBUHAGIAR54]

I have just established a relationship between table 'Particulars' and table 'Barthel'. It is a one-to-many relationship. As a side note the infinity sign on the relationship does not appear.

When I execute the query it comes up with the Max date from BarthelQuery for each patient's entries, but the corresponding 'Index' from BarthelQuery is not from the same record as the Max date.

I presume one has to use two queries in sequence. But even in this way I am having problems.

Here is the sql of the query which I am trying. (meta is a date field)

SELECT First(Particulars.[First Name]) AS [FirstOfFirst Name], First(Particulars.[Second Name]) AS [FirstOfSecond Name], Particulars.[idcard no], Max(BarthelQuery.Meta) AS MaxOfMeta, First(BarthelQuery.Index) AS FirstOfIndex
FROM Particulars INNER JOIN BarthelQuery ON Particulars.[idcard no] = BarthelQuery.idcardno
WHERE (((Particulars.[Date of discharge]) Is Null) AND ((Particulars.Referral)="government"))
GROUP BY Particulars.[idcard no]
ORDER BY First(BarthelQuery.Index);

 

[bob fitz]

Can you attach a copy of your db in mdb (A2002/3) format

 

[LOUISBUHAGIAR54]

Dear Bob,

Many thanks for your reply and your help in finding out the solution to this problem. In the meantime I have solved the problem by first doing a totals query. This is intended to find the max ID (ID is the auto-generated record number). As dates are added with time in chronological order this will also represent the max date for each patient.

The SQL is as follows.

SELECT BarthelQuery.idcardno, Max(BarthelQuery.ID) AS MaxOfID
FROM BarthelQuery
GROUP BY BarthelQuery.idcardno;

I then linked this query to BarthelQuery using the record ID as the link on both queries. The table Particulars is added to find the appropriate patient based on the idcard no.

The SQL of this is

SELECT Query30.idcardno, Query30.MaxOfID, BarthelQuery.Index, BarthelQuery.Meta, Particulars.[Second Name], Particulars.[First Name]
FROM Particulars INNER JOIN (Query30 INNER JOIN BarthelQuery ON Query30.MaxOfID = BarthelQuery.ID) ON Particulars.[idcard no] = BarthelQuery.idcardno
WHERE (((Particulars.Referral)="government") AND ((Particulars.[Date of discharge]) Is Null))
ORDER BY BarthelQuery.Index;

This appears to be working quite well now. Should you think this could have been dealt with more efficiently I would appreciate learning.

Many thanks again for your help.

Louis Buhagiar

 

[LOUISBUHAGIAR54]

Dear Bob,

Many thanks for your interest in helping out. I have managed to solve the problem by creating two queries. The first is called Query 30. It is totals query and uses two fields from BarthelQuery. The following is the SQL.


SELECT BarthelQuery.idcardno, Max(BarthelQuery.ID) AS MaxOfID
FROM BarthelQuery
GROUP BY BarthelQuery.idcardno;

The entries in table Barthel are entered in chronological order. As such the Max of ID which is the auto-generated record number will correspond to the Max (met) which is the date of the entry.

This query (30) is then used to make another query called Last_Barthel_query which links the Max(ID) of query 30 to ID of BarthelQuery. The latter is linked to table Particulars on their idcard no.

The SQL of this query is the following

SELECT Query30.idcardno, Query30.MaxOfID, BarthelQuery.Index, BarthelQuery.Meta, Particulars.[Second Name], Particulars.[First Name]
FROM Particulars INNER JOIN (Query30 INNER JOIN BarthelQuery ON Query30.MaxOfID=BarthelQuery.ID) ON Particulars.[idcard no]=BarthelQuery.idcardno
WHERE (((Particulars.Referral)="government") AND ((Particulars.[Date of discharge]) Is Null))
ORDER BY BarthelQuery.Index;



The query is working well now.

Should you have a more efficient idea, please let me know as I am a novice to ACCESS and would like to keep learning.

Many thanks again.

Louis Buhagiar

 

[bob fitz]

I'm not sure that I've actually been able to help much, but I'm glad that it is working now. Good luck with your project and remember that there is always someone who will help if you need it.