usin expressions in queries.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Expression's within queries?????

--------------------------------------------------------------------------------

Hi People

My sales manager has asked me to implement a system where the database will
flag up any customers who haven't used us for 6 months or longer.

I know i will hav to use a query and the Fields "Customer" & "Date", but
cant think of the expression i would hav to use inorder for the database to
do this operation.

I also want to add a check box so that, we can tick off customers who arent
going to be usin us again, so that the query doesnt flag them up every month.

If any one could help that would be great!!

Thanks
gary
 
G,

Without knowing more about your database structure, it is difficult to give
you a detailed expression to use. Because of this, I'll assume that you have
a Customers table (tbl_Customers) and an Orders table (tbl_Orders) and that
each of these tables has a Customer_ID field. In that case, your initial
query might look like:

SELECT tbl_Customers.Customer_ID,
tbl_Customers.Customer_Name,
Max(tbl_Orders.Order_Date) as MostRecentOrder
FROM tbl_Customers INNER JOIN tbl_Orders
ON tbl_Customer.Customer_ID = tbl_Orders.Customer_ID
GROUP BY tbl_Customers.Customer_ID,
tbl_Customers.Customer_Name
HAVING Max(tbl_Orders.Order_Date) < DateAdd("m",-6, Date()

In order to get rid of those that are no longer active, you need to add a
column to the Customers table (I'll call it Inactive) that has a yes/no data
type. The default value for this field should be No (False) and when you
have determined that someone is no longer active , you can change this to
field value to true.

Then, your query might look like:

SELECT tbl_Customers.Customer_ID,
tbl_Customers.Customer_Name,
Max(tbl_Orders.Order_Date) as MostRecentOrder
FROM tbl_Customers INNER JOIN tbl_Orders
ON tbl_Customers.Customer_ID = tbl_Orders.Customer_ID
WHERE tbl_Customers.Inactive = False
GROUP BY tbl_Customers.Customer_ID,
tbl_Customers.Customer_Name
HAVING Max(tbl_Orders.Order_Date) < DateAdd("m",-6, Date()

HTH
Dale
 
First, you should avoid using a field named Date. If you use it, be sure it
is enclosed in square brackets.

In query design view, set the criteria for a date field to <
DateAdd("m",-6,Date())
See Help for more information about the DateAdd function.
 
thanks for your reply mate!

Can i set this forumla so that it gives 6months or greater???

Also how would i get it so that instead of pulling all jobs 6month or
greater than it just gives customers who havnt used us for over 6 months??

thanks!
 
thanks for the hand matey!!

Dale Fye said:
G,

Without knowing more about your database structure, it is difficult to give
you a detailed expression to use. Because of this, I'll assume that you have
a Customers table (tbl_Customers) and an Orders table (tbl_Orders) and that
each of these tables has a Customer_ID field. In that case, your initial
query might look like:

SELECT tbl_Customers.Customer_ID,
tbl_Customers.Customer_Name,
Max(tbl_Orders.Order_Date) as MostRecentOrder
FROM tbl_Customers INNER JOIN tbl_Orders
ON tbl_Customer.Customer_ID = tbl_Orders.Customer_ID
GROUP BY tbl_Customers.Customer_ID,
tbl_Customers.Customer_Name
HAVING Max(tbl_Orders.Order_Date) < DateAdd("m",-6, Date()

In order to get rid of those that are no longer active, you need to add a
column to the Customers table (I'll call it Inactive) that has a yes/no data
type. The default value for this field should be No (False) and when you
have determined that someone is no longer active , you can change this to
field value to true.

Then, your query might look like:

SELECT tbl_Customers.Customer_ID,
tbl_Customers.Customer_Name,
Max(tbl_Orders.Order_Date) as MostRecentOrder
FROM tbl_Customers INNER JOIN tbl_Orders
ON tbl_Customers.Customer_ID = tbl_Orders.Customer_ID
WHERE tbl_Customers.Inactive = False
GROUP BY tbl_Customers.Customer_ID,
tbl_Customers.Customer_Name
HAVING Max(tbl_Orders.Order_Date) < DateAdd("m",-6, Date()

HTH
Dale
Click to expand...
 
The expression as I presented it looks for all dates more than six months
ago. For the second part of your question, how does the database know who
hasn't used you for six months? If you are reckoning that from the last
order date or something like that, then OrderDate is the field to which you
need to apply the criteria. If it is in a related table it can still be
done, but it would help to know how your database is structured.
 
Back
Top