Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2011
    Posts
    1

    Question 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.

    Thanks!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Your questions are very general and it's hard to provide precise answers to them.
    Quote Originally Posted by catharis50 View Post
    -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?
    Not sure to understand. Can you explain?
    Quote Originally Posted by catharis50 View Post
    -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?
    Yes, you set a condition such as "WHERE [SomeColumn] IS NULL". The implementation depends on the data structure of the table(s).
    Quote Originally Posted by catharis50 View Post
    - Ideas for the most user friendly entry form.
    It depends on what you want, on how skilled you are in Access (+VBA), and many other factors...
    Quote Originally Posted by catharis50 View Post
    - 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
    Yes, you can create a table with all possible locations and use a combo box based on it.
    Quote Originally Posted by catharis50 View Post
    -Is there a way to track (audit) the location change date from a form if the above is possible
    Yes but this can be rather complex. Access does not have Triggers so you'll have to implement the desired functionalities in VBA (let's not talk about Macros!).
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •