Hello,
I am currently making a database to track what equipment athletes have checked out from their school. After getting some suggestions from fellow forum members, I have moved away from my static, flat db and am going for a relational database that will be dynamic. I wasted a lot of time hard coding the db to handle 30 equipment items. That really limited ones ability to go beyond 30 items! So, the way it is now, it can handle an unlimited amount of items. However, I have not been successful in getting the data entry screen that I once had with the static db.
Please look at the attached screenshot. This is what I would like to achieve, I just don't know how to get access to do it.
From a design standpoint, I am not sure if I can do this in parts. You will notice that I would like to essentially see every item that someone could have, and then use check boxes to indicate that the individual has the item checked out.
Here are the database basics:
Table: Personnel
Description: This holds info like name, address, phone number, and so on with a primary key of personnel_ID
Table: Equipment_Items
Description: This holds the key info in the individual items one can check out like the item description, vendor part number, unit cost, and so on with a primary key of equipment_ID
Table: Issued_Equipment
Description: This is a table that essentially lists the equipment_ID number, the personnel_ID number for who has it checked out, the size that they checked out, and the style that they checked out.
Here are my unresolved issues:
1) When the db was static, I could have a value like equipment_item_1 and it be true or false. I would run queries against that item to see who had something out and when I built the form, it was easy - I made 30 lines for items 1-30 and each had a checkbox next to it.
2) I eventually want to have an inventory screen that shows what is in stock. Items that are checked out are subtracted from the total that the coach has entered for that equipment_ID. From this equipment screen, one can either return something to the inventory, or they can delete it from the system. We have some personal issue items that stay with the athlete like undershirts or socks, so these items would need to be deleted alltogether from the item count. The interesting twist for me is that there are different sizes and styles for each item. The easy way around the style issue is to simply create a new equipment item rather than say you have a running suit out (item 6) that is style 4. It is easier to make item 6 Running Suit - Red and item 7 that Running suit - green. BUT, the hard part is the sizes, I do not want to make different item numbers for each size, like item 6 is Running Suit - Red - Small, and so forth. I think this is easy to query, but I have not gotten into that component of the db yet.
Please take a look at the screen shot and see if you have any suggestions as to how I can achieve this result.
Thank you!
I am currently making a database to track what equipment athletes have checked out from their school. After getting some suggestions from fellow forum members, I have moved away from my static, flat db and am going for a relational database that will be dynamic. I wasted a lot of time hard coding the db to handle 30 equipment items. That really limited ones ability to go beyond 30 items! So, the way it is now, it can handle an unlimited amount of items. However, I have not been successful in getting the data entry screen that I once had with the static db.
Please look at the attached screenshot. This is what I would like to achieve, I just don't know how to get access to do it.
From a design standpoint, I am not sure if I can do this in parts. You will notice that I would like to essentially see every item that someone could have, and then use check boxes to indicate that the individual has the item checked out.
Here are the database basics:
Table: Personnel
Description: This holds info like name, address, phone number, and so on with a primary key of personnel_ID
Table: Equipment_Items
Description: This holds the key info in the individual items one can check out like the item description, vendor part number, unit cost, and so on with a primary key of equipment_ID
Table: Issued_Equipment
Description: This is a table that essentially lists the equipment_ID number, the personnel_ID number for who has it checked out, the size that they checked out, and the style that they checked out.
Here are my unresolved issues:
1) When the db was static, I could have a value like equipment_item_1 and it be true or false. I would run queries against that item to see who had something out and when I built the form, it was easy - I made 30 lines for items 1-30 and each had a checkbox next to it.
2) I eventually want to have an inventory screen that shows what is in stock. Items that are checked out are subtracted from the total that the coach has entered for that equipment_ID. From this equipment screen, one can either return something to the inventory, or they can delete it from the system. We have some personal issue items that stay with the athlete like undershirts or socks, so these items would need to be deleted alltogether from the item count. The interesting twist for me is that there are different sizes and styles for each item. The easy way around the style issue is to simply create a new equipment item rather than say you have a running suit out (item 6) that is style 4. It is easier to make item 6 Running Suit - Red and item 7 that Running suit - green. BUT, the hard part is the sizes, I do not want to make different item numbers for each size, like item 6 is Running Suit - Red - Small, and so forth. I think this is easy to query, but I have not gotten into that component of the db yet.
Please take a look at the screen shot and see if you have any suggestions as to how I can achieve this result.
Thank you!
