Access template for buy/sell antiques, collect, art profit/loss %

[Guest]

I would like to see templates in Access that combine the database management
of Access and the math abilities of Excel.

For instance, I buy and sell antiques, collectibles, comics, artwork, books,
etc, I use Access to record the item bought, date, purchase amount, sales
tax, shipping, other monetary charges, and the name/address/telephone info of
the seller.

Later, when I sell the item, I use the same item record and add the same
info, but for the buyer.

The math formulas needed for inclusion on the form are from table fields.

Calculations needed:

1) what is the dollar amount and percent of change from date of purchase to
date sold for each dollar category and totals? Is there a profit or loss on
the item?

2) How many days transpired between the purchase and selling date? What
percent is that?

3) At the of the fiscal or year end, what are the totals for all recorded
financials? The mean, mode, and median (Appearing on a report).

Many templates in Access simply record data, yet in the programs help and
tools area, little info is given on how to create formulas for calculations.
Same goes for macros.

Your assistance is greatly appreciated. Thank you very kindly!

Johan
San Jose, CA

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...6ec1&dg=microsoft.public.access.modulesdaovba

 

[Tim Ferguson]

Some of these are trivial in Access anyway:

1) what is the dollar amount and percent of change from date of
purchase to date sold for each dollar category and totals? Is there a
profit or loss on the item?

This is straightforward arithmetic:

PriceOne = DLookup(something)
PriceTwo = DLookup(somethingelse)
PriceChange = Format(PriceTwo-PriceOne,"percent")
PriceOneInDollars = PriceOne * constDollarsPerPoundToday

etc etc

2) How many days transpired between the purchase and selling date?
What percent is that?

There is a ton of code around for calculating date maths in VBA -- try
googling. In this case it could be as simple as

daysBetween = sellingDate - purchaseDate

although the purists will recommend

daysBetween = DateDiff("d", sellingDate, purchaseDate)

3) At the of the fiscal or year end, what are the totals for all
recorded financials? The mean, mode, and median (Appearing on a
report).

Median and mode are certainly easier to do in Excel than in Access, but
it's still reasonably easy. Again, google for something like "median MS
Access".

Remember that you can use Excel to read data in a Jet database: all the
DAO or ADO code is available although you have to open the database or
establish a connection yourself (approx three lines of code each).

Hope that helps


Tim F