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
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).
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;
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 RelatedMy 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);
I have a database I want to load tire and brake measurements into. I have created a table which I enter records into, including the date, measurement, the wheel position and the item (tires or brakes) being measured.
On the form I have a picture of a tire and a brake at each wheel position and I would like to be able to display in the control by each item the latest measurement in the main table that matches that wheel position and item.
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...)
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)
Hi All,
I am looking for some help with a project I am working on where I need to automatically print a report from my database every time a new record is added to the table. The table contains 13 fields and the report needs to display 12 of them, the other being the index which is set to Autonumber.
The table is being updated solely by ODBC, this is working OK.
There is the potential for records to be added to the table very quickly via the ODBC link, so I need to safeguard that the report is being populated with correct information from the record that triggered the print event. Also, should multiple records be added in close succession, a report needs to be correctly generated/printed for each of one. The DB is to have no user intervention, and will just run on the PC at startup with all access menus/controls locked out.
Any information on how you think I should structure this, or any examples of helpful code that you might have would be very much appreciated.
Thanks in advance, :)
Jon.
I am trying to create a query or series of queries that will identify the latest record of a field called CheckInDate. The table will have multiple entries. For example.
Book#__________CheckOutDate_____________CheckInDat e
__1_____________01/01/2007_______________03/01/2007
__1_____________04/01/2007_______________05/01/2007
__1_____________01/01/2007_______________
__2_____________01/01/2007_______________02/01/2007
__2_____________02/05/2007_______________04/27/2007
__3_____________01/01/2007_______________03/01/2007
__3_____________05/01/2007_______________07/01/2007
I need the results to be:
#1(ALL) will not be listed, as there is a book still checked out
#2 will show the record with the 04/27/2007(ONLY), as I want the last time the book was checked out.
#3 will show the record with the 07/01/2007(ONLY), as I want the last time the book was checked out.
I currently have a somewhat flat table where this is concerned. Please advise on what I would need to do to create this. Thanks!
I am trying to run a Query that searches for a single Tool ID Number and returns the transaction record that has the latest date. My query currently is this:
SELECT LocationStatus.ToolID, LocationStatus.CurrentLocation, LocationStatus.DateofEvent
FROM LocationStatus
WHERE (((LocationStatus.DateofEvent)=(SELECT MAX(dateofevent) FROM [LocationStatus] AS t2 WHERE t2.[ToolID] = [Tool Number])));
But the problem I am having is that the query is returning MULTIPLE Tool ID numbers with matching dates. Before the query runs a parameter box pops up asking for a Tool ID number but there should only be one record returning. HELP!!!:confused::confused:
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?
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?
The strSql string should find the record that I have already saved in my admin table and then compare it with combusername, where is my mistake.
Private Sub Command7_Click()
'Dim db As Database
Dim strSql As String
'Set db = CurrentDb()
strSql = "Select adminpass From admin"
If ((Nz(txtpassword, "") = "") Or (Nz(combusername, "") = "")) Then
MsgBox "Please Enter A Valid Access", vbOKOnly, "Error"
ElseIf (txtpassword = "admin" And combusername = strSql) Then
DoCmd.Close acForm, "Secure"
DoCmd.OpenForm "Home"
ElseIf (txtpassword = "user" And combusername = "user") Then
DoCmd.Close acForm, "Secure"
DoCmd.OpenForm "Home"
Else
MsgBox "Wrong Password, Please Try Again", vbOKOnly, "Error"
combusername = ""
txtpassword = ""
combusername.SetFocus
End If
End Sub
Hello All...
I'm very new to Access.. so please forgive my ignorance..
Here is the scenario..
I have a table with two fields named "item" & "description" that contains 10 records total.
I have another table with 100 records with the fields "invoice #","item", "price paid","date paid","time paid".
The item fields are linked between the two, and the 100 records contain different invoices for these ten items.
I want to pull the price paid off of the latest invoice, based on the time and date... What is the easiest way to do this?
Thanks!
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.
I am trying to make an on-click event that would open a form showing the last inspection done on a site.
Unfortunately, I cannot even first create a dlookup function to use, so I haven't even attempted the rest!
The data needed to reference is in one table, and I just...can't... quite get it.
Here is my last attempt (which at this point probably isn't my best )
Code:
=DLookUp("InspectionID","tblInspections","SITEID = '" & [Forms]![frmFMHome]![txtSITEID] & "' AND InspectionDate = #" & DMax("InspectionDate","tblInspections","SITEID = '" & [Forms]![frmFMHome]![txtSITEID] & "'") & "#")
After breaking it apart I'm pretty sure the DMax function (and using date?) is the culprit, but I can't figure out why.
How do I find the previous record in a query using the autonumber field?
View 10 Replies View RelatedI have ASSET_TYPE, MANUFACTURER and MODEL tables.
I have a Table ASSET_COMBO that links to the above tables.
I have a form to create amend an ASSETS table. The form uses combo fields to filter the options available to the user. I.E the User Picks Manufacturer then selects from list of Asset Types provided by that Manufacturer, then selects from list of Models.
I want to create a form to allow create and amend records on the ASSET_COMBO Table.
The problem I have is that I want to ensure that there are no duplicates on ASSET_COMBO Table. I know that I can Select Multiple keys from the table with no duplicates allowed. But from what I have read this can cause problems. Is there a simple way that I can detect that a query on the ASSET_COMBO Table has returned a valid record or has not found a record.
In my Access app I need to get the latest record added of an item from a MS Sql table and check the period between now and the date saved in the record.
So my guess is:
Select TOP 1 * from tbl_StockItems
Where StockId = Loc_StockId
I then need to check the days between tbl_StockItems.LastStockDate and Today.
How would I write an Access VBA query to give me the amount of days and put that number into a variable.
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
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!
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
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,
Two of the fields in my query are for Progress Note and Progress Note Date. Each client has several progress notes. How can I have the query show only the Progress Note with the latest date?
:confused:
My table shows
ID ID_Clients EndDate
A particular value of ID_Clients can recur many times, but with different EndDate value each time.
I want to create a select query that displays
ID_Clients EndDate LatestPreviousEndDate
Any help with this would be much appreciated.
-Thanks