Need help please!

[LostSoul]

Hi,

I am studying double degree in Project Management (Civil Infrastructure) and Civil Engineering. I enrolled in a two semestral subject called Asset Management Systems and Asset Sustainability. I was given a project/assignment on Asset Management Systems and am required to use MS Access to find solution.

The assignment provides the following info:-

- Details of assets (makes, models, serial numbers, etc) – 87 in total
- Details of employees – 6 in total
- Details of subcontractors – 5 in total
- Details of suppliers – 19 in total
- Details of spare parts (makes, models, serial numbers, etc) – 132 in total

It also states the following criteria:-

- Each asset is required to have scheduled maintenance on a regular basis (dates have been given). In addition to that, each asset is to be repaired if faulty.
- A number of tests and audits is to be carried out periodically for each asset, to make sure assets are in good condition.
- Employees are responsible for scheduled maintenance, tests and audits and subcontractors are responsible for repairs.

It’s asking me to do the following:-

- Group above data in tables.
- List scheduled maintenance, repairs, tests and audits in table/s.
- Have “spare parts used” table.


- Have “spare parts order” table.
- Find out number of spare parts in stock.
- Find out which assets are to be tested and audited in the following week.
- Find out which assets are to have scheduled maintenance in the following week.
- Produce reports for the following:
o Assets which have had scheduled maintenance done.
o Assets which have been repaired due to a fault.
o Assets which have been tested and audited.

I must use forms to make data input easier. I can have as many tables/forms and queries as possible as long as I achieve a positive outcome.

I need your help and suggestions on how to do this as my knowledge in using MS Access is rather limited.

Your help is much appreciated!

Thanks in advance.

[LostSoul]

To simplify the matters a little I’ve started the assignment and have created the following tables and forms:-

- Assets Table/Form

- Spare Parts Table/Form

- Spare Parts Order Table/Form (among the others it has SparePartID and SupplierID columns and I’ve created relationship with SparePartID and SupplierID column from Spare Parts Table and Suppliers Table. Am I on the right track?? )

- Subcontractors Table/Form

- Employees Table/Form

- Suppliers Table/Form (among the others it has SparePartID column and I’ve created relationship with SparePartID column from Spare Parts Table. Am I on the right track?? )

- Scheduled Maintenance Table/Form (among the others it has EmployeeID and SparePartID columns and I’ve created relationship with EmployeeID and SparePartID columns from Employees and Spare Parts tables. Am I on the right track??)

- Repairs Table/Form (among the others it has ContractorID and SparePartID columns and I’ve created relationship with ContractorID and SparePartID columns from Subcontractors and Spare Parts tables. Am I on the right track??)

- Tests and Audits Table/Form (among the others it has EmployeeID column and I’ve created relationship with EmployeeID column from Employees table. Am I on the right track??)

Now, how can I create a query which will determine number of spare parts in stock?
Also, how can I create a query which will “notify” about scheduled maintenance for next 7 days?

Cheers