DCount & DSum

  • Thread starter Thread starter David
  • Start date Start date
D

David

Hello

I run an Access FE with mySql BE.
I am trying to free myself from the resource hogging DCount & DSum now that
my application needs to be used over the web.

My problem is that I need to take stock of items and weight of those items
to get an output of "x items @ xx lbs".
Currently I do it by using two fields in a table which store the weight of
each item being scanned through the system then I DCount and DSum to get the
output for each destination.

Is there a better model to use that would release me from having to use
these commands as they are a real hinderance in low bandwidth areas?

Thanks
David
 
Hello

I run an Access FE with mySql BE.
I am trying to free myself from the resource hogging DCount & DSum now that
my application needs to be used over the web.

My problem is that I need to take stock of items and weight of those items
to get an output of "x items @ xx lbs".
Currently I do it by using two fields in a table which store the weight of
each item being scanned through the system then I DCount and DSum to get the
output for each destination.

Is there a better model to use that would release me from having to use
these commands as they are a real hinderance in low bandwidth areas?

Thanks
David
Click to expand...

Use a Totals query instead. If you could post a description of your table
structure and more about the context (Access is *NOT* ideal for use over the
web...) someone might be able to give a more specific answer.
 
Thank you John

I had a look at Totals Queries. They are based once again on DSum?
Is there something faster about using DSum in a query vs VBA?

My table structure is quite simple
1) ItemCode - This identifies the item in another table and is a 1-many
relationship with my items table
2) Weight - Weight of item
3) ID - Identifies individual item within item grouping

To get my result of x items @ xx lbs I would use:
"Total = " & DCount("Weight","ItemDetail","Bags.Itemcode=" & me.txtItemCode)
& "items @ " & DSum("Weight","ItemDetail","Bags.Itemcode=" & me.txtItemCode)
& "lbs"

I have several different item ID's and I use them on a continuous form that
shows an instant update on how many items in the item group and the total
weight for them each.
DSum and DCount, I have noticed, are quite slow but if this is the only way
of completing the task then that is what I will have to do. I am still
learning so I thought maybe I could gain in insight on a better method if
there is one out there.
Just trying to shave off any excess that is not esential to make networked
functions faster and future programs more efficient.

Hope that makes sense
David
 
Thank you John

I had a look at Totals Queries. They are based once again on DSum?
Click to expand...

No, they do not use dsum. Evidently you did not actually try one!
Is there something faster about using DSum in a query vs VBA?
Click to expand...

VBA would certainly slow things down considerably - just another layer of
complexity!
My table structure is quite simple
1) ItemCode - This identifies the item in another table and is a 1-many
relationship with my items table
2) Weight - Weight of item
3) ID - Identifies individual item within item grouping

To get my result of x items @ xx lbs I would use:
"Total = " & DCount("Weight","ItemDetail","Bags.Itemcode=" & me.txtItemCode)
& "items @ " & DSum("Weight","ItemDetail","Bags.Itemcode=" & me.txtItemCode)
& "lbs"
Click to expand...

What's the context? Where are you *using* these expressions? You can see your
database - I cannot!
I have several different item ID's and I use them on a continuous form that
shows an instant update on how many items in the item group and the total
weight for them each.
Click to expand...

Try a Query:

SELECT Bags.ItemCode, Count(*) AS TotalCount, Sum([Weight]) AS TotalWeight
FROM Bags INNER JOIN ItemDetail ON Bats.ItemCode = ItemDetail.ItemCode;

and base a report or form on this query. Adapt as needed for your table and
fieldnames and relationships.
 
John

No I had not actually tried one. I jumped to the wrong conclusion when I saw
a microsoft KB on running totals queries where they used DSum not Sum and
when I had a look through microsoft.public.access.queries it seemed that all
the totals queries used DSum.

Your query seems to work well (I did have to add GROUP BY Bags.ItemCode) and
the information is coming out a lot faster in the query than it did before.


Thank you for your help
David

John W. Vinson said:
Thank you John

I had a look at Totals Queries. They are based once again on DSum?
Click to expand...

No, they do not use dsum. Evidently you did not actually try one!
Is there something faster about using DSum in a query vs VBA?
Click to expand...

VBA would certainly slow things down considerably - just another layer of
complexity!
My table structure is quite simple
1) ItemCode - This identifies the item in another table and is a 1-many
relationship with my items table
2) Weight - Weight of item
3) ID - Identifies individual item within item grouping

To get my result of x items @ xx lbs I would use:
"Total = " & DCount("Weight","ItemDetail","Bags.Itemcode=" & me.txtItemCode)
& "items @ " & DSum("Weight","ItemDetail","Bags.Itemcode=" & me.txtItemCode)
& "lbs"
Click to expand...

What's the context? Where are you *using* these expressions? You can see your
database - I cannot!
I have several different item ID's and I use them on a continuous form that
shows an instant update on how many items in the item group and the total
weight for them each.
Click to expand...

Try a Query:

SELECT Bags.ItemCode, Count(*) AS TotalCount, Sum([Weight]) AS TotalWeight
FROM Bags INNER JOIN ItemDetail ON Bats.ItemCode = ItemDetail.ItemCode;

and base a report or form on this query. Adapt as needed for your table and
fieldnames and relationships.
Click to expand...
 
Will the GROUP BY build the running totals seperate for each group?
If so i would need the same.
Where did you add the GROUP BY in John W. Vinson SQL code?

like that:

SELECT Bags.ItemCode, Sum([Weight]) AS TotalWeight
FROM Bags INNER JOIN ItemDetail ON GROUP BY Bats.ItemCode =
ItemDetail.ItemCode;

thx a lot for a reply ... im new to sql queries



David said:
John

No I had not actually tried one. I jumped to the wrong conclusion when I saw
a microsoft KB on running totals queries where they used DSum not Sum and
when I had a look through microsoft.public.access.queries it seemed that all
the totals queries used DSum.

Your query seems to work well (I did have to add GROUP BY Bags.ItemCode) and
the information is coming out a lot faster in the query than it did before.


Thank you for your help
David

John W. Vinson said:
Thank you John

I had a look at Totals Queries. They are based once again on DSum?
Click to expand...

No, they do not use dsum. Evidently you did not actually try one!
Is there something faster about using DSum in a query vs VBA?
Click to expand...

VBA would certainly slow things down considerably - just another layer of
complexity!
My table structure is quite simple
1) ItemCode - This identifies the item in another table and is a 1-many
relationship with my items table
2) Weight - Weight of item
3) ID - Identifies individual item within item grouping

To get my result of x items @ xx lbs I would use:
"Total = " & DCount("Weight","ItemDetail","Bags.Itemcode=" & me.txtItemCode)
& "items @ " & DSum("Weight","ItemDetail","Bags.Itemcode=" & me.txtItemCode)
& "lbs"
Click to expand...

What's the context? Where are you *using* these expressions? You can see your
database - I cannot!
I have several different item ID's and I use them on a continuous form that
shows an instant update on how many items in the item group and the total
weight for them each.
Click to expand...

Try a Query:

SELECT Bags.ItemCode, Count(*) AS TotalCount, Sum([Weight]) AS TotalWeight
FROM Bags INNER JOIN ItemDetail ON Bats.ItemCode = ItemDetail.ItemCode;

and base a report or form on this query. Adapt as needed for your table and
fieldnames and relationships.
Click to expand...
Click to expand...
 
Actually, John's SQL should have been:

SELECT Bags.ItemCode, Count(*) AS TotalCount, Sum([Weight]) AS TotalWeight
FROM Bags INNER JOIN ItemDetail ON Bats.ItemCode = ItemDetail.ItemCode
GROUP BY Bags.ItemCode



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


berni said:
Will the GROUP BY build the running totals seperate for each group?
If so i would need the same.
Where did you add the GROUP BY in John W. Vinson SQL code?

like that:

SELECT Bags.ItemCode, Sum([Weight]) AS TotalWeight
FROM Bags INNER JOIN ItemDetail ON GROUP BY Bats.ItemCode =
ItemDetail.ItemCode;

thx a lot for a reply ... im new to sql queries



David said:
John

No I had not actually tried one. I jumped to the wrong conclusion when I
saw
a microsoft KB on running totals queries where they used DSum not Sum and
when I had a look through microsoft.public.access.queries it seemed that
all
the totals queries used DSum.

Your query seems to work well (I did have to add GROUP BY Bags.ItemCode)
and
the information is coming out a lot faster in the query than it did
before.


Thank you for your help
David

John W. Vinson said:
On Thu, 18 Sep 2008 21:54:01 -0700, David
<[email protected]>
wrote:

Thank you John

I had a look at Totals Queries. They are based once again on DSum?

No, they do not use dsum. Evidently you did not actually try one!

Is there something faster about using DSum in a query vs VBA?

VBA would certainly slow things down considerably - just another layer
of
complexity!


My table structure is quite simple
1) ItemCode - This identifies the item in another table and is a
1-many
relationship with my items table
2) Weight - Weight of item
3) ID - Identifies individual item within item grouping

To get my result of x items @ xx lbs I would use:
"Total = " & DCount("Weight","ItemDetail","Bags.Itemcode=" &
me.txtItemCode)
& "items @ " & DSum("Weight","ItemDetail","Bags.Itemcode=" &
me.txtItemCode)
& "lbs"

What's the context? Where are you *using* these expressions? You can
see your
database - I cannot!

I have several different item ID's and I use them on a continuous form
that
shows an instant update on how many items in the item group and the
total
weight for them each.

Try a Query:

SELECT Bags.ItemCode, Count(*) AS TotalCount, Sum([Weight]) AS
TotalWeight
FROM Bags INNER JOIN ItemDetail ON Bats.ItemCode = ItemDetail.ItemCode;

and base a report or form on this query. Adapt as needed for your table
and
fieldnames and relationships.
Click to expand...
Click to expand...
Click to expand...
 
ok didnt work as i hoped

its still creating running totals form the beginning to the end of the colum

i hoped it would just do that for the grouped items

so that when i display just the fields for group 5 it would build running
totals for them alone and not going through all vakues from group 1 to 4 and
than adding up the values from 5

eg:
at the moment it does this:
2300, 2400, 2500, ...
what i want while showing group 5:
0, 100, 200, 300, ....

the totals alone for the groups are working but not the running totals

any ideas?
 
Back
Top