paricular query grouping item in access table

[sal21]

COD_1 is pricipal agengy
COD_2 is sub agency
COD_3 is id of opeartion maked from sub agency
NUMERO is the quantity of operation

the table in question is EXPORT_table

I use Ado Jet conn just opened.

I need:

extract from a the join of COD_1&"-"&Format(COD_2,"#000000") the unique index (in the case of the first 11 records the unique index is 0100-000304)

Grouping from this new index (for example index 0100-000304) the total of each COD_3

example:
COD_1 COD_2 COD_3 NUMERO
0100 394 45 7
0100 394 18 11 (3+8)
0100 394 50 33
0100 394 43 11
0100 394 41 9 (8+1)
0100 394 16 1
0100 394 Z2 1
0100 394 52 10
0100 394 68 1

result of final requested grouping query:
0100-000394 45 7
0100-000394 18 11
0100-000394 50 33
0100-000394 43 11
0100-000394 41 9
0100-000394 16 1
0100-000394 Z2 1
0100-000394 52 10
0100-000394 68 1

if you see the in table the id 18 is repetid 2 (summing in this case) and have unique result of summ, if you see the in table the id 41 is repetid 2 (summing in this case ) and have unique result of summ...

i hope you understand me.

[Carol W.]

I think I understand what you want to do. See attached. Look at qry2 (which is based on qry1, which is based on your table).

Is this what you're looking for?

Edited by Carol W. to remove attachment per request by Sal21.

[Pat]

Does this do what you want:
SELECT Trim([COD_1]) & "-" & Format([cod_2],"000000") AS Expr1, EXPORT_TABLE.COD_3, Sum(EXPORT_TABLE.NUMERO) AS SumOfNUMERO
FROM EXPORT_TABLE
GROUP BY Trim([COD_1]) & "-" & Format([cod_2],"000000"), EXPORT_TABLE.COD_3;

This only requires one query, unless i have misunderstood the problem.

[sal21]

I think I understand what you want to do. See attached. Look at qry2 (which is based on qry1, which is based on your table).

Is this what you're looking for?

yES!
wORK!!!!!

But...

i need to start qry1 before qry2, or not?

I have started qry2 only and i have the corect result.(?!)

[Carol W.]

Sal21,

Qry1 is an intermediate query that formats an expression (expr1) the way you requested. Qry2 is a summary query that groups the data by expr1 and sums Numero.

In short, you don't need to "run" qry1 at all. It automatically "runs" when you open qry2. Just use qry2 for whatever your ultimate purpose is (report, form etc).

Received your PM and will try to accomodate.

[sal21]

Sal21,

Qry1 is an intermediate query that formats an expression (expr1) the way you requested. Qry2 is a summary query that groups the data by expr1 and sums Numero.

In short, you don't need to "run" qry1 at all. It automatically "runs" when you open qry2. Just use qry2 for whatever your ultimate purpose is (report, form etc).

Received your PM and will try to accomodate.

Hi frined, sorry for pvt msg but tk for "remove..." ecc...

to be more clear on my post if you have other solution to a fast query, ( actually i work with 3.400.879 records!)

See attached (anonimus file:-) now)

The xls file contain the structure of mine two table access, is only for example.

table1 contain the field:
cod1 is agengy
cod2 is sub agency
code3 is the name of product
tot is the quantity of product

tabel2 contain the field (actually in my db access is completly blank):

the field agency+subagency (is the joint value from agengy&sub agengy)
the field 45,18,50,43... are the name of product

i need a query to return into table2 from table1 the quantity from each agengy and sub agengy for produtcs.

the first 2 line of tabòe is the finale result from only 100394 and 1001773

in effetct is a transposition of value from vertical from orizontal based two param, or not?

I hope with tath is easy cler...

[Carol W.]

Sal21,

One question -- In Tabella2, you have several duplicates of agency & subagency, for example:

Code: Select all

100394
1001773
10014505

See attached screenshot of Tabella2.

Do you really want to show these combinations twice? I don't understand why you would want to show them twice. Please explain.

tabella2.jpg

[sal21]

Sal21,

One question -- In Tabella2, you have several duplicates of agency & subagency, for example:

Code: Select all

100394
1001773
10014505

See attached screenshot of Tabella2.

Do you really want to show these combinations twice? I don't understand why you would want to show them twice. Please explain.

tabella2.jpg

i am sorry... error during the copy from original access table, consider only unique and not duplicate value.

in effect the real value in field agency & subagency are:
100394
1001773
10014505
1001398

[Carol W.]

Sal21,

See attached.

There are three queries. The query you want to run to create tabella2 is the one called qmk-create tabella2.

Regarding the other queries, qry1 essentially creates expr1 and also includes COD3 and TOT. qry-xtab is a cross-tab query that uses qry1 as its source and puts the data in the cross tab format that you requested.

Qmk-create tabella2 uses qry-xtab as its source and creates tabella2. This is the one you need to run whenever the data in tabella1 changes.

Hope this is what you're looking for.

[sal21]

Sal21,

See attached.

There are three queries. The query you want to run to create tabella2 is the one called qmk-create tabella2.

Regarding the other queries, qry1 essentially creates expr1 and also includes COD3 and TOT. qry-xtab is a cross-tab query that uses qry1 as its source and puts the data in the cross tab format that you requested.

Qmk-create tabella2 uses qry-xtab as its source and creates tabella2. This is the one you need to run whenever the data in tabella1 changes.

Hope this is what you're looking for.

WOW! work perfect for me.
Tk agin.
sal.