DCOUNT function in query?

  • Thread starter Thread starter Jim Franklin
  • Start date Start date
J

Jim Franklin

Hi,

I hope someone can help me. I have two tables in A2k, "Orders" and
"Letters". Fields are as follows:

Orders.OrderID (primary key)
Orders.CustomerID
Orders.OrderDate

Letters.LetterID (primary key)
Letters.CustomerID
Letters.LetterDate

I want to create a query that tells me, for each letter sent out, the number
of orders placed by that customer between 2 and 30 days after the letter was
sent. Each customer can have multiple orders and multiple letters.

Can someone tell me the best way to do this? I have tried a DCount function
in the query, but could not get it to work.

Thanks,
Jim F.
 
Hi Jim,

I think the following should work. In the query design
window, add both tables to the grid and join them on
CustomerID. Add LetterID, LetterDate, OrderDate and
OrderID to the query. Click the sum totals button on the
toolbar (looks like greek sigma).

Under LetterID and LetterDate choose "Group By", Under
OrderDate choose "Where" and enter your criteria on the
criteria line. You can use [LetterDate] in the criteria
to refer to the value of LetterDate, such as >[LetterDate]
+ 1 AND < [LetterDate] + 31. Then, Under OrderID choose
Count.

I think that this should give you a count of all of the
orders for the customer linked to that letter where the
orders are within the specified date range.

Note that if more than one letter is sent to a customer
within the date range, orders will be double-counted.
For example, if two letters are sent a week apart, then
an order is placed a week later, the order will show up
in the count for both letters.

DCount() should also work to do this, but I would usually
only use it if I had to because it is a little slower.
But, if you wanted to use DCount(), I think the following
should work in a query just based on the Letters table:

OrderCount: DCount("[OrderID]","Orders","[OrderDate] > #"
& [LetterDate] + 1 & "# AND [OrderDate] < #" &
[LetterDate] + 31) & "#")

Post back if it doensn't work.

-Ted Allen
 
You might try a query whose SQL looks something like this:

SELECT
Letters.*,
(SELECT
Count(*)
FROM
Orders
WHERE Orders.CustomerID = Letters.CustomerID
AND
Orders.OrderDate Between Letters.LetterDate + 2 And Letters.LetterDate + 30)
AS OrdersPlaced
FROM Letters
 
Hi,

I hope someone can help me. I have two tables in A2k, "Orders" and
"Letters". Fields are as follows:

Orders.OrderID (primary key)
Orders.CustomerID
Orders.OrderDate

Letters.LetterID (primary key)
Letters.CustomerID
Letters.LetterDate

I want to create a query that tells me, for each letter sent out, the number
of orders placed by that customer between 2 and 30 days after the letter was
sent. Each customer can have multiple orders and multiple letters.

Can someone tell me the best way to do this? I have tried a DCount function
in the query, but could not get it to work.

Thanks,
Jim F.
Click to expand...

The syntax of the third "criteria" option of the Domain functions is a
bit tricky - it should be a valid SQL Query WHERE clause without the
word WHERE, as a text string. Try

SELECT Letters.LetterID, Letters.CustomerID, Letters.LetterDate,
DCount("*", "[Orders]", "[CustomerID] = " & [CustomerID] & " AND
[OrderDate] BETWEEN DateAdd("d", -30, #" & [LetterDate] & "# AND
DateAdd("d", -2, #" & [LetterDate] & "#")
FROM Letters;
 
I just noticed after reading John's post that I forgot
the CustomerID part of the criteria, so please disregard
the DCount() part of my post and use John's instead.
-----Original Message-----
Hi Jim,

I think the following should work. In the query design
window, add both tables to the grid and join them on
CustomerID. Add LetterID, LetterDate, OrderDate and
OrderID to the query. Click the sum totals button on the
toolbar (looks like greek sigma).

Under LetterID and LetterDate choose "Group By", Under
OrderDate choose "Where" and enter your criteria on the
criteria line. You can use [LetterDate] in the criteria
to refer to the value of LetterDate, such as > [LetterDate]
+ 1 AND < [LetterDate] + 31. Then, Under OrderID choose
Count.

I think that this should give you a count of all of the
orders for the customer linked to that letter where the
orders are within the specified date range.

Note that if more than one letter is sent to a customer
within the date range, orders will be double-counted.
For example, if two letters are sent a week apart, then
an order is placed a week later, the order will show up
in the count for both letters.

DCount() should also work to do this, but I would usually
only use it if I had to because it is a little slower.
But, if you wanted to use DCount(), I think the following
should work in a query just based on the Letters table:

OrderCount: DCount("[OrderID]","Orders","[OrderDate] > #"
& [LetterDate] + 1 & "# AND [OrderDate] < #" &
[LetterDate] + 31) & "#")

Post back if it doensn't work.

-Ted Allen
-----Original Message-----
Hi,

I hope someone can help me. I have two tables in A2k, "Orders" and
"Letters". Fields are as follows:

Orders.OrderID (primary key)
Orders.CustomerID
Orders.OrderDate

Letters.LetterID (primary key)
Letters.CustomerID
Letters.LetterDate

I want to create a query that tells me, for each letter sent out, the number
of orders placed by that customer between 2 and 30 days after the letter was
sent. Each customer can have multiple orders and multiple letters.

Can someone tell me the best way to do this? I have tried a DCount function
in the query, but could not get it to work.

Thanks,
Jim F.


.
Click to expand...
.
Click to expand...
 
Hi,

Thank you to everyone who responded. I appreciate it!

I finally got this to work using the DCount function as suggested. (I could
not use a simple totals query, due to multiple letters to customers making
multiple orders)

Just thought it might be worth noting to anyone reading this in the UK that
the reason it did not work originally is that DCount takes your date field,
whatever format it is in (mine was dd/mm/yy) and assumes it to be the US
format of mm/dd/yy. Hence my date of 6 Jan 2004 was being read as 1 June
2004. Got round it by using a Format function within the DCount function.

Does that make sense? (And if so, is it the best way to do it?)

Thanks again,
Jim F.



John Vinson said:
Hi,

I hope someone can help me. I have two tables in A2k, "Orders" and
"Letters". Fields are as follows:

Orders.OrderID (primary key)
Orders.CustomerID
Orders.OrderDate

Letters.LetterID (primary key)
Letters.CustomerID
Letters.LetterDate

I want to create a query that tells me, for each letter sent out, the number
of orders placed by that customer between 2 and 30 days after the letter was
sent. Each customer can have multiple orders and multiple letters.

Can someone tell me the best way to do this? I have tried a DCount function
in the query, but could not get it to work.

Thanks,
Jim F.
Click to expand...

The syntax of the third "criteria" option of the Domain functions is a
bit tricky - it should be a valid SQL Query WHERE clause without the
word WHERE, as a text string. Try

SELECT Letters.LetterID, Letters.CustomerID, Letters.LetterDate,
DCount("*", "[Orders]", "[CustomerID] = " & [CustomerID] & " AND
[OrderDate] BETWEEN DateAdd("d", -30, #" & [LetterDate] & "# AND
DateAdd("d", -2, #" & [LetterDate] & "#")
FROM Letters;
Click to expand...
 
Just thought it might be worth noting to anyone reading this in the UK that
the reason it did not work originally is that DCount takes your date field,
whatever format it is in (mine was dd/mm/yy) and assumes it to be the US
format of mm/dd/yy. Hence my date of 6 Jan 2004 was being read as 1 June
2004. Got round it by using a Format function within the DCount function.

Does that make sense? (And if so, is it the best way to do it?)
Click to expand...

Yes, it does make some sort of sense. The programmers who wrote Access
live in the US and use mm/dd format for dates. They had to come up
with a convention to consistantly interpret dates, and chose mm/dd.
Date literals in any Query must be in either month/day/year format or
in an unambiguous format such as 05-Mar-2004 or 2004-03-05.
 
Back
Top