Sum Different Rows

[lovelornloser]

Hi guys,

Wondering if you're able to help on this query;

I'm building a Bradford Scoring DB, everything is running smoothly apart from one niggle I would really like to get around and automate.

Essentially, I'm hoping to run a difference sum query which is fine when it's on the same row, but the issue I'm having is that I'm trying to calculate this from another row, linked by name. This inturn will workout the difference between the two absence episodes i.e. it's been 80 days since their last absence end.

I did find a method which would view the previous AutoID, but this worked well for the whole table, but not within a query limited to name.

The obvious workaround is to allow the team leaders to enter this manually, but this could be a/ open to small errors b/ mildly time consuming
I, like most, work with people who want info NOW and with little effort.

My VB skills are terrible at best, so you might need to spare the tech talk, if possible, please.

Many thanks

 

[jdraw]

Please define Bradford Scoring DB

 

[lovelornloser]

Bradford Scoring is a tool to manage short term sickness
You list out the total number of sickness days, multiply it by itself and then multiply it by the number of sickness episodes which will then give you your bradford score

A X A X B

I have the scoring system working, but line managers like to know the difference between absnce episodes to guage severity, something which I can do manually, but would like to do automatically.

 

[Privateer]

Yeah, you need a sub query working off the ID of the person and the date of the abscenses. Check out Allen's site it explains everything.

http://allenbrowne.com/subquery-01.html

Basically it allows you to run a query inside a query on the same table and find the last event related to that person. If you pull the date, then you do a date diff and get the days between them. There are two key things for your sub query criteria, sort in decending order by date, and do a less than the date of the current record. That means the top one record will be the most recent abscense for this person.

Good luck, subs are very useful.

 

[lovelornloser]

Many thanks for your help.

I'm way through, figuring the best method to be to create a dlookup sub query first, and then bring this into my overall query, link by name


I've attached the query.

Many thanks, again