Unanswered: Help or Ideas for my Inventory data base
I have created an inventory database for my Mom's furniture staging company. The difference with this compared to a traditional inventory database is that once an item has be specified to a locationID it can then move back to the warehouse and/or to a new location.
I need to track how long an item is at each location so I've created a 'item location table' that holds the itemID, the locationID, the move in and move out date.
-My queries are set up to use the entries with 'move out' date Is Null as the current location. Is this the best way to do this?
-Is there a way to set up a query for items that have yet to be entered in this table to default to the warehouse locationID?
- Ideas for the most user friendly entry form.
- Is there a way to create a drop down on a form with all the possible locations, from the location table, this table would also be constantly updated
-Is there a way to track (audit) the location change date from a form if the above is possible
I also have had an issue writing a formula to track items with quantities greater then 1 across multiple locations. So if there were 8 chairs but 4 of them were in two separate locations and they wanted to query the database to see where they were I could do that.
Any help is appreciated. I understand you will need to know how I currently have the data base set up. I would like to wait so I can also specify just for needed details.