Results 1 to 12 of 12
  1. #1
    eugzl is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    68

    need help in structure inventory DB

    Hi All.


    I want to create a small database for my equipment inventory but I have some problems on picking a structure.
    I create some tables:
    Device Brand DeviceType Model
    DeviceID
    BrandID
    BrandDeviceID
    ModelID
    Description
    BrandID
    Brand



    DeviceTypeID
    DeviceType



    ModelID
    BrandID
    DeviceTypeID
    Model

    I briefly describe my script. The f_Device form gives user ability to add new device to the system. That form has cascade combo boxes cboBrand, cboDeviceType and cboModel. When user selected all required value data will saved. In case when user need add device but brand of new device doesn't exist in the system a user will click button from the right from cboBrand to call form fBrand to add new brand name. In case when user need add device but device type doesn't exist in the system a user will click button from the right from cboDeviceType to call form fDeviceType to add new device type. The similar story for device model.
    The Form1 is test form. It has only one button to call f_Device form to add new device.
    The f_Defice form binding to Device table
    The fBrand form binding to Brand table
    The fDeviceType form binding to DeviceType table
    I'm feeling that DeviceType table has problem but cannot figure out how to fix. What structure of that table must be. And second question is how to avoid duplicates in case when need to add new device.
    I will very appreciate if someone can help me to solve those problems.
    In the attachment my last version of Access file
    Thanks.
    InventoryDBF.zip

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Hmm, have a look at

    tblDevices
    dDeviceID (PK)
    dDevice
    dDeviceTypeID (Linked to tlkpDeviceTypes)

    tlkpDeviceTypes
    dtDeviceTypeID (PK)
    dtDeviceType

    tblBrands
    bBrandID (PK)
    bBrand

    tblDeviceBrands (***Can a Device have more than one Brand?)
    dbDeviceBrandID (PK)
    dbDeviceID (Link to tblDevices)
    dbBrandID (Link to tblBrands)

    tblModels (***Device Type is tied to Device so you don't put that here.)
    mModelID (PK)
    mModel
    mBrandID (Linked to tblBrand)
    mDeviceID (Link to tblDevices)

    Now, with all that said if it is the Device table Users are selecting from then the above needs to be changed. You need to add Model ID to the Device table which once connected to the other tables is all the User need to select. Then you just add:

    tlkpModels
    mModelID (PK)
    mModel

    If a Device can have many Models then you need:

    tblDeviceModels (***Can a Device have more than one Model?)
    dbDeviceModelID (PK)
    dbDeviceID (Link to tblDevices)
    dbModelID (Link to tlk[Models)

    Are you starting to see it now? Thinking this might also help...
    https://www.access-diva.com/d16.html

  4. #4
    eugzl is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    68
    Hi GinaWhipp. Thanks for reply.
    Based on your post I created the database relationships of tables.
    Click image for larger version. 

Name:	Relationships.jpg 
Views:	34 
Size:	40.6 KB 
ID:	46771

    Is that correct? Thanks.

  5. #5
    CarlettoFed is online now Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    a possible solution is the following:
    Click image for larger version. 

Name:	Relazioni.png 
Views:	36 
Size:	9.5 KB 
ID:	46772
    also pay close attention to the settings of the various properties of the fields in the tables, in particular to:

    • field type
    • field size
    • required
    • allow zero length
    • indexed

    InventoryDBF.zip

  6. #6
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I'm not sure any of these are quite correct. What are the actual products?
    If it is computer hardware then the driving data should probably be the Model No.

    It will have a Brand (Or Manufacturer might be a better description?) and a model number.
    The model will have a device type and manufacturer.

    I think you get into a world of pain trying to keep a list of Manufactures and Models accurate.

    As an example, lets look at an old LTO 7 tape drive.
    It could be made by HP, IBM, Quantum to name but few, but it will still be an device type of LTO 7. And it might have a Dell badge on it but be made by IBM...
    The model number though should be different per manufacturer, and with that there will be sub classes of model. Never simple.

    I know as I tried to keep track of similar items for about 10 years.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Hmm, I am agreeing with Minty here. We need to know the products, as well as, what exactly you are trying to do. Might also help if you provide some sample data.

  8. #8
    eugzl is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    68
    Thanks for all replays.
    The attachment file has all data that I have now. Hope that amount and type of data will be extended. I will appreciate if you help to design structure of database with perspective to the future. Generally my purpose to develop DB of IT equipment and IT supplies for IT department. For instance, department has PCs, Laptops, Monitors, Printers and another equipment and supplies of different brands (some time without brand), type of devices, and models. I would like create smart database to keep tracking those equipment. When devices was requested, who requested, how many, when was ordered, how many was ordered, how many left and so on.
    Thanks.
    InventoryDBF.zip

  9. #9
    Join Date
    Apr 2017
    Posts
    1,673
    About manufacturer and model info for products - in my (now extinct) IT Devices database user could select from existing values from same field (there is the option for combos with single field in RecordSource query). As those fields were for information about device only, there was no need for keeping a registry of them.

  10. #10
    CarlettoFed is online now Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    The file attached one can be the structure of what you would like to do.
    InventoryDBF_New.zip

  11. #11
    eugzl is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    68
    Hi CarlettoFed. Thanks for reply.
    I used your structure of database and filled out following tables: tblBrands, tblDeviceModels, tblDevices, and tblDeviceType with according data. Now I can add if required new brand or device type or model. But with new structure I cannot figure out how to add new record of device using the my f_Device form with cascade combo boxes to tblDevice table. I tried some variants but couldn't save new record to the table. Is it good idea if tblBrand to link with tblDeviceModels? Will it be correct? In that case I will have Brands, Models, and DeviceTypes tables linked to DeviceModels table. I will appreciate if you will show what I did wrong and how to fix the problem. The attached file has last version.
    Thanks.
    Attached Files Attached Files
    Last edited by eugzl; 12-01-2021 at 01:53 PM.

  12. #12
    CarlettoFed is online now Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    You can try this solution.
    InventoryDBF.zip

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 12-30-2020, 11:55 AM
  2. Replies: 12
    Last Post: 03-11-2015, 08:13 PM
  3. Replies: 1
    Last Post: 11-05-2014, 11:08 AM
  4. Replies: 1
    Last Post: 02-21-2013, 12:18 AM
  5. Products Inventory Dilema,Add To Inventory
    By burrina in forum Forms
    Replies: 3
    Last Post: 12-02-2012, 12:10 PM

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