Pick Latest Record From Query?

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 Replies


ADVERTISEMENT

Find Latest Record With Query

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

Queries :: Getting Latest Record For A Particular Customer Number Using MS SQL Query

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

Queries :: Subquery To Return Latest / Most Recent Value For Each Record In Main Query

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

Pick Listbox Item To Open Specific Record?

May 19, 2012

i have a list box that fills based on the following code.....

Dim strSQL As String
strSQL = "SELECT Products from [Client ProdVend] " & _
"Where Client_Account_Name = '" & Me.Client_Account_Name & "'"
Me.List91.RowSource = strSQL
Dim strSQL As String
strSQL = "SELECT Products from [Client ProdVend] " & _
"Where Client_Account_Name = '" & Me.Client_Account_Name & "'"
Me.List91.RowSource = strSQL

There is another field in the [Client ProdVend] table called ID. I want to be able to select a product in the listbox, but have that selection open up a form based on the ID field associated with that product. Right now i use this.

DoCmd.OpenForm "ProductDetailsEditor", , , "Products='" & Me.List91 & "' AND Client_Account_Name='" & Me.Client_Account_Name & "'"

The Problem is if there are multiple products with the same name, instead of going to the specific instance of the product(cased on the ID)...it just opens all of the products with that name up, starting with the first one.....

View 1 Replies View Related

Forms :: Code To Pick A Field From A Record Selected Via List

Dec 11, 2014

I have a query with the following structure;

aDate(pkey) cost1 cost2 cost3 calc1 calc2 calc3
01/012014 ,,,,,,,,,1,,,,,, 2,,,,,,, 3,,,,,, 1,,,,,,, 2,,,,, 3
01/02/2014 ,,,,,,,,1,,,,,, 2,,,,,,,, 3,,,,,, 2,,,,,, 4,,,,,, 6
01/03/2014,,,,,,, 1 ,,,,, 2,,,,,,, 3,,,,,,, 3,,,,,, 6 ,,,,, 9
01/04/2014 ,,,,,, 1,,,,,,,, 2,,,,,,, 3 ,,,,, 4,,,,,, 8,,,,,, 12

fields calc1,2,3 are running totals of cost1,2,3

I expect/hope to first calculate the sum of a cost field and then minus the value of its corresponding calc field from a specific record.

result = sum(cost1) - calc1 selected record value

I want to select the calc1 record from a drop down list of the primary key. Which cost field is in the equation will static/defined as I intend to make a textbox for each field.i need to know the code to pick a field(and retrieve it's value) from a record selected via dropdown list.

View 4 Replies View Related

Queries :: Allow User To Pick A Record From Continuous Form And Edit It In A New Window

Apr 3, 2014

I'm using the following code to allow the user to pick a record from a continuous form and edit it in a new window. For some reason my where condition isn't working properly as the edit form always opens to the first record instead of the record associated with the "Edit" button that was pressed.

Code:

Private Sub lblEdit_Click()
DoCmd.OpenForm "frmEditPlants", acNormal, , "[PlantID] = " & Me.PlantID, acFormEdit, acDialog
End Sub

View 6 Replies View Related

Auto Print Report For Only Latest Record On Add New Record Event??

Jun 21, 2005

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.

View 7 Replies View Related

General :: Randomly Pick Record From Table And Display Results In Two Text Boxes?

Sep 1, 2013

I have one table (500 Club) with two fields (ID) (Name) i would like to randomly pick a record from the table and display the results in two seperate text boxes on a form one for (ID) and one for (Name). I intend to use this for a monthly draw at my workplace.

View 6 Replies View Related

Latest Record By Date

May 10, 2007

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!

View 1 Replies View Related

Query Doesnt Pick Up All Records, Please Help!

Feb 19, 2007

I have a query which picks picks up all records from a table. One field however seems to randomly not pick up data from certain records even though the data is in the table.

This has been puzzling me for some time now, does anyone have any ideas / suggestions?

View 2 Replies View Related

Latest Date Record Retrieval

Apr 17, 2008

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:

View 4 Replies View Related

Queries :: Latest Record If Status Is AA?

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

Queries :: Return Latest Record In Table

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

Queries :: Building A Query To Pick Between Set Months / Days But Different

May 4, 2014

I am building a database with Access 2013. The information contains data built from a workplace violence report form. I have to build a query to pick the data but must fall between two different years.The data range must be from 09/01 previous year (ex. 2012) and 08/31 current year (ex. 2013).

As the database collects more information, the year range will change but the other information will stay the same (ex 09/01/2013 to 08/31/2014).I do not want to change the query annually, just let it change the year automatically.

View 1 Replies View Related

Pulling Latest Record Based On Date And Time?

Sep 21, 2007

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!

View 1 Replies View Related

Queries :: Return Latest Record For Multiple Criteria

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

Modules & VBA :: Open Form To Record With Latest Date In A Field

May 12, 2014

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.

View 3 Replies View Related

Queries :: Calculated Field To Find Latest Date For Each Record

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

General :: Get Latest Record Added Of Item - Check Period Between Now And Date Saved

Sep 28, 2014

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.

View 4 Replies View Related

Queries :: Produce Query Using Combo / Pick List Field Held In Main Table

Jun 17, 2014

My computer has been updated to 2010 whilst I've been off sick (was 2003 before my accident).

I've created a main table, for devises across the company, and a combo box/selection box based on another table which holds a list of all the "Responsible" employee's aswell as another combo box/selection box for the device location.

So the person entering the information, can enter all the information for a device (torq wrench, socket set etc), who is responsible for it and which department they belong to (where to find the device).

Which all works fine

However, I'd like to create 2 queries, one to enable the user to run a report of all device's allocated with an employee or to be able to run a query for all device's stored in a particular department.

But I have been unable to set the correct query criteria, to enable to query user to be able to selection from a drop down list, which responsible person or location to pull back the correct list.

I was getting an error asking me to set the parenthesis, I have now deleted criteria for both queries, as even if I put [Enter] and type a Responsible person's name exactly as its held on the table, the report comes back blank.

View 1 Replies View Related

Query Only Latest Info

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

Query Latest User

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

Query To Get Only Latest Entries

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

Query Based On Latest Time

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

Show Only Latest Note Entry In Query

Jan 13, 2008

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:

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved