Access Query Design

Hi All,

I am having some difficulty trying to put together a query that reports the following exceptions:

Same Day Opportunities
Note: I have specified the field names relevant to the objective in brackets to help.

1. Same product (REF_NO) delivered to a site (LOC_NO) on multiple occasions within the same day (RECEIPT_DATE) with the same code date (CODE_DATE) made up of less than a full pallet quantity (RECEIVED TI HI).

Examples:

Item 71247 was delivered in two seperate deliveries from the supplier on the 6/5/2011. The first delivery was for 22 cases, the second was for 40 cases. A full pallet contains 200 cases. If the supplier sent only one delivery with the entire 62 cartons, then the cost of receiving this order would be halved as I would only handle on pallet.

Item 90922 was delivered in two seperate deliveries from the supplier on the 4/5/2011. The first delivery was 80 cases, the second was for 52 cases. A full pallet contains 128 cases. I should have recieved the entire order on 1 pallet rather than two.

Notes:

1.The production table (ST_RECEIPT_MASTER) is very large and contains more than two million records at any given time.
2. The outputs of the final query will be exported to a users desktop in ms excel.
3. The end state query will be paramater driven using form controls as follows:

>=[Forms]![UF_REPORT_SELECTION]![DCStart] And <=[Forms]![UF_REPORT_SELECTION]![DCEnd]
>=[Forms]![UF_REPORT_SELECTION]![WeekFrom] And <=[Forms]![UF_REPORT_SELECTION

Performance is a major concern hence I require some expert assistance with this.

CF

db1.mdb