first timer-

[Guest]

This is my first time trying to use Access and I am lost... I can typically "play around" and read through help to become efficient enough to do basic work in a program, but not this time. Maybe I should stick with Excel

I am trying to put together a database to keep track of investments in a small stock club with friends. There are 5 of us and we each contribute different amounts of money to our portfolio each month

I want to enter on a for
1. the Value of the portfolio on the day we each invest our money (before our addition
2. The amount I inves
3-6. The amount each of my friends invest

Using a few simple equations I should be able to keep track of
1. percent of the portfolio each of us own
2.The value of each of our "accounts
Other things too, but if I can just get past this I will be set

I was able to do this easily using Excel, but I want to be able have a form to enter the required data and be able to look at reports instead of just looking at a bunch of numbers on a spreadsheet

One problem is I cannot figure out how to use values from a row above the row I want the results to be in. Here is what I mean... to figure the current value of my portion of the portfolio I need to know (A)What percent I owned after our last investment and (B)The current value of the portfolio (which I will manually enter on the form).

The second problem is I do not even know how to get the calculations to work. I can put an expression in a form, but I can't get the result to transfer to the database.

Maybe I just need to give up...its not easy to admit defeat

 

[Marc]

<snip>

This is my first time trying to use Access and I am lost... I can

typically "play around" and read through help to become efficient enough to
do basic work in a program, but not this time. Maybe I should stick with
Excel.

I am trying to put together a database to keep track of investments in a

small stock club with friends. There are 5 of us and we each contribute
different amounts of money to our portfolio each month.

I want to enter on a form
1. the Value of the portfolio on the day we each invest our money (before our addition)
2. The amount I invest
3-6. The amount each of my friends invest.

Using a few simple equations I should be able to keep track of
1. percent of the portfolio each of us owns
2.The value of each of our "accounts"
Other things too, but if I can just get past this I will be set.

I was able to do this easily using Excel, but I want to be able have a

form to enter the required data and be able to look at reports instead of
just looking at a bunch of numbers on a spreadsheet.

One problem is I cannot figure out how to use values from a row above the

row I want the results to be in. Here is what I mean... to figure the
current value of my portion of the portfolio I need to know (A)What percent
I owned after our last investment and (B)The current value of the portfolio
(which I will manually enter on the form).

The second problem is I do not even know how to get the calculations to

work. I can put an expression in a form, but I can't get the result to
transfer to the database.

Maybe I just need to give up...its not easy to admit defeat

Hi
Access requires a different mindset to Excel.
On the simplest level - and you will need to grow from here - but it is a
starting point:
Each worksheet is a table
Each heading is a field in that table
Each row of data is a record in that table

So worksheet Investors becomes Table Investors with fields name, etc
The tricky part is when your worksheet covers more than one 'idea'
Like if you have Investors sheet with row headings Stock1 Stock2 Invest$1
Invest$2.
Access doesn't work well with this.
What you need is a separate table called Stocks, with fields Id, name,
datefirst invest, etc.
Then you need a table joining your investors to the stocks - say call it
investments - fields - investmentid, investorid (fk), stockid (fk), datein,
amountin, percentin (maybe)

Once you have set up your tables go into the relationships view (the button
with 3 little tables on it) and if not already there drag the investorid of
the investorstable and join it to the investorid on the investments table.
HTH
Marc

 

[tina]

don't feel bad. people unfamiliar with Access typically look at the tables'
row/column structure in datasheet view and conclude that because it looks
like an Excel spreadsheet it will work like one. but Access is very
different from Excel in how it stores and handles data.
another typical newbie mistake is to ignore tables and focus on forms first.
in building a database, your first step is to concentrate on
tables/relationships. having said that, i recommend that you go buy a book
that will teach the basics of using and understanding Access. For a newbie,
the Microsoft Access Bible (version specific) from IDG Books is a very good
teacher and reference. other newgroup post-ers, including the MVPs, can
recommend other equally good (or better) texts, i'm sure.
if it suits you to invest some time in it, you'll find Access a very
powerful and versatile tool that you can get a lot of value out of without
having to learn the software exhaustively, or use complex coding.

good luck!

This is my first time trying to use Access and I am lost... I can

typically "play around" and read through help to become efficient enough to
do basic work in a program, but not this time. Maybe I should stick with
Excel.

I am trying to put together a database to keep track of investments in a

small stock club with friends. There are 5 of us and we each contribute
different amounts of money to our portfolio each month.

I want to enter on a form
1. the Value of the portfolio on the day we each invest our money (before our addition)
2. The amount I invest
3-6. The amount each of my friends invest.

Using a few simple equations I should be able to keep track of
1. percent of the portfolio each of us owns
2.The value of each of our "accounts"
Other things too, but if I can just get past this I will be set.

I was able to do this easily using Excel, but I want to be able have a

form to enter the required data and be able to look at reports instead of
just looking at a bunch of numbers on a spreadsheet.

One problem is I cannot figure out how to use values from a row above the

row I want the results to be in. Here is what I mean... to figure the
current value of my portion of the portfolio I need to know (A)What percent
I owned after our last investment and (B)The current value of the portfolio
(which I will manually enter on the form).

The second problem is I do not even know how to get the calculations to

work. I can put an expression in a form, but I can't get the result to
transfer to the database.

 

[Rolls]

This application does not appear to be complicated. But I'd suggest that
you break it down a little further. I would have a table for the club
members. There would be a table for the securities & cash owned by the
club. There would be another table for cost plus valuation adjustments.
You would look up the daily closing prices and enter changes, so that the
sum of all entries for a particular security is its end-of-day value. This
way the database will calculate the portfolio valuation each day rather than
asking you to calculate and enter the total. It should be able to do this
because you'll need to match daily valuation to calculate the net asset
value per share. When there are capital additions and distributions to
members these will be based on the NAV, same as a mutual fund. After
calculating these factors you'll be able to calculate yield to each member.

Try this with Excel since you're familiar with Excel. Once you can do all
the calculations with Excel, create an Access database.