Solved Ideas for Equipment Register along with Accessories (1 Viewer)

[Pac-Man]

Hello,

I want to build a database for our workshop tools along with issue and return record so that equipment could be managed in effective way. Issue is, I am not able to understand/get an idea that how to manage components with it. For instance, I have a main equipment say grinding machine which has several components other than the machine itself like disk, spanner, L-keys etc. Most of the time the machine is issued to a person along with most of the components like 8 out of 10, some time only specific component is required by a person and is issued and rarely machine with all of the related components are required by the person and are issued to him. Currently all this is done on paper register. Now the questions/concerns are:

1. Should I make separate tables for machines and components and then link the third table or can I make only one table in which machine and components are written and link of machine with the component is make in second table. My preferable way is the later one as fields for machine and component are almost identical and will also ease me at the time of issue of machine and components independently in a single form but I am not sure if it is recommended way and will not create problems in future.
2. How will I link machine with components if second method is used from point 1 in which machine and components are in the same table.
3. Can I issue machine and/or components to a person using two listboxes by using add remove button. i.e. I select few items in the left listbox and then click on add button which will add the selected into the right listbox and similarly remove from right listbox to using remove button.

Thanks in advance for any suggestions/sample code/demo etc.

 

[GaP42]

Fundamentally 2 tables: Equipment, using EQUIPID as PK and Equipment Additional Items, EQADDID as PK. Assuming some items might be assigned to multiple pieces of equipment (eg spanner, particularly if you have more than one piece of equipment of the same type), you also need a table to resolve the M:M between them, Equipment Associated Item.
To assign the piece of equipment you then need a loan register - the person who loaned the item, the period, the identified piece of equipment. To include the additional items the loan register would also include the records for the associated items, however on making a loan, your form might display by default all the associated items, but give the user the option of untagging any other them. On committing the loan, only those associated items tagged for loan are added to your loan register.
You can then work out the return process.

 

[mike60smart]

Hello,

I want to build a database for our workshop tools along with issue and return record so that equipment could be managed in effective way. Issue is, I am not able to understand/get an idea that how to manage components with it. For instance, I have a main equipment say grinding machine which has several components other than the machine itself like disk, spanner, L-keys etc. Most of the time the machine is issued to a person along with most of the components like 8 out of 10, some time only specific component is required by a person and is issued and rarely machine with all of the related components are required by the person and are issued to him. Currently all this is done on paper register. Now the questions/concerns are:

1. Should I make separate tables for machines and components and then link the third table or can I make only one table in which machine and components are written and link of machine with the component is make in second table. My preferable way is the later one as fields for machine and component are almost identical and will also ease me at the time of issue of machine and components independently in a single form but I am not sure if it is recommended way and will not create problems in future.
2. How will I link machine with components if second method is used from point 1 in which machine and components are in the same table.
3. Can I issue machine and/or components to a person using two listboxes by using add remove button. i.e. I select few items in the left listbox and then click on add button which will add the selected into the right listbox and similarly remove from right listbox to using remove button.

Thanks in advance for any suggestions/sample code/demo etc.

Hi
If you have started building your database can you upload a copy?
Or a screen print of your current Paper Method

 

[Pac-Man]

Hi, thanks for the reply. I haven't built the database yet other than the three tables as suggested by @GaP42 two of which with the following fields:
TblEquip (tblCmp)
EqID (CmpID)
EqName (CmpName)
EqSrNo (CmpSrNo)
EqTag (CmpTag)
EqBrand (CmpBrand)
EqPurchaseDate (CmpPurchaseDate)
While third table to link Machine with components with following fields:
ID
EqID
CmpID

I don't have the register page photo right now however here is how it is being written (first row below are the column headings):
Sr.# Equip Name Date Issued Issued To o Signature Return date Signature
1. Baby Grinder 01/05/2023 Jawad Ali Sign 08/05/2023 Cable was damaged Sign

 

[mike60smart]

Ok Thanks for that.

You can use Cascading Combobox's
1st Combobox allows you to select the Specific Equipment
2nd Combobox would only show those Components associated with the Euipment Selected

Can you upload an Excel file listing Equipment and Components?

 

[Pac-Man]

Ok Thanks for that.

You can use Cascading Combobox's
1st Combobox allows you to select the Specific Equipment
2nd Combobox would only show those Components associated with the Euipment Selected

Can you upload an Excel file listing Equipment and Components?

Actually I don't have one. We have the hardware present with us physically and are just logging issue/return on register so that we know if an equipment is not present in workshop, we should know who has borrowed it.

 

[mike60smart]

Ok If you don't currently have a list can you create one and upload?

 

[GaP42]

Hi, thanks for the reply. I haven't built the database yet other than the three tables as suggested by @GaP42 two of which with the following fields:
TblEquip (tblCmp)
EqID (CmpID)
EqName (CmpName)
EqSrNo (CmpSrNo)
EqTag (CmpTag)
EqBrand (CmpBrand)
EqPurchaseDate (CmpPurchaseDate)
While third table to link Machine with components with following fields:
ID
EqID
CmpID

I don't have the register page photo right now however here is how it is being written (first row below are the column headings):
Sr.# Equip Name Date Issued Issued To o Signature Return date Signature
1. Baby Grinder 01/05/2023 Jawad Ali Sign 08/05/2023 Cable was damaged Sign

Pac-Man, there are actually 4 tables suggested - the EquipmentRegister - with the attributes you list, the Additional items (which I think would not have the same level of details - eg spanner to tighten the grinder wheel probably does not need brand/serial no / purchase date etc - they just need to be associated to the Grinder - they were purchased with it, however you know your equipment and how much is needed to track it), the table to associate them - as you have indicated and the LoanRegister - which for baby grinder eg has an element of a damagereport, which depending on needs could simply be a large text field or a Damage/Service History type table.