Queries :: How To Find 1st Date Customer Used
Dec 2, 2014I have a database with 8 years of info on customers orders. I need to find out when each customer first used our services.
View Replies
ADVERTISEMENT
Queries :: Show Only Most Recent Date For Each Customer
Nov 4, 2014I have a query that selects a group of customers and order dates.
I want the query to only show the most recent date for each customer.
How would i achieve this?
Newbie Needs To Find Out How To Remove Autonumber From Access Customer Order Template
May 19, 2006I am trying to use the template provided in the Access database wizards, pages and projects. Its called Order Entry. What i would like to do is edit the template to fit my needs but i need to change the Product ID feild from Autonumber to a product id of my own. I would however like to keep it as the Key.
Any Suggestions?
Mulith
Newbie Needs To Find Out How To Remove Autonumber From Access Customer Order Template
May 19, 2006I am trying to use the template provided in the Access database wizards, pages and projects. Its called Order Entry. What i would like to do is edit the template to fit my needs but i need to change the Product ID feild from Autonumber to a product id of my own. I would however like to keep it as the Key.
Any Suggestions?
Mulith
Queries :: Find Latest Date
Jan 18, 2014I have a list of project, each of which have dates which work were carried out on them. Each project can have more than one date. I want to be able to find the last date that any work was carried out, and then calculate how many days have passed since that happened.
View 6 Replies View RelatedQueries :: If Statement - Find Cumulative Forecast Up Until A Best Before Date
May 22, 2013I want my query to find the cumulative forecast up until a best before date.
This is fine if a forecast goes past the best before date as the query picks it out but if the forecast doesn't continue then it won't match them up.
I need the IF statement to say that IF no forecast is present then look at the last cumulative forecast.
How would I go about this?
Queries :: Find Result In A Table Containing Highest Date Value - Too Many Results
Jul 6, 2013Setup a query to find the result in a table containing the highest date value.
The query is linked to two tables : Payment information containing the date, and tenant information containing the tenant.
In the query i have selected the tenant name from the payment table (which is linked to the tenant name in the payment table) and the payment terms - ie weekly / monthly etc. I've then selected the payment date from the payments table.
The query should return for each tenant the latest date they paid.
On the pay date i selected the Max option.
But it shows me more than one record.
SQL query is shown here
SELECT Max(tblPayments.DateDue) AS MaxOfDateDue, tblLease.cboPaymentTerms, IIf([cboPaymentTerms]=2,DateAdd("ww",1,[DateDue]),IIf([cboPaymentTerms]=3,DateAdd("ww",2,[DateDue]),IIf([cboPaymentTerms]=4,DateAdd("ww",4,[DateDue]),IIf([cboPaymentTerms]=1,DateAdd("m",1,[DateDue]),"n/a")))) AS calcNextPayDueDate, tblPayments.cboTenant
FROM tblPayments INNER JOIN tblLease ON tblPayments.cboTenant = tblLease.cboTenant
GROUP BY tblLease.cboPaymentTerms, IIf([cboPaymentTerms]=2,DateAdd("ww",1,[DateDue]),IIf([cboPaymentTerms]=3,DateAdd("ww",2,[DateDue]),IIf([cboPaymentTerms]=4,DateAdd("ww",4,[DateDue]),IIf([cboPaymentTerms]=1,DateAdd("m",1,[DateDue]),"n/a")))), tblPayments.cboTenant;
Queries :: Calculated Field To Find Latest Date For Each Record
Jul 11, 2015If I have four date Fields in a query, Astart, Bstart, Cstart, and Dstart and want to have a calculated field to find the latest date for each record how would I do that? I have tried things like:
LatestDate: MAX(Astart, Bstart, Cstart, Dstart).
Queries :: Find Latest Date In A Table Where Dates Are In 2 Separate Columns And Multiple Rows
May 19, 2015I am trying to find the latest date in a table where the dates are in 2 separate columns and multiple rows. (there are business reasons why there are 2 dates per row they represent different but comparable activities)
I have a table "Assessment tracker" with the following structure
Name Type
Candidate short text
Unit short text
EV1 Date Date
EV2 Date Date
My Data:
Candidate Unit EV1Date EV2 Date
TH1 10 07/05/2015 25/05/15
TH1 10 07/05/2015 07/06/15
I have a query "Candidate AC Dates" that compares the 2 dates EV1 and EV2 and outputs a 3rd column with the latest date.
Query:
PARAMETERS [Candidate Name] Value;
SELECT [Assessment Tracker].Candidate, [Assessment Tracker].Unit, [Assessment Tracker].[EV1 Date], [Assessment Tracker].[EV2 Date], Max(MaxDate([Assessment Tracker]![EV1 Date],[Assessment Tracker]![EV2 Date])) AS Achdate
FROM UnitData INNER JOIN [Assessment Tracker] ON UnitData.Unit = [Assessment Tracker].Unit
[Code]....
Output:
CandidateUnitEV1 DateEV2 DateAchdate
TH11007/05/2015 25/05/201525/05/2015
TH11007/05/2015 07/06/201507/06/2015
It does this by using a function shamelessly copied from the web somewhere...
Function Maxdate(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Date' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)
' Cycle through each value from the row to find the largest.
[Code]....
This is working well (I think)
I then want to find the latest date for the 2 records i.e. the Max value for the Achdate.
Query:
SELECT [Candidate AC Dates].Candidate AS Expr1, [Candidate AC Dates].Unit AS Expr2, Max([Candidate AC Dates].Achdate) AS MaxOfAchdate
FROM [Candidate AC Dates]
GROUP BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit
ORDER BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit, Max([Candidate AC Dates].Achdate) DESC;
But this is returning
Candidate Unit MaxOfAchdate
TH1 1025/05/2015
I expect it to return
Candidate UnitMaxOfAchdate
TH1 10 07/06/2015
It looks to me like MAX is considering only the day value rather than the whole date. I suspect this is because it is considering the results of the function in the first query as a short text rather than a date field. (I've tried to force this through declaring the variables as dates but don't know where else to force this. (I am UK based hence the DD/MM/YYYY format)
Queries :: Find Date In Table 1 Closest To Another Date In Table 2?
Mar 17, 2015I have a table, tblVisits, holding patient's pre and post surgery visits:
Code:
PatientID VisitDate
1 1/5/12
1 3/10/12
1 9/1/13
2 ...
And another table holding patient's surgeries (each patient will have only one surgery)
Code:
PatientID SurgeryDate
1 4/1/12
2 ...
I need to compare these two tables and create a variable that indicates which pre-surgery visit date (i.e., VisitDate < SurgeryDate) is closest to the surgery date. In the above example, it would return:
Code:
PatientID VisitDate ClosestToSurgery
1 1/5/12
1 3/10/12 Yes
1 9/1/13
2 ...
I've tried various MIN and MAX approaches and can't seem to get it right.
General :: Create Check On A Field (customer ID) Of Customer Table In MS Access?
Sep 7, 2012create a check on a feild(customer id) of customer table in MS Access , as "Customer ID is of 8 characters, the first 4 are alphabets and the last 4 are digits"
View 6 Replies View RelatedAdd Customer Name Into Table Each Time Unique Customer Added To SaleTable
Dec 11, 2013I have a Table Sales - that lists customer and order information
Most of the customers are new - is there a way to populate Customer Name Table with a new entry each time a new customer is input into the Sales Tale
(Without having to add the custome to Customer Name Table - then going to Sales Order)
OR
Failing that - is there a way to use a list box that points to Customer Name Table (and if it doesn't find the one you want, will allow you toadd a new record) In the SAME form as you use to add to Sales Table.
So:
Date (textbox linking to form.Sales) Customer Name (List Box linking to form.CustomerName + adding the info to form.Sale)
Should Be Easy For Someone - Show Only Last Customer Order, By Date?
Feb 22, 2006Hi all, i have a query which will be used for reports, and only the last order placed by a customer is needed to be shown. Here's the table used in the query:-
tblCustomerPurchases
CustomerNumber
PurchaseNumber - PK,Autonumber
DateOfPurchase
SeasonID
TotalCost
So obviously for one customer there can be several orders, but i just want to show the latest one by the date field. I have tried grouping by the date field on both Last and Max, but with all the other fields from the table in the query (i need to have all the fields from the table for the report by the way) it still shows all the orders for a customer.
If i only include the customernumber field and dateofpurchase field and then group by date i have 4923 records, which is the correct amount, only one order per customer (the latest one) but adding any other fields to the query gives me the full 7000 odd orders (several per customer).
Any help would be much appreciated, many thanks in advance.
Getting Several Records For One Customer In Fields Of A Table Based On Date
Oct 26, 2011I am trying to create a database for farm auditing. The idea is you gather information monthly from the same farms and enter this into the database.So as time pogresses for example, farm 1 will have 6 entries for each field in each table, in 6 months time. But i need to be able to see each entry that was put in each month. I do not want it to just show the most recently modified information, and I want it to show the date f these.
View 1 Replies View RelatedCombining Customer And Customer Contact Tables
Dec 24, 2011I have a DB set up with a debtors table (Customers) and a Customer Contacts table, I was thinking of combining these two tables into one.Most of the Debtors are companies, but a few are actual people. With the current setup, i need to have both a debtor and a customer in two different tables, but with exactly the same data.
View 1 Replies View RelatedQueries :: Mail Label With No Duplicate Customer Names
May 6, 2014 I am working with an orders database and want to build a mailing label query that will not print duplicate labels.In the query I have the customer address book and the orders table. I know how to create the query to filter the year I want but how do I add the customer information as to not duplicate the customers and waste labels?
Example:
Order Date Year: ([Order Date]) Last Name
Orders Addressbook
I want to be able to bring up all sales for 2014 but have no duplicate last names.
Queries :: Getting Latest Record For A Particular Customer Number Using MS SQL Query
Jul 27, 2013My table appears like this with the following columns:
Name| CUSTNo| ORderNo.| AcctDate| OrderDate| OrderDescription
I would like to write a ms-sql query to return the records of each customer number with the latest OrderDate. One more thing, if the customer has two separate order numbers on the same date(should be the latest date), the query should be able to pull up both the records.
I tried with the code below but its taking a long time to execute and finally hanging up MS ACCESS.
SELECT * FROM TableName AS a WHERE Not Exists (SELECT *
FROM TableName b WHERE b.CUSTNo = a.CUSTNo AND b.OrderDate >= a.OrderDate);
Queries :: Create Query To List Hires By Customer Age Group
Jan 24, 2014I am doing my project to create data base for Video Hire shop. Was allright so far; however hit the wall now.
I need to create query to list Hires(rentals) by customer age group. I have created query with the following fields: Customer ID, date (Date()), DOB field and calculating field: AgeGroup:Now()-[DOB]. When click to display data in AgeGroup field is displayed in days. How to set it up to display decades not number of days days?
Find Max Random Date In Tbl1 Before Active Record Date In Tbl2
Jul 9, 2013I'm reworking a db to make it web compatible. Right now I'm working on my Price and Sales tables.There are about 900 Sales records, 450 Price records (for about 45 Items).
I have re-done my Price table with an Autonumber Key field. (It had a multi-key which I understand web db does not support.) Each autonumber key represents a Date with new Price for a Company/Item. The Price change Dates are random.
I have put a Foreign Field in my Sales table for the Price key field.
My dilemma is matching the Sales with the Prices.
When the Price Date and Sales Date do not match (at least half of them don't match), I need to look back in the Price table to the max Date BEFORE the Sales Date in the Sales table for that Company/Item in order to select the correct Price key.
Queries :: Show Total Ordered For Week By Fish For Single Customer?
Aug 19, 2014I need a report that show Total ordered for week by Fish for a single customer. I have customer table and OrderDetals table with order date. Shipping date Monday to Friday. When preview the report the it shows the current week Total order quantity for each fish for single customer. How do I get this.
View 2 Replies View RelatedQueries :: Huge Report With Visits Per Customer - Grab Information Of Last Visit?
Dec 2, 2013we have a huge report in our company that includes all our customers, all the visits done by our sales reps, all the products they have and if a product was out of stock in the visit or not.
I want to create a querry that exports a table with 3 columns:
1-->customer
2-->date of last visit
3-->average of out of stock products on the LAST visit
I tried to make a pivot table, managed to grab the last visit (setting the date to maximum) but the average of out of stock products were aggregated totally and not for the last day.
how to handle this in MS access 2010?
Queries :: Using Hidden Main Switchboard To Store Data Such As Current Customer ID
Oct 1, 2013I have a very large Access application and use a hidden "main switchboard" to store data such as the current customer ID. Prior to the advent of tempvars, I embedded a key control into many query's underlying combo boxes, list boxes and forms criteria:
"Forms![main switchboard]![currentcustomerid].
The problem arises when the user quits the application on with forms using this in their underlying query(ies). As far as I can tell, the application closes forms in the order in which they were opened, so the "main switchboard" closes first. Then, for some reason, the current form has a requery and the user is presented with a prompt to enter the parameter "forms![main switchboard]![currentcustomerid]. I tested and found I could replace the criteria reference to a tempvar to "fix" the problem. I wonder if there would be a way to open every query in design view and do a search and replace on the criteria statement.
Modules & VBA :: Find Date Which Is 6 Months Back From Today Date
Jan 27, 2015I want to find out the last 6 months date from todays date. So as todays date is 27th january 2015 so the code should give me the date which is 6 months back from todays date so it will be something like 27th July 2014.
View 1 Replies View RelatedQueries :: Find A Way For Access To Find Unique Dates And Unique Names?
Aug 1, 2014I have been working on a simple data base for some time now (beginner level) and am still trying to improve it. I would like to do something but before that I would like to have your opinion to know if it is even possible?I have a query QryMainReport:
Start Date/Time
End Date/Time
Employee
At the moment this is what the format of my report looks like (I removed other unnecessary fields):
StartTime----------EndTime---------------Employee
12/06/2014 01:00--12/06/2014 03:00------John Smith
12/06/2014 04:00--12/06/2014 06:00------Jane Doe
13/06/2014 02:00--13/06/2014 05:00------John Smith
13/06/2014 08:00--13/06/2014 08:00------Jane Doe
I would like to do as a report. (Dates would always be from Sunday to Saturday). I am not sure it is possible to do that. I suppose first it would mean:I would have to do a query to separate the times from the dates?I would have to find a way for Access to find the unique dates and unique names?Does it mean I have to use cross tab queries?
Find First Date Approval
Mar 28, 2008I have a query based on an SQL database that our company uses to document problems and resolutions. I need to run a report that will show all the reports that were completed in the last 30 day. The field that the approval date is documented is called dbo_approved. Sometimes a report is approved and then at a later date reopened to add something then approved again. The design of this database is such that the first approval is one record and the second approval is a different record. So, using a normal query I will find some records that were previously appproved which I don't care about. I only want a list of reports that were approved for the first time within the last 30 days. Is there a criteria I can use in my query to only look for the first approval date?
As always I appreciate the help.
Jim
Query To Find Last Date
Jan 16, 2015I'm trying to query to find the latest date of a file that has multiple dates. I need the criteria to be the last date, and not just display the last date so I can find the transaction. I thought Dmax may work, but I don't know what my criteria would be. I put an example below. I'm looking for the last date, which would be 5/5/2014. I can't put in the date as the criteria since I am using this for a large amount of files, and the dates differ.
File #
setupdate
transaction
100001
1/1/2014
1
100001
3/5/2014
2
100001
5/5/2014
3