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

loop vs SQL

P: n/a
I'm a novice at VBA. I have a table with 2 fields, "Category" & "
"Amount". Using a form, I want to find all of the instances of an
entry in "category" (ex,"Groceries") and total the "Amount".

I'm not sure if I should use "While...Wend", Do While...Loop, or a Sql
statement.

Any help is appreciated.

Feb 4 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
davec wrote:
I'm a novice at VBA. I have a table with 2 fields, "Category" & "
"Amount". Using a form, I want to find all of the instances of an
entry in "category" (ex,"Groceries") and total the "Amount".

I'm not sure if I should use "While...Wend", Do While...Loop, or a Sql
statement.

Any help is appreciated.
A SQL statement is going to me much faster and easier 99% of the time. About
the only time loops are justified is if the Recordset is already being retrieved
for some other reason (and isn't too large) or where you need to make reference
to values in rows other than the current row in the Recordset.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Feb 4 '08 #2

P: n/a
I am a novice/curious too:
wouldn't DSum be just as easy?

How would the SQL-statement look like?

Michiel

"Rick Brandt" <ri*********@hotmail.comwrote in message
news:Up*****************@nlpi069.nbdc.sbc.com...
davec wrote:
>I'm a novice at VBA. I have a table with 2 fields, "Category" & "
"Amount". Using a form, I want to find all of the instances of an
entry in "category" (ex,"Groceries") and total the "Amount".

I'm not sure if I should use "While...Wend", Do While...Loop, or a Sql
statement.

Any help is appreciated.

A SQL statement is going to me much faster and easier 99% of the time.
About the only time loops are justified is if the Recordset is already
being retrieved for some other reason (and isn't too large) or where you
need to make reference to values in rows other than the current row in the
Recordset.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Feb 4 '08 #3

P: n/a
On Mon, 04 Feb 2008 12:55:08 GMT, "Michiel Rapati-Kekkonen"
<mi*****@nonsense.zzwrote:
>I am a novice/curious too:
wouldn't DSum be just as easy?

How would the SQL-statement look like?

Michiel
DSum will only return a single sum. If the sum of a single category
is what was needed, it would do fine.

Note in the original post, he needs the "Amount" for each category.
What is needed is an aggregate query.

This is probably over simplified, but it would look something like
this:

SELECT Category, SUM(Amount) as Cat_Total
From MyTable
Group By Category

This will show him a result with each category and the amounts summed
up for that category.

HTH,
Arch
>

"Rick Brandt" <ri*********@hotmail.comwrote in message
news:Up*****************@nlpi069.nbdc.sbc.com.. .
>davec wrote:
>>I'm a novice at VBA. I have a table with 2 fields, "Category" & "
"Amount". Using a form, I want to find all of the instances of an
entry in "category" (ex,"Groceries") and total the "Amount".

I'm not sure if I should use "While...Wend", Do While...Loop, or a Sql
statement.

Any help is appreciated.

A SQL statement is going to me much faster and easier 99% of the time.
About the only time loops are justified is if the Recordset is already
being retrieved for some other reason (and isn't too large) or where you
need to make reference to values in rows other than the current row in the
Recordset.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Feb 4 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.