Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Order/Inventory/Sales database

Discussion in 'Information Technology' started by equuscycle, Nov 8, 2005.

  1. equuscycle

    equuscycle
    Expand Collapse
    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!
     
  2. Loading...

    Similar Threads Forum Date
    USA NSA has massive database of Americans' phone calls (USA Today 5.11.2006) Breaking News Jun 11, 2013
    Some database errors while navigating SPN forums Announcements Dec 29, 2004

  3. Allan Murphy

    Allan Murphy
    Expand Collapse
    Guest

    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: allanmurphy@unwired.com.au
    "equuscycle" <equuscycle@discussions.microsoft.com> wrote in message
    news:22E45244-0AE6-433E-A1E1-F377F04C1B81@microsoft.com...
    > 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!
     
  4. equuscycle

    equuscycle
    Expand Collapse
    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
     
  5. Larry Linson

    Larry Linson
    Expand Collapse
    Guest

    "equuscycle" wrote

    > ... 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
     
  6. Allan Murphy

    Allan Murphy
    Expand Collapse
    Guest

    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.

    --
    Allan Murphy
    Email: allanmurphy@unwired.com.au


    "equuscycle" <equuscycle@discussions.microsoft.com> wrote in message
    news:22E45244-0AE6-433E-A1E1-F377F04C1B81@microsoft.com...
    > 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!
     
  7. equuscycle

    equuscycle
    Expand Collapse
    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" wrote:

    > "equuscycle" wrote
    >
    > > ... 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
    >
    >
    >
     
  8. Larry Linson

    Larry Linson
    Expand Collapse
    Guest

    "equuscycle" <equuscycle@discussions.microsoft.com> wrote

    > . . . 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
     
  9. equuscycle

    equuscycle
    Expand Collapse
    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" wrote:

    > "equuscycle" <equuscycle@discussions.microsoft.com> wrote
    >
    > > . . . 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
    >
    >
    >
     
  10. Larry Linson

    Larry Linson
    Expand Collapse
    Guest

    "equuscycle" <equuscycle@discussions.microsoft.com> wrote

    > 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
     

Share This Page