need to take excel formulas and create same in MS Acces query

[Randy]

I am terrible with date coding in MS Access. something I need to work on. In excel I can usually work it out. I have this excel sheet and I want to convert all the formulas to an equivalent MS Access query formula.
Attached is the completed excel workbook.

Any help creating this date driven formulas is very much appreciated.

 

[CJ_London]

the Access equivalent of Excel

if >> iif(something,then do this, else do that)
today >> Date()
averagegeifs >> DAvg ( expr , domain [, criteria] ) or Avg in a query
Sumifs>> DSum ( expr , domain [, criteria] ) or sum in a query

There is no equivalent to iferror, but in the context you are using it you would need to anticipate the error type

e.g. to avoid a divide by zero error you would use

iif(x=0,0,y/x)

And you would not use J, K etc column identifiers, you would use the column heading you have in row 1.

so to start you off you would have something like for this one

=IFERROR(ROUND(AVERAGEIFS(J:J,D,O3,A:A,">"&TODAY()-90),2),0)

Select SalesTypeID, Avg(netsaleslocalcurrency) AS AvgDailyBooking
From MyTable
 WHERE IncomingOrderDate > Date()-90
 GROUP BY SalesTypeID

 

[Randy]

excellent, I understand all of that. Yes I know that the IFERROR is not really needed in Access.

So I have modified your statement as follows.

SELECT CustomerAccount, Avg(netsaleslocalcurrency) AS AvgDailyBooking
FROM dbo_Fact_IncomingOrders_local
WHERE IncomingOrdersDate > Date()-90
GROUP BY CustomerAccount
ORDER BY CustomerAccount;

and this worked perfectly. So now if you can help me add the second column, I think I can figure out all the others.

say look at column V Total Booked Month (Today) -3

If you can add that code to the above SQL statement, I think I can figure out the other columns.

Again thank you so much for this help.

 

[Randy]

Ok I continued my research and tried this.
SELECT CustomerAccount, DAvg("netsaleslocalcurrency","dbo_Fact_IncomingOrders_local", "IncomingOrdersDate > Date()-90") AS AvgDailyBooking,DSUM("netsaleslocalcurrency","dbo_Fact_IncomingOrders_local", "IncomingOrdersDate <NOW()-3") AS MNTHtoDate
FROM dbo_Fact_IncomingOrders_local
GROUP BY CustomerAccount
ORDER BY CustomerAccount;


However the answer for each customer is the same. see attached screen shot.

 

[CJ_London]

You need to limit this by company

 DAvg("netsaleslocalcurrency","dbo_Fact_IncomingOrd ers_local", "IncomingOrdersDate > Date()-90[COLOR=red] AND CustomerAccount=" & customeraccount[/COLOR])

 

[Randy]

ok just to learn and understand I simplified the query to just one calculation for this test.

SELECT CustomerAccount, DAvg("netsaleslocalcurrency","dbo_Fact_IncomingOrders_local", "IncomingOrdersDate > Date()-90 AND CustomerAccount=" & customeraccount) AS AvgDailyBooking
FROM dbo_Fact_IncomingOrders_local
ORDER BY CustomerAccount;

but now all I get is an error message.

 

[CJ_London]

from the left justification of the data, it looks like your account number is actually text. Either convert to a number or modify to this

AND CustomerAccount='" & customeraccount & "'"

Also, rather than group by, which I suspect will run quite slowly, try your query as this

SELECT DISTINCT CustomerAccount, DAvg("netsaleslocalcurrency","dbo_Fact_IncomingOrders_local", "IncomingOrdersDate > Date()-90 AND CustomerAccount='" & customeraccount & "'") AS AvgDailyBooking 
FROM dbo_Fact_IncomingOrders_local
ORDER BY CustomerAccount;

 

[Randy]

I did some more work, still only one number for each customer, but at least I am learning the date syntax.

as you can see I had to hard code the 18 to start with 2/1/2015. How can I use a function to determine the 1st day of the current month. Do not want to change the code every day.

SELECT CustomerAccount, DAvg("netsaleslocalcurrency","dbo_FACT_IncomingOrders_Local","IncomingOrdersDate > Date()-90") AS Rolling90Days, DSum("netsaleslocalcurrency","dbo_Fact_IncomingOrders_Local","IncomingOrdersDate =NOW()-1") AS ActBookingPriorDay,DSum("netsaleslocalcurrency","dbo_Fact_IncomingOrders_Local","IncomingOrdersDate <NOW()-3 AND IncomingOrdersDate >NOW()-18") AS "MNTHtoDate-3",DSum("netsaleslocalcurrency","dbo_Fact_IncomingOrders_Local","IncomingOrdersDate <NOW()-2 AND IncomingOrdersDate >NOW()-18") AS "MNTHtoDate-2",DSum("netsaleslocalcurrency","dbo_Fact_IncomingOrders_Local","IncomingOrdersDate <NOW()-1 AND IncomingOrdersDate >NOW()-18") AS "MNTHtoDate-1"
FROM dbo_Fact_IncomingOrders_Local
GROUP BY CustomerAccount
ORDER BY CustomerAccount;

 

[Randy]

sorry posted before I refreshed. Let me look at your code and give that a try. again many thanks

 

[CJ_London]

Be aware now() gives a different answer to date because it includes a time element.

so if mydate=1/2/2015 (uk calendar) and now() is 18/2/2015 at 1 second past midnight

then mydate>=date()-18 is true
but mydate>=now()-18 is false - a date has a zero time element so is effectively midnight at the beginning of the day in question.

to get the first of the current month you would use

date()-day(date())+1

 

[Randy]

thank you. code updated as follows

SELECT DISTINCT dbo_Fact_IncomingOrders_Local.CustomerAccount, DSum("netsaleslocalcurrency","dbo_FACT_IncomingOrders_Local","IncomingOrdersDate > date()-90")/90 AS Rolling90Days, DSum("netsaleslocalcurrency","dbo_Fact_IncomingOrders_Local","IncomingOrdersDate >date()-2") AS ActBookingPriorDay, DSum("netsaleslocalcurrency","dbo_Fact_IncomingOrders_Local","IncomingOrdersDate <date()-2 AND IncomingOrdersDate >date()-day(date())+1") AS ["MNTHtoDate-3"], DSum("netsaleslocalcurrency","dbo_Fact_IncomingOrders_Local","IncomingOrdersDate <DATE()-1 AND IncomingOrdersDate >date()-day(date())+1") AS ["MNTHtoDate-2"], DSum("netsaleslocalcurrency","dbo_Fact_IncomingOrders_Local","IncomingOrdersDate <DATE() AND IncomingOrdersDate >date()-day(date())+1") AS ["MNTHtoDate-1"]
FROM dbo_Fact_IncomingOrders_Local
GROUP BY dbo_Fact_IncomingOrders_Local.CustomerAccount
ORDER BY dbo_Fact_IncomingOrders_Local.CustomerAccount;

 

[CJ_London]

myDate is just a name for illustration purposes, you can substitute IncomingOrdersDate

'my' is often used as a prefix by responders for illustration e.g.

myField, myDate, myTable etc

 

[Randy]

yep figured that out. again posted before I refreshed, new code above, but it still does not give correct value by customeraccount.

MS Access answer set has one single value for all customers

The excel JPG is the correct answer set. I would like to do this in MS Access, but cannot seem to figure out the statement.

 

[CJ_London]

what is your statement looking like?

 

[CJ_London]

overlapping posts

DSum("netsaleslocalcurrency","dbo_FACT_IncomingOrd ers_Local","IncomingOrdersDate > date()-90")/90 AS Rolling90Days

you have not taken into account what I said here

DAvg("netsaleslocalcurrency","dbo_Fact_IncomingOrders_local", "IncomingOrdersDate > Date()-90 AND CustomerAccount='" & customeraccount & "'") AS AvgDailyBooking

 

[Randy]

I am sorry, I guess I did not post that result, I apologize, I tried this, and got no answer, had to end up killing the query

SELECT Distinct Customeraccount,DAvg("netsaleslocalcurrency","dbo_Fact_IncomingOrders_local", "IncomingOrdersDate > Date()-90 AND CustomerAccount='" & customeraccount & "'") AS AvgDailyBooking
from dbo_Fact_IncomingOrders_local
ORDER BY CustomerAccount

it did not work, never gave an answer set, and I killed the query after an hour

 

[CJ_London]

It is slow because you are using a domain function (DAvg) and I'm sure you have a much larger dataset than what you have shown so far. So we'll go another route - try creating this as a query

SELECT Customeraccount, Avg(netsaleslocalcurrency) AS AvgDailyBooking
from dbo_Fact_IncomingOrders_local
Group By CustomerAccount
WHERE IncomingOrdersDate > Date()-90

and save it - we'll call it qryDaily - note there is no need to order it at this stage

check the results are what you require

You will see this is the SQL equivalent of

 DAvg("netsaleslocalcurrency","dbo_ Fact_IncomingOrders_local", "IncomingOrdersDate > Date()-90 AND CustomerAccount='" & customeraccount & "'")

then create another query which will be like this

SELECT DISTINCT dbo_Fact_IncomingOrders_local.CustomerAccount, AvgDailyBooking
FROM dbo_Fact_IncomingOrders_local LEFT JOIN qryDaily ON dbo_Fact_IncomingOrders_local.customeraccount=qryDaily.customer account
ORDER BY dbo_Fact_IncomingOrders_local.customeraccount

The reason for the second query is because although the first query is OK on its own you have different criteria for the other figures you want. So using the first query as a model, you can create the other queries separately for ActBookingPriorDay etc and then add them to the second query - suggest you do this in the query builder as it is a lot easier

 

[Randy]

interesting, let me go down that path. And yes my data set is 100,000 of lines of orders. thank you.