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

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

"Taha Ahmed" <(E-Mail Removed)> wrote in message
news:c2f45039-58ea-4b22-a128-(E-Mail Removed)...
> 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

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 ?



On Jun 9, 2:03*pm, "Access Developer" <accde...@gmail.com> wrote:
> 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
>
> "Taha Ahmed" <tahaahme...@googlemail.com> wrote in message
>
> news:c2f45039-58ea-4b22-a128-(E-Mail Removed)...
>
>
>
> > 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 -

On Thu, 9 Jun 2011 09:43:05 -0700 (PDT), Taha Ahmed
<(E-Mail Removed)> wrote:

>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/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

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

"Taha Ahmed" <(E-Mail Removed)> wrote in message
news:4abd5fd3-29b5-463c-8d4c-(E-Mail Removed)...
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 ?



On Jun 9, 2:03 pm, "Access Developer" <accde...@gmail.com> wrote:
> 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
>
> "Taha Ahmed" <tahaahme...@googlemail.com> wrote in message
>
> news:c2f45039-58ea-4b22-a128-(E-Mail Removed)...
>
>
>
> > 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 -

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

On Jun 9, 2:34*pm, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
wrote:
> On Thu, 9 Jun 2011 09:43:05 -0700 (PDT), Taha Ahmed
>
>
>
>
>
> <tahaahme...@googlemail.com> wrote:
> >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/For...-US/accessdev/
> *http://social.answers.microsoft.com/.../en-US/addbuz/
> *and see alsohttp://www.utteraccess.com- Hide quoted text -
>
> - Show quoted text -

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 ?



On Jun 9, 3:11*pm, "Access Developer" <accde...@gmail.com> wrote:
> 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
>
> "Taha Ahmed" <tahaahme...@googlemail.com> wrote in message
>
> news:4abd5fd3-29b5-463c-8d4c-(E-Mail Removed)...
> 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 ?
>
> On Jun 9, 2:03 pm, "Access Developer" <accde...@gmail.com> wrote:
>
>
>
> > 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
>
> > "Taha Ahmed" <tahaahme...@googlemail.com> wrote in message
>
> >news:c2f45039-58ea-4b22-a128-(E-Mail Removed)....
>
> > > 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 -

On Thu, 9 Jun 2011 13:13:14 -0700 (PDT), Taha Ahmed
<(E-Mail Removed)> wrote:

>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/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com