By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,666 Members | 1,784 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,666 IT Pros & Developers. It's quick & easy.

please help an access noob

P: n/a
So I have this query that returns
ID Item Quantity
primary text int
unique
I have a form with a command button that runs a function, but I need
the function to be able to tally the quantity for each unique item.
I don't understand how to loop a record set in a function nor do I know

how to add unique Items to a new table.
I figure this should be rather simple, please help.
Thanks in advance,
Derek

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"pointBoarder" <De*********@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...
So I have this query that returns
ID Item Quantity
primary text int
unique
I have a form with a command button that runs a function, but I need
the function to be able to tally the quantity for each unique item.
I don't understand how to loop a record set in a function nor do I know

how to add unique Items to a new table.
I figure this should be rather simple, please help.
Thanks in advance,
Derek


Normally this is done in a simple query with no need to create any summary
table. When looking at the design of your query, select View>Totals you
should have "Group By" under the item field and Count under the ID column,
so you would have something like:

SELECT Item, Count(*) AS MyCount FROM MyTable GROUP BY Item
Nov 13 '05 #2

P: n/a
in table design, create a 'unique' index on the 'item'

in your function, use a query based on your existing query that
SELECT item, sum(quantity) from yourQuery group by item

Nov 13 '05 #3

P: n/a
"Justin Hoffman" <j@b.com> wrote in message
news:d7**********@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
"pointBoarder" <De*********@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...
So I have this query that returns
ID Item Quantity
primary text int
unique
I have a form with a command button that runs a function, but I need
the function to be able to tally the quantity for each unique item.
I don't understand how to loop a record set in a function nor do I know

how to add unique Items to a new table.
I figure this should be rather simple, please help.
Thanks in advance,
Derek


Normally this is done in a simple query with no need to create any summary
table. When looking at the design of your query, select View>Totals you
should have "Group By" under the item field and Count under the ID column,
so you would have something like:

SELECT Item, Count(*) AS MyCount FROM MyTable GROUP BY Item


Sorry, I mis-read the question. The above counts the records per item when
we should be summing the quantity, ie

SELECT Item, Sum(Quantity) AS MySum FROM MyTable GROUP BY Item


Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.