Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Dec 2018
    Posts
    12

    Best way to mark items as sold in bulk as part of pallet?

    I'll explain as best I can, hopefully it doesn't get confusing. I'm very new to Access and this is just something I'm messing around with in my free time to eventually show the boss once I get it running. I'll attach the DB at the end, but all the data is just junk I input as I test things out.

    I work in a university surplus warehouse and we get lots of computers. The vast majority get put on a pallet and sold at an auction once the warehouse gets full. Right now I have it set up to when we receive a computer, it gets assigned a Location, for example Pallet 1. I'm wanting to be able to mark all items on Pallet 1 as sold and be able to indicate the price for just the pallet, but keeping it annotated that the computers were part of a pallet for reporting purposes. I had tried using an update query to, for example, mark Pallet 1, with 50 computers, as sold $100. But that made it look as if each computer sold for $100. I had kind of tried messing around with a separate Locations table but I had to make the Location the Primary Key, which wouldn't allow me to reuse the Pallet numbers. We have auctions several times a year, so I want to be able to assign computers to a Pallet #>Mark Pallet # as sold with a price for the pallet, not the individual items>Re-use that pallet number for the next round of computers. I appreciate any help. I just took an intro to Access type class recently and it was really fun, so I'm enjoying trying to learn all this.

    EDIT: I have removed the Google Drive link and uploaded the DB to the post.



    Surplus_Inventory.zip
    Last edited by lowfatcottagecheese; 12-23-2018 at 05:25 PM. Reason: Added attachment instead of link.

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Suggest a table for the computers, one for pallets (I'd use this as the source for a combo to pick the pallet), a junction table tblCompPallet to show which units are on a pallet, and tblPalletPrice. A computer makes one record in its own table, which allows that unit to be sold individually or on a pallet. tblCompPallet lists each unit (computer) PK and repeats the pallet ID. tblPalletPrice lists a pallet once with the pallet price.

    Will take a look at your design in the meantime.
    EDIT:
    On second thought, please upload your db to your post. I don't like accessing files from drop boxes and such. Some people here will simply not do so.
    Compact/repair and zip your db before uploading the zip file.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Problem I see is tblLocation values will not be unique. I am guessing "Sales Floor" will have multiple items associated with it that are individual sales. And you already said you want to reuse pallet numbers.

    Should assign a unique lot number (could be autonumber PK), even if the lot consists of a single item. Then a related table would list the individual items packed into that lot (again, even if there is only 1 item).

    tblLots would have fields like LotNum, Location, DateSold, SaleAmount. The related tblLotDetails would have fields at a minimum LotID_FK, InventoryID_FK. Or maybe just save the LotID_FK into tblInventory since each item can be in only one lot.

    Not seeing a SerialNumber field in tblInventory.


    I do prefer downloading db from forum but haven't had issue with other downloads, unless it requires me to set up a user profile to 'sign in', then I abandon.
    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.

  4. #4
    Join Date
    Dec 2018
    Posts
    12
    Thanks, I'll try messing around with that. I think I still have quite a bit to learn--didn't think I'd need so many tables and I need a better understanding of FKs and relationships but I'll keep at it. Thanks for the info.

  5. #5
    Join Date
    Dec 2018
    Posts
    12
    Thanks, I'll look into a junction table. Like I mentioned in another response, I have a lot to learn because some of what you guys are saying is foreign to me lol I did upload my zipped DB into the original post.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    LFCC,

    Here is a link to many articles on Database Planning and Design.
    I recommend you work through 1 or 2 of the tutorials from RogersAccessLibrary mention in the link.
    You will learn about tables and fields and relationships to design a database. What you learn through this experience can be used with any database. Since you have been working/messing with the subject matter, I would think 30 to 45 minutes working through the tutorial(s) will get you ready to work efficiently with your own set up.

    The articles and videos are great for concepts and refresher and reference, but the tutorials are the heart of the process.
    Good luck.

  7. #7
    Join Date
    Dec 2018
    Posts
    12
    Quote Originally Posted by orange View Post
    Thanks, appreciate the resource.

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Have to wonder why you have tables for computers and inventory (which is computers also) plus a table for processor. If you were building these things, then I get the table for processors as you might want to have a lookup table to pick what goes into the build. Since this is about used equipment, I don't get it. If all you want is a list of processors, manufacturers, models, etc. that you have in inventory, you dont' repeat that somewhere else because a) it's double data management and b) you can get that info from those fields of your inventory table.

    The recommendation for further study is a good one.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Good point Micron. tblComputers and tblProcessors are lookup tables for model types (can same model computer be matched with various model processors?) - this does allow for a record that more completely describes item. However, usual approach would be to have a unique identifier field (autonumber will serve) as primary key in tblComputers which would be saved into tblInventory as a foreign key. Then the descriptive info (model, manufacturer, type) would not be duplicated into tblInventory. What you are doing will work, just isn't fully normalized data.

    tblInventory has info about specific units, which is why I suggested there should be a SerialNumber field.

    Note that Pentium is misspelled as Penitum.
    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. #10
    Join Date
    Dec 2018
    Posts
    12
    Quote Originally Posted by June7 View Post
    Good point Micron. tblComputers and tblProcessors are lookup tables for model types (can same model computer be matched with various model processors?) - this does allow for a record that more completely describes item. However, usual approach would be to have a unique identifier field (autonumber will serve) as primary key in tblComputers which would be saved into tblInventory as a foreign key. Then the descriptive info (model, manufacturer, type) would not be duplicated into tblInventory. What you are doing will work, just isn't fully normalized data.

    tblInventory has info about specific units, which is why I suggested there should be a SerialNumber field.

    Note that Pentium is misspelled as Penitum.
    So in one of my forms, I have it set to where you can pick the model from a combo box and the manufacturer and type (all-in-one/laptop/desktop) autopopulate into a text field. We sometimes get the same model computer with varying processors, so that tblProcessors is to allow a combobox to specify the processor on a form. Is that not the best way? I think I'm thinking of it in an Excel mindset somewhat. Also, the ServiceTag is specific to each computer, that's what the university puts on it as a barcode, so that's what we would typically scan with a scanner as opposed to typing in the serial number.

    Thanks again for all the info, like I said this is just a personal project, so I'm going to step back and learn some more before moving forward. Appreciate the help.

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    LFCC,

    What info is required by the University and/or Department? It's often a good starting point to identify the output, and keep that in mind to ensure your design returns/allows access to the necessary output data.

    Since you reuse pallets, and you have multiple auctions in a year, you might want to consider some "concept" that allows this combination to be unique. eg Pallet1 Auction1_2018 (Lot/Batch/Group..)

    for example, mark Pallet 1, with 50 computers, as sold $100. But that made it look as if each computer sold for $100
    For clarity, you are saying the 50 computers (the contents of pallet 1) for $100. You are not required to identify
    selling price of individual computers.
    Your database shows the computer Make, model, manufacturer, processor....What is the purpose of this? Is it used somewhere by someone?

  12. #12
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I have it set to where you can pick the model from a combo box and the manufacturer and type (all-in-one/laptop/desktop) autopopulate into a text field.
    The idea is fine, but I didn't realize that was in play because you don't have type and model table(s) but mostly because you're not using any PK values as foreign keys in the other tables. In other words if we assume that the ProcessorID (which you don't have) is an autonumber field, that PK number would go in the inventory table as a foreign key. Thus what would show in tblInventory.Processor would be 5 (for example) not the processor text value. In a query the text value comes from joining InventoryFk to ProcessorPK and including the name value field ( where you have i3) from Processor. Just to be clear, text values can be used as PK's but I don't think there's a lot of acceptance for that.

    Type (which is a reserved word) perhaps should also be a combo with a row source from tblTypes. Typically, what's known as cascading combos is employed to control the type based on the computer chosen. It might be notable that while you're controlling (somewhat) the type based on model, I can pick any processor - correct or not. That might be OK for your business model - just thought I'd make a note of it.

    Are you aware that when autopopulating the Type textbox it is still editable? That may be fine for your needs. Another thing to be aware of when using bound combos is that an existing value can be accidentally changed. The consideration then, is whether or not to lock such controls on existing records. Not saying you should, just that it's something to consider.

    All in all, it looks like you're on the right path. If you can get co-workers to test when you think it's ready, they might find ways to break it (a good thing) before you bring it to management.
    Good luck!
    Last edited by Micron; 12-24-2018 at 10:22 AM. Reason: spelin and gramur
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    Join Date
    Dec 2018
    Posts
    12
    Quote Originally Posted by orange View Post
    LFCC,

    For clarity, you are saying the 50 computers (the contents of pallet 1) for $100. You are not required to identify
    selling price of individual computers.
    That is correct.


    Your database shows the computer Make, model, manufacturer, processor....What is the purpose of this? Is it used somewhere by someone?
    Well we get all those different combinations in, so I figured that's how we would want to track it. I guess you're saying why need all that data? why not just make it receive a computer with ### S/N and leave it at that? Honestly our department is like 3 people and we're kind of the red headed step child of the university, I just know that for now we put this into an Excel sheet and the manger sends it to his boss who does nobody-knows-what with it...

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I have maintained inventory records and always include make/model/etc info. We also included the serial number if there was one as those service tags can become detached or worn on some equipment (perhaps unlikely for computers but inventory wasn't just for computer equipment).

    Doesn't sound like to me that you are maintaining the master inventory for the organization. When you receive the equipment for surplus, aren't you provided documentation itemizing the items and sign a receipt (chain of custody)?

    However, should not save manufacturer and type into tblInventory. In your case, Model is the primary/foreign key. Retrieve manufacturer and type in a query that joins tables. As Micron noted, text as keys is not ideal because they index slower and use more storage.
    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.

  15. #15
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Well sort of....
    My real point is what exactly is the requirement. Since the ultimate user is the Boss, and there is a middle man -the manager - between you and the Boss, and you don't know precisely what the Boss needs, it's difficult to provide focused advice.

    Did you work through a tutorial I mentioned in post #6?

    General process seems to be:

    Gather Computers-->Identify/Record Attributes-->Assign to Pallet-->Auction-->Sale-->Report

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-11-2017, 11:38 AM
  2. How can I hyperlink in bulk
    By Tony of Wigmore in forum Programming
    Replies: 1
    Last Post: 02-12-2016, 04:19 AM
  3. Bulk Assigning of available data
    By etdahmer in forum Access
    Replies: 1
    Last Post: 06-09-2014, 03:24 PM
  4. Can report items be part of a "group"?
    By tstoneami in forum Reports
    Replies: 2
    Last Post: 08-13-2013, 11:51 AM
  5. How Can export Large table part by part
    By shabar in forum Import/Export Data
    Replies: 2
    Last Post: 02-04-2013, 06:29 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