Help with some sorting

[justice8965]

Hi all. I have an Access97 database. I'm currently having a little trouble with a query I'm making.

Basically, I have to pull a customer's electric usage from the past 12 months. That part is easy. However, I need it sorted by month, like January 1st-31st all summed up, then Feb, etc. The problem is that most of the dates for the electric readings are in the middle of month (ie jan 16th to feb 17th). How would I go about pulling the data starting on the first day of the month going to the last?

 

[Galaxiom]

Access can do wonders manipulating data but it can't recover what isn't there.
If you only have readings taken in the middle of the month you can't hope to accurately determine what was used in the last half of one month and the first half of the next. But I expect you know that.

You could work with averages to approximate the new monthly periods.

Firstly find the number of days in each measured period by subtracting one date from the next.
Divide the usage by the days to get the average for each period.
Find the number of days between the beginning of the month and the reading day and multiply that by the average for the appropriate period.
Repeat for the end section of the month using the average of the next period.
Add the two figures.

Values in Date formatted fields can simply be subtracted to give the number of days between the two dates.

 

[namliam]

Well you dont... if data is aggregated already to this level there is basicaly no going back.

You could make a guestimate taking the avarage per day, and then multiply that to "fill" in the gaps... But that is not going to be the real thing...

And... Welcome to AWF !

 

[justice8965]

Thanks for the welcome

Yeah I actually figured out what you guys said about 2 minutes after posting this. Don't know why I thought this was possible. Looks like I'll use the average method. Thanks for the replies.