| |

VerySource

 Forgot password?
 Register
Search
View: 10|Reply: 3

Grouping and numbering problems

[Copy link]

1

Threads

2

Posts

3

Credits

Newbie

Rank: 1

Credits
3

 China

Post time: 2020-3-1 08:00:01
| Show all posts |Read mode
The existence table table1 is structured as follows
CW Depth1 Depth2 ....
A 2500 2512
B 2511 2513
A 2711 2080
A 2900 2901
B 1153 1787
Want to group CWs, sort by Depth1
Then add the auto numbering in the group
as follows
CW Depth1 Depth2 ID
A 2500 2512 1
A 2711 2080 2
A 2900 2901 3
B 1153 1787 1
B 2511 2513 2

How to solve the above problems with Access
Reply

Use magic Report

0

Threads

11

Posts

10

Credits

Newbie

Rank: 1

Credits
10

 China

Post time: 2020-5-11 18:15:01
| Show all posts
SELECT *, (select count (*) from a56 where a.cw = cw and a.Depth1> = Depth1) from a56 a order by cw, Depth1
Reply

Use magic Report

0

Threads

2

Posts

3

Credits

Newbie

Rank: 1

Credits
3

 China

Post time: 2020-5-21 13:30:01
| Show all posts
declare @temptable table (
    CW varchar (2),
    Depth1 int,
Depth2 int
)
insert @temptable select 'A', 2500,2512
union select 'B', 2511,2513
union select 'A', 2500,2512
union select 'A', 2711,2080
union select 'A', 2900,2901
union select 'B', 1153,1787


select cw, Depth1, Depth2, ID = (select count (*) from @temptable b where a.depth1> = b.depth1 and a.cw = b.cw group by cw) from @temptable a order by cw, Depth1


result
/ *
CW Depth1 Depth2 ID
A 2500 2512 1
A 2711 2080 2
A 2900 2901 3
B 1153 1787 1
B 2511 2513 2
* /
Reply

Use magic Report

1

Threads

2

Posts

3

Credits

Newbie

Rank: 1

Credits
3

 China

 Author| Post time: 2020-6-15 19:00:02
| Show all posts
I just used the method provided bycairui1, which can be used to get normal results, but it seems that the efficiency is very low. It took at least half a minute to check the wrong 200 data.
Reply

Use magic Report

You have to log in before you can reply Login | Register

Points Rules

Contact us|Archive|Mobile|CopyRight © 2008-2020|verysource.com ( 京ICP备17048824号-1 )

Quick Reply To Top Return to the list