Page 3 of 5 FirstFirst 12345 LastLast
Results 31 to 45 of 61
  1. #31
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    I'm trying to understand this set up and am ready to take another stab at it.



    So this transaction table is the backbone of the design. It will handle anything from the intake of a part, to the assignment of a part to a machine, to the purchase of a machine or sale of a machine? The transaction type will categorize everything and add/subtract from inventory? Machines will be kept in inventory as well with their details held in tblMachines? But not all transactions will have an fk for Machines? So my parts table may show 2 of this, 5 of that and say 10 machines? If I'm off here, it may be the end of the line for me.

  2. #32
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    No. That doesn't seem right.

  3. #33
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I have a bit of free time over the weekend, I'll try to mock up an example for you

  4. #34
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    I'd appreciate that. I feel like I'm on the precipice of understanding.

  5. #35
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    lefty2cox,

    I may have not answered your intended question in my previous post.
    I focused on the Note/Comment aspect and this is a common approach. However, on re-reading, I think your intent was having some sort of WorkOrder related to each refurbishment. Some list of things to do and a status showing what has been done, by whom and when and any details/comments of issues/tips...

  6. #36
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    Exactly. I should have been clearer in my initial description. The database has a very specific and unusual purpose. It's not a typical "business" per se. I should also add that I have a weird understanding of access and databases. I've been self employed for the past 30+ years. I've been building databases with access to track my businesses for most of that time. I've built several and am self-taught using books and the interwebs. The result is a weird conglomeration of knowledge. Many aspects of my knowledge may be considered advanced, compared to the average person, while others quite novice. Giant holes, if you will. That's why I understand some advanced concepts including VBA but then fall short of novice in other areas. It makes it difficult to help me. I understand that and apologize for it. I've been task driven during my education, rather than learning properly.

  7. #37
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If you have been successful in a viable, self-employed business for 30+ years using databases that you created, I'd consider that as a very positive and proven approach. Nothing to be apologizing for. Many/most couldn't do that.
    I attached a link to Workorder (in previous post) that may give you some insight on design and use.
    I think CJ may be mocking-up something based on #33.

    There are many Access/Database related articles and tutorials in various formats in the Database Planning and Design link in my signature.

    Good luck with your project. The forum is here to respond to questions.

  8. #38
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    I'm in the process of going through it now. Thanks for pointing it out. it's only been staring me in the face for I don't know how long at this point.

  9. #39
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    Quote Originally Posted by CJ_London View Post
    tblTranTypes
    tranTypePK
    TrantypeName (e,g, Order, receipt, issued, reserved, stock adjustment, etc)
    Multiplier (1 for stock added, -1 for stock issued)

    tblTransactions

    tranPK
    PartFK
    TranTypeFK
    tranDate
    Qty (always positive, depending on transaction type multiplying by the multiplier will convert to negative when required)
    I'm in the process of going over the suggested reading/watching and leaning a lot. I had a question regarding the inventory scheme. Let's say I had a transaction that was a part order, but didn't want it to show in inventory until it was received. Would I use a multiplier of "0" for "Order" in tblTranTypes? In my mind, the quantity of the order would be 0 x Qty, which would be 0. When the part arrives, it would then be added into inventory when I enter a new transaction with a type of "Received" with a 1 as multiplier? Or would I be changing the TranTypeFK in the original transaction record to received? Or am I still way off on everything?

  10. #40
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Really depends on how you want to proceed - I assumed the basis was you only entered data on receipt of an order (or the completion of a sale). You are starting to move into full stock management

    The quantity cannot be 0 otherwise you won't know whether you have ordered 1, 2 or 10 items. So you either use a multiplier of 0 or you can use a a bit of boolean logic or just exclude orders from the summing when calculating current stock

    booleans evaluate to 0 for false and -1 for true.

    If you want to calculate current stock the simple basis is multiplier * quantity. Using boolean logic you would use (multiplier+transactiontype='order') * quantity

    may seem a bit about face but the calc for a stock receipt/movement etc would be

    (1+0) * quantity - since this is not an order

    for an order the calc would be
    (1+-1)* quantity which would return 0

    I've started to put an example together but sounds like you need to do more thinking about what you actually require.... might be into moving goalposts

  11. #41
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    I was thinking more like the multiplier would be 0. So the quantity on an order would be say 2, then the multiplier would be 0. So the stock adjustment would be 0. But I would still know 2 were ordered.

    As far as goal posts moving, you are correct. And I apologize. I'll only add that whatever you give me will not be in vein. I'm not looking for a solution to the problem. I'm looking to learn. Even if it's not my final solution, looking at what you put together will be invaluable to me. I've learned a crap ton reverse engineering from what people have offered. I appreciate that you guys are looking to solve the problem. That's over and above. I'm just looking to learn. Even if I learn something that doesn't fit my exact goal, I've still learned and am grateful.

  12. #42
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here is a link to a 26 minute video on How to Create a Stock Management Database by Software-Matters. It will offer some insight on the requirements and design. I'm sure you will learn something re stock management and database design, but it may not be fully applicable to your situation. It should give you some reference as to how a software firm develops a database application.
    I have no affiliation with Software-Matters, but I have suggested this video to others in the past.

  13. #43
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Please see attached. It is a rough draft and based on the initial premise and is merely a suggestion for the direction you may go. It is based on the assumption that the order of events is along the lines of

    1. buy a machine
    2. buy replacement parts or issue form stock
    3. sell a machine

    The form that opens lists all 'items' - that is parts, labour and machines
    you can filter the list by right clicking on one of the fields
    you add new items at the bottom of the list
    all numeric values are entered as positive

    in the middle is transactions - so select an item and enter a relevant transaction.

    Fields not relevant to the transaction are disabled - all other fields must be completed

    at the bottom is a notes section, each note is tied to the currently selected transaction

    With this method you can see all the transactions related to any item - again that list can be filtered if you want.

    Note you cannot use this form to list all the transactions for a specific machine. To do that, open the simplecosting query - ultimately this would be on a form and you would have filtering options to choose a specific machine

    And to see current stock, open the simplecurrentstock query.

    There are lots of improvements that could be made - for example when issuing stock, for the app to find the price and supplier. And for the machine dropdown list to exclude machines that have been sold - but given you are still deciding what is actually required, not worth doing at this time.

    You need to determine what your actual process is since that has not been defined as yet. For example - do you place orders for multiple items on one supplier for a specific machine? or multiple machines? Do machines have specific suppliers for the specific parts? These could all influence the final form design.

    Anyway, hopefully it will give you a start
    Attached Files Attached Files

  14. #44
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    Thanks. I'll be taking that in today over lunch.

  15. #45
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    Thank you also CJ for the file. I'll be opening that later as well.

Page 3 of 5 FirstFirst 12345 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 33
    Last Post: 09-22-2020, 04:37 PM
  2. passing values between forms
    By CurtisC in forum Access
    Replies: 2
    Last Post: 04-13-2020, 08:55 AM
  3. passing values between forms
    By paulw in forum Access
    Replies: 4
    Last Post: 06-15-2011, 08:52 AM
  4. PASSING Values between Forms
    By chhinckley in forum Programming
    Replies: 1
    Last Post: 08-27-2010, 10:19 AM
  5. Replies: 3
    Last Post: 06-02-2009, 09:51 AM

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