Sales Agent Database

[Joe]

I am an EXTREME Access newbie.

A good friend of mine sells things for people on eBay. He acts as a selling
agent. In order to track everything, I set up a spreadsheet system that works
just fine. Later, I made a VERY simple Access database for him for inventory
tracking. However, he asked me if it would be possible to set up the
spreadsheet system as an Access database because he really likes the
interface a lot more than working with spreadsheets. So I told him I’d give
it a shot.

In the spreadsheet system, there are 3 sheets to the workbook: Customers
(the people he sells things for), Buyers (the people who win the auctions),
and Auctions (which has all of the details of the auctions like auction
number, start date, end date, starting bid, etc.)

The problem I’m having is that a number of different fees and charges are
predicated on two amounts (Starting Bid and Final Sale Price). As it is now,
he types in those two amounts and everything else (eBay fees, sales tax,
commission, etc.) is calculated for him using Excel formulas. A couple of the
formulas are quite complex with 5 or 6 nested IF/AND statements.

What I haven’t been able to figure out is: How can I set up a table so when
he enters the amounts, the associated fees and charges are then found or
calculated, displayed in the table, and added to the total price? So far, I
haven’t been able to make it work using any combinations of tables, queries,
or reports, so I’m thinking that I may have a basic misunderstanding of how
something like this should be designed.

Any help would be most appreciated.

Joe

 

[Sharkbyte]

Joe:

eBay provides their fee schedule, to the public, so you should be able to
develop algorithms based on this information. All you would need is the
condition, which eBay also provides.

Because of the various pricing levels, the fees are built around, I would
recommend using Case statements to eliminate many of the nested IF statements.

It would seem to be a pretty straight forward project. But I could be
missing something. Please let me know if I am.

Sharkbyte

 

[Joe]

Sharkbyte,

Thanks very much for responding so quickly!

Yes, I have all of the algorithms and conditions figured out already, I just
can't seem to figure out how to adapt them to Access. I'm on my way to look
up Case Statements right now.

Again, Thanks for your help!

-Joe-

 

[tina]

to add to Sharkbyte's reply: you can't implement a coding solution at the
table level directly, because there are no events to run it from. your user
shouldn't be entering data directly into Access tables, anyway. that's what
forms are for. at the form level, you can write code to run a Case
statement, or anything else you need including update/append/delete queries,
based on events in the form and its' controls (a button click,
entering/changing data in a control, etc).

hth

 

[Joe]

tina,

Ah-HA! So apparently, I was designing this exactly backwards. I should be
using forms to input data and tables to store it? I was attempting to do the
reverse. (Sorry, but as I said, I am an EXTREME newbie.)

I did look at the sample databases, and the "getting started" files, but I
guess I just plain missed the correct concept of the interaction between
forms, tables, queries and reports.

I'll go back to square one, re-read the basics, and try again. (It's not
like this is a massively complex thing. As Sharkbyte said, it's a really
straight forward project.)

Thank you,

Joe