Alternative to group by

[Ed]

Is there an alternative to using the totals button for grouping
together records by one field and totaling another? Like this:

ID# QTY ID# QTY
01 5 01 7
01 2 changed to..... 02 3
02 3 03 4
03 3
03 1

The reason I need an alternative is that I still need to be able to
alter the data in the query. If I use a group by clause, I can't.

Thanks a lot.

 

[John W. Vinson]

Is there an alternative to using the totals button for grouping
together records by one field and totaling another? Like this:

ID# QTY ID# QTY
01 5 01 7
01 2 changed to..... 02 3
02 3 03 4
03 3
03 1

The reason I need an alternative is that I still need to be able to
alter the data in the query. If I use a group by clause, I can't.

Thanks a lot.

There's a good reason that aggregate queries aren't updateable.

Suppose you took the record with ID# 03, QTY 4.

You update the QTY to 6.

Which of the two records in the actual table should be updated? What should it
be updated TO?

It's ambiguous! Access (and I, for that matter) have no way to know which
record should actually be changed.

Perhaps you can come up with some other way of presenting the data that still
lets you update, such as using DSum() to calculate a total - though of course
that total still won't be updateable.

 

[KARL DEWEY]

The problem is that when you roll all the ID# 01's together to get sum the
machine will not know which of the records you wish to alter.

 

[Ed]

There's a good reason that aggregate queries aren't updateable.

Suppose you took the record with ID# 03, QTY 4.

You update the QTY to 6.

Which of the two records in the actual table should be updated? What should it
be updated TO?

It's ambiguous! Access (and I, for that matter) have no way to know which
record should actually be changed.

Perhaps you can come up with some other way of presenting the data that still
lets you update, such as using DSum() to calculate a total - though of course
that total still won't be updateable.

I didn't really explain the entire situation. The field I want to
change Is a separate field. I'm trying to have a query where it will
display the item's information, total on hand (the sum of the counts
from the different stores), the suggested re-order amount (calculated
based on the total on hand and stocking requirements in the items
information), and then have a field where I can input what I actually
want to order. The report would then pull the info from this query and
generate the order forms for each vendor.

 

[John W. Vinson]

I didn't really explain the entire situation. The field I want to
change Is a separate field. I'm trying to have a query where it will
display the item's information, total on hand (the sum of the counts
from the different stores), the suggested re-order amount (calculated
based on the total on hand and stocking requirements in the items
information), and then have a field where I can input what I actually
want to order. The report would then pull the info from this query and
generate the order forms for each vendor.

Then I'd suggest using a Form based on the items table - NOT including
whatever table has the quantities; on the form put a textbox using DSum() to
display (uneditably of course!) the sum of the quantities.

 

[Ed]

Then I'd suggest using a Form based on the items table - NOT including
whatever table has the quantities; on the form put a textbox using DSum()to
display (uneditably of course!) the sum of the quantities.

I'm not sure how to do what you suggest because I need a different
total for each of the items counted. How would I write the equation?
Something like DSum("Quantity On Hand","Item Location",[Item Location]!
[Item ID])?

 

[John W. Vinson]

Then I'd suggest using a Form based on the items table - NOT including
whatever table has the quantities; on the form put a textbox using DSum() to
display (uneditably of course!) the sum of the quantities.

I'm not sure how to do what you suggest because I need a different
total for each of the items counted. How would I write the equation?
Something like DSum("Quantity On Hand","Item Location",[Item Location]!
[Item ID])?

Well, I really don't know, because I don't know the name of your table or its
structure. The arguments to DSum (or any of the domain functions) are, first,
the name of the field to sum; second, the name of the table or query
containing that field; and third, an optional text string which evaluates to a
valid SQL WHERE clause without the word WHERE. So assuming that the field is
named "Quantity On Hand", the table is named "Item Location", and the field
which identifies the item which may have multiple "quantity on hand" records
is ItemID; and that the Form contains a textbox named txtItemID containing the
desired ItemID, you'ld use

=DSum("[Quantity on Hand]", "[Item Location]", "[Item ID] = " & [txtItemID])

as the Control Source of a textbox. If my assumptions are wrong, adjust to fit
your actual structure.

 

[Ed]

I'm not sure how to do what you suggest because I need a different
total for each of the items counted. How would I write the equation?
Something like DSum("Quantity On Hand","Item Location",[Item Location]!
[Item ID])?

Well, I really don't know, because I don't know the name of your table orits
structure. The arguments to DSum (or any of the domain functions) are, first,
the name of the field to sum; second, the name of the table or query
containing that field; and third, an optional text string which evaluatesto a
valid SQL WHERE clause without the word WHERE. So assuming that the fieldis
named "Quantity On Hand", the table is named "Item Location", and the field
which identifies the item which may have multiple "quantity on hand" records
is ItemID; and that the Form contains a textbox named txtItemID containing the
desired ItemID, you'ld use

=DSum("[Quantity on Hand]", "[Item Location]", "[Item ID] = " & [txtItemID])

as the Control Source of a textbox. If my assumptions are wrong, adjust to fit
your actual structure.
--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

This worked perfectly. Thanks a lot!