Really big query problem

[Geoff Codd]

This is going to take a genius to work out, what I am trying to to is create a query showing apportioned data.

The query I currently have returns the following information

FName BName DName Dates Cons
Medicine Coupland Dental 31/08/01 8160
Medicine Coupland Dental 30/09/01 7338
Medicine Coupland Dental 31/10/01 8366
Medicine Coupland Dental 30/11/01 9986
Medicine Coupland Dental 31/12/01
Medicine Coupland Dental 31/01/02
Medicine Coupland Dental 28/02/02
Medicine Coupland Dental 31/03/02
Medicine Coupland Dental 30/04/02 3518

What I need to do is if a record doesn't have a consumption I want to find the Minimum Date of the Reading Greater than the current, and then apportion the consumption evenly across the record which don't have a reading, so I should end up with a query that looks like this

FName BName DName Dates Cons Appcon
Medicine Coupland Dental 31/08/01 8160 8160.0
Medicine Coupland Dental 30/09/01 7338 7338.0
Medicine Coupland Dental 31/10/01 8366 8366.0
Medicine Coupland Dental 30/11/01 9986 9986.0
Medicine Coupland Dental 31/12/01 - - - - 722.3
Medicine Coupland Dental 31/01/02 - - - - 722.3
Medicine Coupland Dental 28/02/02 - - - - 652.4
Medicine Coupland Dental 31/03/02 - - - - 722.3
Medicine Coupland Dental 30/04/02 3518 699.0

If anyone knows how I can do this I'd be most grateful

Thanks
Geoff