Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / November 2005

Tip: Looking for answers? Try searching our database.

record position in the query

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Mike - 22 Nov 2005 21:51 GMT
I am trying to make a query where I add a new field that returns the record
position (which does not exist in the orinal table).

For example, the table is:
apples
oranges
bananas

When I make the query, I want it to return:
1  apples
2  oranges
3  bananas

Does anyone have any ideas? This would be fabulous!
OfficeDev18 - 22 Nov 2005 21:59 GMT
I don't know of any way to do it in a query, but it's easy to do in a report.

Interested?

>I am trying to make a query where I add a new field that returns the record
>position (which does not exist in the orinal table).
[quoted text clipped - 10 lines]
>
>Does anyone have any ideas? This would be fabulous!

Signature

Sam

Mike - 22 Nov 2005 22:20 GMT
A report may also be an option. Can you help out?

> I don't know of any way to do it in a query, but it's easy to do in a report.
>
[quoted text clipped - 14 lines]
> >
> >Does anyone have any ideas? This would be fabulous!
Jeff Boyce - 23 Nov 2005 00:43 GMT
Mike

Records in Access tables have no inherent order.  If you want to be able to
sort your table rows in some arbitrary order (i.e., not alpha, not numeric),
YOU have to include a field that you fill with the values on which you want
Access to sort.  Then you have to use a query and sort by that field.

Regards

Jeff Boyce
<Office/Access MVP>

>I am trying to make a query where I add a new field that returns the record
> position (which does not exist in the orinal table).
[quoted text clipped - 10 lines]
>
> Does anyone have any ideas? This would be fabulous!
OfficeDev18 - 23 Nov 2005 14:15 GMT
Come to think of it, Jeff has the right idea. Simply add a field to your
table, DataType AutoNumber, and make it the primary key, and make sure its
primary property is set to yes. That will number anything you want. The only
caveat, as Jeff points out, is that if there's no rhyme or reason to the
order, you night have to do some re-numbering manually.

If you'd rather do the report, make your report as you would normally. Then
add an unbound textbox (call it say txtMyCount), and initialize it to count
for you, as follows:

Make a global variable, MyCount, type Long. In the Open event of your report,
initialize MyCount = 0. In the Detail OnPrint event, have the following code:

MyCount = MyCount + 1
Me.txtMyCount = MyCount

Either way will work.

HTH

>Mike
>
[quoted text clipped - 13 lines]
>>
>> Does anyone have any ideas? This would be fabulous!

Signature

Sam

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.