用户从“mmyyyy”数据中提取月份名称列表

I have a table with UserID and MonthYear which is in the following format 12015 for January 2015 or 32014 for March 2014, 122015 for December and so on. Each UserId has multiple lines for months.

我有一个UserID和MonthYear的表,其格式为2015年1月的12015或2014年3月的32014,12月的122015,依此类推。每个UserId有几个月的多行。

The query

select UserId, MonthYear 
from table1 
order by UserId, MonthYear;

returns something like this:

返回这样的东西:

x223 12015
x223 32015
x223 82015 
x223 92015
x223 102015
x223 112015
.
.
.
x100 72014
x100 92014

I want to create a table/query from table1 that outputs in the following format

我想从table1创建一个表/查询,以下列格式输出

x223 (February, April, May, June, July, December)
x100 (January, February...December)

This is the best I could come up to:

这是我能做到的最好的事情:

select 
    UserId, Months 
from 
    table2 
where 
    Months not in (select left(MonthYear), 1 
                   where len(MonthYear) < 6)  
group by 
    UserID, Months;

I am trying to do this in Access but any hint that works in SQL is also very valuable.

我试图在Access中执行此操作,但任何在SQL中工作的提示也非常有价值。

1 个解决方案

#1

All you need is a copy of the ConcatRelated() function and a saved query to extract the month numbers from [table1].[MonthYear]. With the saved query named [qryExtractMonthNumbers] ...

您只需要一份ConcatRelated()函数和一个保存的查询来从[table1]中提取月份数。[MonthYear]。使用名为[qryExtractMonthNumbers]的已保存查询...

SELECT DISTINCT
    UserId,
    Val(Mid(MonthYear,1,Len(MonthYear)-4)) AS intMonth
FROM table1

... we can use it as the row source for the ConcatRelated() call in the following query ...

...我们可以在以下查询中将它用作ConcatRelated()调用的行源...

SELECT 
    UserId,
    ConcatRelated("MonthName(intMonth)","qryExtractMonthNumbers","UserId='" & UserId & "'","intMonth") AS MonthNames
FROM (SELECT DISTINCT UserId FROM table1)

which returns

UserId  MonthNames
------  ----------------------------------------------------
x100    July, September
x223    January, March, August, September, October, November

#1