By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,988 Members | 1,341 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,988 IT Pros & Developers. It's quick & easy.

Filtering Query

P: 24
I'm confronted by yet another MS Acess Problem, I'm trying to create a query that will select only the first column of every row; the purpose behind this is because there are more than one record in a given field but I'm only wanting to retrieve the most recent entry. Eventually I would put all the data into a report. Anyone have any suggestions? My view of it is to be similar to an excel spreadsheet.
Mar 20 '07 #1
Share this Question
Share on Google+
5 Replies


P: 60
If you are wanting to select just the first column, then use the SQL statement

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM tableName.columnName
  2.  
If, however, you wish to select the most recent row, you can use

Expand|Select|Wrap|Line Numbers
  1. DoCmd.GoToRecord , , acLast
  2.  
Mar 20 '07 #2

P: 24
If you are wanting to select just the first column, then use the SQL statement

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM tableName.columnName
  2.  
If, however, you wish to select the most recent row, you can use

Expand|Select|Wrap|Line Numbers
  1. DoCmd.GoToRecord , , acLast
  2.  
For the SQL statement, what I have is a table with a list of names and corresponding columns containing an ID, an organization, and a status. The organization column holds more than one record, so a person could have belonged to more than one organization.

I'm looking for a way to retrieve the most recent record in such a case; I guess when I said the first column I should have said the first record in each selected column.
Mar 20 '07 #3

P: 24
For the SQL statement, what I have is a table with a list of names and corresponding columns containing an ID, an organization, and a status. The organization column holds more than one record, so a person could have belonged to more than one organization.

I'm looking for a way to retrieve the most recent record in such a case; I guess when I said the first column I should have said the first record in each selected column.
Wait I said it wrong, I need the most recent record from the sub-form which would be the last record in each field. Can you provide some sample SQL code for this? And if possible provide some VBA on how to properly utilize the DoCmd in going to the last record?
Mar 20 '07 #4

P: 60
Let me see if I understand this correctly:

You have a table with 4 rows:
1) Names
2) ID
3) Organization
4) Status
You want to get the last record added to the database and the organization column can have more than one entry.

Is the organization column in a seperate table linked back to the original through a relationship or is it that you just may have multiple organizations with the same names, IDs, and status' attached to it?
Mar 21 '07 #5

P: 24
Let me see if I understand this correctly:

You have a table with 4 rows:
1) Names
2) ID
3) Organization
4) Status
You want to get the last record added to the database and the organization column can have more than one entry.

Is the organization column in a seperate table linked back to the original through a relationship or is it that you just may have multiple organizations with the same names, IDs, and status' attached to it?
The names, IDs, and status are in the parent table linked to another table that holds the organization. I found a solution though, what I found to work was to group by the last organization. Thank you though :-)

The new issue I'm having trouble with is calculating a subtotal for the number of IDs (employees) in an organization (so many people in organization A, B, C, etc) and a subtotal for status (so many people that are X, Y, Z, etc). I'm currently researching it right now, but if there's something you can add I would certainly appreciate it.
Mar 21 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.