Evaluate how many orders where open on specific day

[kaherdin]

Evaluate how many orders where open on specific day
This is one part of a, for me, large access 2010 project I’m trying to develop for my workplace.

Background
I want to be able to show a chart, a graph, of how many open orders we have had during the last month (showing per day) and during the last year (showing per months). In order to do this I am trying to create a set of queries that would provide me with such information in the form/structure;

[Date] [OpenOrdersTotal]
Yyyy-mm-dd 4
Yyyy-mm-dd 5
Yyyy-mm-dd 3
Yyyy-mm-dd 4

And after that I would create a chart

I have
1. TblOrders
[OrderID] Autonum
[CaseID] Foreign primary key
[DepartmentID] Foreign primary key
[DateOrderMade] Date (yyyy-mm-dd)
[DateOrderAnswerd] date (yyyy-mm-dd)

2. TblDates
[ReportDate] Date (yyyy-mm-dd)
​

The evaluation in a query that runs all [ReportDate] logically would say;

Count(IIF([ReportDate] BETWEEN [DateOrderMade] AND [DATEORDERANSWERD]);1;0)

...but that wont do it.
And after spending eight hours reading and trying :banghead: I now turn to the place where I myself have learned the most from.

I have attached a copy of the segment above to this post and anyone willing to give me a hand in resolving can download it.

 

[EternalMyrtle]

Why don't you just use a report? Then you can group by date.

 

[kaherdin]

Why don't you just use a report? Then you can group by date.

As I am saying in my OP, I'm trying to show per date (day) how many orders that where open that specific date (day).
What you are suggesting is great for showing when the orders either were made or answered.

 

[EternalMyrtle]

Adding this calculated field to your query will filter out only the open orders as of the report date:

OpenOrders: IIf([DateOrderAnswerd]>[ReportDate],"Open")

Set the field criteria to "Open"

Be sure to add both of your tables to the query and the relevant fields.

 

[EternalMyrtle]

Try opening qryOpenOrders and see if this is what you wanted.

 

[EternalMyrtle]

You need to tweak the calculated field since your order dates need to be before your report dates:

OpenOrders: IIf([DateOrderAnswerd]>[ReportDate] And [DateOrderMade]<[ReportDate],"Open")

By the way, this data is less useful without a join. I do not think you will be able to count records or make a useful report with groupings without one.

 

[kaherdin]

You need to tweak the calculated field since your order dates need to be before your report dates:

OpenOrders: IIf([DateOrderAnswerd]>[ReportDate] And [DateOrderMade]<[ReportDate],"Open")

By the way, this data is less useful without a join. I do not think you will be able to count records or make a useful report with groupings without one.

EternalMyrtle
This is what I am trying right now. And, from the look of it, it works well.
This is what I was looking for and the actual tweak I was lacking before viewing your suggestion was the use of one expression and one criteria together.

SELECT tblDates.ReportDate, tblOrders.DateOrderMade,
 tblOrders.DateOrderAnswerd, IIf([DateOrderAnswerd]>[ReportDate] And
 [DateOrderMade]<[ReportDate],"Open","Closed") AS OpenOrders, 
tblOrders.OrderID

FROM tblDates, tblOrders
WHERE (((IIf([DateOrderAnswerd]>[ReportDate] And [DateOrderMade]
<[ReportDate],"Open","Closed"))="Open"));

Tomorrow, back at work, I'll try this out with the real database. I'm quite certain that it will work out now.
- This solution was much more "straight at the issue" than I could expect, and I am very greatful for your input!

I'll set the thread to "Solved" as soon as i get a PoC

By the way, on the real database the tblOreders is connected to tblCases that inturn is connected to over twelve keys in different tables

There will be results, I promise you!

To continue;
- Next step will be to try to use Count() and get a count for each date.

 

[Cronk]

I see some "holes" where some orders may not be counted. Such as what happens to orders which are not yet answered. There is no provision for null dates. Also, what about orders taken on the [report date], whether answered that day or not.

Maybe sql condition should be (and you can simpliy by dropping field delimiters and the superfluous IIf construct)

WHERE (DateOrderAnswerd>ReportDate OR DateOrderAnswerd Is null) And DateOrderMade <=ReportDate

If you want a count of open orders then have the calculated field in the SELECT as

Sum(IIf( (DateOrderAnswerd>ReportDate OR DateOrderAnswerd Is null) And DateOrderMade <=ReportDate),1,0))

 

[EternalMyrtle]

I see some "holes" where some orders may not be counted. Such as what happens to orders which are not yet answered. There is no provision for null dates. Also, what about orders taken on the [report date], whether answered that day or not.

Cronk is right, this is an improvement.

 

[kaherdin]

I see some "holes" where some orders may not be counted. Such as what happens to orders which are not yet answered. There is no provision for null dates. Also, what about orders taken on the [report date], whether answered that day or not.

Maybe sql condition should be (and you can simpliy by dropping field delimiters and the superfluous IIf construct)

WHERE (DateOrderAnswerd>ReportDate OR DateOrderAnswerd Is null) And DateOrderMade <=ReportDate

If you want a count of open orders then have the calculated field in the SELECT as

Sum(IIf( (DateOrderAnswerd>ReportDate OR DateOrderAnswerd Is null) And DateOrderMade <=ReportDate),1,0))

Because of the fact that I'm not using the english version of Access 2010 at my workplace, could you maybe explain (or just attach a screenshot) of how you mean the SQL code should be implemented, including the "count" code.
I really appreciate your help, as this is sort of a big deal for me personaly to get this to work

 

[EternalMyrtle]

I can update the query and attach it tomorrow. Cronk's additions are absolutely necessary for it to work properly.

 

[Cronk]

Myrtle,

I'll leave it to you then to update the query - easy if you are familiar with the sql view. You did the main work on this.

This type of report I've found is very common in process handling systems but mainly for a period of longer than one day say weekly, or monthly etc. The requirement is the number of open cases at the start, the new ones in the period, the number completed and the number of open cases at the end of the period. I soon learned to include null end dates.

 

[EternalMyrtle]

This type of report I've found is very common in process handling systems but mainly for a period of longer than one day say weekly, or monthly etc. The requirement is the number of open cases at the start, the new ones in the period, the number completed and the number of open cases at the end of the period. I soon learned to include null end dates.

When it comes down to it, I just didn't think about it hard enough! But, as you guessed, I am also not accustomed to this kind of report.

 

[kaherdin]

Sum(IIf( (DateOrderAnswerd>ReportDate OR DateOrderAnswerd Is null) And DateOrderMade <=ReportDate),1,0))

Access 2010 seems to have som issues with the SQL code here.
I tried to tweak and check for missing ")" or similar but it wont run...

 

[pr2-eugin]

Try this..

Sum[B][COLOR=DarkRed]([/COLOR][/B]IIf[B][COLOR=Red]([/COLOR][COLOR=Magenta]([/COLOR][/B][COLOR=Blue][B]([/B][/COLOR]DateOrderAnswerd>ReportDate OR DateOrderAnswerd Is null[COLOR=Blue][B])[/B][/COLOR] And [COLOR=Green][B]([/B][/COLOR]DateOrderMade <=ReportDate[B][COLOR=Green])[/COLOR][COLOR=Magenta])[/COLOR][/B],1,0[B][COLOR=Red])[/COLOR][COLOR=DarkRed])[/COLOR][/B]

Easier if you had some advanced editor like NotePad++..

 

[kaherdin]

Try this..

Sum[B][COLOR=darkred]([/COLOR][/B]IIf[B][COLOR=red]([/COLOR][COLOR=magenta]([/COLOR][/B][COLOR=blue][B]([/B][/COLOR]DateOrderAnswerd>ReportDate OR DateOrderAnswerd Is null[COLOR=blue][B])[/B][/COLOR] And [COLOR=green][B]([/B][/COLOR]DateOrderMade <=ReportDate[B][COLOR=green])[/COLOR][COLOR=magenta])[/COLOR][/B],1,0[B][COLOR=red])[/COLOR][COLOR=darkred])[/COLOR][/B]

Easier if you had some advanced editor like NotePad++..

- Yes, true that.
This is the complete SQL code from the query

SELECT tblDates.ReportDate, tblOrders.DateOrderMade, tblOrders.DateOrderAnswerd, Sum(IIf((([DateOrderAnswerd]>[ReportDate] Or [DateOrderAnswerd] Is Null) And ([DateOrderMade]<=[ReportDate])),1,0)) AS OpenOrders
FROM tblDates, tblOrders
WHERE (((tblOrders.DateOrderMade)<=[ReportDate]) AND ((tblOrders.DateOrderAnswerd)>[ReportDate] Or (tblOrders.DateOrderAnswerd) Is Null));

I get an error msg saying roughly;
"Your tried to run a query with a sum function that did not contain the stated expression 'ReportDate'".

As I stated above I am new to access and because of the fact that Im running a none english version of Access 2010 its not that easy to implement.

If anyone could copy the complete SQL code that would produce the count of OpenOrders for each date [ReportDate] I would be willing to buy that individual a beer, PayPal wise or something like that...

 

[pr2-eugin]

You need to add the fields to the Group By parts.

SELECT tblDates.ReportDate, tblOrders.DateOrderMade, tblOrders.DateOrderAnswerd, Sum(IIf((([DateOrderAnswerd] > tblDates.[ReportDate] Or [DateOrderAnswerd] Is Null) And ([DateOrderMade] <= tblDates.[ReportDate])),1,0)) AS OpenOrders
[COLOR=Red][B]FROM tblDates, tblOrders[/B][/COLOR]
WHERE (((tblOrders.DateOrderMade) <= tblDates.[ReportDate]) AND ((tblOrders.DateOrderAnswerd)>tblDates.[ReportDate] Or (tblOrders.DateOrderAnswerd) Is Null))
GROUP BY tblDates.ReportDate, tblOrders.DateOrderMade, tblOrders.DateOrderAnswerd;

Although I am worried about the FROM part, you do not seem to have a JOIN on these two tables..

 

[kaherdin]

Although I am worried about the FROM part, you do not seem to have a JOIN on these two tables..

This is a part of the question at hand; I want to evaluate if;
ReportDate falls between DateOrderMade and DateOrderdAnswerd
That would result in that that specific OrderID was "open" during that date (ReportDate).

To join these two tables at query level wouldnt be an issue, but I dont know a logical way of doing so at this moment...

All help is greatfuly accepted.

Could one way of resolving the main question to be to define the dates inbetween OrderDateMade and OrderDateAnswerd and then use these values (dates) in a secondary query to count for each ReportDate?

 

[pr2-eugin]

So you would like to find the Overlapping records? Join them by basic JOIN, then check out the neat criteria that Paul has used - http://www.baldyweb.com/OverLap.htm

 

[kaherdin]

pr2-eugin
I "just" want a count of how many records (orders) that where open on a specific date.

I.e.
Date Count(Open)
2013-01-05 3
2013-01-06 4
2013-01-05 7

The Overlapping records-templete is extremly usefull and I will implement it when looking at "Orders open during week XX" or "Orders open during month XX"

Update1:
I'm testing Pauls Overlapping query and it seems as if I set startdate and enddate to the same date (I.e. 2013-09-09) it acctually shows the open orders for that specific date (2013-09-09).

Now I could use this information further
This is a great way of learning SQL/querying!