cross query grp by id but text in result

[ano]

query:
SELECT i3.bought AS detail, i2.bought AS kind, i2.id AS grp, DatePart('ww',i3.datetime) AS week, "a" AS a
FROM (bought AS i3 INNER JOIN ingredients_1 AS i1 ON i3.bought = i1.ID) INNER JOIN ingredients AS i2 ON i1.kind = i2.ID
ORDER BY i2.id, i3.bought;

cross query:
TRANSFORM Count(Query1.a) AS CountOfa
SELECT Query1.kind, Query1.detail
FROM Query1
GROUP BY Query1.grp, Query1.detail
PIVOT Query1.week;

something like above . grp is the order i like kind is the translation. error is kind not used in aggreate

[June7]

Right. Remove kind field.

Provide sample data and desired output.

[ano]

query:
SELECT i3.bought AS detail, i2.bought AS kind, i2.id AS grp, DatePart('ww',i3.datetime) AS week, "a" AS a
FROM (bought AS i3 INNER JOIN ingredients_1 AS i1 ON i3.bought = i1.ID) INNER JOIN ingredients AS i2 ON i1.kind = i2.ID
ORDER BY i2.id, i3.bought;

cross query:
TRANSFORM Count(Query1.a) AS CountOfa
SELECT Query1.kind, Query1.detail
FROM Query1
GROUP BY Query1.grp, Query1.detail
PIVOT Query1.week;

something like above . grp is the order i like kind is the translation. error is kind not used in aggreate

example :
table 1 : id,week,item,price

1,1,1,-$100
2,1,2,$4
3,2,3,$2
4,2,4,$1.5
5,3,2,$2
6,3,3,$6
7,3,2,$1

table 2 : id,item,cat

1,aaa,7
2,bbb,9
3,ccc,10
4,ddd,8
5,eee,8
6,fffff,9
7,cat3,7
8,cat2,8
9,cat4,9
10,cat1,10

" W E E K"
"(cat)grp item 1 2 3"
-------------------------------
"(7)cat3 aaaa 1"
"(8)cat2 dddd 1"
"(9)cat4 bbbb 1 2"
"(10)cat1 ccccc 1 1"
the (cat) is not visible added to show the order

[June7]

You want aaa on same row as cat3 and ddd same as cat2 - why not eee and similarly for bbb and fffff? What rule determines this? This is not a straightforward crosstab.

Should probably show some real data using field and table names shown in your SQL. Or attach db for analysis.

Review http://allenbrowne.com/ser-67.html

[ano]

the 2nd table are the available items with the category they belong to
if cat = id then thats cat name
ur question abt ffff is not relevant bec to main table not added ffff yet

SELECT Table1.week, Table2.item, Table1.price, table2.cat
FROM Table2 INNER JOIN Table1 ON Table2.[ID] = Table1.[item];


TRANSFORM Count([Table1 Query1].[price]) AS CountOfprice
SELECT [Table1 Query1].[cat], [Table1 Query1].[item], Count([Table1 Query1].[price]) AS [Total Of price]
FROM [Table1 Query1]
GROUP BY [Table1 Query1].[cat], [Table1 Query1].[item]
PIVOT [Table1 Query1].[week];
the select [cat] should be item from table2

[June7]

Okay, fffff is not relevant. Your output still does not make sense. How do you expect to associate cat3 and aaa, cat2 and ddd?

Why are available items and categories in same table?

If you can't provide db, I can't try to help further.

[ano]

Okay, fffff is not relevant. Your output still does not make sense. How do you expect to associate cat3 and aaa, cat2 and ddd? simple sql link or relation create copy

Why are available items and categories in same table? the way i grow up less files less problems

If you can't provide db, I can't try to help further.

ok database5.zip uploaded

[June7]

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;

[ano]

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;

mmmm ur answer still get the numeric instead of text (numeric is for linking not display) ohhh just remove cat and change table2_1 to it tnx
mmmm u create extra table cat but the cat is referenced directly in table 2 to get the text of cat so not understand why u select cat 1st

i think u found it tnx alot
tnx enjoy ur day tc

[ano]

mmmm ur answer still get the numeric instead of text (numeric is for linking not display) ohhh just remove cat and change table2_1 to it tnx
mmmm u create extra table cat but the cat is referenced directly in table 2 to get the text of cat so not understand why u select cat 1st

i think u found it tnx alot
tnx enjoy ur day tc

TRANSFORM Count([Table1 Query].price) AS CountOfprice
SELECT [Table1 Query].tabl3_item, [Table1 Query].Table2_item, Count([Table1 Query].price) AS [Total Of price]
FROM [Table1 Query]
GROUP BY [Table1 Query].tabl3_ID, [Table1 Query].tabl3_item, [Table1 Query].Table2_item
PIVOT [Table1 Query].week;
the solving is to add id in the group by

btw a global company has many employee and many boss (global,continent,country,city,branche,department, team,employee) u mean u need for each level a bos table? that mean changing boss need many adjustment instead just change boss(cat)