Query Customers That Havent Purchased In The Last Six Months
Nov 15, 2007
I have a database that i am trying to clear out old customers that have't bought something in the last 6 months.
I have a table with the customer details in and another table with the purchases in.
I have been using the folowing criteria:
Not Between Date() And #01/05/2007#
But i think this still still shows customers who have actually bought more recent than 01/05/2007.
Is there a way to just show customers who have not purchased anything within the last 6 months of the date the query is run on and to only show their last transaction date.
View Replies
ADVERTISEMENT
Mar 22, 2013
I currently have a form where users can enter an "End Date", click a button, and it queries the data from 12 months prior to "End Date" entered.
Is there a way to force the query to show all 12 months, even if there are no records for a particular month? For example: if the user enters February 2013 in the date field, I would like the query to return:
March 2012
April 2012
May 2012
June 2012
July 2012
August 2012
September 2012
October 2012
November 2012
December 2012
January 2013
February 2013
...so even if June 2012 has no records, it is included in the query with a value of zero.
This is what I have so far:
WHERE (((Qry_Tbl_Assets.Dte) Between DateAdd("m",-12,[Forms]![Main_screen]![End_Date]) And [Forms]![Main_screen]![End_Date]))
View 2 Replies
View Related
Nov 23, 2006
hi.. i have one table with fields 1-8
2 forms are used to fill in the details for each record using the primary key on each form.
eg
FORM1
jobid
date
day
time
price
FORM2
jobid
pickup
dropoff
vehicle
is there a way that on FORM1, i can indicate to my user whether or not any details have been entered into FORM2
maybe a colour system.. eg
if table.pickup and table.dropoff and table.vehicle are empty
form indicator on FORM1 is red (to show that none of the details on FORM2 have been entered)
else if table.pickup or table.dropoff or table.vehicle are not empty
form indicator on FORM1 is green (to show that atleast one of the fields on FORM2 has been entered)
it doesnt have to be a colour scheme, it can be wording.. eg
Form2 Empty, or Form2 Completed...
just some kind of sign to my user..
anyone ever tried or used something like this before?
View 3 Replies
View Related
Jan 2, 2007
My customer searches hires a car between two dates - StartDate and EndDate. I want to build an expression that searches my database for all the customers currently using cars (using cars on the day of the search.) How can this be done?
View 2 Replies
View Related
Dec 18, 2007
Hello all, I'm new to this forum.
I'm having problems creating a query for a database (which I am building as part of my university assignment).
The database is a simple ordering system style database, and contains the tables customer, order, order/product and product. I am trying to build a query that can identify customers who have not made an order during the previous week.
I have tried a criterion " <(now()) - 7 " but all this does is show old orders. I am completely stuck. Any help would be appriciated.
In case it is needed here is a list of fields in each of my tables:
Customer:
Customer ID (PK)
Company Name
Company Street Address
Company Town
Company Region
Company Postcode
Order:
Order ID (PK)
Order Taken By
Date (DD/MM/YYYY) ( =now() )
Customer ID (FK)
Delivery Street Address
Delivery Town
Delivery Region
Delivery Postcode
Order/Product:
Order ID (CK)
Product ID (CK)
Quantity Ordered
Product:
Product ID (PK)
Product Description
Product Cost
Any help would be appriciated. Thanks for reading.
View 2 Replies
View Related
Aug 13, 2014
Query which I want to create about finding the date of birth of the customer.
I explain:
I have a table (tblCustomers) which among others contains the field "DateOfBirth". I would like to create a query so that looking into tblCustomer about the dates where the month and day of birth of the client is the same as the Date () so that I know every day which customers have birthdays.
View 5 Replies
View Related
Feb 27, 2006
This is sort of a general how to fix it question. I have a database that holds all of my companies information regarding policies.
For accounting reasons, they have seperated some policies and created an entire new database. So there is Company 01 and Company 02.
I have been asked to create some reports comparing the data in both Companies. So far I've been able to get quick reports out but merging the companies information into a table and running the report from there. The only thing I don't like about that is the information loses it's "Live" factor. It also seems like a amateur way of manipulating data.
An example (one I'm working on now) is my boss wants to know how many policies have been cancelled by our clients and compare them between the two companies. I can get a Count for Company 01 and a Count for Company 02. But when I compare them in a third query, I only show where clients cancelled in both Company 01 and Company 02. I need to show all the clients and 01 and 02. I'm ok with the field showing up blank or Null.
If someone can help with that immediate problem that would be great. But I'm also looking for some insight on how to have these two tables work better together. If I had created the program, I would have had both companies in one database, and just have an ID for Company 01 and Company 02.
Does it seem right to just set up a Make Table query and make some macros to keep it updated? I would have to append one table to the other. And I would have to do this as much as on an hourly basis if my bosses want up to date information.
Can I just say....Grrrrr? Thanks for listening.
View 1 Replies
View Related
Sep 25, 2007
hello guys,
hope you can help cause this is turning me crazy.
it's an inventory issue. i have two tables:
1) StockIN: ProductID, PurchaseDate, PricePurchased
2) StockOUT: ProductID, OrderedDate, QtyOrdered
Throught 01 year, I can make 10 purchases of pens with different prices.
I need to run a query of an individual department's consumption of pens in the month of June for example. I can get the right qty, no problem. But for $$$ purposes, I needed the query to return the most recent PricePurchased as compared to the OrderedDate.
Any ideas??
Thanks in advance!
Tiago.
View 1 Replies
View Related
May 21, 2014
I'm trying to make a query to filter or show only those customers when it is the time for their monthly payment.The query I have consists of four fields which are
1- Order ID
2- Payment
3- Date (Default value set to Date ())
4- Date for next time Payment (Default value set to date () + 30)
I made another field called "states". In this field I putted the following expression
Code:
IIf(([Date for next time Payment]-Date())= 0 "Should Pay";" ")
Then, I set the criteria for such field to "should pay" so that only customer "should pay" will appear in the query datasheet. However, this method has the following shortcomings:
1- It works only for one day (alert day) ,i.e., customers will filtered only when the expression is true.
2- Customers who have paid will still appear as a "should pay" until the day (the day that make the expression true) finish.
3- Customers whose pay late (maybe after 1 week) will disappear form the query datasheet after alert day finish.
To overcome the above shortcomings, i modified the expression to
Code:
IIf(([Date for next time Payment]-Date())<-1 And ([Date for next time Payment]-Date())>-15;"Should Pay";" ")
This method will extend the alert duration to 15 days, so the customers who haven't pay yet will appear in the query datasheet for 15 days. However, the customers who have payed will appear also, and that is the problem. The problem here is I can't compare the current payment date with previous one for one customer. method to create suitable expression, or even another query scheme ??
View 1 Replies
View Related
Dec 4, 2012
I have a database that monitors vehicle maintenance, servicing schedules and stuff like that.
My problem is we purchase items for the vehicles and I am trying to link the items purchased against the vehicles, the purchased items need to show description, cost, invoice number our purchase number, supplier.
The problem comes when we have just stock items recieved that do not have any relation to the vehicles in the main table.
View 1 Replies
View Related
Dec 29, 2014
I hope this amendment to the code for the file attachment that doing a cumulative collection of the items purchased, but only "product code and place of storage," where the code works efficiently with the condition class code but I can not be modified to work two conditions together.
View 2 Replies
View Related
Feb 27, 2007
How do I write an expression to pull data greater than six months from a user entered date.
View 3 Replies
View Related
Jun 28, 2005
Is there something I can put into the criteria of a date box to only use the last 6 months. I dont want have have to do this type of criteria: >12/1/2004. I want it to just use information from the last 6 months no matter what the current month is. If anyone knows what I am trying to say, please help me out! Thanks
Bowes
View 4 Replies
View Related
Nov 29, 2005
I have a combo box that contains the months. They appear in month order on the form but when I query, they are in alphabetical. How can I get this to be in month order on the query?
View 12 Replies
View Related
Jun 1, 2005
Hey Guys,
I've been racking my brains on this one and need your help. I was asked this question by a friend who has just set up a database for a non-profit organisation in the UK. I have attached an simple example database to illustrate the problem.
There is a table called "tblMembers" which contains data on members of clubs and the dates their membership expires.
There is a query called "qryExpiryCrossCheck" and it needs to display people whose membership has expired in the previous month (i.e. May 2005).
Sounds easy? Well the query needs to know that 'now' is June 2005 and that the previous month is May 2005 and then as I said display people whose membership has expired in the previous month.
Any ideas/solutions would be greatly appreciated. As I said this is a favour for a friend who is carrying out some free work for a non-profit organisation.
Cheers,
Rusty
:D
View 3 Replies
View Related
Nov 30, 2005
Ok,
I have a table with 3 fields Line address, Availability, Relevance Date
Every month i put in the data given and i have back from june (so i dont actually have 12 months.
At the moment i am grouping by line address(Row header) and datepart("m",[relevance date]) - (column header) and have the availability as a value and sum. Because i only have data from june it only gives me 6,7,8,9,10. How do i get it to always display 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 and just put the availability into the relevant months and put nothin in the others.
Current SQL: -
TRANSFORM Sum(Monthly_cleansed_data.Availability) AS SumOfAvailability
SELECT Monthly_cleansed_data.[Line Address] AS LineID
FROM Monthly_cleansed_data
GROUP BY Monthly_cleansed_data.[Line Address]
ORDER BY Monthly_cleansed_data.[Line Address]
PIVOT DatePart("m",[Relevance Date]);
Thanks
k0r54
View 2 Replies
View Related
May 30, 2006
I have a field called DATE_END_DEERS which is in a format of YYYYMMDD (20060530). I need to run a query that shows 6 months subtracted from this date. I can never get an answer that is even close. Can someone please help? ACCESS2000.
View 3 Replies
View Related
Dec 12, 2006
I have a little problem
I have an access database containing the following
LibraryYearMonthVisits
Lenzie1999Dec1095
Lenzie1999Feb789
Lenzie1999Mar1293
Lenzie1999Apr1526
Lenzie1999May1231
Lenzie1999Jun1171
Lenzie1999Jul1206
Lenzie1999Aug1287
Lenzie1999Sep1206
Lenzie1999Jan1451
Lenzie1999Nov1706
Lenzie1999Oct1414
Lenzie2000Oct956
Lenzie2000Jan1113
Lenzie2000Feb858
Lenzie2000Mar1110
Lenzie2000Apr1198
Lenzie2000May1203
Lenzie2000Jun1105
Lenzie2000Jul1152
Lenzie2000Aug916
Lenzie2000Sep985
As you can see the months are in order alphabetically. How can I fix my query so the months can be in the right order. Jan,Feb,Mar etc?
I imagine it is a simple procedure but I am not sure how. PLEASE HELP :confused:
Derek
View 3 Replies
View Related
May 22, 2007
I have a query that I need to always pull data from the newest past 3 months, so if this query were ran today it would take from months 2,3,4 (Feb, Mar, Apr). Any clue on how to do this?
View 14 Replies
View Related
Apr 10, 2008
Hello Everyone,I have a query where I am trying to pull records where the user took a class that is 2.5 years old or newer based on their license expiration date. Can someone tell me what I'm missing/doing wrong?SELECT tblCE_Details.LIC_NUMBER, tblCE_Details.Course_Code, tblCE_Details.Course_Date, sociwork1.expirat_dtFROM Sociwork1 INNER JOIN tblCE_Details ON Sociwork1.LIC_NUMBER = tblCE_Details.LIC_NUMBERWHERE (tblCE_Details.LIC_NUMBER = Forms![SW form]!SLIC_NUMBER) AND tblCE_Details.Course_Date >= (sociwork1.expirat_dt - Month(30))ORDER BY tblCE_Details.Course_Date;Thanks,Crhodus
View 3 Replies
View Related
Nov 30, 2005
I have a combo box that contains the months. They appear in month order on the form but when I query, they are in alphabetical. How can I get this to be in month order on the query?
View 2 Replies
View Related
Jun 23, 2014
I want to create a cosstab query with dates for the next 12 months accross the top as columns and employee names down the side as rows. I then want to populate with data showing what each person is programed to do under the dates. For instance trainer 1 is delivering training from the 23/06-30/06 then designing a course from 02/07-10/07. that type of thing.
I could represent an activity using a colour i.e. yellow for delivery, green for design etc.
Also do I need to create a table with all the dates? Is there a quick way of doing this?
View 7 Replies
View Related
Oct 8, 2014
I'm having problems figuring this one out -- I'm fairly new to access. I have included a JPG attachment that shows the information I currently have in use and what I would like. I need a SQL statement that will generate the rolling 12 months by period.
The end results will be a table that is populated with the rolling 12 month values so I can qry a sharepoint infopath form to look up the rolling value (look up against lng_PERIOD and chr_EE_RACF) to populate the YTD values.
View 4 Replies
View Related
May 11, 2012
I have a query to calculate the monthly depreciation for a machine for specific period of time.
My problem is how to get the number of months for each period assuming i want to start from month September 2009 till may 2012.
Attached the data extracted by the query.
I want to calculate the depreciation to be grouped year by year in report.
[TD][TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
View 3 Replies
View Related
Sep 3, 2005
Hi, i have a query which looks at a table named "attendance" the query includes the fields: "employee number", "absent code" (a 3 letter code), "absent reason" (list of different reasons) "shiftdate" (weekly shift dates in format: DD/MM/YYYY). If possible i would like to be able to promt the user to enter a employee number, then a absent reason (ideally from a list box) and show all occurences (with all the shiftdates) only of that particular absent reason within the last 12 months, and ideally calculates in another field the number of times that employee has been absent for that particular reason for the last 12 months.
Does this make sence?, i know how to promt the user to enter a employee number but not sure how to go about the rest, any help with what seems to be quite a challenging task to me would be excellent.
View 4 Replies
View Related
Jul 13, 2007
Can someone help?
I need the code for a filter or query which will show the past x (12, 24 etc) months worth of data. Each record has a date tag so i need a filter which will just show records for say the past 3 months.
Any ideas?
View 4 Replies
View Related
