Only Show records that appear 4 times or more in a column within 24 hours (1 Viewer)

Dissander

Registered User.
Local time
Today, 08:05
Joined
Mar 22, 2017
Messages
29
Dear all,
I have a database on blood transfusion. Every Blood sample of patient is allocated a unique lab number. Should they need blood transfusion, all units transfused during the validity of the sample (usually for 3 days), will be under the lab number. Some patients get transfusion other don't.
I would like to create a query that only shows records of patients that have received more that 4 units of blood within 24 hours.
Every time we issue blood to patient X (during validity period), the lab number appears on my database.
Essentially, I'd like to show records with lab number that appear 4 times or more.
e.g
LabNumber DateRequired patientID BloodProduct BloodProductId
1234 21/05/2017 MR X RedCell 111111
1234 21/05/2017 MR X RedCell 111112
1234 21/05/2017 MR X RedCell 111113
1234 21/05/2017 MR X RedCell 111114
1235 22/05/2017 MR X RedCell 111999
1236 25/05/2017 MR Y Platelets 111456
1237 26/05/2017 MR O - -
1256 29/05/2017 MR X RedCell 1565656
1256 29/05/2017 MR X RedCell 1565657
Many thanks,
Much appreciate.
 

isladogs

MVP / VIP
Local time
Today, 15:05
Joined
Jan 14, 2017
Messages
18,186
When you say within 24 hours, am I right in assuming that 11am on one day and 10am the next day would therefore count?
The reason for asking it that you aren't recording the times.
 

Dissander

Registered User.
Local time
Today, 08:05
Joined
Mar 22, 2017
Messages
29
That's correct. Thank you, Ridders. Much appreciated.
 

isladogs

MVP / VIP
Local time
Today, 15:05
Joined
Jan 14, 2017
Messages
18,186
The explanation will depend on whether the time is included in the date field or as a separate field. Easier if both in same field

Either way will involve counting the number of records within a date range using an aggregate query.
 

Dissander

Registered User.
Local time
Today, 08:05
Joined
Mar 22, 2017
Messages
29
Sorry, i should have mentioned, yes indeed, the time is also included with the date.
LabNumber DateRequired patientID BloodProduct BloodProductId
1234 21/05/2017 10:50 MR X RedCell 111111
1234 21/05/2017 11:00 MR X RedCell 111112
1234 21/05/2017 12:00 MR X RedCell 111113
1234 21/05/2017 13:00 MR X RedCell 111114
1235 22/05/2017 05:00 MR X RedCell 111999
1236 25/05/2017 17:00 MR Y Platelets 111456
1237 26/05/2017 14:09 MR O - -
1256 29/05/2017 11:06 MR X RedCell 1565656
1256 29/05/2017 23:00 MR X RedCell 1565657

Thank you.
 

isladogs

MVP / VIP
Local time
Today, 15:05
Joined
Jan 14, 2017
Messages
18,186
Ok sorry I've read post 1 again and have more questions
It all seemed clear at first but not now
Maybe because its late here....

1. Query to show with >4 units or >=4. Which?
2. Can those units come from more than 1 lab?
3. Does it count if different blood products are given?
4. Which fields should be shown in the query results?

Also what's the name of the table?

I'll have a look tomorrow unless someone else answers in the meantime
 

Dissander

Registered User.
Local time
Today, 08:05
Joined
Mar 22, 2017
Messages
29
1. Query to show with >4 units or >=4. Which? >=4 (starting with 4 units and more than 4)
2. Can those units come from more than 1 lab? just 1 lab
3. Does it count if different blood products are given? - In the column of products, I have RedCell, platelets, Plasma & Cryo. I am more interested in Redcell (patient who received 4 or more units of RedCell within 24 hours), but I was also hoping to repeat the Query (copy & paste) with individual blood product as criteria.
4. Which fields should be shown in the query results?
labNumber, DateRequired, PatientID, Product (RedCell)
I really appreciate your time and support.
Thank you
 

isladogs

MVP / VIP
Local time
Today, 15:05
Joined
Jan 14, 2017
Messages
18,186
Hi

Attached is a sample db with your data plus a few more records that I added to test it.
I've left in all the queries to show you the steps I used to build up the final query qryFINALCount4OrMoreIn24Hours.
The tricky part was the 'within 24 hours'

This is the data I used:


These are the query results:


Hopefully that's what you want
Miss G isn't listed as her 4 transfusions took place in just over 24 hours

It would almost certainly be worth doing this as a function instead.
I haven't done that as you wanted a query.

Now I'm going to throw a spanner in the works.
Lets say Miss G has a 5th transfusion from the same lab at 13:00 on 31/05/2017.
That means her 2nd to 5th transfusions took place in less than 24 hours.
This adds a whole new level of complexity.
The above query won't include her but I imagine you would want to do so...

HTH
 

Attachments

  • ExampleRecords.PNG
    ExampleRecords.PNG
    40.7 KB · Views: 451
  • QueryResults.PNG
    QueryResults.PNG
    12.6 KB · Views: 443
  • BloodProducts.accdb
    504 KB · Views: 73
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:05
Joined
Jan 20, 2009
Messages
12,849
It would almost certainly be worth doing this as a function instead.

I doubt it. Functions work where the record has no relationship to the other records which is certainly not the case here. An single aggregate query with a self join can process all records simultaneously.

Join the table to another instance of itself on the fields that are common (PatientID, LabNumber?) and the datetime on the second instance as up to 24 hours later than the first. Take care with that datetime condition. Avoid DateDiff so the engine is not having to process all records in the second instance before selecting those to be joined. Add 1 to the date time in the first instance and test for records from the second instance that are Between the first date and the first date + 1. This will use the index on the field to select for the join.

Group By the datetime in the first instance of the table, count the records and return those HAVING a count of 4 or more. This will return a record for every time where a transfusion has been followed by another three within the 24 hours so it will make near duplicates that overlap where more than four transfusion have occurred.

Put a WHERE clause on the first date field to avoid processing the entire table. Be sure it is a WHERE rather than a HAVING or the whole table will be processed for the join, grouped then only the range you asked for returned. WHERE is applied first and will avoid those outside the range being processed.


Now I'm going to throw a spanner in the works.
Lets say Miss G has a 5th transfusion from the same lab at 13:00 on 31/05/2017.
That means her 2nd to 5th transfusions took place in less than 24 hours.
This adds a whole new level of complexity.
The above query won't include her but I imagine you would want to do so...
The query I described above handles this intrinsically.
 

Dissander

Registered User.
Local time
Today, 08:05
Joined
Mar 22, 2017
Messages
29
Dear Ridders, you are genius. I followed your example and it worked. I can't believe I got the results so quickly,- results that would have taken me months to work out. Thank you very much.
 

isladogs

MVP / VIP
Local time
Today, 15:05
Joined
Jan 14, 2017
Messages
18,186
You're welcome.

If my spanner in the works is an issue, try galaxiom's suggestion.
I haven't tested it but I'm sure it will work.
 

Dissander

Registered User.
Local time
Today, 08:05
Joined
Mar 22, 2017
Messages
29
Thank you Galaxiom. I am also trying your suggestion. My knowledge of access is very very basic, but I want to learn. So I will definitely give it a go now. Much appreciated. You guys are amazing.
 

Dissander

Registered User.
Local time
Today, 08:05
Joined
Mar 22, 2017
Messages
29
Galaxiom, I am sorry, I do not understand this part : the datetime on the second instance as up to 24 hours later than the first. Take care with that datetime condition. Avoid DateDiff so the engine is not having to process all records in the second instance before selecting those to be joined. Add 1 to the date time in the first instance and test for records from the second instance that are Between the first date and the first date + 1. This will use the index on the field to select for the join. Could you put in in layman terms please?Thank you very much.
 

Users who are viewing this thread

Top Bottom