Henry Heuer
asked on
Update Query - Data fill, row numbers
I am a beginner at SQL. I am having trouble with an update query. My goal is quite simple.
I have a database of about 1000 records that I use to keep track of documents to be put in a three-ringed notebook. The report I need is a Table of Contents to be put in the front of the three-ringed notebook.
The report is simple. It has just three columns: 1. Tab Number, 2. Date of Document, and 3. Description of Document.
The report I have created filters out 20 records (based on certain criteria) and then sorts the records by Date of Document. I need an update query to fill in the Tab Number field of the database so that the Tab Number printed is the number of the line item for each of the 20 records printed, e.g., 1 - 20.
If at a later time I add an additional document with a date in the middle of the list, then I will need to re-run the update query to revise the Tab Number for each of the now 21 records to be printed.
First printing with three columns.
1. January 2, 2017 Letter
2. February 2, 2017 Contract
3. March 5, 2017 Deed
4. April 20, 2017 Memorandum
5. . . .
If I add a new document with, say, a date of January 15, 2017, this new document would then be Tab Number 2, and the former Tab 2 and Tab 3 would have to be updated to Tab 3 and Tab 4.
Updated printing with the same three columns.
1. January 2, 2017 Letter
2. January 15, 2017 Another letter (the new entry)
3. February 2, 2017 Contract
4. March 5, 2017 Deed
5. April 20, 2017 Memorandum
6. . . . .
I am having trouble getting the update query to work.
This is what I have so far, using a dummy constant of “15”, to update the contents of the field, Tab-2.:
UPDATE [T-03-01 DC] SET [T-03-01 DC].[Tab-2] = 15
WHERE ((([T-03-01 DC].A_Designation)=Yes));
This works, but of course, every record has the Tab-2 field value: 15.
However, in place of the dummy constant “15”, I need a sequence of numbers to update the field, Tab-2, in each of the 21 records that are to be printed.
My question if this: Is there a command to update the Tab Number field (Tab-2), so that the value of Tab-2 for each record is the line number of the printout.
Thanks in advance for your help.
I have a database of about 1000 records that I use to keep track of documents to be put in a three-ringed notebook. The report I need is a Table of Contents to be put in the front of the three-ringed notebook.
The report is simple. It has just three columns: 1. Tab Number, 2. Date of Document, and 3. Description of Document.
The report I have created filters out 20 records (based on certain criteria) and then sorts the records by Date of Document. I need an update query to fill in the Tab Number field of the database so that the Tab Number printed is the number of the line item for each of the 20 records printed, e.g., 1 - 20.
If at a later time I add an additional document with a date in the middle of the list, then I will need to re-run the update query to revise the Tab Number for each of the now 21 records to be printed.
First printing with three columns.
1. January 2, 2017 Letter
2. February 2, 2017 Contract
3. March 5, 2017 Deed
4. April 20, 2017 Memorandum
5. . . .
If I add a new document with, say, a date of January 15, 2017, this new document would then be Tab Number 2, and the former Tab 2 and Tab 3 would have to be updated to Tab 3 and Tab 4.
Updated printing with the same three columns.
1. January 2, 2017 Letter
2. January 15, 2017 Another letter (the new entry)
3. February 2, 2017 Contract
4. March 5, 2017 Deed
5. April 20, 2017 Memorandum
6. . . . .
I am having trouble getting the update query to work.
This is what I have so far, using a dummy constant of “15”, to update the contents of the field, Tab-2.:
UPDATE [T-03-01 DC] SET [T-03-01 DC].[Tab-2] = 15
WHERE ((([T-03-01 DC].A_Designation)=Yes));
This works, but of course, every record has the Tab-2 field value: 15.
However, in place of the dummy constant “15”, I need a sequence of numbers to update the field, Tab-2, in each of the 21 records that are to be printed.
My question if this: Is there a command to update the Tab Number field (Tab-2), so that the value of Tab-2 for each record is the line number of the printout.
Thanks in advance for your help.
ASKER
Thank you OMC2000 for your help.
I am a new user of the update query function of Access, so I do not understand the basics. I am also a beginner with SQL.
I have Windows 10; Access 2016.
I opened Query1 in design view, then SQL View. Then I copied your suggestion into the SQL View dialog box:
DECLARE @id INT
SET @id = 0
UPDATE [T-03-01 DC]
SET @id = [Tab-2] = @id + 1
GO
When I tried to save the Query1, I got this error message: Invalid SQL statement: expected "delete", "insert", "procedure", "select", or "update" . DECLARE in the first line of your 5 lines of code was highlighted.
Any suggestions as to what I am doing wrong?
Thank you.
I am a new user of the update query function of Access, so I do not understand the basics. I am also a beginner with SQL.
I have Windows 10; Access 2016.
I opened Query1 in design view, then SQL View. Then I copied your suggestion into the SQL View dialog box:
DECLARE @id INT
SET @id = 0
UPDATE [T-03-01 DC]
SET @id = [Tab-2] = @id + 1
GO
When I tried to save the Query1, I got this error message: Invalid SQL statement: expected "delete", "insert", "procedure", "select", or "update" . DECLARE in the first line of your 5 lines of code was highlighted.
Any suggestions as to what I am doing wrong?
Thank you.
Sorry, I thought your question was about MS SQL Server. I don't see direct method to reorder records using SQL statement.
However, you could get ordered records using the following statement
SELECT A.*, (select count(*) from [T-03-01 DC] where A.[Date of Document]>=[Date of Document]) AS RowNo
FROM [T-03-01 DC] AS A
ORDER BY A.[Date of Document];
If you really need to update records in the database, I guess the only way to do it is VBA procedure, where you read record by record with a query like one above and update your table with a new value for [Tab-2] column from RowNo
However, you could get ordered records using the following statement
SELECT A.*, (select count(*) from [T-03-01 DC] where A.[Date of Document]>=[Date of Document]) AS RowNo
FROM [T-03-01 DC] AS A
ORDER BY A.[Date of Document];
If you really need to update records in the database, I guess the only way to do it is VBA procedure, where you read record by record with a query like one above and update your table with a new value for [Tab-2] column from RowNo
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
DECLARE @id INT
SET @id = 0
UPDATE [T-03-01 DC]
SET @id = [Tab-2] = @id + 1
GO
or like the following:
WITH myUpdate ( rnum )
AS
(
SELECT ROW_NUMBER() over (order by [Date of Document]) As rnum
FROM [T-03-01 DC]
-- WHERE something = something
)
update MyTab
set [Tab-2] = 15 + rnum
FROM myUpdate
and you don't need the first column at all, you could get it during query execution
select ROW_NUMBER() OVER (ORDER BY (SELECT [Date of Document])) AS [Tab Number], [Date of Document], [Description of Document]
from [T-03-01 DC]
order by [Date of Document]