Setting up an Invoice system

[Caleb]

Hi, I want to create an invoicing system with access but I only have a
limited knowlege of the program. I have a couple of questions and if anyone
can answer any of them I'd appreciate it very much.

Q1. Invoice numbering...
I know there's no point using autonumber because I may need to change
or delete these numbers but I'd like to set something up (probably have to
use VB) so that Access keeps track of the last number I used and for every
new record created uses that number + 1.

Q2. Pricing...
Depending on the customer the prices will differ (ie if they're the
end user or reselling my products). What I'm planning on doing is creating a
table with all the product info and I would have a drop down menu listing the
different buying categories. Depending on what category is selected I need
the coresponding price to show up. What I could do is create a table for
each product and using the Lookup wizard for the "Data Type" create a
dropdown menu with a list of the prices and which category they fall into ie:
R - 9.99 <-- Retail
W - 6.49 <-- Wholesale
WD - 5.29 <-- Warehouse
but I think that that might be a poor choice. So any suggestions would be
helpful.

Q3. Customer Numbering...
I'm really just curious to know what people's opinions are on this.
Currently my customers are sorted by name, they have no "code" assigned to
them. I think it would make managing the database easier if I assigned them
all specific customer IDs. Any thoughts on the best way to go about creating
a number for them? I was just going to start at say 1000 or 100 or something
like that and go up from there. I also thought about just using a short form
limiting the amount of charcters entered to say 5 so John's Utility Store =
JNUTS.

Well that covers all of it. Sorry that this was so long. Once agian any
help or suggestions would be very much appreciated. Thanks!

 

[Rick B]

If I were you, I would start with one of the built-in templates, or with the
Northwinds sample database and modify it to meet my needs.

Rick B

 

[Caleb]

Thanks for the input Rick, I looked at the built in template but it uses the
auto number data type for the product numbering. I still need a way to figue
out how to get the computer to keep track of all invoice entrys and I need to
be able to make adjustments... which I don't think auto number will work for.
I also need a way of changing the the prices for the same product depending

 

[Rick B]

Yes, it comes with access.

To adress your concerns with the template, simply modify the table to
include a part number field. You would need to update the parts table to
include three prices instead of one. In your customer records, you would
need to add a pricingtype field to record what pricing they get.

You will have to modify anything you find ot there, but it certainly seems
easier than starting from scratch.

Good Luck,
Rick B

 

[Caleb]

Thanks for your help so far but I still need some more suggestions. How do I
create the Invoice numbering system? Currently I'm in the 18600s so when (if
evere ) I complete the data base I'll want to start at whatever number I
last used and possibly enter old numbers. I figure I'll just use a 6
character text for the data type with the input mask "######" but I would
like the number to roll forward everytime a new invoice is created. How do I
do this?