Update query to resequence

[Guest]

I am attemping to build a two types of queies to resequence groups of records
in a DB. For example:

Records Now After update Query "A" After update
Query "B"
TSEQ DESC TSEQ DESC TSEQ
DESC
1 A 1 A
1 A
3 B 2 B
2 D
4 C 3 C
3 B
5 D 4 D
4 C
6 E 5 E
5 E

TSEQ is part of a unique key.

Query "A" is intended to remove any gaps in the sequence #

Query "B" is intended to run via an event in a form when the user changes
the TSEQ field.

I have started working on the query and have so far:
UPDATE [To Do List] SET [To Do List].Tseq = [tseq]+1
WHERE ((([To Do List].[To Do_CustomerID])="BAGTO") AND (([To Do List].[Proj
#])=1));

As you can see, I'm not even close, so here are my questons:

1. For "Query A" I assume I have to resequence from highest TSEQ to lowest
TSEQ so I don't duplicate a unique key. So how do I ORDER BY in Descending
order (the query design in MSAccess doesn't give me the option). Also, how
do I get the total # of records the query returned so I know what number to
decrement from?

2. For "Query B" I assume I need to 3 queries. One to give "D" a temporary
TSEQ, second to increment TSEQ for all records with a TSEQ >=2 by one, and a
third to give "D" the now available value of 2.

Being an access newbie, and a query neophite I am open to any and all
suggestions, or even a total change in approach. Actual SQL examples would
be mucho appreicated.

Bottom line is want to remove gaps be able to remove gaps from the sequence,
and want to be able to change the sequence.

Thanks..

 

[Rick B]

If I understand you, you are trying to reorder your data alphabetically by
DESC. Just build a query and sort by DESC. Period.

You seem to be worrying about the order of the records in the table. Tables
have no particular order. Access throws the data in there in any order it
wants. Your queries, forms, and reports are where you worry about order.
If you are opening your table and working from there, then you are missing
out. The tables are not designed to be a user interface.

Rick B

 

[Guest]

I didn't explain very well. Let me try again:

I have a DB with a todo list. Records are as follows:

Task ; Seq #

Wake up ; 1
Go to Bathroom ; 2
Brush Teeth ; 4
Take Shower ; 5
Go to Work ; 6

First, I want an update query to fix the missing number (Seq # 3 is
missing), so now I have:
Wake up ; 1
Go to Bathroom ; 2
Brush Teeth ; 3
Take Shower ; 4
Go to Work ; 5

Second, lets say I want to now brush my teeth AFTER I take a shower. Via a
form, I would change the SEQ # of "Brush Teeth" to "4" (or "Take Shower" to
"3"). Now I need an update query (or queries) to produce this:

Wake up ; 1
Go to Bathroom ; 2
Take Shower ; 3
Brush Teeth ; 4
Go to Work ; 5

Problem is, TSEQ is a unique primary key (in my actual DB, I am using 3
fields for the unique key).

I believe that TSEQ is not a candidate for a calculated field, since I must
store the order the steps occur in.

I hope I explained it better, and look forward to replies!!

If I understand you, you are trying to reorder your data alphabetically by
DESC. Just build a query and sort by DESC. Period.

You seem to be worrying about the order of the records in the table. Tables
have no particular order. Access throws the data in there in any order it
wants. Your queries, forms, and reports are where you worry about order.
If you are opening your table and working from there, then you are missing
out. The tables are not designed to be a user interface.

Rick B

I am attemping to build a two types of queies to resequence groups of records
in a DB. For example:

Records Now After update Query "A" After update
Query "B"
TSEQ DESC TSEQ DESC TSEQ
DESC
1 A 1 A
1 A
3 B 2 B
2 D
4 C 3 C
3 B
5 D 4 D
4 C
6 E 5 E
5 E

TSEQ is part of a unique key.

Query "A" is intended to remove any gaps in the sequence #

Query "B" is intended to run via an event in a form when the user changes
the TSEQ field.

I have started working on the query and have so far:
UPDATE [To Do List] SET [To Do List].Tseq = [tseq]+1
WHERE ((([To Do List].[To Do_CustomerID])="BAGTO") AND (([To Do List].[Proj
#])=1));

As you can see, I'm not even close, so here are my questons:

1. For "Query A" I assume I have to resequence from highest TSEQ to lowest
TSEQ so I don't duplicate a unique key. So how do I ORDER BY in Descending
order (the query design in MSAccess doesn't give me the option). Also, how
do I get the total # of records the query returned so I know what number to
decrement from?

2. For "Query B" I assume I need to 3 queries. One to give "D" a temporary
TSEQ, second to increment TSEQ for all records with a TSEQ >=2 by one, and a
third to give "D" the now available value of 2.

Being an access newbie, and a query neophite I am open to any and all
suggestions, or even a total change in approach. Actual SQL examples would
be mucho appreicated.

Bottom line is want to remove gaps be able to remove gaps from the sequence,
and want to be able to change the sequence.

Thanks..