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

On Thu, 18 Sep 2008 13:27:00 -0700, David <(E-Mail Removed)>
wrote:

>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

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.
--

John W. Vinson [MVP]

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


"John W. Vinson" wrote:

> On Thu, 18 Sep 2008 13:27:00 -0700, David <(E-Mail Removed)>
> wrote:
>
> >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
>
> 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.
> --
>
> John W. Vinson [MVP]
>

On Thu, 18 Sep 2008 21:54:01 -0700, David <(E-Mail Removed)>
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.
--

John W. Vinson [MVP]

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" wrote:

> On Thu, 18 Sep 2008 21:54:01 -0700, David <(E-Mail Removed)>
> 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.
> --
>
> John W. Vinson [MVP]
>

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" wrote:

> 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" wrote:
>
> > On Thu, 18 Sep 2008 21:54:01 -0700, David <(E-Mail Removed)>
> > 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.
> > --
> >
> > John W. Vinson [MVP]
> >

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
http://I.Am/DougSteele
(no e-mails, please!)


"berni" <(E-Mail Removed)> wrote in message
news:33F9D22B-952A-4A40-B69D-(E-Mail Removed)...
> 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" wrote:
>
>> 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" wrote:
>>
>> > On Thu, 18 Sep 2008 21:54:01 -0700, David
>> > <(E-Mail Removed)>
>> > 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.
>> > --
>> >
>> > John W. Vinson [MVP]
>> >

thx a lot for your help

trying it ...

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?