I am fairly new to Access and am receiving errors when trying to build a query. The data I am working with has been scrubbed and sampled in the attached both as the source data and as the database file. As the actual data contains millions of rows, I want to keep this in Access instead of Excel.
Sales_Ord = order number
ShipDate = date order shipped
WeekRange = Sun-Sat week ShipDate falls in
SRQty = quantity fulfilled by warehouse site 1
DCQty = quantity fulfilled by warehouse site 2
What I will ultimately be looking to do is pivot this data and determine what percent of orders in each week split between warehouse 1 and warehouse 2 as a percentage of total orders shipped that week.
I tried building a Splits Count formula that assigns a value of “1” to the record if both the “SRQty” and “DCQty” columns contain numbers greater than zero and a “0” to the record if either the “SRQty” or “DCQty” column contains a zero. Ideally, then, I would pivot these totals to show the sum of “SplitsCt” per week out of the total number of orders shipped that week (should be able to do this, as build Excel pivots daily).
I think the issue comes down to my attempt to replicate Excel’s “If/And” statement, which I have tried and adapted and retried based on online advice I have been reading. However, I still cannot get my query to execute without requiring I enter a parameter value for “SplitsCt”. I would really appreciate if someone could actually confirm what they suggest with my file’s setup and let me know (as a novice) how I can replicate going forward, including any change in equation.
Whoever can help me solve this will literally make my week.