Hello and thanks for having a look!

I'm trying to make a complex selection from a prior query and I'm having a hard time locating a solution, in part because I'm having a hard time describing what I'm after or what to search for. Here's the rub:

What I have ([table1]):

What I would like if possible:

With the goal being that I have a set of Items for every month in the year (above example showing only Oct and Nov). I feel like a Group By and Join solution is what I need, but I'm stuck after spending all day on this.

Any help is appreciated!

Update - Solution:

Using a combination of suggestions from two contributors below, I was able to rewrite my original query that generated my starting table above.

I had been running this query:

TRANSFORM First([Points]) AS ItemPoints
SELECT identifier, month
FROM [source]
GROUP identifier, month
PIVOT name;

But this created a column for month which is pretty obvious in hindsight.

The solution was the following query:

TRANSFORM First([source].Points) AS ItemPoints
SELECT [source].identifier
FROM [itemNames], [source]
GROUP BY [source].identifier
ORDER BY ScoreMonth & [itemNames].ItemId
PIVOT ScoreMonth & [itemNames].ItemId;

Where [itemNames] is a query that returns a list of unique item names, the "item 1", "item 2" bit.

This resulted in the following table:

Which I can work with :)

CodePudding user response：

What your are trying to do is called a cross-tab query. However your query has 2 column headings Month and Item. Month is invisible. Access and Excel only allow 1 column heading in a cross-tab query. So my solution is to manually generate the 2 column cross-tab. So for the manual cross-tab we need to do a cross-join query to generate all the rows and columns we will need. Then we use a calculated-field and the dlookup function to insert the correct value from Table1 into the correct cell in the upcoming cross-tab querie's root query. ItemNames is a table of all the item names, Months (here 2 months), Identifiers is all identifiers. Since we cant have 2 columns with the same name we are going to use ItemMonth as a work around.

Value: Nz(DLookUp("item1","Table1","identifier = '" & [identifier] & "'   AND monthnumber = " & [MonthNumber]),0)

ItemMonth: [MonthNumber] & [ItemName]

'Table 1

ID  identifier  monthnumber item1   item2
3   xyz-1   10  0   0
4   xyz-2   10  0   0
5   xyz-1   11  1   1
6   xyz-2   11  1   1

'after cross join : query1 in picture

MonthNumber ItemName    identifier  Value   ItemMonth
10  Item 1  xyz-1   0   10Item 1
11  Item 1  xyz-1   1   11Item 1
10  Item 2  xyz-1   0   10Item 2
11  Item 2  xyz-1   1   11Item 2
10  Item 1  xyz-2   0   10Item 1
11  Item 1  xyz-2   1   11Item 1
10  Item 2  xyz-2   0   10Item 2
11  Item 2  xyz-2   1   11Item 2

The cross-tab set up is as simple as it gets. The cross-tab is a type of totals query so pay attention to the summary functions:

'result of our cross-tab query but the column names are still wrong so we will fix that with a report

identifier  10Item 1    10Item 2    11Item 1    11Item 2
xyz-1   0   0   1   1
xyz-2   0   0   1   1

To generate the repot I just selected the cross-tab query and hit report. Then went into design mode and edited all the column labels. If you have to do this whole process frequently or have a whole lot of items use VBA to auto adjust the report labels.

Aside 1. In Access, cross-tab queries bug-out if they are based on even slightly complicated queries. If your cross-tab is based on a query and it doesn't work try turning the cross-tab's query into a table with a make-table query. Then base the cross-tab on the new table.

Aside 2. I suggest Looking at the cross-tab as only a way to view your data. Every time you add or subtract an item to this data that results in adding or subtracting 12 columns to the cross-tab. that means you have to adjust any reports and forms based on a cross-tab every time the items change. Way to much work.