Order/Inventory/Sales database

[Guest]

I have a small book shop and have been keeping records the hard way (Excel).
I'm trying to create a database to track my purchases and sales and be able
to pull out reports of inventory on hand. I've got most of it figured out
(on paper -- haven't gotten to the creation part yet), but I'm stumped by one
thing. I sometimes order lots of books in bulk (50-100 at a time) and
sometimes come across deals of single books. I'd like to be able to create
reports that show the unit cost of each book and what I've sold it for so I
can do a P&L on individual items to see if it's worth my while to continue to
buy lots from certain sellers. Do I need to create two tables: one to enter
the purchase of lots and then to allow me to enter each inventory item
separately and another to list individual books purchased? And if so, how do
I link those so that my inventory listing sees everything that's still
available?

Maybe I'm tryng to make this harder than it is, so any help will be much
appreciated!

 

[Allan Murphy]

Check out the Office template at these Microsoft sites
http://office.microsoft.com/en-us/templates/TC010184581033.aspx?CategoryID=CT011366681033
and
http://office.microsoft.com/en-us/templates/TC010175081033.aspx?CategoryID=CT011366791033.
You will need to create the database with the ideas from both templates.

I have created a database for an investment property that I use to produce
various financial reports. I will send you a copy if want more ideas.

Allan Murphy
Email: (e-mail address removed)

 

[Guest]

Thanks, Allan. I'll check out the links you gave me and let you know if I
need to see a copy of what you've done. I appreciate your help!

Tina

 

[Larry Linson]

... small book shop ... create a database to track my purchases and
sales and be able to pull out reports of inventory on hand. I've got
most of it figured out (on paper -- haven't gotten to the creation part
yet), but I'm stumped by one thing.

Oh, you are 'way ahead of where I was when I created my first application!
Congratualations.

... order lots of books in bulk (50-100 at a time) and sometimes come
across deals of single books. I'd like to be able to create reports that
show the unit cost of each book and what I've sold it for so I can do
a P&L on individual items to see if it's worth my while to continue to
buy lots from certain sellers.

Do I need to create two tables: one to enter the purchase of lots and
then to allow me to enter each inventory item separately and another
to list individual books purchased? And if so, how do I link those
so that my inventory listing sees everything that's still available?

I see no reason to have two tables. One "Lot Purchase" table with a Lot
IDentifier so you can link individual books in Inventory to the lot. As you
enter a Lot, you will enter the number of books in that Lot -- one is a
perfectly valid number. You could, if your requirements demand it, and it
appears they _might_, at this point use code and a query to add a record for
each book to Inventory. I suggest that the Lot Purchase table may be mostly
just for historical interest, so you calculate the per book cost and save it
with the book information in Inventory.

That same Inventory table can include Sales information, and, if you need
the space, after a decent interval, you could archive the old Sales
information. But, for most purposes, you will be working from your Inventory
table. I record with no Sales Price is "unsold" and that can be the criteria
for selecting books for your InStock report. A record with a Sales Price is
"sold" and that can be the criteria for your Sales report. Obviously, there
will be some dates and perhaps other information, as well. Dates will be
used in selecting for the Reports, too... you will need, at a minimum,
Purchase Date and Sale Date.

Maybe I'm tryng to make this harder than it is, so any help will be much
appreciated!

Actually, my brief narrative may well make it sound a good deal simpler than
it will be, so take that as having been forewarned. Only you know the needs
of your business and your mode of operation. Writing a spec and design is
not a trivial task... much less one that could be covered "in depth" in a
newsgroup response.

Larry Linson
Microsoft Access MVP

 

[Allan Murphy]

Beside the templates that I suggested I also have an inventory database that
I developed that keeps track of the stock movement. It was develop for a few
years ago for an airline to record Duty Free stock on hand, requests and
delivery etc.

This was required as sometimes the airline did not carry enough quantity of
items that they had for sale on international flights and needed a method to
record requests and deliveries etc.

If you need a copy let me know.

 

[Guest]

Larrry -- THANK YOU! I was trying to make this much harder than it needed to
be, and your suggestion of a "lot of 1" for single purchases solved the
problem that had me stumped!

You said I was way ahead of you when you created your first application, but
I'm actually not. I used access about 7 years ago (with help and training)
to create a database to merge the part numbering systems of 2 companies
during a merger. I thought, hey, if I did something that complex, creating a
database to track my purchases/sales/inventory on hand should be a snap!
WRONG! Like any "language", when you don't use it for a few years, you
forget a LOT and reading throught some of these threads to get ideas has been
similar to trying to read a foreign language! So, I REALLY appreciate your
simple answers!

Allan -- I also appreciate your links and offers to look at databases you've
created. Unfortunately, I've forgotten so much about Access that I'm
struggling to figure out how they did some of the things that were done in
the 2 samples I've been looking at. So at this point, trying to look at
something you created may not help me much. I may have to bite the bullet
and find some refresher classes to remind myself of all the things that I've
forgotten and am now floundering with!!!!

Thanks again, both of you!
Tina

 

[Larry Linson]

. . . Like any "language", when you don't use it for a
few years, you forget a LOT and reading throught
some of these threads to get ideas has been similar
to trying to read a foreign language! So, I REALLY
appreciate your simple answers!

In a previous incarnation as a mainframe and minicomputer programmer, over
the years (through 1991), I used a list of languages as long as your arm.
I'd be hard put to "re-start" with most any of those. So, you are not alone!

It's the freely-given help I got with those languages and applications that
led me to try to help others here in newsgroups. And, you know what I
found -- answering questions, sometimes having to try the approach first to
make sure it will work, I often "learn" more than I "teach." And, I also
learn by just reading many threads of questions and answers, for which
learning, I am grateful to those who ask and to those who answer.

Larry Linson
Microsoft Access MVP

 

[Guest]

I hear you LOUD and CLEAR! I used to teach a lot of my co-workers different
Microsoft applications (when I used them ALL the time), and a lot of times
I'd have to sit down to figure out how I did something before I could teach
them - sure helped me to remember it (for awhile, anyway!).

One more question: I've changed my premise and now have a table for
purchases with a field for LotID, which will specify the number of books in
any one purchase. Is it a query that I will use to automatically prompt me
for X number of item specifics - X being the LotID (title, author, etc) to
fill in another table called Products?

Once I know if I'm on the right track I'll have to do some more digging to
figure out just how to write it, but I first just wanted to make sure that a
query would do the job I'm looking for.

Thanks, Larry!

 

[Larry Linson]

One more question: I've changed my premise and now have a table for
purchases with a field for LotID, which will specify the number of books in
any one purchase. Is it a query that I will use to automatically prompt me
for X number of item specifics - X being the LotID (title, author, etc) to
fill in another table called Products?

If you can group only books with the same title and author as your lots, and
that was what I had in mind, even if a particular purchase from your
supplier included multiple lots, I would expect you to write VBA code to
execute a Query or SQL that will create an Inventory record for each book,
with a foreign key pointing back to the Lot in the purchases table. If you
have a lot of data, you may want to have a Purchases table and a related
Lots table.

If you reorder books with some regularity, you might want to have a Books or
Products table, and include a foreign key pointing to that Table in either
the Inventory record or the Lot record, to eliminate the need to reenter and
store duplicates.

See, I told you I might have oversimplified. <GRIN> But, adhering to good
relational database design (normalization) is going to save you lots of
grief when you need (as is almost always the case) to use the information
for purposes you did not anticipate when you originally designed the
application.

Once I know if I'm on the right track I'll have to do some more digging to
figure out just how to write it, but I first just wanted to make sure that a
query would do the job I'm looking for.

Best of luck with your application. Keep asking questions if you run into
stumbling blocks and, just maybe, we will all gain some knowledge.

Larry Linson
Microsoft Access MVP