分组号码 [英] Grouping numbers

查看：57
本文介绍了分组号码的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一张桌子，其中列出了球员名单，参赛队伍和他们在那里玩的
年，我的代码看起来像这样。

SELECT AlsoPlayedFor。 playerID，AlsoPlayedFor.teamID，

AlsoPlayedFor.TeamName，Min（[AlsoPlayedFor]。[Year]）& " - " &

Max（[AlsoPlayedFor]。[Year]）AS [Year]

FROM AlsoPlayedFor

GROUP BY AlsoPlayedFor.playerID，AlsoPlayedFor。 teamID，

AlsoPlayedFor.TeamName;


，取最小年份和最大年份，并显示为年 -

年份


但是，比方说玩家玩了5年，所以1990年，

1991,1992,1993,1995


它会显示为1990-1995，但我希望它显示为1990-1993，

1995，这是否可行？另外，如果

年是1990年，1992年，1993年，1994年，1995年我希望它显示为1990，

1992-1995。 br />

请帮助

解决方案

3月3日，晚上11:54，克里斯 ; < chrislab ... @ gmail.comwrote：


我有一个表格，列出了球员名单，参赛队伍以及

年他们在那里玩，我的代码看起来像这样


SELECT AlsoPlayedFor.playerID，AlsoPlayedFor.teamID，

AlsoPlayedFor.TeamName，Min（[AlsoPlayedFor]。[年份]）& " - " &

Max（[AlsoPlayedFor]。[Year]）AS [Year]

FROM AlsoPlayedFor

GROUP BY AlsoPlayedFor.playerID，AlsoPlayedFor。 teamID，

AlsoPlayedFor.TeamName;


，取最小年份和最大年份，并显示为年 -

年份


但是，比方说玩家玩了5年，所以1990年，

1991,1992,1993,1995


它会显示为1990-1995，但我希望它显示为1990-1993，

1995，这是否可行？另外，如果

年是1990年，1992年，1993年，1994年，1995年我希望它显示为1990，

1992-1995。 br />

请帮助



克里斯。我看到你还在努力解决这个问题。给这个

a尝试：


1.将以下代码粘贴到Access数据库中的新模块中：


Public Function ListYears（playerID，teamID）

Dim _

rst作为DAO.Recordset，_

prevYear As Integer，_

seqStart As Integer，_

strList As String


设置rst = CurrentDb.OpenRecordset（_

" ; SELECT [Year] FROM [AlsoPlayedFor]"& _

" WHERE [playerID] ="& playerID&""& _

" AND [teamID] ="& teamID&""& _

" ORDER BY [Year]"，_

dbOpenSnapshot ）

strList =""

prevYear = 0

seqStart = 0

Do While Notrst。 EOF

如果是rst！年< prevYear + 1那么

strList = strList& _

IIf（seqStart< prevYear，" - "& prevYear，""）& _

"，" &安培; _

rst！年

seqStart = rst！年

结束如果

prevYear = rst！年

rst.MoveNext

循环

rst.Close

Set rst = Nothing

ListYears = Mid（strList，3）

结束函数

2.将查询更改为


ListYears（[playerID]，[teamID]）AS Years

FROM AlsoPlayedFor

GROUP BY AlsoPlayedFor.playerID，AlsoPlayedFor.teamID;

这样做吗？


3月4日上午6:32，Gord < g ... @ kingston.netwrote：


3月3日晚上11点54分，Chris < chrislab ... @ gmail.comwrote：



我有一张桌子，列出了球员名单，球队和<他们在那里玩了
年，我的代码看起来像这样


SELECT AlsoPlayedFor.playerID，AlsoPlayedFor.teamID，

AlsoPlayedFor.TeamName，Min（[AlsoPlayedFor]。[Year]）& " - " &

Max（[AlsoPlayedFor]。[Year]）AS [Year]

FROM AlsoPlayedFor

GROUP BY AlsoPlayedFor.playerID，AlsoPlayedFor。 teamID，

AlsoPlayedFor.TeamName;


取最小年份和最大年份并显示为年 -

年


但是举个例子说玩家玩了5年所以1990年，

1991,1992,1993,1995


它会显示为1990-1995，但我希望它显示为1990-1993，

1995，这是possiable ???另外，如果

年是1990年，1992年，1993年，1994年，1995年，我希望它能显示为1990年，

1992-1995。


请帮助



克里斯。我看到你还在努力解决这个问题。给这个

a尝试：


1.将以下代码粘贴到Access数据库中的新模块中：


Public Function ListYears（playerID，teamID）

Dim _

rst作为DAO.Recordset，_

prevYear As Integer，_

seqStart As Integer，_

strList As String


设置rst = CurrentDb.OpenRecordset（_

" ; SELECT [Year] FROM [AlsoPlayedFor]"& _

" WHERE [playerID] ="& playerID&""& _

" AND [teamID] ="& teamID&""& _

" ORDER BY [Year]"，_

dbOpenSnapshot ）

strList =""

prevYear = 0

seqStart = 0

Do While Notrst。 EOF

如果是rst！年< prevYear + 1那么

strList = strList& _

IIf（seqStart< prevYear，" - "& prevYear，""）& _

"，" &安培; _

rst！年

seqStart = rst！年

结束如果

prevYear = rst！年

rst.MoveNext

循环

rst.Close

Set rst = Nothing

ListYears =中（strList，3）

结束功能


2.将查询更改为


SELECT AlsoPlayedFor。 playerID，AlsoPlayedFor.teamID，

ListYears（[playerID]，[teamID]）AS Years

FROM AlsoPlayedFor

GROUP BY AlsoPlayedFor.playerID，另外播放了.teamID;


这样做吗？ - 隐藏引用的文字 -


- 显示引用的文字 -



我在表达式中收到undefined function'ListYears'的错误。


3月4日，上午9:15，克里斯 < chrislab ... @ gmail.comwrote：


3月4日上午6:32，Gord < g ... @ kingston.netwrote：


3月3日晚上11点54分，Chris < chrislab ... @ gmail.comwrote：


我有一张桌子列出了球员名字，球队参赛为了和他们在那里玩的
年，我的代码看起来像这个


SELECT AlsoPlayedFor.playerID，AlsoPlayedFor.teamID，

AlsoPlayedFor.TeamName，Min（[AlsoPlayedFor]。[Year]）& " - " &

Max（[AlsoPlayedFor]。[Year]）AS [Year]

FROM AlsoPlayedFor

GROUP BY AlsoPlayedFor.playerID，AlsoPlayedFor。 teamID，

AlsoPlayedFor.TeamName;


取最小年份和最大年份并显示为年 -

年 


但是举个例子说玩家玩了5年所以1990年，
1991,1992,1993,1995


它将显示为1990- 1995年，但我希望它显示为1990-1993，

1995，这是否可行？另外，如果

年是1990年，1992年，1993年，1994年，1995年，我希望它能显示为1990年，

1992-1995。


请帮助


克里斯。我看到你还在努力解决这个问题。给这个

a尝试：


1.将以下代码粘贴到Access数据库中的新模块中：


公共函数ListYears（playerID，teamID）

Dim _

th As DAO.Recordset，_

prevYear As Integer，_

seqStart As Integer，_

strList As String


Set rst = CurrentDb.OpenRecordset（_

" SELECT [Year] FROM [AlsoPlayedFor]& _

" WHERE [ playerID] ="& playerID&""& _

" AND [teamID] ="& teamID&""& _

" ORDER BY [Year]"，_

dbOpenSnapshot）

strList =""

prevYear = 0

seqStart = 0

做N otrst.EOF

如果是rst！年< prevYear + 1那么

strList = strList& _

IIf（seqStart< prevYear，" - "& prevYear，""）& _

"，" &安培; _

rst！年

seqStart = rst！年

结束如果

prevYear = rst！年

rst.MoveNext

循环

rst.Close

Set rst = Nothing

ListYears = Mid（strList，3）

结束函数


2.将查询更改为


SELECT AlsoPlayedFor.playerID，AlsoPlayedFor.teamID，

ListYears（[playerID]，[teamID]）AS Years

FROM AlsoPlayedFor

GROUP BY AlsoPlayedFor.playerID，AlsoPlayedFor.teamID;


这样做吗？ - 隐藏引用的文字 -


- 显示引用的文字 -



我在Expression中收到一条错误，上面写着未定义的函数，ListYears。



1.您是否将VBA代码粘贴到新的访问模块（常规

模块，而不是类模块）并保存？


2.您是否在Access中运行查询？


I have a table which lists player names, teams played for and the
years they played there and my code looks like this

SELECT AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,
AlsoPlayedFor.TeamName, Min([AlsoPlayedFor].[Year]) & "-" &
Max([AlsoPlayedFor].[Year]) AS [Year]
FROM AlsoPlayedFor
GROUP BY AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,
AlsoPlayedFor.TeamName;

which takes the Min year and the Max Year and displays it like "Year-
Year"

But lets say for example the player played for 5 years so it 1990,
1991, 1992, 1993, 1995

It would display as 1990-1995 but I want it to display as 1990-1993,
1995, is this possiable??? Also I need it to gothe other wayso if the
years are 1990, 1992, 1993, 1994, 1995 I want that to display as 1990,
1992-1995.

PLEASE HELP

解决方案

On Mar 3, 11:54 pm, "Chris" <chrislab...@gmail.comwrote:

I have a table which lists player names, teams played for and the
years they played there and my code looks like this

SELECT AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,
AlsoPlayedFor.TeamName, Min([AlsoPlayedFor].[Year]) & "-" &
Max([AlsoPlayedFor].[Year]) AS [Year]
FROM AlsoPlayedFor
GROUP BY AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,
AlsoPlayedFor.TeamName;

which takes the Min year and the Max Year and displays it like "Year-
Year"

But lets say for example the player played for 5 years so it 1990,
1991, 1992, 1993, 1995

It would display as 1990-1995 but I want it to display as 1990-1993,
1995, is this possiable??? Also I need it to gothe other wayso if the
years are 1990, 1992, 1993, 1994, 1995 I want that to display as 1990,
1992-1995.

PLEASE HELP

Hi, Chris. I see you''re still struggling with this problem. Give this
a try:

1. Paste the following code into a new Module in your Access database:

Public Function ListYears(playerID, teamID)
Dim _
rst As DAO.Recordset, _
prevYear As Integer, _
seqStart As Integer, _
strList As String

Set rst = CurrentDb.OpenRecordset( _
"SELECT [Year] FROM [AlsoPlayedFor] " & _
"WHERE [playerID]=" & playerID & " " & _
"AND [teamID]=" & teamID & " " & _
"ORDER BY [Year]", _
dbOpenSnapshot)
strList = ""
prevYear = 0
seqStart = 0
Do While Not rst.EOF
If rst!Year <prevYear + 1 Then
strList = strList & _
IIf(seqStart <prevYear, "-" & prevYear, "") & _
", " & _
rst!Year
seqStart = rst!Year
End If
prevYear = rst!Year
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
ListYears = Mid(strList, 3)
End Function
2. Change your query to

SELECT AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,
ListYears([playerID],[teamID]) AS Years
FROM AlsoPlayedFor
GROUP BY AlsoPlayedFor.playerID, AlsoPlayedFor.teamID;
Does that do it?


On Mar 4, 6:32 am, "Gord" <g...@kingston.netwrote:

On Mar 3, 11:54 pm, "Chris" <chrislab...@gmail.comwrote:


I have a table which lists player names, teams played for and the
years they played there and my code looks like this

SELECT AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,
AlsoPlayedFor.TeamName, Min([AlsoPlayedFor].[Year]) & "-" &
Max([AlsoPlayedFor].[Year]) AS [Year]
FROM AlsoPlayedFor
GROUP BY AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,
AlsoPlayedFor.TeamName;

which takes the Min year and the Max Year and displays it like "Year-
Year"

But lets say for example the player played for 5 years so it 1990,
1991, 1992, 1993, 1995

It would display as 1990-1995 but I want it to display as 1990-1993,
1995, is this possiable??? Also I need it to gothe other wayso if the
years are 1990, 1992, 1993, 1994, 1995 I want that to display as 1990,
1992-1995.

PLEASE HELP


Hi, Chris. I see you''re still struggling with this problem. Give this
a try:

1. Paste the following code into a new Module in your Access database:

Public Function ListYears(playerID, teamID)
Dim _
rst As DAO.Recordset, _
prevYear As Integer, _
seqStart As Integer, _
strList As String

Set rst = CurrentDb.OpenRecordset( _
"SELECT [Year] FROM [AlsoPlayedFor] " & _
"WHERE [playerID]=" & playerID & " " & _
"AND [teamID]=" & teamID & " " & _
"ORDER BY [Year]", _
dbOpenSnapshot)
strList = ""
prevYear = 0
seqStart = 0
Do While Not rst.EOF
If rst!Year <prevYear + 1 Then
strList = strList & _
IIf(seqStart <prevYear, "-" & prevYear, "") & _
", " & _
rst!Year
seqStart = rst!Year
End If
prevYear = rst!Year
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
ListYears = Mid(strList, 3)
End Function

2. Change your query to

SELECT AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,
ListYears([playerID],[teamID]) AS Years
FROM AlsoPlayedFor
GROUP BY AlsoPlayedFor.playerID, AlsoPlayedFor.teamID;

Does that do it?- Hide quoted text -

- Show quoted text -

I get an error that says "undefined function ''ListYears'' in Expression.


On Mar 4, 9:15 am, "Chris" <chrislab...@gmail.comwrote:

On Mar 4, 6:32 am, "Gord" <g...@kingston.netwrote:

On Mar 3, 11:54 pm, "Chris" <chrislab...@gmail.comwrote:

I have a table which lists player names, teams played for and the
years they played there and my code looks like this

SELECT AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,
AlsoPlayedFor.TeamName, Min([AlsoPlayedFor].[Year]) & "-" &
Max([AlsoPlayedFor].[Year]) AS [Year]
FROM AlsoPlayedFor
GROUP BY AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,
AlsoPlayedFor.TeamName;

which takes the Min year and the Max Year and displays it like "Year-
Year"

But lets say for example the player played for 5 years so it 1990,
1991, 1992, 1993, 1995

It would display as 1990-1995 but I want it to display as 1990-1993,
1995, is this possiable??? Also I need it to gothe other wayso if the
years are 1990, 1992, 1993, 1994, 1995 I want that to display as 1990,
1992-1995.

PLEASE HELP

Hi, Chris. I see you''re still struggling with this problem. Give this
a try:

1. Paste the following code into a new Module in your Access database:

Public Function ListYears(playerID, teamID)
Dim _
rst As DAO.Recordset, _
prevYear As Integer, _
seqStart As Integer, _
strList As String

Set rst = CurrentDb.OpenRecordset( _
"SELECT [Year] FROM [AlsoPlayedFor] " & _
"WHERE [playerID]=" & playerID & " " & _
"AND [teamID]=" & teamID & " " & _
"ORDER BY [Year]", _
dbOpenSnapshot)
strList = ""
prevYear = 0
seqStart = 0
Do While Not rst.EOF
If rst!Year <prevYear + 1 Then
strList = strList & _
IIf(seqStart <prevYear, "-" & prevYear, "") & _
", " & _
rst!Year
seqStart = rst!Year
End If
prevYear = rst!Year
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
ListYears = Mid(strList, 3)
End Function

2. Change your query to

SELECT AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,
ListYears([playerID],[teamID]) AS Years
FROM AlsoPlayedFor
GROUP BY AlsoPlayedFor.playerID, AlsoPlayedFor.teamID;

Does that do it?- Hide quoted text -

- Show quoted text -


I get an error that says "undefined function ''ListYears'' in Expression.

1. Did you paste the VBA code into a new Access Module (a regular
Module, not a Class Module) and save it?

2. Are you are running the query from within Access?


这篇关于分组号码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆