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_SELEC
>=[Forms]![UF_REPORT_SELEC
Performance is a major concern hence I require some expert assistance with this.
CF
db1.mdb
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_SELEC
>=[Forms]
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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?
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?
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
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
Sorry - I've not been on EE much today...
I think not having an index on Receipt_date could be a big issue in terms of performance (Access will have to read the entire table to find the records it needs) and I suggest you add one. (I'm assuming that your date selection ids using Receipt _date; if not, then the same comment applies to whichever date field you are using).
Also I am not clear how you use the Weekno selection in conjunction with the date selection- I've assumed both values must be satisfied for a record to be considered
Based on these assumptions, you can identify which groups of records are involved using the query below...
Select REF_NO, LOC_NO, RECEIPT_DATE, CODE_DATE
From tablename
Where Receipt_date Between [Forms]![UF_REPORT_SELECTI
and Week_no between [Forms]![UF_REPORT_SELECTI
And Cases_Rec < Pallet_Qty
Group By REF_NO, LOC_NO, RECEIPT_DATE, CODE_DATE
Having Count(Purchase_order) >1
To get the actual records involved you would need to save this query and then create a new query from this and the main table and join them on all 4 fields.
I can't see this being a quick job at all to run such a query.
To provide satisfactory performance I think I would be considering creating a 'holding' table and running a query when the application is opened , to select all records which have qty_received less than the pallet quantity for say, the last 4 weeks. That should give you a working dataset from which you can run the selective queries on an ad-hoc basis.
I think not having an index on Receipt_date could be a big issue in terms of performance (Access will have to read the entire table to find the records it needs) and I suggest you add one. (I'm assuming that your date selection ids using Receipt _date; if not, then the same comment applies to whichever date field you are using).
Also I am not clear how you use the Weekno selection in conjunction with the date selection- I've assumed both values must be satisfied for a record to be considered
Based on these assumptions, you can identify which groups of records are involved using the query below...
Select REF_NO, LOC_NO, RECEIPT_DATE, CODE_DATE
From tablename
Where Receipt_date Between [Forms]![UF_REPORT_SELECTI
and Week_no between [Forms]![UF_REPORT_SELECTI
And Cases_Rec < Pallet_Qty
Group By REF_NO, LOC_NO, RECEIPT_DATE, CODE_DATE
Having Count(Purchase_order) >1
To get the actual records involved you would need to save this query and then create a new query from this and the main table and join them on all 4 fields.
I can't see this being a quick job at all to run such a query.
To provide satisfactory performance I think I would be considering creating a 'holding' table and running a query when the application is opened , to select all records which have qty_received less than the pallet quantity for say, the last 4 weeks. That should give you a working dataset from which you can run the selective queries on an ad-hoc basis.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial