Page 2 of 6 FirstFirst 123456 LastLast
Results 16 to 30 of 89
  1. #16
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by tristandoo View Post
    As far as calculated fields how would i get rid of them but still get that data to show up?
    You can create objects like forms and reports. Their class modules can manage the calculations on the fly by including your expressions within unbound controls or the class module directly via VBA.



    Normalize your tables/relations first, then build queries, then forms and reports. Sometimes queries reports and forms are created simultaneously.

  2. #17
    tristandoo is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2014
    Posts
    43
    what do queries do exactly? I tried to figure out their purpose bit could comprehend it.

    If anyone has any ideas on the inventory let me know because I am moving no where fast

  3. #18
    tristandoo is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2014
    Posts
    43
    Here is Version 4.

    Right now Inventory is one of my biggest issues. I have a form that I can edit the inventory amount but it doesn't calculate how much inventory I have after a sale. How on God's earth is this done? Everything I have tried has lead to #Error. I have done the research on how other POS style and Inventory style DBAs work and have attempted to model them to no avail.
    Attached Files Attached Files

  4. #19
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Queries manipulate data by calculating, sorting, and filtering. They join related tables so associated information can be viewed in a single dataset for reporting purpose.

    There are any number of ways to calculate inventory balance and display the result. It is probably easier to do on a report than on form. A lot depends on the database structure. It is essential to get that correct before proceeding. Your Inventory table is almost a normalized structure but the Sold table is not even close. This will greatly complicate inventory balance calculations.

    The broad concept is to sum all transactions of product in and sum all transactions of product out and calculate the difference. Depending on data structure, that could be 1 query or 3 queries or involve a VBA procedure. Review http://allenbrowne.com/AppInventory.html.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #20
    tristandoo is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2014
    Posts
    43
    Would you (or anyone else) like to "draw" this out, explain in great detail, or set this up for me? Defiantly giving that a read.

    I one allowed to do calculations inside querys? Or is this a big no no the same way tables are?

    EDIT:
    Gave it a read through, didn't understand some/most of the VBA (don't know the language is all) still don't quite know where to start. I think the way I have the "Cookies - Sold" table is what is screwing me up.
    Last edited by tristandoo; 01-04-2014 at 04:10 AM. Reason: Addition

  6. #21
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    As I said, the purpose of queries is to manipulate data. Calculations are one way to manipulate data and therefore very appropriate in queries.

    Here is a revision of your db that keeps and handles your original structure.

    If you want the inventory balances to adjust after every image click, that will require code with each image that saves the record.

    BTW, should avoid spaces and punctuation/special characters (underscore is exception) in names. The hyphens in Do-Si-Do field name and Cookies - Sold table name are a real nuisance.
    Attached Files Attached Files
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #22
    tristandoo is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2014
    Posts
    43
    Wow way to go above and beyond what I was even thinking someone would do. Love what you did with it but how do I change the inventory in the table? I have to keep adding more and more to it.

    Well I guess a form that would change that value when new boxes come in would work. just put in the amount of new boxes and it adds it to the query...

  8. #23
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Forms are the best way to provide a public interface. In a pinch, you could type updated values directly into the inventory table based on a physical inventory. I believe it all depends on how elaborate you want to make your DB and how you want to control your inventory and orders placed.

    It looks as though you need to decide what an order actually is. Is it an order that needs to be fulfilled? Is an order the act of a sale? Answers to these sorts of questions will define how your tables are structured and their relationships. In other words, if you are trying to track orders, track sales, keep a thumb on inventory.... you are going to need a more robust table structure.

    Tables first, then queries, then user interface.

    Using June's example, it would not be too dificult to update the inventory with some additional code. However, without a better idea of what you are trying to accomplish and with what features, it may be innapropriate to change the inventory simply because an "Order" was placed.

  9. #24
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    As ItsMe describes, a robust relational database (one that has thorough transaction documentation to allow a 'paper trail' that an auditor would be happy with) will be far more elaborate.

    You could just manually revise the six inventory records to change the InStock value (more accurate name would be Received). Or you can enter a transaction for each shipment received with attributes in table for DateReceived, Quantity, OrderNumber, ShippingNumber, whatever. Either way, the calcs I have set up will work. I do not recommend saving the calculated inventory balance into table (see Allen Browne link again).

    You already have the Inventory form.

    Look at the image of tables in the Allen Browne link for an idea of this 'transactions' model. Look at the Northwind template database available for download from Microsoft. There is also a Desktop Product Inventory template - open Access > File > Small Business.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #25
    tristandoo is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2014
    Posts
    43
    Let me lay out how this is going to work. We will be sitting at a table saling cookies. Someone asks for their cookies, the pos form is then used to input their order, then one of the salers will grab the cookies then the order is closed. Inventory is needed to know if we are out of something and to know how much we have when we get there.

    Is that enough detail on how I want it to work and how the inventory should work out? I don't really need to track shipment info or anything like that. I would like to track what we have sold (cookies_sold table) just so we can tell how many (inventory) and what kind (inventory and sold) to ask for when we get more cookies.

    Get it?

  11. #26
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    None of that info alters any of the advice already given.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #27
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    If that is the case, I would use the DB June uploaded and when you get a new shipment of cookies add the quantity into your inventory table. Each and every time you get a shipment of cookies, use the inventory form to add the quantities shipped into the respective fields. If the field says "20" and you just recieved a shipment of "5", you would type "25".

    This way you do not really have to change anything with the DB. You could, possibly, add another table to list "Teams" or "Groups" or "Tables". Have a table named tblTeams that contains a distinct record for each table. The PK from this table could be stored in your table named "CookiesSold" by adding another column/field to "CookiesSold". This new column would be the foreign key value used to identify the Team that sold the cookie(s).

    Your POS form would have to incorporate a combobox for the user to associate the correct table or team to the sale.

    Also, your POS form could automaticly display the current date by adjusting the properties of your control named "SaleDate". You can set the Default Value by pasting "Date()" ,without the quotes, in the Default Value field found in the properties sheet.

  13. #28
    tristandoo is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2014
    Posts
    43
    Ok well the latest one works well enough to where if I can't get the inventory form to be set up oh well.

    Would it be easy to make a form to just put in the number of boxes that came in to update the inventory?

    (I won't be the only one to use there will be 1 or 2 others that have no Access experience so the simpler for them the better)

  14. #29
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I changed the form that displays at startup to be a menu type form. Now the user has two options to interface with the DB. I grouped these two controls together towards the top. The form I added and the code I wrote is dependent on values in your inventory table not to be adjusted in any way. The way the code is written is referred to as softly typed. In other words, the code will not work if samoas is changed to samoa.

    That is just the way it is. Without building a proper DB that includes many GUI's and tracks many aspects of operations... this is what you have for minimal effort.
    Attached Files Attached Files

  15. #30
    tristandoo is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2014
    Posts
    43
    Thank you so much ItsMe! This should work just fine!

    I have made delete queries before and they have reset the "ID" field. How come they are not doing so now? Maybe I am forgetting a step or something...

    New query(design)
    tblCookiesSold
    close
    *
    Delete
    close and save

    It deletes the records but doesn't reset the autonumber field... Not all that important I guess but just something I noticed.

Page 2 of 6 FirstFirst 123456 LastLast
Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums