SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Creating an inventory model using Access and...

    For my project in school (now bear with me I haven't used Access on a daily basis for two years), I have to model an inventory system after my current employer's inventory system. The problems I am running into are listed below with as much detail per problem as I can possibly elaborate on at the present time. Those of you who can help me, I thank you in advance, every little bit helps.

    First Problem: I want to create a field within a table ITEM(ItemNum, ItemName, QtyInWH, QtyInStands, TotalOnHand, ....) where [Item, TotalOnHand] is equal to [Item, QtyInWH] + [Item, QtyInStands], however, for some reason it won't allow me to sum the two fields in one table into a third in the same.

    Second Problem: I am creating another table entitled STANDLIST(StandAbbrev, StandName, ItemNum, QtyInStand, ParLevel, etc). I want to have QtyInStand linked to the ITEM table, where [STANDLIST, QtyInStand] automatically adds itself to [ITEM, QtyInStands].

    Third Problem: For the time being I believe this is the last problem. This is the biggie. I have to have reports associated with this system, however, i want to have a real-time reporting done. What i mean by this is that when i go to the report form, it will bring up a specific stand, and all the items, including their inventory for that stand. Because there are three fields that change after every show (entitled Gratis, Spoilage and Ending Inventory), I need to be able to input those figures manually. Then the report will calculate the total starting inventory - gratis - spoilage - ending inventory = total sold * price = Sales. The Sales will add itself to a NET SALES located at the bottom of the report, as the SALES will accumulate after each item has been finished off. Then I want to save the report to a file by a unique identifier and the printer at the same time.

    Again, thanks to anyone who may be able to help. If you can help me further with explanations, feel free to e-mail me, kpearson983@yahoo.com

    ~Kevin

  2. #2
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Kpearson983
    For my project in school (now bear with me I haven't used Access on a daily basis for two years), I have to model an inventory system after my current employer's inventory system.
    Hi Kevin,

    Welcome to SitePoint.

    Generally speaking we don't do homework questions. You have some interesting problems and questions, so please post us how you have attempted to solve the problems and what difficulties you have encountered.

    Quote Originally Posted by Kpearson983
    Again, thanks to anyone who may be able to help. If you can help me further with explanations, feel free to e-mail me, kpearson983@yahoo.com

    ~Kevin
    see this link:
    http://www.catb.org/~esr/faqs/smart-questions.html#noprivate
    I for one will only post answers back to the forum. How would it help the next person if there was only a question and no answer here?

    I'll give you some clue about the first question. What you want to do is to use a calculated column in an Access table, made up of two other columns.

    This is not possible in Access. What you need to do is to create a query, and in the query define a new output column calculated according to your formula.

    HTH

  3. #3
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the heads up. I understand the reasoning for private email followups. I have tried the query builder but the Access 2002 book isn't helping too much, and trying to setup the query based on what the book says, isn't working too well. For the second problem, I need to accomplish the first problem before i can try to inter-add/subtract fields from tables. Obviously that would ahve to be done in a query as well. For the third problem, I have assumed i may have to use a Form, using the fields that i want to real-time update as just null values in the original table. From there, save it (the form) and then have it print in report form. I know there's a lot of code to be able to allow write-allowed access in the report.

  4. #4
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First Problem: I want to create a field within a table ITEM(ItemNum, ItemName, QtyInWH, QtyInStands, TotalOnHand, ....) where [Item, TotalOnHand] is equal to [Item, QtyInWH] + [Item, QtyInStands], however, for some reason it won't allow me to sum the two fields in one table into a third in the same.
    You have to make a query. in the database window go to "Queries", then ""Create new query in design". When it asks you to select a table press cancel, now you see an empty query designer. In the toolbar click on the icon which says "SQL". now the query designer turns into a blank page for you to type your queries without using the braindamaged designer.

    Now you are ready to type your query:

    First Query
    Code:
    select ItemNum, ItemName
      from ITEM
      where TotalOnHand = (QtyInWH  +  QtyInStands)
    Do you see how it works?
    If you want to cheat you can always go to the menu bar and choose "View, Design View" to see how your SQL query is displayed in the designer.

    Whilst learning you should often switch between the designer and the SQL view, so that you see what is actually being designed.

  5. #5
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I understand what you're saying, and actually, i've already been through that process. I think you're misunderstanding what i meant. The TOTALONHAND field is part of the ITEM table, so TotalOnHand has to be equal to the sum of QtyInWh and QtyInStands...I guess what i'm trying to say is..is there a way to have the field in design view of the table, calculate the sum of the two fields in its table?

  6. #6
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Kpearson983
    I guess what i'm trying to say is..is there a way to have the field in design view of the table, calculate the sum of the two fields in its table?
    No.

    This is not possible in Access. What you need to do is to create a query, and in the query define a new output column calculated according to your formula.
    So:

    Code:
    Select (totalinwh + totalinstand) as TotalInHand,
      totalinwh, totalinstand
     from items


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •