acess sum

[Taha Ahmed]

Hi i am new to access, not using code just the basics..

the problem is i have 4 columns and need another one... called total
market value..

value date / asset class / yield / market value

so i need a 5th one .. I want it to display the total of the market
value at each record...

for example..

5/1/2010 / muni / 5%/ 50000/160000

5/2/2010/ corp/ 6%/ 60000/160000

5/3/2010/ corp/ 7%/ 50000/160000

i tried using the sum .. i enter in the build sum("[market value]") it
doesn't work.. it says" you tried to execute the query that does not
include the specified expression "value date" as a part of the
aggregate function",

i have even tried to use dsum, that apparentlyw orks but that does not
allow me to import the total field in excel.

btw i am using access 2003..

please help

 

[Access Developer]

A good way to do that in Access would be to show the data in a Report.
Include market value in the Query that's RecordSource for your Report, the
second one as a Calculated Field with a slightly different name (e.g., the
Field in the Query Might read MktValForTot : [Market Value]. In the text
box in which you display the Field MktValForTot, set the Running Sum
property to "Over All". I believe that will accomplish what you want.

 

[Taha Ahmed]

Thanks for your help. The problem is that I don't want a report I want
to import this query into excel. Any other way ?

is there a code that i can copy/paste in vba editor ?

A good way to do that in Access would be to show the data in a Report.
Include market value in the Query that's RecordSource for your Report, the
second one as a Calculated Field with a slightly different name (e.g., the
Field in the Query Might read MktValForTot : [Market Value].  In the text
box in which you display the Field MktValForTot, set the Running Sum
property to "Over All".  I believe that will accomplish what you want.

--
 Larry Linson, Microsoft Office Access MVP
 Co-author: "Microsoft Access Small Business Solutions", published by Wiley
 Access newsgroup support is alive and well in USENET
comp.databases.ms-access

Hi i am new to access, not using code just the basics..

the problem is i have 4 columns and need another one... called total
market value..

value date / asset class / yield / market value

so i need a 5th one ..  I want it to display the total of the market
value at each record...

for example..

5/1/2010 / muni / 5%/ 50000/160000

5/2/2010/ corp/ 6%/ 60000/160000

5/3/2010/ corp/ 7%/ 50000/160000

i tried using the sum .. i enter in the build sum("[market value]") it
doesn't work.. it says" you tried to execute the query that does not
include the specified expression "value date" as a part of the
aggregate function",

i have even tried to use dsum, that apparentlyw orks but that does not
allow me to import the total field in excel.

btw i am using access 2003..

please help- Hide quoted text -

- Show quoted text -

 

[John W. Vinson]

Hi i am new to access, not using code just the basics..

the problem is i have 4 columns and need another one... called total
market value..

value date / asset class / yield / market value

so i need a 5th one .. I want it to display the total of the market
value at each record...

for example..

5/1/2010 / muni / 5%/ 50000/160000

5/2/2010/ corp/ 6%/ 60000/160000

5/3/2010/ corp/ 7%/ 50000/160000

i tried using the sum .. i enter in the build sum("[market value]") it
doesn't work.. it says" you tried to execute the query that does not
include the specified expression "value date" as a part of the
aggregate function",

i have even tried to use dsum, that apparentlyw orks but that does not
allow me to import the total field in excel.

btw i am using access 2003..

please help

I'm not sure I follow. What was the SQL view of the query using DSum()? And
what exactly are you summing? What happened when you tried to export?

A pure SQL solution would use a Subquery:

SELECT [Value Date]. [Assed Class], [Yield], [Market Value], (SELECT
Sum([Market Value] FROM yourtable) AS TotalMarketValue;

The subquery could have criteria if "at each record" refers to some subset of
the data in the table.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Access Developer]

Again, easiest way... import the information into Excel without the total.

On the first row, set the next column equal to the last (market value) . On
the second row,
create a formula defining the next column as the sum of the previous row's
market value and total. Highlight that cell and drag it down to the end of
your data.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

Thanks for your help. The problem is that I don't want a report I want
to import this query into excel. Any other way ?

is there a code that i can copy/paste in vba editor ?

A good way to do that in Access would be to show the data in a Report.
Include market value in the Query that's RecordSource for your Report, the
second one as a Calculated Field with a slightly different name (e.g., the
Field in the Query Might read MktValForTot : [Market Value]. In the text
box in which you display the Field MktValForTot, set the Running Sum
property to "Over All". I believe that will accomplish what you want.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

Hi i am new to access, not using code just the basics..

the problem is i have 4 columns and need another one... called total
market value..

value date / asset class / yield / market value

so i need a 5th one .. I want it to display the total of the market
value at each record...

for example..

5/1/2010 / muni / 5%/ 50000/160000

5/2/2010/ corp/ 6%/ 60000/160000

5/3/2010/ corp/ 7%/ 50000/160000

i tried using the sum .. i enter in the build sum("[market value]") it
doesn't work.. it says" you tried to execute the query that does not
include the specified expression "value date" as a part of the
aggregate function",

i have even tried to use dsum, that apparentlyw orks but that does not
allow me to import the total field in excel.

btw i am using access 2003..

please help- Hide quoted text -

- Show quoted text -

 

[Taha Ahmed]

Really I don't how to use SQL and VB, I was just using the
Dsum("[Market Value]", "[table name]") in the expression builder box.
this did do what i wanted it to do. But the problem is that when i
imported this to excel, all other fields imported except the Total
Market Value (the dsum function). Please help if you can ?

I believe i have to use

SELECT [Value Date]. [Assed Class], [Yield], [Market Value], (SELECT

Sum([Market Value] FROM yourtable) AS TotalMarketValue;

in the vb editor page, as new module ? is that ryte ?

also when you say yourtable ? my table name is "home data range"
should that be in parenthesis or bracket or "" ?

Thanks, really appreciate your help

Hi i am new to access, not using code just the basics..

the problem is i have 4 columns and need another one... called total
market value..

value date / asset class / yield / market value

so i need a 5th one ..  I want it to display the total of the market
value at each record...

for example..

5/1/2010 / muni / 5%/ 50000/160000

5/2/2010/ corp/ 6%/ 60000/160000

5/3/2010/ corp/ 7%/ 50000/160000

i tried using the sum .. i enter in the build sum("[market value]") it
doesn't work.. it says" you tried to execute the query that does not
include the specified expression "value date" as a part of the
aggregate function",

i have even tried to use dsum, that apparentlyw orks but that does not
allow me to import the total field in excel.

btw i am using access 2003..

please help

I'm not sure I follow. What was the SQL view of the query using DSum()? And
what exactly are you summing? What happened when you tried to export?

A pure SQL solution would use a Subquery:

SELECT [Value Date]. [Assed Class], [Yield], [Market Value], (SELECT
Sum([Market Value] FROM yourtable) AS TotalMarketValue;

The subquery could have criteria if "at each record" refers to some subset of
the data in the table.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -

 

[Taha Ahmed]

the problem with that is everytime I update the data in the access
query, lets say Value Date >2/5/2010 will display all the records
after that day. But the column that I have manually entered and then
later dragged would not automatically disappear from the rows that
have no value. May be I am not able explain this but i have tried
what you have mentioned and it does not serve the purpose.

I just want to do this in access, i would really appreciate if you can
help me out with this.. it been 6 darn hours and im not getting
anywhere with this !

can't i just do sum(market value) and that should give me the total ?

Again, easiest way... import the information into Excel without the total..

On the first row, set the next column equal to the last (market value) .  On
the second row,
create a formula defining the next column as the sum of the previous row's
market value and total.  Highlight that cell and drag it down to the end of
your data.

--
 Larry Linson, Microsoft Office Access MVP
 Co-author: "Microsoft Access Small Business Solutions", published by Wiley
 Access newsgroup support is alive and well in USENET
comp.databases.ms-access


Thanks for your help. The problem is that I don't want a report I want
to import this query into excel. Any other way ?

is there a code that i can copy/paste in vba editor ?

A good way to do that in Access would be to show the data in a Report.
Include market value in the Query that's RecordSource for your Report, the
second one as a Calculated Field with a slightly different name (e.g., the
Field in the Query Might read MktValForTot : [Market Value]. In the text
box in which you display the Field MktValForTot, set the Running Sum
property to "Over All". I believe that will accomplish what you want.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

news:c2f45039-58ea-4b22-a128-ddc37543cc80@v18g2000yqe.googlegroups.com....

Hi i am new to access, not using code just the basics..
the problem is i have 4 columns and need another one... called total
market value..
value date / asset class / yield / market value
so i need a 5th one .. I want it to display the total of the market
value at each record...
for example..
5/1/2010 / muni / 5%/ 50000/160000
5/2/2010/ corp/ 6%/ 60000/160000
5/3/2010/ corp/ 7%/ 50000/160000
i tried using the sum .. i enter in the build sum("[market value]") it
doesn't work.. it says" you tried to execute the query that does not
include the specified expression "value date" as a part of the
aggregate function",
i have even tried to use dsum, that apparentlyw orks but that does not
allow me to import the total field in excel.
btw i am using access 2003..
please help- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[John W. Vinson]

Really I don't how to use SQL and VB, I was just using the
Dsum("[Market Value]", "[table name]") in the expression builder box.
this did do what i wanted it to do. But the problem is that when i
imported this to excel, all other fields imported except the Total
Market Value (the dsum function). Please help if you can ?

I believe i have to use

SELECT [Value Date]. [Assed Class], [Yield], [Market Value], (SELECT

Sum([Market Value] FROM yourtable) AS TotalMarketValue;

in the vb editor page, as new module ? is that ryte ?

No; this is not VBA code, it's a Query. It's SQL, the native language of
Queries; the query design grid is just a tool to build SQL strings.

Create a new Query in your database. Don't select any tables. Switch to SQL
view using the View menu option or the SQL choice on the View icon at the left
end of the ribbon. Copy and paste the following over the word SELECT; which
should be all that you see:

SELECT [Value Date]. [Assed Class], [Yield], [Market Value],
(SELECT Sum(X.[Market Value]) FROM [home data range] AS X) AS TotalMarketValue
FROM [home data range];
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com