[Report] Group by a field not sorting by it

[Atticus]

Hi all

In an access report, is it possible to group by a field but not sort by
it?

Example:

Table Sales

Prod Invoice Movement Quantity
P1 4019 005 1
P1 4019 006 1
P2 4019 003 5
P2 4019 004 2
P1 4020 001 5
P1 4020 002 3
P1 4021 003 7
P1 4022 004 4
P2 4020 001 2
P2 4021 002 5

I need to group first by Prod, then by Invoice with a sum of the
quantity, but sorted by Movement

I need the report to look like this (explanations to the right not in
the report, of course ;-):

Prod: P1
Invoice Quantity
4020 8 /---> Sum of movements 001+002 for P1 &
invoice 4020
4021 7 /---> Movement 003
4022 4 /---> Movement 004
4019 2 /---> Sum of movements 005+006 for P1 &
invoice 4019
Prod: P2
4020 2 /---> Movement 001
4021 5 /---> Movement 002
4019 7 /---> Sum of movements 003+004 for P2 &
invoice 4019

Is this possible with Access 2000?

Thank you in advance and best regards

Atticus

 

[Marshall Barton]

In an access report, is it possible to group by a field but not sort by
it?

Example:

Table Sales

Prod Invoice Movement Quantity
P1 4019 005 1
P1 4019 006 1
P2 4019 003 5
P2 4019 004 2
P1 4020 001 5
P1 4020 002 3
P1 4021 003 7
P1 4022 004 4
P2 4020 001 2
P2 4021 002 5

I need to group first by Prod, then by Invoice with a sum of the
quantity, but sorted by Movement

I need the report to look like this (explanations to the right not in
the report, of course ;-):

Prod: P1
Invoice Quantity
4020 8 /---> Sum of movements 001+002 for P1 &
invoice 4020
4021 7 /---> Movement 003
4022 4 /---> Movement 004
4019 2 /---> Sum of movements 005+006 for P1 &
invoice 4019
Prod: P2
4020 2 /---> Movement 001
4021 5 /---> Movement 002
4019 7 /---> Sum of movements 003+004 for P2 &
invoice 4019

I don't see a consistent sorting in your example, but if you
want to sort by the total (sum) of the movement, you must
calculate the sum in the report's record source query. The
query could be something along these lines:

SELECT Product, Invoice, Sum(movement) as SumMovement
FROM table
GROUP BY Product, Invoice

With that kind of record source query, the report should be
very simple.

 

[Atticus]

Thank you Marshal

I need the report to group the lines within every Product first, then
by Invoice, but sorting by Movement so invoices with older movements
shoul appear first. The sum should be of the Quantity of that Product
in that Invoice.

The problem is that if I group by Product, then Invoice, and sum the
quantities, it shows the result this way:

Prod: P1
Invoice Quantity Movements
4019 2 005, 006
4020 8 001, 002
4021 7 003
4022 4 004
Prod: P2
4019 7 003, 004
4020 2 001
4021 5 002

As you see it is sorting by Invoice, not Movements. Imagine the
movement field as a date field, and I want to group by product, then
invoice but sorting the older ones first.

Hope this put a little more light on my problem

Thank you





Marshall Barton ha escrito:

 

[Marshall Barton]

Whatever you are trying to do, you need to calculate the
aggregated value in the report's record source query.

This time it sounds like you want to group by the minimum
value of movement for an invoice number??

SELECT product, invoice, Min(movement) As Earliest
FROM table
GROUP BY product, invoice

If you also want to list the movements for an invoice, as in
your new example, check out this function:
http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'