Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2013
    Posts
    70

    Unanswered: a design question

    Hi all, I am working on an inventory database in Access 2000. The state of NY requires us to maintain an inventory of all the equipment, drugs, etc on our fire department's ambulance. It is broken into two sections. The Part 800 and Non-Part 800 section. The Part 800 items are mandated by statute. The Non-800 section is everything else we carry that is allowed but not mandated. The inventory is conducted every month.

    I have attached a screenshot of the relationship screen of the table structure.
    I have 7 tables. There are six elements that make up an inventory record. They are the other six tables.
    1. tblDateLU - The date of the inventory (month and year are all I need)
    2. tblItems - the inventory item
    3. tblLocations - the location of the item (there can be several instances of an item because they can be in several locations
    4. tblCategoryLU - the category of the inventory item
    5. tbl800InvLU - whether the inventory item is an 800 or non-800 item
    6. tblLevelLU - the lowest emt practice level that uses the inventory item( i.e. EMT-Basic, Advanced EMT or Paramedic)

    The 7th table is the inventory list (tblInventories). This all appears to work based on all the testing I have done to try and break the logic. But I have noticed an area that I think might be a problem when I give it to others to use.

    Right now I have one form (frmInventory, I have attached a screenshot) that I use to do almost all of the work. It collects the inventory data and displays the ongoing data of the inventory being done. It also has a small print section with 4 reports. There is one other form (frmAddItem) that I use in three different ways. In one it is opened as a single item add only form in the NotInList event of the inventory item and the second it is opened separately as a single entry edit form for the current inventory item. I also have a hidden command button that allows me to use this same form as a datasheet to directly edit the tblItems table in case there is a problem. This way I do not have to close the database and reopen it with a shift click to bypass the startup limitations.

    Every inventory is based on a specific date (month/year) and is a collection of about 250 items (records). The main form currently asks for the date, inventory item, quantity on hand, and item location for each item that is inventoried in each location. I am thinking that it is inefficient data entry and a good possible way to have user error by entering the same date for every record.

    So two questions (FINALLY). Am I right in thinking that it is inefficient data entry and a good vector for errors by entering the date for every record? And, if so, is there a way to select the date for the inventory once, then add each of the inventory item records and have that date's foreign key added to each record automatically? I hope all this is clear?

    Many thanks for any thoughts on this design question. Best, Scott

  2. #2
    Join Date
    Mar 2013
    Posts
    70

    a design question (corrected)

    I forgot to add the screen shots to the first post. I apologize for the inconvenience.

    Hi all, I am working on an inventory database in Access 2000. The state of NY requires us to maintain an inventory of all the equipment, drugs, etc on our fire department's ambulance. It is broken into two sections. The Part 800 and Non-Part 800 section. The Part 800 items are mandated by statute. The Non-800 section is everything else we carry that is allowed but not mandated. The inventory is conducted every month.

    I have attached a screenshot of the relationship screen of the table structure.
    I have 7 tables. There are six elements that make up an inventory record. They are the other six tables.
    1. tblDateLU - The date of the inventory (month and year are all I need)
    2. tblItems - the inventory item
    3. tblLocations - the location of the item (there can be several instances of an item because they can be in several locations
    4. tblCategoryLU - the category of the inventory item
    5. tbl800InvLU - whether the inventory item is an 800 or non-800 item
    6. tblLevelLU - the lowest emt practice level that uses the inventory item( i.e. EMT-Basic, Advanced EMT or Paramedic)

    The 7th table is the inventory list (tblInventories). This all appears to work based on all the testing I have done to try and break the logic. But I have noticed an area that I think might be a problem when I give it to others to use.

    Right now I have one form (frmInventory, I have attached a screenshot) that I use to do almost all of the work. It collects the inventory data and displays the ongoing data of the inventory being done. It also has a small print section with 4 reports. There is one other form (frmAddItem) that I use in three different ways. In one it is opened as a single item add only form in the NotInList event of the inventory item and the second it is opened separately as a single entry edit form for the current inventory item. I also have a hidden command button that allows me to use this same form as a datasheet to directly edit the tblItems table in case there is a problem. This way I do not have to close the database and reopen it with a shift click to bypass the startup limitations.

    Every inventory is based on a specific date (month/year) and is a collection of about 250 items (records). The main form currently asks for the date, inventory item, quantity on hand, and item location for each item that is inventoried in each location. I am thinking that it is inefficient data entry and a good possible way to have user error by entering the same date for every record.

    So two questions (FINALLY). Am I right in thinking that it is inefficient data entry and a good vector for errors by entering the date for every record? And, if so, is there a way to select the date for the inventory once, then add each of the inventory item records and have that date's foreign key added to each record automatically? I hope all this is clear?

    Many thanks for any thoughts on this design question. Best, Scott
    Attached Thumbnails Attached Thumbnails realtionshipScreen.jpg   inventoryForm.jpg  

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
  •