Create a new column using information from separate columns

[cfmartin76]

Hello!

I have a little bit of a problem, and I wasn't able to find the
solution anywhere.

I have column A "Title" that is linked with column B "Day of the week".
Each title can have 1, 2 or 3 days of the week (which will then be
column C, D, etc). It looks like this:

TITLE DAY1 DAY2 DAY3
Math Mon Tue
History Tue Fri Mon
English Wed Mon

How can I create a new column that lists all the "titles" that appear
on Monday and a separate column for the ones that appear on Tuesday?
(the names of the new columns would be "Monday", "Tuesday", etc) If a
title has more than 2 days I want that title to appear on the columns
for both days.

It will look like this:
MON TUE WED THU FRI
Math Math English History
History History
English

Is there a macro for this?

Thanks!

 

[Jerry Whittle]

Not a macro. You could do it with a crosstab query IF your table was set up
properly. The table should look like:

TITLE DayOfWeek DateSequence
Math Mon 1
Math Tue 2
History Tue 1
History Fri 2
History Mon 3
English Wed 1
English Mon 2

Then something like this will be close:

TRANSFORM First(Cfmartin.TITLE) AS FirstOfTITLE
SELECT Cfmartin.DateSequence
FROM Cfmartin
GROUP BY Cfmartin.DateSequence
PIVOT Cfmartin.DayOfWeek In ("MON","TUE","WED","THU","FRI");