Confused about how to go about this query 1

[Watermelon]

Hi,

I don't have a lot of experience with queries and I think I need to use them now. I have a form where a user can enter a catalog and starting catalogpage. After the user
enters this information I want to increase the page number field by 2 for every page >= the starting catalogpage in the catalog the user selected. This I imagine I can do with an update sql statement right under a "Run" or "GO" button.

However, I'm confused about something else I need to do. In addition to updating this table, I also need to update another table (a BillofMaterials table). In this table I need to also increment the catalogpage numbers for the catalog the user selected. The complication is that I need to update them in reverse order. Is there anyway to update
records from bottom to top. In other words, I need to start incrementing the page numbers starting with the highest number and finishing with the lowest.

Would I need to run a separate query where I would first get the records, sort them in descending order and then run another query where I update the records?

Any ideas?

Thanks,
watermelon

 

[tunsarod]

For the sake of this example I have assumed a Table called Catalogues with the following fields:
RecNum as autonumber type
CatNum as number type
PageNum as number type

Create the above table and populate some records like this:

RecNum CatNum PageNum
1 1 1
2 1 2
3 1 3
4 1 4
5 1 5
6 1 6
7 1 7
8 1 8
9 1 9
10 2 1
11 2 2
12 2 3
13 2 4
14 2 5
15 2 6
16 2 7
17 2 8
18 2 9


Now go to the Queries Tab of the Database Window select New.
Then select Design View.
Next Close the Table Selection box without selecting a Table.

This should leave you with the Query Design grid and nothing entered. Switch to the SQL window and enter the following SQL statement.

UPDATE Catalogues SET Catalogues.CatPage = [CatPage]+2
WHERE (((Catalogues.CatNum)>=2));

Switch back to design view and take a good look at the data now entered in the Design Grid. Notice that the query is an Update Query. Before running the query changee it to a Select Query. Click the Query Type button on the Command Bar and choose Select.

Run the query and it'll list the data from the table.

Now modify the query back to an Update type query and run it. Reset the query back to a Select type and review the information. You will see that for all records with CatNum greater than 1 have had the PageNum value increased by 2.

So you can use this as a basis for designing an Update Query to suit the real table and the real data view Form.

To use a value from a field on the Form as a record selection criteria in the query design design grid simply refer to the Form field.

Supposing a Form called "View Cats" has two fields called txtCat and txtPage representing the obvious. And you want to run a query that increments the page numbers for all pages above the currently displayed page number for the selected Catalogue use the following SQL statement:

UPDATE Catalogues SET Catalogues.CatPage = [CatPage]+2
WHERE (((Catalogues.CatPage)>= Forms![View Cats]![txtPage]) AND ((Catalogues.CatNum)= Forms![View Cats]![txtCat]));

Hope this does the trick.

Rod

 

[tunsarod]

Watermelon,

I forgot to mention that when the query is in Select mode you can set the Sort option for the Page number field to Descending. In this case the SQL looks like this:

SELECT Catalogues.CatPage, Catalogues.CatNum
FROM Catalogues
WHERE (((Catalogues.CatPage)>=Forms![View Cats]![txtPage) AND ((Catalogues.CatNum)=Forms![View Cats]![txtCat]))
ORDER BY Catalogues.CatPage DESC;

However if you then change the Select query to an Update query the SQL statement looses the reference to Sort order which seems to indicate that the Sort option is ignored.

In any case the action of the process is so fast that you couldn't tell which direction it is going in.

If it is important then suggest that you create a Function procedure in the Form's code module which opens a recordset based on the Catalogues table and cycles through the records selecting and updating records based on the criteria from the Forms fields.

By using a For Next Loop such as "For X = RecordCount to 1 Step -1 " where RecordCount is first found by taking a count of the records in the table, for example:

RecordCount = DCount("[RecNum]","Catalogues&quot

I suggest you read the examples given in the Access Help file for: Recordsets, editing

It should help you to work up a solution.

Rod

 

[Watermelon]

Tunsarod,

Thanks a lot for that detailed recommendation.
I actually went ahead and did a couple of make
queries though before I read your tip. I created
a temp table. When I wrote out the records to this temp
table I was incrementing the page numbers. I didn't
have to worry about any key violation, like I did previously, because the records were being written
out one a time to the new table. Then, I cleared my old table and copy the new records over to that.

Anyways, maybe next time in a similiar situation, I'll consider your recommendation.

Thanks,
Watermelon

 

[BoxHead]

Rod still deserves a star! John

Use what you have,
Learn what you can,
Create what you need.