last row or record

there is no ID in this table.
table1:
col1  col2   col3
-----   ------   -----
aaa   bbb   ddd
aab   jjj      fff
ccc   qqq   ppp

like to get this output:
col1  col2  col3
-----   ------   -----
ccc   qqq   ppp

-- 
Message posted via http://www.accessmonster.com

0
igg
2/3/2010 7:34:26 AM
access.queries 6343 articles. 1 followers. Follow

2 Replies
529 Views

Similar Articles

[PageSpeed] 45

sorry to buttoning in between...

try this...

create form using table, and in form on load properties.

 DoCmd.GoToRecord , , acLast

regards 



"igg via AccessMonster.com" wrote:

> there is no ID in this table.
> table1:
> col1  col2   col3
> -----   ------   -----
> aaa   bbb   ddd
> aab   jjj      fff
> ccc   qqq   ppp
> 
> like to get this output:
> col1  col2  col3
> -----   ------   -----
> ccc   qqq   ppp
> 
> -- 
> Message posted via http://www.accessmonster.com
> 
> .
> 
0
Utf
2/3/2010 9:56:01 AM
Unless there is something in the data that tells you which record is the LAST 
record you are out of luck.  Records are unordered when they are stored in the 
database.

Perhaps you can get what you want with a totals (aggregate) query that uses 
the LAST operator.

SELECT Last(Col1), Last(Col2), Last(Col3)
FROM [YourTable]

That will return the last record retrieved by the query, which is often (not 
always) the last record entered into the table.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

igg via AccessMonster.com wrote:
> there is no ID in this table.
> table1:
> col1  col2   col3
> -----   ------   -----
> aaa   bbb   ddd
> aab   jjj      fff
> ccc   qqq   ppp
> 
> like to get this output:
> col1  col2  col3
> -----   ------   -----
> ccc   qqq   ppp
> 
0
John
2/3/2010 12:40:45 PM
Reply:

Similar Artilces:

Sort does not recognize the header row
I encounter this problem in only one of my worksheets. Others are fine. When I select the upper left data cell (a formatted date cell) the sort does not recognize the header row 1, even though the sort window is set to recognize a header row. The 3 options for the sort order hierachy show the contents of the header row cells, not the column letters. It is as if Excel is selecting the column headers as the header row, rather that the first row of data. I can get around it by inserting a blank row 2 beneath the header row, but I would prefer not to have to do this. The header row is 5...

CombBox to SEARCH and ADD records.
Greetings, To clear up my previous post (much confusement in them!) I have a form where all navigation buttons have been created manually (hidden navigation bar). I want a combo box to allow user to both SEARCH and ADD new values. I can SEARCH with a combo box after using the button wizard (search records option) but cannot change its properties to add new values to the list. Can this be done with form/vba code? Thanks Rob Rob W wrote: > Greetings, > > To clear up my previous post (much confusement in them!) I have a > form where all navigation buttons have been created m...

Can't edit record
I have a Students form that has as its record source the Students table. It has sub form which displays a list of the classes the student has take/is taken along with the name of the teacher and a status flag, StatusID. The Classes.StatusID is a foreign key linked to the Status table. By clicking a button, the classes form is displayed with the record source being a query that reflects all of the classes shown on the Students form. The Students form is still open in the background. Here is the query and the code behind the button. strSQL = "SELECT Classes.* " _ & &quo...

lookup not in row but in matrix #3
Hello Beban a nice job! That's what I wanted. How do I a. include your functions in an existing excel file? b. add my own functions best, Frens : -- fren ----------------------------------------------------------------------- frens's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1505 View this thread: http://www.excelforum.com/showthread.php?threadid=26682 frens wrote: > Hello Beban > > a nice job! That's what I wanted. > > How do I > a. include your functions in an existing excel file? > b. add my own functions > > b...

Frx
I know how to divide a column or multiply a column by a row by using CBR in the row format and using 'x/BASE' in the column format. My questions is if my report has multiple columns how does Frx know which column to pull the BASE data from or does CBR only work with single column reports? ...

Using VBA to change the color of Row and Column Headings
Is it possible to change the color of Excel 2003 column and/or row headings using VBA. I would like to have them appear, under certain conditions, as something other than the dull gray. I know how to inhibit their display but can't figure out how to change the color. Maybe it is not possible. Thank you. "Jack Gillis" <XXXXXXXX@widomaker.com> wrote... >Is it possible to change the color of Excel 2003 column and/or row >headings using VBA. I would like to have them appear, under certain >conditions, as something other than the dull gray. I know how to &g...

programatically insert record
I have a many to many relationship between security clearances and billets. a clearance can be associated with more than one billet and a single billet can be associated with many clearances. That; however, may not have anything to do with my problem. I've built a form that has a listbox that displays the assocaited billets for an employee's clearance. Next to it I put a drop down list box that looks up the list of billets and stores it's value for later use. I've then added an "add" button underneath the drop down list box. What i'd like to do is cho...

Record Macro in Excel 2007 not recording everything
Hi, I've been using Excel 2007 for a few weeks now and I've noticed that the 'Record Macro' functionality is not so complete anymore. For example if you record a macro when adding a picture to your sheet,resizing & moving it, then saving your sheet. The result is the insert & save sheet code. The resizing & moving of the picture is not present in the macro. Another example, add a comment to a cell. Then start recording macro and change font of this comment. Then you got a nice empty macro. I hope this is a setting because otherwise I will go back to Excel 2003. ...

Formatting a Row Question
Is it possible to conditionally format an entire row based on input into one cell in that row, or am I "rowing" upstream? All help on this appreciated, Bryan Yes, perhaps a simple example to illustrate .. Select row1 (click on the row header) Click Format >Conditional Formatting Under Condition 1, make the settings: Formula Is | =$B1=1 Click Format button > Patterns tab > gray > OK Click OK at the main dialog Now key in a "1" into B1, press Enter Row1 will be coloured gray Clear B1, the colour disappears (Note that the $ sign in the formula is import...

How to amalgamate 1000s of rows of data into a usable chart
I have the following data in colums A - D with the headings below. (There are 1000s of rows of data, but only the 4 columns) PhoneNumber_____Status____________________BillableTime_____Transfers 9588_____________Delivered to Answ Mach_____15_____________ 0060_____________Delivered to Answ Mach_____23_____________ 2024_____________No Answer________________0______________ 2024_____________No Answer________________0______________ 2024_____________No Answer________________0______________ 3868_____________Delivered to Answ Mach_____35_____________ 2342_____________Delivered to Voice__________1204__...

one cell in a row contains a number
I have a fixed row of 7 cells, any one of which might contain a number. the cell with the number is the relevant one and it will determine my formula; i will want to multiply the cell value by a number in another cel which is chosen according to the number of the cell in the original row. eg A1:A7- A3 contains 21 formula in A50 will wish to determine 21 x value of cell in column 3 of a range of columns because the number 21 is in cell 3 in the row. I was planning to use V or HLookup I think but cant fathom how to get the cell number form the original row. TIA Chris =SU...

How to get notified when inserting cells, rows, or columns in Exce
I'm making a client to Excel that needs to know if the user inserts cells, rows, or columns in a worksheet. The SheetChange event isn't informative enough. How do I find a proper notification? ...

pasting non-contiguous range of cells to new row, same cell locati
I need a paste macro that will paste a non-contiguous range of cells (ie "A1:B1,G1") to the current row in the same column locations (ie "A5:B5,G5"), assuming current row is 5th row. Does anyone have code for this need that you can share? Try this Sub test() Range("A1:B1").Copy Cells(ActiveCell.Row, "A") Range("G1").Copy Cells(ActiveCell.Row, "G") End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Not excelling at macros" <Not excelling at macros@discussions.microsoft.com> wrote in message news:349879...

CRM 4.0 Bulk Deletion through Data Migration Manager leaving behind records?
Hi, We used the Data migration manager to load entity records and then subsequently used the Bulk Deletion in DMM to delete those records. The migration is showing as "Bulk Deletion Completed" in DMM; however, the entity base table records that are tied to it (per importsequencenumber column) are still showing as deletionstatecode = 0. Other records that we deleted via Bulk Deletion get set to deletionstatedcode = 2. Can someone please advise how I should get rid of these records? At this point must I manually change the deletionstatecode to 2 or physically delete using SQL? Tha...

Select the last cell?
In my macro how can I select the last cell in B Column that has data in it? -- Thanks in advance for your help....Bob Vance .. .. .. .. > In my macro how can I select the last cell in B Column that has data in it? It depends on your version. Either Range("B65536").End(xlUp).Select or Range("B16384").End(xlUp).Select http://support.microsoft.com/default.aspx?scid=kb;en-us;148355 Rgds, Andy cells(rows.count,2).End(xlup).Select -- Regards, Tom Ogilvy "Bob" <enter_address@hotmail.com> wrote in message news:c0pfp3$aa4$1@lust.ihug.co.nz... >...

Change Subform based on record selection
Hi, I have a main form. On this main form there is a subform1 with a list of questions listed in datasheet view. Ideally, I would like there to be another subform2 that updates based on the selected record. For example, in subform1- I select the statement "Enter your age details," then subform2 will change to have a textbox for Age, DOB, Time of Birth, etc. Then when I select another statement from subform1, subform2 will change again. Is there any way to accomplish this? Or is there a better suggestion to gather specific information from a user based on the question t...

Create rows from a lookup
Hi all, I have 2 worksheets containing data that I need to combine, by creating rows WS1 has product data in the range of: Product_ID Name price Options_Ref 1 Product 1 10.00 18 2 Product 2 15.00 25 3 Product 3 5.00 18 4 Product 4 20.00 7 WS2 has the option list data such as: Options_Ref Option_Description 7 Red 7 Blue 18 12" 18 14" 18 16" 25 ...

CRM 3.0 Account Last Contact Date
I can't seem to find out how to filter accounts by last contact date. We need to be able to filter those accounts that have not been contacted recently, so we can call them. ...

Inserting & deleting a row
Can someone help me write a macro that would insert a row of cells above the selected ones on the worksheet? Also one that would delete a selected row of cells. Thanks, Chance Try this Change For the activecell Sub test() ActiveCell.EntireRow.Insert End Sub Sub test2() ActiveCell.EntireRow.Delete End Sub For the selcetion Sub test() Selection.EntireRow.Insert End Sub Sub test2() Selection.EntireRow.Delete End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Chance" <anonymous@discussions.microsoft.com> wrote in message news:114760...

Record with longest entry
I know this may sound a little paculiar, but I need some help to write a query that will list the record that has the longest entry (textual length - Num chars) in a field. I have a table - tbl_contacts with 3 fields of interest - first_name, last_name, email I need to extract the first_name and last_name for the record with the longest email. How can I do this? Thank you. Try this -- SELECT TOP 1 first_name, last_name, email FROM tbl_contacts GROUP BY first_name, last_name, email ORDER BY Len([email]) DESC; -- Build a little, test a little. "QB&quo...

SOS!!! lookup Excel vs Outlook? Record messages to Excel
Hi Everybody! I've got a distribution list of 700 emails. I've sent them an email and got replies. Say, the sender's address is in col A. I need to get the replies recorded to col B, and an indicator of an attachment put into col C - all against the senders email. IS THAT POSSIBLE?? :confused: Thanks in advance for help. I'd be grateful if directed to a similar topic - I failed to find it, and there is a tough deadline coming :eek: ALSO, how can I send an email to all recepients in a folder? Cheers! Lana W -- LanaWhite ---------------------------------------------...

Period end payroll reports/posting liabilities
When trying to post payroll liabilities from the Period End Payroll Reports window, we get the following error for several users: A get/change operation on table "UPR_Distribution_WORK" failed. A record was already locked. We've already tried deleting the following tables: ACTIVITY, UPR10400, SY00800, SY00801, Dex_Lock and Dex_Session. Any ideas? ...

How can I put a formula into every other row tia sal2
Greetings all I'm trying to get excel to calculate a formula for every other row is this possible. Example: Column A1-A9 has 1 thru 9 in it. Column C1 has the formula A1+3 and I would like that formula to be in every other row of C. So C1 would be A1+3 ,C2 would be blank, C3 would be A2+3, C4 would be blank, C5 would be A3+3 and so on. Is this possible? tia sal2 Hi Try =IF(MOD(ROW(),2)=1,A1+3,"") -- Regards Roger Govier "temp@temp.com" <not@not.com> wrote in message news:Xns9890481A7165tenotempcom@127.0.0.1... > Greetings all > > I'm try...

Excel 2007: UDF can't reference row below 65536
In Excel 2007 (I'm on SP2), if I enter this formula: =SUM(A65537) It works fine. But if I enter this formula: =MYUDF(A65537) Excel turns it into this: =MYUDF(#REF!) That isn't the UDF's fault' Excel is doing that before the UDF has the opportunity to do anything at all. Is there a workaround or fix? Thanks, Greg It sounds like you are working in Compatibility Mode. How many rows does your workbook have on a sheet? I created a simple MyUDF function, and it accepts any range up to 1048576 unless the workbook calling it was saved in an earlier ...

Continuous Form
In our system we have students and the classes that they have attended (along with other details). There will now be an opportunity for students to audit classes after they have finished their studies but these students are not to appear in the existing reports. In other words, except for one report listing these students and the auditing classes, there should be no change in the present status of these students (graduated) or the last class attended. I suggested defining a new table consisting of the student_id, the class being audited, the start and end date of the auditing. In t...