By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,098 Members | 1,893 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,098 IT Pros & Developers. It's quick & easy.

MS Access Loop ?

P: 7
Hi All,

I have some order data that i need to collect some information.

I have date order opened, closed, cancelled etc.I dont have anything that ways an order was open between the open and close dates for example.

I need to calculate the number of orders open each day for a period. And then max and mins for the period.

Any ideas or assistance much appreciated.
Sep 10 '07 #1
Share this Question
Share on Google+
14 Replies


Scott Price
Expert 100+
P: 1,384
Hi All,

I have some order data that i need to collect some information.

I have date order opened, closed, cancelled etc.I dont have anything that ways an order was open between the open and close dates for example.

I need to calculate the number of orders open each day for a period. And then max and mins for the period.

Any ideas or assistance much appreciated.

Please explain a little more clearly what you are trying to accomplish and what you have tried so far.

What do you mean saying you want to calculate the number of orders open... Open how and by whom?

Also, please post the table structure that you have storing the orders information.

Regards,
Scott
Sep 10 '07 #2

P: 7
Hi Scott,

I have the following table structure

Unique identifier
Date
Event_Type
Customer.

I have event types of

New_Order
Order_Fill
Order_Edit
Order_Void
Order_Cancel

What I am trying to do is calculate the total number of orders open each day. I ran a query that grouped by date and counted open orders. this gave me an incorrect result as if for example the Order_new event was logged on 1 January and the order was filled on 10 January it only counted the order as open on 1 january. The order was in fact open for 9 days between 1 january and 10 january. So it should have been counted as open for the days 1 - 9 january.

I have tried to create a new record with a new event_type (order_open)for each day whilst the order_new event date was less than the order fill date but was unsuccesful. I thought a loop while procedure might work but unfortunately I dont have the skills to write the script to produce the records to poulate the orders table.

Hope this helps.
Regards
GoneFishing
Sep 11 '07 #3

Scott Price
Expert 100+
P: 1,384
Hi Scott,

I have the following table structure

Unique identifier
Date
Event_Type
Customer.

I have event types of

New_Order
Order_Fill
Order_Edit
Order_Void
Order_Cancel

What I am trying to do is calculate the total number of orders open each day. I ran a query that grouped by date and counted open orders. this gave me an incorrect result as if for example the Order_new event was logged on 1 January and the order was filled on 10 January it only counted the order as open on 1 january. The order was in fact open for 9 days between 1 january and 10 january. So it should have been counted as open for the days 1 - 9 january.

I have tried to create a new record with a new event_type (order_open)for each day whilst the order_new event date was less than the order fill date but was unsuccesful. I thought a loop while procedure might work but unfortunately I dont have the skills to write the script to produce the records to poulate the orders table.

Hope this helps.
Regards
GoneFishing
I think you have a design issue on the table structure... Essentially you should have four tables with the data that you are giving me:

Table one;
tblCustomers
CustomerID Autonumber PK
CustomerFirst
CustomerLast
CustomerEtc...

Table two;
tblOrders
OrderID Autonumber PK
CustomerID Number FK
OrderDetails
OrderEtc...

Table three;
tblOrderEvents
OrderEventID Autonumber PK
OrderID Number FK
EventID Number FK
OrderEventDate Date/Time

Table four;
tblEvent
EventID Autonumber PK
EventType

You are currently suffering from trying to create a many to many relationship between orders and events without the linking table. Testing this statement is easy: Order #1 can have more than one EventType... However, Event #1 can be associated with more than one Order. That tells us a many to many relationship exists between these two subjects, thus requiring the linking table called tblOrderEvents.

Then you will to check for any OrderID that has an EventID equal to New_Order that is less than or equal to whatever date, and does not have an EventID equal to Order_Close, OR Order_Close is greater than or equal to whatever date.

I have to step out briefly, but will be back in a bit with more detail on how to accomplish the second step.

Regards,
Scott
Sep 11 '07 #4

Scott Price
Expert 100+
P: 1,384
With the structure that I posted, try this SQL query:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblOrders.OrderID, tblOrders.CustomerID, tblOrderEvents.EventsID, tblOrderEvents.OrderEventDate
  2. FROM tblEvents INNER JOIN (tblOrders INNER JOIN tblOrderEvents ON tblOrders.OrderID = tblOrderEvents.OrderID) ON tblEvents.EventsID = tblOrderEvents.EventsID
  3. WHERE (((tblOrderEvents.EventsID)=1) AND ((tblOrderEvents.OrderEventDate)<=#9/8/2007#)) OR (((tblOrderEvents.EventsID)=2) AND ((tblOrderEvents.OrderEventDate)>=#9/8/2007#));
  4.  
Regards,
Scott
Sep 11 '07 #5

P: 7
Thanks Scott,

that work in giving me the orders. I think I now need to be able to count them, whilst the order open date is less than the order fill date. The complication i am coming across is getting the count right for each day the order is open. as i only have a single record for order open.

is there someway to do this, a count while of other statement.

Appreciate your guidance with this problem
Sep 12 '07 #6

Scott Price
Expert 100+
P: 1,384
Thanks Scott,

that work in giving me the orders. I think I now need to be able to count them, whilst the order open date is less than the order fill date. The complication i am coming across is getting the count right for each day the order is open. as i only have a single record for order open.

is there someway to do this, a count while of other statement.

Appreciate your guidance with this problem
Do you want a count of how many days an order is open, or how many orders are open for any given day?

Regards,
Scott
Sep 12 '07 #7

P: 7
Hi,

Its how many orders are open on any given day.

regards
Sep 12 '07 #8

P: 7
if you can help with how mnay days open also that would be a great help if not too inconvenient.

regards
Sep 12 '07 #9

Scott Price
Expert 100+
P: 1,384
Hi,

Its how many orders are open on any given day.

regards
You'll need to experiment with the Count() function in your query, or a DCount() function in a calculated control on form or report.

If you need more help than the MS Access help file can give, I'll take a look at it later, but today is a bit busy for me, sorry :(

Regards,
Scott
Sep 12 '07 #10

Scott Price
Expert 100+
P: 1,384
if you can help with how mnay days open also that would be a great help if not too inconvenient.

regards
This is possible, but with the setup we currently have, it will take some coding. I can work on it later on.


Regards,
Scott
Sep 12 '07 #11

Scott Price
Expert 100+
P: 1,384
This will count the orders that your query gives as being open on a particular day, enter it in an unbound text box on a form, etc...:

Expand|Select|Wrap|Line Numbers
  1. =DCount("[OrderID]","qryOrderEvent")
To check for how many days a particular order is open:

Create three queries. The first will be called qryDateOpen and have this SQL:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblOrderEvents.OrderID, tblOrderEvents.EventsID, tblOrderEvents.OrderEventDate AS OpenDate
  2. FROM tblOrderEvents
  3. WHERE (((tblOrderEvents.EventsID)=1));
The second will be called qryDateClose and have this SQL:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblOrderEvents.OrderID, tblOrderEvents.EventsID, tblOrderEvents.OrderEventDate AS CloseDate
  2. FROM tblOrderEvents
  3. WHERE (((tblOrderEvents.EventsID)=2));
The third will be called qryDaysOpen and have this SQL:

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW [qryDateClose].[CloseDate]-[qryDateOpen].[OpenDate] AS DaysOpen, qryDateClose.OrderID
  2. FROM qryDateOpen INNER JOIN qryDateClose ON qryDateOpen.OrderID = qryDateClose.OrderID;
  3.  
Hope this helps!

Regards,
Scott
Sep 13 '07 #12

P: 7
Thanks Scott,

The =Dcount("[OrderID]","qryOrderEvent") statement is giving me a number, am i missing something or have I done something wrong.

Regards
Sep 13 '07 #13

P: 7
sorry just to clarify its giving me a number but no dates, so i cant tell how many were open for each date in the period.

Regards
Sep 13 '07 #14

Scott Price
Expert 100+
P: 1,384
The DCount will give you the number of records that are open given the date you pass into the qryOrderEvent. So to know what date it is, capture it from wherever you are entering it...

You could do something like this to concatenate the two together:
Expand|Select|Wrap|Line Numbers
  1. =DCount("[OrderID]","qryOrderEvent") & " Orders open on: " & DLookUp("[OrderEventDate]","qryOrderEvent")
This gives an output of "5 Orders open on: 9/8/2008"

Regards,
Scott
Sep 13 '07 #15

Post your reply

Sign in to post your reply or Sign up for a free account.