Grouping Data based on dates where primary key is overlapping across dates

[singhals]

I have a data set something like this:

Order_Id Shipping_Date Sales
1 6/15/2010 150
2 7/30/2010 100
2 8/1/2010 200
3 7/29/2010 105
3 7/30/2010 78
3 8/2/2010 130
4 7/30/2010 120
4 8/1/2010 210
5 8/4/2010 100
6 7/25/2010 59
7 6/30/2010 214
7 7/1/2010 52
8 7/29/2010 100
8 7/30/2010 59
8 7/31/2010 214
8 8/1/2010 52

Now I not trying to group this data on a monthly sales basis. However since many of the Order_Ids overlap into various months, I am having problems grouping the data on a monthly basis. Ideally I would like to classify the order_id to in middle of shipping dates and have thought of following rules to group the data.

1. Case where single shipping date - Take the date of shipping for grouping - E.g. Order ID 6 should be grouped in 7/25/2010 for total sales of 59 going towards July.
2. Case where there are odd number of dates - take the middle date. E.g. Order ID 3 should be grouped in 7/30/2010 for total sales of 313 going towards July.
3. Case where there are only 2 order IDs - the Order_Id should be grouped in first shipping date. Thus order ID 2 would belong to 7/30/2010 for total sales of 300 going towards July.
4. Cases where there are more than 2 even number of order IDs - take the first date of 2 middle dates. Thus order ID 8 would belong to 7/30/2010 for total sales of 425 going towards July.

Is it possible to somehow set up such kind of rules in Access?

I realize that above rules are quite weird but this is the best I could think of. Any suggestions for changes in above rules are very welcome.

Regards,
Sagar

[Steven Kogan]

Microsoft has a support article for calculating the median. It seems what you'd like is the median date per order.

Check the support article at http://support.microsoft.com/kb/210581

You would need to modify the code so that the WHERE clause limits the recordset to the orderid for which you want the median date.

You'd also want to modify the vba case where there is an even number of dates so that it selects the first of the two middle dates. For that you could simply modify the line:

Median = (x + y) /2

to

Median = y