Queries :: Finding Latest Number Using A Query
Jun 19, 2013
I need to find the latest number using a query.
All of the numbers follow this format:
[Doc No]
ABCD123
but the tricky part is the number could be suffixed with a revision. It is the latest revision I would like to find. For example, If I have a number followed by 3 revisions I would have:
[Doc No]
ABCD123
ABCD123+200
ABCD123+300
ABCD123+400
I need a query to give me the latest revision, in this case
[Doc No]
ABCD123+400
There is a possibility there are no revisions in which case the latest will be
[Doc No]
ABCD123.
How is this possible in a query???
View Replies
ADVERTISEMENT
Jul 27, 2013
My 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);
View 2 Replies
View Related
May 29, 2014
I have two tables. The first is called Drawing Register and contains the fields Drawing No (primary field) and Drawing Title.The second table is the Drawing Register Details table which contains the fields Drawing No (joined field - Drawing Register table), Revision, Revision Notes, Date Issued.
I have a query and report which will list all the revisions for each drawing. This is very important and useful. However I want a summary report which will only show the last revision for each drawing.
I copied the original query, turned on the Totals option and under the Date Issued field changed Group by to Max. However it is still returning all results for each drawing instead of only the last issue date for each drawing.
View 14 Replies
View Related
Nov 23, 2014
How do I query from this table:
ORIGINAL_table
FName | END_Date|
--------|---------------|
John| Oct 09, 2014|
John| Oct 15, 2014|
John| Oct 25, 2014|
Mike| Dec 10, 2014|
Mike| Dec 15, 2014|
Mike| Dec 20, 2014|
Mike| Dec 25, 2014|
Jimm| Dec 10, 2014|
Jimm| Dec 15, 2014|
Jimm| Dec 20, 2014|
Alex| Dec 01, 2014|
Alex| Dec 05, 2014|
Alex| Dec 10, 2014|
Thom| Nov 10, 2014|
Thom| Oct 10, 2014|
Thom| Aug 10, 2014|
The output will be:
FName | END_Date|
----------|---------------|
John| Oct 25, 2014|
Mike| Dec 25, 2014|
Jimm| Dec 20, 2014|
Alex| Dec 10, 2014|
Thom| Nov 10, 2014|
The query table(output) will just have to be latest date("END_Date") per "FName"
View 1 Replies
View Related
Apr 26, 2013
I try to make a query that can show me data from the lastest and max date from the table i have ( see attchment file )
I try using max but still the result is not what i am looking for...
View 6 Replies
View Related
May 7, 2014
I have a table of accounts and a table of rates. There is a one-to-many relationship between them (i.e. each account can have multiple rates, each with their own - unique - effective date)
I'm trying to build a query which will show me all of the accounts in the accounts table and the most recent rate (based on the effective date) for each of those accounts.
This is as far as I've gotten with the SQL :
Code:
SELECT [tblAccounts].[AccountID], [tblAccounts].[AccountNumber], [tblAccounts].[AccountName], [LatestRate].[IntRate], [LatestRate].[EffectiveDate]
FROM [tblAccounts]
LEFT JOIN
(SELECT TOP 1 [tblRates].[AccountID], [tblRates].[IntRate], [tblRates].[EffectiveDate]
FROM [tblRates]
ORDER BY [tblRates].[EffectiveDate] DESC) AS LatestRate
ON [tblAccounts].[AccountID] = [LatestRate].[AccountID]
But this can't work because the [LatestRate] subquery can only ever return one record (i.e. the most recent rate across all of the accounts)
I need the most recent rate for each of the accounts in the main query
(FYI - I use an outer join as it is possible for no rate to be available in the rates table for a given account, in which case I want to return the null value rather than omit the account from the resulting dataset...)
View 2 Replies
View Related
Aug 15, 2013
I have a database that is used (partially) to enter appointments during a day. In the appointment column I've entered the date and time as dd-mmm-yyy-hh-nn-ss. I'm now trying to search for appointments entered on a specific day through a user input ([Enter Date] in the query criteria) Problem is, this only returns records where the time hasn't been entered and the time shows as 00:00:00.
I've tried CDate which gives me add/mmm/yyy return. if I try to search against that I get nil returns though. As a test I put DATE() in the criteria but it then says that it's an invalid foremat. I've also tried various machinations of "Like" and "Between" without success.
View 5 Replies
View Related
Jul 25, 2015
How to find the max value of a field in a query... How does a query display the max record based on the primary key... want to isolate the last record created, the one with the max primary key.
View 2 Replies
View Related
Feb 26, 2008
I have a table with about 20.000 records.
From this table I would like to "search" in a column the field that holds the text with the most characters.
Is there a way to find this with a formula or expression?
View 3 Replies
View Related
Mar 23, 2013
I have a table of SOPs/WIs. Each, with time, gets a new version - this I keep in the child table.
tblVersions
------------
VersionID (PK)
SOPID(FK)
Version (integer)
IssueDate (date)
I want to make a list of the currently valid versions, i.e. for each SOPID pick the VersionID with the latest date.
One can do many things with subqueries and Max - functions, but after some experimentation I have stumbled upon this:
Code:
SELECT Last(tblTest.VersionID) AS LastOfVersionID
FROM tblTest
WHERE (((tblTest.IssueDate)<Date()-10))
GROUP BY tblTest.SOPID;
The function Last has a bit tricky reputation and description on the web. It does yields the right result in my testcase though , see the attached db - run query3.
Query3
Code:
LastOfVersionID
100
90
20
40
666
888
55
8
Update:
Well, it isn't. Just changing the VersionID numbers to something other than an ascending sequence renders an incorrect result with the above query. So what is the best way to get the desired result? (Access 2007)
View 4 Replies
View Related
Dec 18, 2014
I'm having trouble with a simple query. They have an access database with a table like this (simplified for this example):
--------------------------------
Name Result Date
--------------------------------
Bob 5 1/1/2014
Bob 6 2/2/2014*
Bob 7 3/3/2015
Bob 8 5/4/2015*
Len 5 1/1/2014
Len 6 2/2/2014*
Len 7 3/3/2015
Len 5 7/4/2015*
---------------------------------
how I would write a query that gives me the latest result that each person earned in each year (I put a * symbol against the rows above that I am talking about)?
Bob 6 2/2/2014
Bob 8 5/4/2015
Len 6 2/2/2014
Len 5 7/4/2015
(The database is normalised and this table has an ID field).
View 5 Replies
View Related
Dec 22, 2014
I have a query with a record id, report date and status.
How do I pull the latest record if the status is AA?
For latest record in report date I used Max in Totals. With just this max it is pulling the latest date for each set of records with the same record id.
This is a start now how do i pull the latest record that has a AA status?
View 14 Replies
View Related
Jan 18, 2014
I 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 Related
Feb 23, 2014
I'm trying to create in access2010
(1) a query that returns the latest record (newest) in a table called 'Invoices' and then
(2) places this value in a form called 'FrmInputInvoices' as the default value when the form opens. Newest record is by Autonumber and the table defaults this to top of table as views newest down to oldest.
Re (1) Query is called 'QInvoices'; the values I want to return in my query is ID (my autonumber) and Invoice_No . Must be a simple answer to put in the criteria, but I can't find this.
Re(2) What code do I use in my Form field named 'Invoice_No when the curser defaults there on opening?
View 4 Replies
View Related
Apr 17, 2015
Have a strings like this
Code:
dsa;hwq;67;dk;71c
Code:
uqiea;762c;iyh
Is there any possibilites to write a function which find number in string, even if some part (between ";" and ";") has number and text (like 762c)?
if there is number in string then function is true
View 5 Replies
View Related
May 29, 2015
Giving up after a zillion tries. I have a table (tblLOADS) containing: BROKER, PUDATE, MATERIAL & DRIVER. I am able to create this query:
SELECT tblLOADS.L_ID, tblLOADS.BROKER, Max(tblLOADS.Pudate) AS MaxOfPudate, tblLOADS.Material, tblLOADS.Driver
FROM tblLOADS
GROUP BY tblLOADS.L_ID, tblLOADS.BROKER, tblLOADS.Material, tblLOADS.Driver
HAVING (((Max(tblLOADS.Pudate))>0));
Problem: It's datasheet view displays all of the records for BROKERS, PUDATE, MATERIAL & DRIVER, instead of ONLY the records for the last PUDATE of each of the BROKERS, with their corresponding MATERIAL & DRIVER fields.
View 12 Replies
View Related
Mar 12, 2014
I have a table that contains readings from several pieces of equipment as well as the status of each one. Each record has a timestamp, equipment number, status, etc. What I want is to create a query that will return the latest record for each equipment number. Simplified example table:
Timestamp EquipmentNumber Status
Today ------Machine1 ----------Running
Today ------Machine2 ----------Running
Yesterday -Machine1 ----------Down
Yesterday -Machine2 ----------Running
There are more than 20 different Equipment numbers and they are read several times per day and sometimes some of them get missed. What I'm looking for is a way to get a list of all the machines with their latest reading so they can tell which machines are running and which are down based on the last time they were read (instead of specifying a date). I can get this for one machine with no problem. I'm having trouble getting it for more than one machine. I tried a union query (with just 2 of the machines included for testing) but it only returns the results from one machine:
Code:
SELECT TOP 1 TestCompressorRoundQuery.LoggedAt, TestCompressorRoundQuery.AssetNumber,
TestCompressorRoundQuery.CompressorID, TestCompressorRoundQuery.Status, TestCompressorRoundQuery.CompressorIntegrity, TestCompressorRoundQuery.Notes
FROM TestCompressorRoundQuery
WHERE (((TestCompressorRoundQuery.AssetNumber)="104399"))
UNION ALL
[Code] ....
I'd rather not have to create a seperate query for each machine and then join all of those together! I think perhaps a Union query might not be the correct approach. All the data is coming from only one table.
View 6 Replies
View Related
Jul 11, 2015
If 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).
View 2 Replies
View Related
Feb 17, 2015
I have a list of archive folders with unquie IDs and images files which have a version history.
I need to remove the history and just keep the latest archive version set of files. It seems an append query would be best but just can't think the best way todo it.
Table Example:
Archive: Folder: User: SubFolder: Filename:
01 TA05420010125-01 8769091 WAG 20010125-01.tif
01 TA05420010125-01 8769091 TIM 20010125-02.tif
02 TA05420110324-01 8769091 WAG 20110324-01.tif
02 TA05420110324-01 8769091 TIM 2011032402.tif
02 TA05420110324-01 8769091 PD 20110324-02.tif
03 TA05420150114-01 8769091 VYE 20010125-01.tif
03 TA05420150114-01 8769091 DFU 20010125-02.tif
In the above example I only want to keep user 8769091 latest filename info from archive 3 and exclude the rest. I need todo this for over 3000 users who could have multiple versions.
View 5 Replies
View Related
Sep 7, 2005
How do I make an append query only append the records beginning with the next autonumber?
I have linked tables in db...one db is for warehouse employees to input their orders and the other table is in the original db for managers to use for reports. I need to append the records to original db throughout the day, but the records cannot be deleted from warehouse db because it keeps running total of order minutes (for production purposes) so employees know how many minutes they have (and need to make up) through the day. Also, managers need up to date db so they can see if they are on track.
The autonumber field is my primary key..
I hope this all makes sense.
Thanks in advance
Noreene
View 3 Replies
View Related
Sep 11, 2006
Hi,
I am working on a report and I am stuck with the following problem:
Here at work we use two tables. One for creating records and for the updates of the created records. In the attachment you see a part of the table 'updated'. What you see is one ServiceNumber (1-551747091) which is changed users (Eigenaar.) three times.
I want to query the latest user of each ServiceNumber. So that the result is:
ServiceNumber | User
1-551747091 | MKB_VERHUIZEN
etc.
How to do that? :confused:
Thanks!
View 3 Replies
View Related
Feb 26, 2007
I am having difficulty formulating this query.
I have 2 tables... 1 is customer info (name, address, etc...), and the second is shipments (ShipToCustomerName, ShipmentDate, etc...).
Now, I wish to query the shipments table to get ONLY the LATEST shipment information for EACH customer.
If customer A has a shipment in Jan 1, Feb 2, and March 3... while customer B has a shipment in April 4 and May 5. then the result of the query should be
customer A - March 3
customer B - May 5
Is there a simple way to construct such a query?!?
Thank you for your help,
- arm
View 1 Replies
View Related
May 19, 2015
I 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)
View 14 Replies
View Related
Apr 7, 2007
I have two fields. A barcode field will have several instances of the same data, and the other field has different dates and times when it was scanned. I need to be able to query the barcode field and only return the record with the latest time. Can this be done in a query or do I need to do something else? Here is a sample of data I am working with. I attached a small sample of data as an example.
Thanks,
View 3 Replies
View Related
Aug 22, 2007
I'm fairly new to SQL and have a problem finding the latest record.
Lets say I have a development project that has many disbursal payments to be sent. I want to find the latest disbursal record created for that one project instead of having all of the disbursal records show on the query.
I'm trying to work out how to do this in a query but haven't got a clue where to start.
Please, if anyone can help I'd be very grateful!:D
View 2 Replies
View Related
Dec 16, 2005
I am trying to pick the latest record from "tblEmpVac.id" field but I keep getting syntax errors. I am thinking if the "tblEmpVac.id" = Count('tblEmpVac.id') then that will show the most current record as it will count and match the last record correct?
Another problem is if I put it into the WHERE section, it will give a conflict. So I don't know where to put it. Below is the SQL, if anyone can help it would be great.
SELECT DISTINCTROW tblEmp.position, tblEmp.fname, tblEmp.lname, tblEmpWorkHistory.region, tblEmpWorkHistory.[current store], tblEmpVac.current_year, tblEmpVac.id, tblEmpVac.entitlement, tblEmpVac.days_taken, tblEmpVac.days_carryover, [days_carryover]+[entitlement]-[days_taken] AS total
FROM ((tblEmp INNER JOIN tblEmpVac ON tblEmp.ssn = tblEmpVac.ssn) INNER JOIN tblEmpWorkHistory ON tblEmp.ssn = tblEmpWorkHistory.ssn) INNER JOIN tblEmpWorkHistoryData ON tblEmpWorkHistory.ewh_id = tblEmpWorkHistoryData.ewh_id
WHERE (((tblEmp.position)<>"Terminated") AND ((tblEmpVac.current_year)="2005"))
ORDER BY tblEmp.position, tblEmpWorkHistory.region, tblEmpVac.id;
View 2 Replies
View Related
