I need to create an Autonumber field in a query I am currently running. The query right now calls fields such as names, addresses, phone numbers and is distinct on the client id. Each time I run the query I am calling for different sets of records off of the client id (ie: id's 50000-55000 or 32000-37000). Each time I run the query I need it to number the rows 1, 2, 3, 4, 5, ...5,000.
I hope this is understandable and someone can help.
Thanks
6 58980
If you wish to number your query row results just throw in a COUNT(*) or COUNT([ColumnName]) in your select query.
cheers,
Okay, for example... in a query like: -
SELECT Clients.[Client ID], Clients.[First Name], Clients.[Last Name], Clients.Phone
-
FROM Clients
-
GROUP BY Clients.[First Name], Clients.[Last Name],
-
Clients.Phone , Clients.[Client ID];
-
Where would that "Select Count([Client ID])" go?
It would just be another column in your column selection. -
SELECT COUNT([Client ID]), Clients.[Client ID], Clients.[First Name], Clients.[Last Name], Clients.Phone
-
FROM Clients
-
GROUP BY Clients.[First Name], Clients.[Last Name],
-
Clients.Phone , Clients.[Client ID];
-
cheers,
Okay, so when I add the count, it just gives me a whole column of 1's, like it stops counting at the first record. Is there anyway to incorporate a loop? Or is that not right?
Is this going into a report? You can just use a running sum in the report. If not, you'll need to use a subquery ranking.
Sorry try something like this if it is not in a report. -
SELECT tblB.[Client ID], tblB.[First Name], tblB.[Last Name], tblB.Phone, (SELECT COUNT(*) FROM Clients AS tblA WHERE tblA.[Client ID] <= tblB.[Client ID])
-
FROM Clients
-
GROUP BY tblB.[First Name], tblB.[Last Name],
-
tblB.Phone , tblB.[Client ID];
-
cheers,
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Mike Wiseley |
last post by:
You can create a query in the SQL view that adds a new column to a table.
For example, to add a new text field named X10, you use the following
language:
Alter Table MyTargetTable1
Add Column...
|
by: Apple |
last post by:
1. I want to create an autonumber, my requirement is : 2005/0001
(Year/autonumber), which year & autonumber no. both can auto run.
2. I had create a query by making relation to a table & query,...
|
by: ben.werdmuller |
last post by:
Hi,
Is there an easy way in ASP/VBscript to grab an autonumber (primary
key) field just after an SQL insert? This is probably easy, but I'm
stuck ..
Cheers.
|
by: S. van Beek |
last post by:
Dear reader,
By append a new record to a table I always expect the next higher value in
the range of the autonumber field.
But sometimes if I have deleted same records from a table the...
|
by: marko |
last post by:
I would like my autonumber field to start at 100000. How can i do that?
|
by: HS1 |
last post by:
Hello
I have a table in Access Database. This table has a AutoNumber field. I use
a DataGrid to show that table
When I insert a new record in for this table using a DataGrid, there is a...
|
by: minjie |
last post by:
I need to upgrade a MS Access database (Version 2002) with a script
only, i.e., via SQL statement, not via Access GUI, and I'm having
trouble defining an AutoNumber field. I got an exception error...
|
by: ramzansadiq |
last post by:
Hi guru's,
I m unable to find how to Insert a primary key which is also an autonumber field into Table. My query is as below.It is inserting record if i m hardcoding the ID Field. It is VBA Access...
|
by: Richard Sherratt |
last post by:
I've inherited a system that was designed by someone with no
understanding of database design. From the logical design point of
view, there is no logical design. The physical design is a nightmare....
|
by: Qtip23 |
last post by:
Hello Everyone,
I have been searching for ways to prevent the autonumber field from increasing when a user decides he/she does not wish to enter a record.
I searched Bytes and I think I saw...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |