Query All records except latest

Access 2003

I need to limit my query to ALL RECORDS EXCEPT the most current record.

Any ideas on how to do this
-- 
deb
0
Utf
3/23/2010 7:11:02 PM
access 16762 articles. 3 followers. Follow

2 Replies
998 Views

Similar Articles

[PageSpeed] 26

Deb -

It will look something like this, assuming no more than one record per date 
per key field.  You can adjust to use date/time if that is needed.  It 
depends on what makes your records unique.  Try something like this, 
substituting your table and fieldnames...

Select myKeyfield, field1, field2, field3, myDate
FROM myTable
Where myDate < (Select max(myDate) from myTable AS T2 WHERE T2.myKeyfield = 
myTable.myKeyfield)

-- 
Daryl S


"deb" wrote:

> Access 2003
> 
> I need to limit my query to ALL RECORDS EXCEPT the most current record.
> 
> Any ideas on how to do this
> -- 
> deb
0
Utf
3/23/2010 7:20:01 PM
On Tue, 23 Mar 2010 12:11:02 -0700, deb <deb@discussions.microsoft.com> wrote:

>Access 2003
>
>I need to limit my query to ALL RECORDS EXCEPT the most current record.
>
>Any ideas on how to do this

Access does not know or care which is "the most current" record. You'll have
to define the criteria for that yourself, based on the structure of the table
and the nature of the data. Do you have a sequential ID, or a date/time stamp,
or something else which would let you identify the "current" record?
-- 

             John W. Vinson [MVP]
0
John
3/23/2010 7:55:57 PM
Reply:

Similar Artilces:

Search Function with queries
Hi guys, new user to access 2000 and i am trying to create a search query that will allow me to enter in a value and return that value. The part i am having trouble with is i need to do this for many fields and if i use the method i came up with, when i leave a entry empty using the "AND" function i get nothing and when i use the "OR" function i get all the values for both. maybe you guys can help me get in the right direction. this is the command i am using in SQL: SELECT [Chain Machine prod Machine list].Description, [Chain Machine prod Machine list]...

macro query disable download failure
Hello, I have a macro which retrieve different queries from the web. It happens that a resource at that moment isn''t available. If this happens the querie gives a message which said unable to open http://webpage This message i don't want to display (other routines herefor). I use "Application.DisplayAlerts = False" but this won't help. Can anybody help me with this problem. Thanks ...

Combo box to find records
Hi, need help with what I am sure is a simple problem. I have a form and wish to find records using a combo box. Which I know how to do. The problem is it takes me to "one" record. I would like to select, for example, employee name: John Smith and it return all records for John Smith while filtering out all other records. I would be thankful for any help. Thanks, Michael On Wed, 30 Jan 2008 20:24:04 -0800, Michaelchessking <Michaelchessking@discussions.microsoft.com> wrote: >Hi, need help with what I am sure is a simple problem. > >I have a form and wis...

Nz function in query
Hi All, the query below shows an error if any of the date in / date out entries in the table are empty. How can i impiment the Nz function to stop this. SQL from query... SELECT Employees.[First Name], Employees.[Last Name], Format([Normal start Time],"h:nn AMPM") AS ['Normal Start Time], Format([Normal End Time],"h:nn AMPM") AS ['Normal End Time], Format([scan time in],"h:nn AMPM") AS ['Scan Time In], Format([scan time Out],"h:mm AMPM") AS ['Scan Time Out], Format( [Scan Date In],"dddd dd,mm,yyyy") AS ['Scan Date ...

Count Records
Hi Guys, How can I count records in a a combobox to allow me to scroll using the slider bar without first having to scroll to the bottom of the list? Regards John Count the records in the rowsource of the combobox: NumRecord = DCount("*","NameOfTheRowSource") PC Datasheet Providing Customers A Resource For Help With Access, Excel And Word Applications resource@pcdatasheet.com "John" <johnlee2509@btopenworld.com> wrote in message news:0E1DBE7E-AD54-4715-A547-EC2DDE9CDE39@microsoft.com... > Hi Guys, > How can I count records in a a combobox...

how do i create subset and union in XPath Query
Hi Friends, I need a XPath query, whcih ll return me subset of data.... check below xml stream <label id="MyExpenseDetails_lbl" xlink:role="terseLabel">Short Expense Details</label> <label id="MyExpenseDetails_lbl" xlink:role="displayLabel">Expense Details</label> <label id="InternalExpense_lbl" xlink:role="displayLabel">Internal Expense</label> <label id="ExternalExpense_lbl" xlink:role="terseLabel">Short External Expense</label> See above xml data having 4 label ...

Access 2003 query to Excel 2007 problem?
I have a query that I'd set up a while back for a user on an Access 2003 db. The main table has keys that point to other tables (schools and departments). She needs the results in Excel, so we have been exporting it directly to an excel file (v97-2003), which has been working just fine. However, the other day when I was working with her on this year's stuff, we'd run it once as a test, it worked fine, then I made a minor change to add a field, and re-ran it, and got quite different results. The first time we ran it, the resulting Excel file had "resolved" the k...

After Add Record, Put Cursor in specific Text Box
Hi, I had the wizard set up a button to add record (it puts in the code below in the On Click property of the button). I'd then like to have the focus go to text box [ProjectType]. I don't know how to do that. TIA :) Private Sub cmdAddRecord_Click() On Error GoTo Err_cmdAddRecord_Click DoCmd.GoToRecord , , acNewRec Exit_cmdAddRecord_Click: Exit Sub Err_cmdAddRecord_Click: MsgBox Err.Description Resume Exit_cmdAddRecord_Click End Sub Private Sub cmdAddRecord_Click() On Error GoTo Err_cmdAddRecord_Click If Me.Dirty Then Me.Dirty = False If ...

Pressing Tab to add a new record at the end of one record in a for
Back in Access 97 and 2003, I noticed that when you reach the end of a record in a form and press tab, it used to take you to a new record. But, in Access 2007, it's not doing that. Please let me know what I need to check for to enable this function. There's a form property (Cycle) that affects how a <Tab> affects the screen. You can set it to (re-)cycle back through the fields of the record you're on, or move to the next record. Regards Jeff Boyce Microsoft Office/Access MVP "briangw" <briangw@discussions.microsoft.com> wrote in message news:329198...

Import Access Query to Excel
I am trying to import MS Access Query to Excel. I tried with "Data --> Import External Data --> Import Data" but I found two issues. First, "Select Table" lists only 5 of 15 query tables I made. Second, data range only displays column headings, no record. Both Access and Excel files are on local drive. My computer is on domain network and I heard there is a security issue with Import External Data within domain environment. However, I used to do this on my previous employment within domain environment. I appreciate any help! Thank you! Jonathan Kim Jonathan, wh...

Rank Query With Groups (Array?)
Hello, I have a table of data with grades which are grouped by study. I need to rank the grades relative to all the other grades in the same study. Below is an example the study and grade fields with the desired outcome for the rank. Study Grade Rank 11111 89 1 11111 75 2 11111 65 3 22222 99 1 22222 87 2 Hopefully this can be done with simple SQL in a query and not with code but I am open to anything that works. Many thanks in advance for your time and expertise! Try this -- SELECT Study, Grade, (SELECT Count(*) FROM YourTable AS [XX] WHERE You...

Find 3rd Record
Hi there, I need to find the third record in a query. This is my basic select query: SELECT tblGuests.GID, tblGuests.Date FROM tblGuests ORDER BY tblGuests.GID, tblGuests.Date; There are several thousand records in tblGuests, some Guests (GID) have just one record, others have 20 or more. I've thought of DateDiff function but that doesn't seem to work. I have also thought that I need to find only records where there are more than 2 records for a certain GID so I can filter out the Guests who have only visited 1 or 2 times using the count function but have no idea how to do t...

Exporting all Contacts except 'Private' Contacts
[I posted this query in microsoft.public.outlook.contacts about a week ago but received no responses, so I am trying this newsgroup, as it has heavier traffic.] I would like to provide my contact database to my associates, but I want to first eliminate any Contacts marked 'Private'. In taking a look at the export filters, I don't see any means of accomplishing my objectives. How can I export all Contacts except those marked 'Private'? There is an illistration in OL help re Exporting a partial list of contacts. This may require all the contacts you wish to export being ...

Web Query File Reference
Is there a Web Query File (.IQY) Format Reference anywhere? Thanks, - Vlad ...

Routine to run/export queries
Looking for some links to examples that do the following (or something close): 1. user would create a whole bunch of queries themselves (using Access) 2. form that lets the user runs these queries to display results and/or export 3. Would be good if user could select multiple queries and get multiple resultsets to display or be exported to an excel file with multiple worksheets. Example: user selects 5 queries and wants an excel file made with 5 tabs with the 5 different outputs of the queries, or the outputs of the 5 queries show in a form with multiple tabs in Access (hide...

Still unable to run successful query
I've used the IFF suggestions given but it returns only Not Active results. I'm working on a case problem that reads: Create a query to display all matching records from the tblProgram and tblMember tables, selecting the ProgramType and MonthlyFee fields from the tblProgram table, and the FirstName and LastName fields from the tblMember table. Add a calculated field named MonthlyFeeStatus as the last column that equals Active if the MembershipStatus field is equal to Active and equals Not Active otherwise. -- jj Assuming MembershipStatus is a string (varchar) data field,...

How to ignore records with a duplicate ID based on a value
I hae searched the forum tono avail, so I'll ask for help. I have a table [Scope Event Table] which records events with status changes. The table uses an auto numbered primary key (not shown in the example below). It has data like this: DR_ID Event_Date Reason 12556 01/03/2008 Added 12556 01/24/2008 Closed 12874 01/05/2008 Added 14128 02/09/2008 Added I am a novice at SQL. I am trying to build a query that would pull only the DR_ID’s where the most recent Reason = “Added”. So I would want results to look like this: DR_ID Event_Date Reason 12874 01/05/2008 Added 14128 02/09/2008...

Query Help Please
I have a relational db with a Client Table and a Project Table. The Client table holds general info that usually doesn't change. The Project table has a record for each project we've done for the client, and contains a Manager field. The manager could change from year to year. I want to show by current manager all the other projects for that client (regardless of who previous manager was). Current manager is just the manager on the latest project. I'm guessing I need a query of a query, but I'm stumped. Any advice would be appreciated. Thanks. Do you hav...

SQL Not Working
What is wrong with this SQL? I am trying to use a Median function and trying to have the median calculate for each JobCode. SELECT tEmployeeMasterCopy.JobCode, (SELECT Median("tEmployeeMasterCopy","HourlyRate") FROM tEmployeeMasterCopy as M1 WHERE M1.JobCode = tEmployeeMasterCopy.JobCode) AS MedianByJobCode FROM tEmployeeMasterCopy GROUP BY tEmployeeMasterCopy.JobCode; Thanks in advance. It seems that (SELECT Median("tEmployeeMasterCopy","HourlyRate") FROM tEmployeeMasterCopy as M1 WHERE M1.JobCode = tEmployeeMasterCopy.JobCode) ...

Help Using APPEND Query
Hi, I'm looking to create a "LOG" table. This table will contain 4 fields; 1. SessionID - AutoNumber 2. DateTime - Text 3. User - Text 4. AccessLevel - Text Very simple. Purpose: I can eventually (6months or more from now) create a report to see if more staff/users/admin are accessing the system and what times they are accessing the system, am/pm etc. I have created a public function which determines and returns the username I have created a public function which determines and returns a string identifying their access level. "Student" "Staff" "Adm...

Recording problems
when I record audio on onenote I have trouble hearing the playback. Is there a way to get it louder? I have turned my volume all the way up. I tried using an external mike to record but when I tried to play it back I could hear nothing at all. What can I do? Debbie P. wrote: > when I record audio on onenote I have trouble hearing the playback. > Is there a way to get it louder? I have turned my volume all the way > up. I tried using an external mike to record but when I tried to play > it back I could hear nothing at all. How do things work when recording with some o...

How to properly record attendance?
Access 2k3 I need to record when a student attends class. Date attended, time they logged in, etc. I started to set up this db years ago but the person requesting it decided they did not want it, now they do. Even though I know a hundred fold more than I did then, I still have much to learn. I remember before I posted here and I was creating a record for the student each time they logged in(which will only ever be once per day) in the tblAttendance table. I was told then that this is not the correct way for access to do this. However, for the life of me, I can not figure o...

Make values stay put in established record & reset in new record?
I'm having a problem making the values on a form reset to $0.00 when I go to a new record. Some more info would help us to help you. How are you going to a new record? A button you put on the form? the new record button on the navigation buttons at bottom left of form? What does the value show instead of $0.00 when you go to a new record? Are there any other values that don't reset when you go to a new record? Jeanette Cunningham "Peg" <Peg@discussions.microsoft.com> wrote in message news:B39F82B9-43E2-4B9A-8F9B-C64B46240857@microsoft.com... > I'm having a...

Activity Report/Query Questions
Im trying to get a report out of CRM (whether Crystal or Advanced Find) that will pull all of the contacts that have not had an activity associated with them in the past year. Maybe it's me but i just cannot seem to find a way to do this. This must be a common thing that most companies use so Im figuring it must be possible. Has anyone done a data pull like this before and if so what did you do? Thanks for any help Robert Both advanced find and Crystal reports using the Crystal Enhancements for CRM use fetch XML for queries -- and this is the limiting factor in the type of quer...

Partial match for query criteria
Hello, I am trying to create a query that will deliver results based on matching only a part of the text in a table field. For example, if I have a table that contains the names, address and phone numbers of 10,000 businesses but I only want my query to show me businesses that have the word "National" in their name, how would I do that? I'm not sure how to set up the criteria. Thanks for your help! Use the Like operator with wildcards. In the 'criteria' row of the business name column in query design view enter: Like "*National*" Or you can use a ...