|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
August 5th, 2009, 01:12 AM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
Modifying the Standard Inventory DB to allow for individual items?
Hi all,
I'm looking to create a stock control log where by we can record goods moving in and out of our company. We are a Not for Profit organisation that looks after Video equipment for schools and colleges and so have many items coming for our suppliers and moving to and from various studios. We need to keep track or ALL individual serial numbers. We also have a need to link in test records on this equipment. The built in inventory DB in Access 2007 is quite good, but I'm sure there would be a way to add a sub-form/table to the "Inventory Transaction List" whereby multiple items in/out can have individual serial number recorded (using a hand-held bar code scanner). Of course, I could just add one line in this table for each individual item received or sent, but this will be time consuming, especially if we have multiple items (or the same type - e.g. 20 specific camera models) move at one time. We don't really need to track re-order levels but again we do need to track where an individual item is and any given time. On top of this we, test these individual items to make sure they all work properly. These test are relatively simple but are different for each item - e.g. a Camera will be tested for Pan/Tilt (up/down/left/right), Zoom in/out, and focus; whereas a screen may be tested for showing an image at specific resolutions. I was not sure if I should simply put all possible tests for all items as individual field in a single table, but this would be very wasteful of space. Better (I think) would be to create individual tables for tests and then somehow link these to the sub-table of the "Inventory Transaction List" so that only the correct test will be shown for the item concerned. It guess it would be possible to Use the "Category" field in the "Inventory" table to link through individual items to the correct test schedule. I'm thinking as I type this how it might be possible, but my first concern is to get multiple entries of same type items with individual serial number working. I am very rusty in this department. Help Please... 
|
|
#2
August 5th, 2009, 01:29 AM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Modifying the Standard Inventory DB to allow for individual items?
Hmm, the more I think about this the more awkward I thinks it going to be.
In the "Inventory Transaction List" we have a Quantity field. Depending on this quantity, will depend on how many items I will need to individually record. If these individual items are recorded in a separate table, lets say the "Individual Items" table, then changing the quantity field must dynamically add records to this table that will then need to be updated with the relevant serial numbers. Of course, you may make a mistake with the quantity, so records will need to be dynamically removed as well. Whether something like this can be be dynamically added to a form I don't know. Any thoughts? "Swin" wrote: Hi all, I'm looking to create a stock control log where by we can record goods moving in and out of our company. We are a Not for Profit organisation that looks after Video equipment for schools and colleges and so have many items coming for our suppliers and moving to and from various studios. We need to keep track or ALL individual serial numbers. We also have a need to link in test records on this equipment. The built in inventory DB in Access 2007 is quite good, but I'm sure there would be a way to add a sub-form/table to the "Inventory Transaction List" whereby multiple items in/out can have individual serial number recorded (using a hand-held bar code scanner). Of course, I could just add one line in this table for each individual item received or sent, but this will be time consuming, especially if we have multiple items (or the same type - e.g. 20 specific camera models) move at one time. We don't really need to track re-order levels but again we do need to track where an individual item is and any given time. On top of this we, test these individual items to make sure they all work properly. These test are relatively simple but are different for each item - e.g. a Camera will be tested for Pan/Tilt (up/down/left/right), Zoom in/out, and focus; whereas a screen may be tested for showing an image at specific resolutions. I was not sure if I should simply put all possible tests for all items as individual field in a single table, but this would be very wasteful of space. Better (I think) would be to create individual tables for tests and then somehow link these to the sub-table of the "Inventory Transaction List" so that only the correct test will be shown for the item concerned. It guess it would be possible to Use the "Category" field in the "Inventory" table to link through individual items to the correct test schedule. I'm thinking as I type this how it might be possible, but my first concern is to get multiple entries of same type items with individual serial number working. I am very rusty in this department. Help Please...
|
|
#4
August 5th, 2009, 11:26 AM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Modifying the Standard Inventory DB to allow for individual it
Cheers Steve
To relate this to the standard Inventory DB in 2007: Your TblEquipmentModel = "Inventory Table" In this table there is a Value List box bound to the "Category" field, which is similar to your TblEquipmentType. (BTW, is there a simply way to convert the Value List to proper tables? I will be moving the tables to SharePoint later and whilst Value List fields are supported, they do not stop the user typing in anything they want and ignoring the list.) There is also a single field in the "Inventory Table" that can be expanded to link to the TblEquipmentManufacturer. In the Inventory DB, there is an additional Table called "Inventory Transaction Table" that will record alterations to the stock level. I could then link to this the TblEquipmentSerialNumber, so that indivual item can be recorded for each transaction (i.e. we recieve 20 Cameras on the 4/5/09, then a camera goes out to location 1, on the 5/5 etc etc. I should be able then create a query to let me know the history of a specific item and it exact location! It may also be posible to develop another form to make to movment of items a little simpler. Once the serial has been entered, I should be able to find the item and move it where I want.... Sounds simple!!!! Someone came up with the idea of using a text box on the "Inventory Transaction Form" that will allow the entry of a serial number via the Bar Code Reader. The Bar code reader enters a Caridge Return after the bar code, so the After Update even of the text box could be used to scan for this character. A Record could then be added to the TblEquipmentSerialNumber that relates to the current record selected in the "Inventory Transaction Table". Have I got my thinking right? "Steve" wrote: ...get multiple entries of same type items with individual serial number working ... TblEquipmentType EquipmentTypeID EquipmentType TblEquipmentManufacturer EquipmentManufacturerID EquipmentManufacturer TblEquipmentModel EquipmentModelID EquipmentManufacturerID EquipmentTypeID EquipmentModel TblEquipmentSerialNumber EquipmentSerialNumberID EquipmentModelID EquipmentSerialNumber If this works for you, you then would record equipment at locations ....... TblLocation LocationID Location TblEquipmentLocation EquipmentLocationID EquipmentSerialNumberID LocationID Steve "Swin" wrote in message ... Hi all, I'm looking to create a stock control log where by we can record goods moving in and out of our company. We are a Not for Profit organisation that looks after Video equipment for schools and colleges and so have many items coming for our suppliers and moving to and from various studios. We need to keep track or ALL individual serial numbers. We also have a need to link in test records on this equipment. The built in inventory DB in Access 2007 is quite good, but I'm sure there would be a way to add a sub-form/table to the "Inventory Transaction List" whereby multiple items in/out can have individual serial number recorded (using a hand-held bar code scanner). Of course, I could just add one line in this table for each individual item received or sent, but this will be time consuming, especially if we have multiple items (or the same type - e.g. 20 specific camera models) move at one time. We don't really need to track re-order levels but again we do need to track where an individual item is and any given time. On top of this we, test these individual items to make sure they all work properly. These test are relatively simple but are different for each item - e.g. a Camera will be tested for Pan/Tilt (up/down/left/right), Zoom in/out, and focus; whereas a screen may be tested for showing an image at specific resolutions. I was not sure if I should simply put all possible tests for all items as individual field in a single table, but this would be very wasteful of space. Better (I think) would be to create individual tables for tests and then somehow link these to the sub-table of the "Inventory Transaction List" so that only the correct test will be shown for the item concerned. It guess it would be possible to Use the "Category" field in the "Inventory" table to link through individual items to the correct test schedule. I'm thinking as I type this how it might be possible, but my first concern is to get multiple entries of same type items with individual serial number working. I am very rusty in this department. Help Please...
|
|
#5
August 5th, 2009, 01:05 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Modifying the Standard Inventory DB to allow for individual it
Ok, one question about this - do I actually need the intermediate table?: -
If this works for you, you then would record equipment at locations ....... TblLocation LocationID Location TblEquipmentLocation EquipmentLocationID EquipmentSerialNumberID LocationID Any individual item will only ever be one location (it can't be in two place at one time). A single location may have multiple items. I would say that the location is an extended property of the individual item - it always HAS to be somehwere In this case couldn't I just add to the TblEquipmentSerialNumber thus, TblEquipmentSerialNumber EquipmentSerialNumberID EquipmentTransactionID EquipmentSerialNumber FromLocationID ToLocationID Then junk TblEquipmentLocation?
|
|
#6
August 5th, 2009, 05:03 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Modifying the Standard Inventory DB to allow for individual it
I would suggest adding EquipmentLocationDate to TblEquipmentLocation. It
would then be easy to get the location from where the equipment came from by looking at the EquipmentLocationDate just prior to the date an equipment was moved to the current location. EquipmentLocationDate also will allow you to answer where was a piece of equipment on April 1, 2009 as well as allow you to display a history of where a piece of equipment has been. Steve "Swin" wrote in message ... Ok, one question about this - do I actually need the intermediate table?: - If this works for you, you then would record equipment at locations ....... TblLocation LocationID Location TblEquipmentLocation EquipmentLocationID EquipmentSerialNumberID LocationID Any individual item will only ever be one location (it can't be in two place at one time). A single location may have multiple items. I would say that the location is an extended property of the individual item - it always HAS to be somehwere In this case couldn't I just add to the TblEquipmentSerialNumber thus, TblEquipmentSerialNumber EquipmentSerialNumberID EquipmentTransactionID EquipmentSerialNumber FromLocationID ToLocationID Then junk TblEquipmentLocation?
|
|
#7
August 5th, 2009, 06:35 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Modifying the Standard Inventory DB to allow for individual it
Ok, I've been struggling with this. the more I think about it, the more items
you realise you need to take care of So Far table are TblEmployees ID Company Last Name First Name E-mail Address Job Title Business Phone TblTransactionType ID Description Add/Remove TblInventoryTransactionList ID Transaction Item Employee Transaction Type Quantity Created Date P0 Number Comments Tblnventory ID Item Description Equipment Type Supplier Manufacturer Model Reorder Level TblEquipmentManufacture ID Equipment Manufacturer TblEquipmentType ID Equipment Type TblEquipmentSerial ID Serial Number InventryItemID TblEquipmentLocation ID TransactionID EquipmentSerialNumberID EquipmentLocationID Suppliers ID Company Last Name First Name E-mail Address Job Title Serial Number TbleEquipmentManufacture ID Equipment Manufacturer As with the original, the DB will be based around the "inventory Transaction List". This is roughly what I "should"/want to be able happen. 1) Enter a new transaction (good in/out) for an item. 2) Most of the time have serial number attached, but even if they don't, we need to record that an item is gone too/come from a specific location. 3) I then should scan in the individual items associated with the transaction. 4) If the item scaned has a record in the TblEquipmentSerials Table, then add a record to the TbleEquipmentLocation Table linking the Serial Number to the Transaction number. 5) If the serial Number doesn't exist in the Serial Table, then we need to add it to the table and link it to the Item, then do 4) above. 6) (similar to 5) If there is no serial number we need to a an "NA" record to the Serial Table, link it to the Item, then do 4) above. The date is included in the Transaction Table. Does this sound reasonable? "Steve" wrote: I would suggest adding EquipmentLocationDate to TblEquipmentLocation. It would then be easy to get the location from where the equipment came from by looking at the EquipmentLocationDate just prior to the date an equipment was moved to the current location. EquipmentLocationDate also will allow you to answer where was a piece of equipment on April 1, 2009 as well as allow you to display a history of where a piece of equipment has been. Steve "Swin" wrote in message ... Ok, one question about this - do I actually need the intermediate table?: - If this works for you, you then would record equipment at locations ....... TblLocation LocationID Location TblEquipmentLocation EquipmentLocationID EquipmentSerialNumberID LocationID Any individual item will only ever be one location (it can't be in two place at one time). A single location may have multiple items. I would say that the location is an extended property of the individual item - it always HAS to be somehwere In this case couldn't I just add to the TblEquipmentSerialNumber thus, TblEquipmentSerialNumber EquipmentSerialNumberID EquipmentTransactionID EquipmentSerialNumber FromLocationID ToLocationID Then junk TblEquipmentLocation?
|
|
#8
August 5th, 2009, 07:22 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Modifying the Standard Inventory DB to allow for individual it
Four suggestions to start off with .........
1. Rename the PKs in your tables to table name less "Tbl" plus ID; ex. EmployeeID. You then will always know what table you are working with in queries, forms and reports. 2. Where you have a foreign key, give it the same name as the PK field it is related to, ex. from 1, in TblTransactionType change PK to TransactionTypeID. Then in TblInventoryTransactionList, change Transaction Type to TransactionTypeID. 3. After you do 1 and 2, remove all spaces in field names. You will be glad you did as you create queries, forms and reports. 4. Be consistent in naming your tables. Most tables begin with "TBL". That's very good! You will always know that you are working with a table in queies, forms and reports. Change Suppliers to TblSuppliers. Also, remove the "e" in TbleEquipmentManufacture. Steve "Swin" wrote in message ... Ok, I've been struggling with this. the more I think about it, the more items you realise you need to take care of So Far table are TblEmployees ID Company Last Name First Name E-mail Address Job Title Business Phone in ID Description Add/Remove TblInventoryTransactionList ID Transaction Item Employee Transaction Type Quantity Created Date P0 Number Comments Tblnventory ID Item Description Equipment Type Supplier Manufacturer Model Reorder Level TblEquipmentManufacture ID Equipment Manufacturer TblEquipmentType ID Equipment Type TblEquipmentSerial ID Serial Number InventryItemID TblEquipmentLocation ID TransactionID EquipmentSerialNumberID EquipmentLocationID Suppliers ID Company Last Name First Name E-mail Address Job Title Serial Number TbleEquipmentManufacture ID Equipment Manufacturer As with the original, the DB will be based around the "inventory Transaction List". This is roughly what I "should"/want to be able happen. 1) Enter a new transaction (good in/out) for an item. 2) Most of the time have serial number attached, but even if they don't, we need to record that an item is gone too/come from a specific location. 3) I then should scan in the individual items associated with the transaction. 4) If the item scaned has a record in the TblEquipmentSerials Table, then add a record to the TbleEquipmentLocation Table linking the Serial Number to the Transaction number. 5) If the serial Number doesn't exist in the Serial Table, then we need to add it to the table and link it to the Item, then do 4) above. 6) (similar to 5) If there is no serial number we need to a an "NA" record to the Serial Table, link it to the Item, then do 4) above. The date is included in the Transaction Table. Does this sound reasonable? "Steve" wrote: I would suggest adding EquipmentLocationDate to TblEquipmentLocation. It would then be easy to get the location from where the equipment came from by looking at the EquipmentLocationDate just prior to the date an equipment was moved to the current location. EquipmentLocationDate also will allow you to answer where was a piece of equipment on April 1, 2009 as well as allow you to display a history of where a piece of equipment has been. Steve "Swin" wrote in message ... Ok, one question about this - do I actually need the intermediate table?: - If this works for you, you then would record equipment at locations ....... TblLocation LocationID Location TblEquipmentLocation EquipmentLocationID EquipmentSerialNumberID LocationID Any individual item will only ever be one location (it can't be in two place at one time). A single location may have multiple items. I would say that the location is an extended property of the individual item - it always HAS to be somehwere In this case couldn't I just add to the TblEquipmentSerialNumber thus, TblEquipmentSerialNumber EquipmentSerialNumberID EquipmentTransactionID EquipmentSerialNumber FromLocationID ToLocationID Then junk TblEquipmentLocation?
|
|
#9
August 5th, 2009, 08:43 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Modifying the Standard Inventory DB to allow for individual it
Ahh... The benifits of "borowing" a template - even if it is an MS in built
one! Barr the 'protocol', is the design saine and workable? "Steve" wrote: Four suggestions to start off with ......... 1. Rename the PKs in your tables to table name less "Tbl" plus ID; ex. EmployeeID. You then will always know what table you are working with in queries, forms and reports. 2. Where you have a foreign key, give it the same name as the PK field it is related to, ex. from 1, in TblTransactionType change PK to TransactionTypeID. Then in TblInventoryTransactionList, change Transaction Type to TransactionTypeID. 3. After you do 1 and 2, remove all spaces in field names. You will be glad you did as you create queries, forms and reports. 4. Be consistent in naming your tables. Most tables begin with "TBL". That's very good! You will always know that you are working with a table in queies, forms and reports. Change Suppliers to TblSuppliers. Also, remove the "e" in TbleEquipmentManufacture. Steve "Swin" wrote in message ... Ok, I've been struggling with this. the more I think about it, the more items you realise you need to take care of So Far table are TblEmployees ID Company Last Name First Name E-mail Address Job Title Business Phone in ID Description Add/Remove TblInventoryTransactionList ID Transaction Item Employee Transaction Type Quantity Created Date P0 Number Comments Tblnventory ID Item Description Equipment Type Supplier Manufacturer Model Reorder Level TblEquipmentManufacture ID Equipment Manufacturer TblEquipmentType ID Equipment Type TblEquipmentSerial ID Serial Number InventryItemID TblEquipmentLocation ID TransactionID EquipmentSerialNumberID EquipmentLocationID Suppliers ID Company Last Name First Name E-mail Address Job Title Serial Number TbleEquipmentManufacture ID Equipment Manufacturer As with the original, the DB will be based around the "inventory Transaction List". This is roughly what I "should"/want to be able happen. 1) Enter a new transaction (good in/out) for an item. 2) Most of the time have serial number attached, but even if they don't, we need to record that an item is gone too/come from a specific location. 3) I then should scan in the individual items associated with the transaction. 4) If the item scaned has a record in the TblEquipmentSerials Table, then add a record to the TbleEquipmentLocation Table linking the Serial Number to the Transaction number. 5) If the serial Number doesn't exist in the Serial Table, then we need to add it to the table and link it to the Item, then do 4) above. 6) (similar to 5) If there is no serial number we need to a an "NA" record to the Serial Table, link it to the Item, then do 4) above. The date is included in the Transaction Table. Does this sound reasonable? "Steve" wrote: I would suggest adding EquipmentLocationDate to TblEquipmentLocation. It would then be easy to get the location from where the equipment came from by looking at the EquipmentLocationDate just prior to the date an equipment was moved to the current location. EquipmentLocationDate also will allow you to answer where was a piece of equipment on April 1, 2009 as well as allow you to display a history of where a piece of equipment has been. Steve "Swin" wrote in message ... Ok, one question about this - do I actually need the intermediate table?: - If this works for you, you then would record equipment at locations ....... TblLocation LocationID Location TblEquipmentLocation EquipmentLocationID EquipmentSerialNumberID LocationID Any individual item will only ever be one location (it can't be in two place at one time). A single location may have multiple items. I would say that the location is an extended property of the individual item - it always HAS to be somehwere In this case couldn't I just add to the TblEquipmentSerialNumber thus, TblEquipmentSerialNumber EquipmentSerialNumberID EquipmentTransactionID EquipmentSerialNumber FromLocationID ToLocationID Then junk TblEquipmentLocation?
|
|
#10
August 6th, 2009, 11:42 AM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Modifying the Standard Inventory DB to allow for individual it
Arggghhh.
Just updated all the inbuilt Table, Query, Form and Field names in the standard DB, now I've broke a lot of things!!!!!
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
