Avatar of creativefusion
creativefusionFlag for Australia

asked on 

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
Microsoft Access

Avatar of undefined
Last Comment
creativefusion
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

What are you expecting to export to Excel?
Full records or just the few fields required to identify the 'problems'?

If you want ful records then that will mean in effect running two queries (albeit  they can be combined into one ).

Do you have indexes set on  REF_NO  LOC_NO  RECEIPT_DATE and  CODE_DATE?
Avatar of creativefusion

ASKER

Hi,

I have attached a file containing all the data that I need to export to excel. Please note, I have some other tables that are connected by OBDC which drag through the fields that are missing. Primary joins for additional fields are REF_NO and STATE.

Aside from these, the rest is purely calculations.

Indexes are only set on REF_NO.

CF
SameDay.xls
ASKER CERTIFIED SOLUTION
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of creativefusion

ASKER

Great work thanks very much.
CF
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo