We help IT Professionals succeed at work.
Get Started

Access Query Design

creativefusion
on
222 Views
Last Modified: 2012-05-11
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
Comment
Watch Question
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE