Calculate the number of weeks between order dates.

[Pure Salt]

I am trying to calculate the number of weeks between order dates.



You can create a DateDiff calculation on two separate date fields, can anyone tell me how to do it in the one field?

We need to be able to work out the order frequency of each customer.

Thanks

[ItsMe]

You could use the weekday function and count the number of a given weekday.

[Pure Salt]

Thanks. Please could you give me an example.

[ItsMe]

What have you tried so far? I would probably include an alias in my query.

MyWeekDay: Weekday([tblName].[FieldName])

[JoeM]

It was my impression that the user wants the number of weeks between two dates, but these dates are NOT two different date fields within a single record, but rather, the difference in a single date field between multiple records (am I right?).

So for example,

Name    DateField
Joe     3/14/2014
Joe     3/28/2014
Fred    3/14/2014
Fred    3/21/2014
Fred    3/28/2014

So, for "Joe", the difference would be two weeks.

Where things get tricky is if the user has more than two records. How do you decide which two dates to check between?
Access is not like Excel. The order of records really has very little meaning in Access, so comparing a particular record to another one can get kind of tricky. Sometimes I end up using VBA and recordsets, or sometimes Forms/Reports.

I think we need to see a data sample, and an explanation of how to determine which records to compare.

Also, I am not sure how the WEEKDAY function will help in determining the number of weeks between two dates. If you want to return WEEK NUMBER, see this: http://datapigtechnologies.com/blog/...ber-in-access/

[ItsMe]

...I am not sure how the WEEKDAY function will help in determining the number of weeks between two dates...

Once the alias is in place you could use the Count() function. My approach is to get the alias in place first, then decide how to apply the aggregate function.

[JoeM]

But what would exactly you be counting?
How is counting the day of the week going to help you count the difference in week numbers?
For example, let's just say that there are three date records in there, March 17, March 31, and April 28.
Using the default settings, that function would return 2 (Monday) for all three records.
How would counting the number 2 three times help you determine how many weeks there are between each date?

[ItsMe]

But what would exactly you be counting?

I do not know. It will depend on the business rules and relations. Perhaps if the OP uses the query builder to place the alias in an object, they will discover an appropriate place or, at least, tell us what did not work.

[JoeM]

I guess I am just having trouble seeing where you were going with that, and how that would help solve the question at hand.
I thought maybe you had some cool new technique I had not seen before.

[rpeare]

Assuming you have a table like this

OrderID	CustomerID	OrderDate
1	1	1/1/2014
2	1	1/7/2014
3	1	1/16/2014
4	1	2/1/2014
5	2	1/2/2014
6	2	1/17/2014
7	2	1/31/2014
8	2	2/20/2014

you can get the a list of orders and next orders with this query:

SELECT tblOrders.OrderID, tblOrders.CustomerID, tblOrders.OrderDate, Min(IIf([tblorders_next]![orderdate]>[tblorders]![orderdate],[tblorders_next]![orderdate],Null)) AS NextOrder
FROM tblOrders LEFT JOIN tblOrders AS tblOrders_Next ON tblOrders.CustomerID = tblOrders_Next.CustomerID
GROUP BY tblOrders.OrderID, tblOrders.CustomerID, tblOrders.OrderDate
HAVING (((Min(IIf([tblorders_next]![orderdate]>[tblorders]![orderdate],[tblorders_next]![orderdate],Null))) Is Not Null));

then you can calculate the days between orders and do whatever you want with that (calculate an average number of days or weeks between orders etc.

[ItsMe]

I guess I am just having trouble seeing where you were going with that, and how that would help solve the question at hand.
I thought maybe you had some cool new technique I had not seen before.

Actually, I was stuck on stupid. I have, in the past, employed tables containing records for every date within a given range. In the table with all of these records for dates are columns that store integer equivalents for Weekday, Month, and Year. The purpose is to count periods. This is a common need in accounting.

I forgot to include an integral part to my approach of counting periods for a date range.

I also use the weekday function, month function, and year function as alias' in a queries to help with sorting by periods. I was just confused when I offered advice earlier.

[Pure Salt]

Thanks for this.

I have Order ID & Customer ID in one table & Delivered in another table.

Not sure what has happened to my brain, complete blockage on this one!!

Assuming you have a table like this

OrderID	CustomerID	OrderDate
1	1	1/1/2014
2	1	1/7/2014
3	1	1/16/2014
4	1	2/1/2014
5	2	1/2/2014
6	2	1/17/2014
7	2	1/31/2014
8	2	2/20/2014

you can get the a list of orders and next orders with this query:

SELECT tblOrders.OrderID, tblOrders.CustomerID, tblOrders.OrderDate, Min(IIf([tblorders_next]![orderdate]>[tblorders]![orderdate],[tblorders_next]![orderdate],Null)) AS NextOrder
FROM tblOrders LEFT JOIN tblOrders AS tblOrders_Next ON tblOrders.CustomerID = tblOrders_Next.CustomerID
GROUP BY tblOrders.OrderID, tblOrders.CustomerID, tblOrders.OrderDate
HAVING (((Min(IIf([tblorders_next]![orderdate]>[tblorders]![orderdate],[tblorders_next]![orderdate],Null))) Is Not Null));

then you can calculate the days between orders and do whatever you want with that (calculate an average number of days or weeks between orders etc.

[JoeM]

Clever solution, rpeare!