Results 1 to 5 of 5
  1. #1
    p3rsian is offline Novice
    Windows 8 Access 2013
    Join Date
    Mar 2015
    Posts
    2

    Track Items with Varying Properties

    Maybe this is a better question for the Database Design section, but I'm not even really that far yet.



    So what I'm trying to do is create an inventory tracking database that tracks my inventory. (Fairly Simple right?)

    Here's the kicker, each item has varying numbers of properties I want to track and they do not each share the same properties. Without going into too much detail about the project, here's an example:

    Item A, properties: Shiny (reflects 50% of light), Liquid ( Viscosity of 8)**
    Item B, properties: Weight ( 30 lbs), Height ( 3 ft.), Speed (5 mph)
    Item C,
    Item D,
    etc...

    **i don't know what viscosity is measured in, that may make no sense... sorry!

    Item A might not have a Height value so I don't want to track it, but if i pulled a list of my inventory, I don't want there to be a whole string of zeros or blanks where properties don't apply.

    There is a set list of possibilities for the properties (and possible ranges for those values), but it is a fairly long list so if i pull a list i don't want to have to scroll 10 pages to the right on each item that may only have 3 properties. I just can't wrap my head around an easy way to design this...

    I could have a table with each possible property and it's value range, but then I can't think of how to attach it to the items themselves... Any ideas out there?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Maybe like:

    tblItems
    ItemID (primary key)
    Description

    tblProperties
    PropertyID (primary key)
    PropertyType (Weight, Height, Speed, Luminosity, Viscosity, etc.)
    Units (lbs, ft, mph, pct, Poise, etc)

    tblItemProperties
    ItemID (foreign key)
    PropertyID (foreign key)
    PropertyValue (30, 3, 5, 50, 8, etc)
    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.

  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    You certainly cannot have all the information in one table, so here are some ideas:

    Main table - Values (fields) which are common to all inventory items:

    ItemID
    Name
    Category
    Quantity
    etc.

    PropertyList table

    Just a list of all the allowable properties, might be used in combo boxes

    PropertyID
    PropertyName
    UnotOfMeasurement


    AllowableProperties

    Lists the properties that can be applied to each item category. Notice that I have put the Item Category in here as well. This is because the same property could easily have different allowable ranges for different item categories; for example the Viscosity range for solvents would be quite different than the Viscosity range for lubricants.

    Item Category
    PropertyName
    MinimumValue
    MaximumValue

    ItemProperties

    Here you would list the properties for each of your inventory items

    ItemID
    PropertyID
    PropertyValue


    This is by no means the only arrangement you could have, and I'm sure there will be many more fields that you might need, but this should get you started. Good luck with your project; post back if you need more ideas.

  4. #4
    p3rsian is offline Novice
    Windows 8 Access 2013
    Join Date
    Mar 2015
    Posts
    2
    Thanks to both of you, the ideas seem similar. If I'm reading what you're saying right then I could have a list of all my items but not what properties go with them. If I pulled an individual item though, then I'd be able to have a list of the properties right?

    What if I wanted to compare 2 similar items' properties but they don't have the same properties... I guess I could do 2 seperate queries and put them side-by-side...

    Hmmm, this could work... let me do some testing.

  5. #5
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    then I could have a list of all my items but not what properties go with them
    It depends on what you mean by "list". A report can easily do this, with its group headings and group detail features.

    For forms, you would need need a form - sub-form arrangement, with the properties list in the subform. When set up correctly, the property list in the subform will change automatically to match the item record currently displayed in the main form, so you can scroll or search the main form as required.

    Comparing two items is a little bit harder, but with a little bit of VBA coding, two side-by-side list boxes on a form would probably come close to what you want to do.

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

Similar Threads

  1. Report with varying row heights
    By AKWells in forum Reports
    Replies: 7
    Last Post: 04-24-2014, 10:48 AM
  2. Replies: 6
    Last Post: 12-03-2013, 02:59 AM
  3. Need to Remove Varying Middle Text
    By EZRider in forum Queries
    Replies: 4
    Last Post: 10-10-2012, 03:45 PM
  4. Replies: 7
    Last Post: 05-12-2012, 03:46 PM
  5. Get Varying Query Columns into Report
    By Elios in forum Reports
    Replies: 1
    Last Post: 05-05-2011, 03:16 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