I was hoping to see some real data, something other than "cat1", "cat2", "cat3", "cat4". So, using that data sample, consider:
Query1: onlyCAT
SELECT Table2.item, Table2.cat
FROM Table2
WHERE (((Table2.item) Like "cat*"));
Query2: comboData
SELECT Table1.ID, Table1.week, Table1.price, [Table2].[ID] & [onlyCAT].[cat] AS IC, Table2.item, onlyCAT.item, onlyCAT.cat
FROM (onlyCAT INNER JOIN Table2 ON onlyCAT.cat = Table2.cat) INNER JOIN Table1 ON Table2.ID = Table1.item;
Query3:
TRANSFORM First(comboData.week) AS FirstOfweek
SELECT comboData.cat, comboData.Table2.item, comboData.onlyCAT.item
FROM comboData
GROUP BY comboData.cat, comboData.Table2.item, comboData.onlyCAT.item
PIVOT "WK" & DCount("*","comboData","IC=" & [IC] & " AND ID<" & [ID])+1;
cat |
comboData.Table2.item |
onlyCAT.item |
WK1 |
WK2 |
WK3 |
7 |
aaa |
cat3 |
1 |
|
|
8 |
ddd |
cat2 |
2 |
|
|
9 |
bbb |
cat4 |
1 |
3 |
3 |
10 |
ccc |
cat1 |
2 |
3 |
|
Or this version:
Query1
SELECT Table1.ID, Table2_1.cat, Table2.item, Table2_1.item, Table1.week, [Table2].[ID] & [Table2_1].[cat] AS IC
FROM (Table2 AS Table2_1 INNER JOIN Table2 ON Table2_1.ID = Table2.cat) INNER JOIN Table1 ON Table2.ID = Table1.item
WHERE (((Table2.item) Not Like "cat*"));
Query2
TRANSFORM First(Query1.week) AS FirstOfweek
SELECT Query1.cat, Query1.Table2.item, Query1.Table2_1.item
FROM Query1
GROUP BY Query1.cat, Query1.Table2.item, Query1.Table2_1.item
PIVOT "Wk" & DCount("*","Query1","IC=" & [IC] & " AND ID<" & [ID])+1;