X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Home Inventory With Complex Location Descriptions, Access 2013    
post Jan 8 2019, 10:11 PM

Posts: 112
Joined: 29-March 06
From: Colorado

I am starting to design an home inventory db and need some guidance. I want to track the location of home inventory items in the common way (such as items in the living room or items in the master bedroom, etc.) For this the logic seems simple enough. But I also want to extend the granularity of the location description for some rooms of my home. For example, I have a cabinet of drawers in my garage that has 32 drawers. For this I'd want to define the location of an item in one of the drawers (say a specific wrench) as 'in the garage and in drawer 24 of the white drawer cabinet. Because I have other locations in the garage that have multiple sublocations I have a need for several muli-level location description. For this, I see a need for several "one to many" table relationships. While this also seem logical, what doesn't seem obvious is the forms needed to enter an item in its proper place. For a one level location description the form would be simple - drop down to enter the room of the house and I'm done. But the form for the multi-level items is less obvious particularly if I'll have two level items, three level items and maybe four level items. I was hoping to get some insight on how to structure the tables and forms to handle this. Or am I looking at this completely wrong? Thanks in advance.
Go to the top of the page
post Jan 11 2019, 09:47 AM

Posts: 1,733
Joined: 7-April 10
From: Detroit, MI

To do this, you are going to need to use subforms that show the levels. The wrench is an example of Main Form containing the wrench information, one subform contains room location (garage), second subform contains cabinet, dresser, shelf, etc., and the last, the particular drawer it is located in. You will have to think this through as to how many sub details are needed for any particular item

Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
post Jan 11 2019, 09:05 PM

Posts: 22
Joined: 2-July 10

I think you can use the attached database and in the Purchase Register > Material Register you can register all the materials for the inventory and in the location field you can enter the location

and for your item 'in the garage and in drawer 24 of the white drawer cabinet" you can enter the location as GWD24 which will mean Garage > White Drawer > Drawer 24.
This post has been edited by Junaid: Jan 11 2019, 09:24 PM
Attached File(s)
Attached File  ShopbooK.zip ( 1.44MB )Number of downloads: 11

Go to the top of the page
post Jan 23 2019, 01:59 PM

Posts: 112
Joined: 29-March 06
From: Colorado

Thank you for the replies. I've been out of town for awhile and am just getting back to looking at this.
Very much appreciated!!
Go to the top of the page
post Jan 24 2019, 08:40 AM

UtterAccess VIP / UA Clown
Posts: 32,217
Joined: 21-January 04
From: LI, NY

You will multiple tables here. For example a Rooms table to indicate the general location:

RoomID (PK Autonumber)

Then you will need the more granular level
RoomLocationID (PK Autonumber)
RoomID (FK)

You might even take this to an even more granular level. For example. The Room Locations could be things like Chest or closet or Cabinet. Then another table would drill down to the drawer or shelf etc.

Scott <>
Scottgem's Blog
Microsoft Access MVP since 2007
Author: Microsoft Office Access 2007 VBA
Go to the top of the page
post Jan 28 2019, 09:49 PM

Posts: 300
Joined: 4-April 12
From: Bendigo, Australia

Edit: Trying to figure out how to delete a post, sorry.
This post has been edited by haresfur: Jan 28 2019, 09:54 PM

-- Evan
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    26th May 2019 - 06:50 AM