First time using access. big project.

[TrKent]

Hello, I am very new to access, still in the process of learning what all access does. My project for work is to create a database that tracks a multitude of information about our company's equipment and vehicles. I will try to list out what all I would like it to do and if anyone can give me some pointers on where to start this that would be great.



-Each vehicle/machine must have a service history (record of each time it was serviced, who performed it, what filter(s) and fluids were used, what the miles (or hours) were)

-Track when the next service for each vehicle/equipment is and if possible predict the date when it will be done base upon the average miles driven per day/ hours operated per day. also with that it needs to show what services needs to be performed during the next service. then generate a form to give to the employee that shows which services need to be done to the vehicles they drive and machinery they operate.

-Keep track of an inventory with many different suppliers and for 3 separate companies (there are two companies this database will be for, one with 2 separate locations) create order forms and track which filters went to which vehicle/equipment along with the cost associated with that. (i.e. each time a service is recorded a filter and fluid amount must be deducted from the inventory and then a form must keep track of which vehicle used which filter and fluids and the cost associated with that.)

there are quite a bit more intricacies that i would like to put into the database but i guess this is a good place to start.

Thanks, your help is much appreciated

[spkoest]

My first step would be to layout your tables in a way (normalized) that will make your data entry and reporting easy.

[TrKent]

I have started that but it seems like i have relationships going everywhere and some tables with just one specific field. i.e. for the vehicles, i created a table that has just model years so that i can create a lookup field for the model years. Then for filters i have one table that has all the filters listed in one field but back in the vehicles table i have 3 separate fields listing oil, air, and fuel filters for each vehicle. it seems like i'm creating redundancies and extra data i don't need to use.

[spkoest]

Start with a table for your vehicle and assign the vehicle a number.

If the data you need to capture has a one to one relationship with vehicle number it probably should go in your vehicle table. A separate table could be created to hold the values and the description for that piece of data.

If you can have more than one piece of data for a specific vehicle then keep that data in a seperate table with the vehicle number so the data can be pulled together with a query.

Take that approach.

[TrKent]

Alrighty, i'll try that, thanks

[accesskid]

This sounds a very simplistic database, if any problems occur dont hesitate to contact me i am always wanting to help and further my knowledge in all areas as i work as a software developer and use access very regulary.

Kind regards

[TrKent]

This sounds a very simplistic database, if any problems occur dont hesitate to contact me i am always wanting to help and further my knowledge in all areas as i work as a software developer and use access very regulary.

Kind regards

I keep trying to start putting everything into tables, and then scrapping it because i realize the idea isn't working. I really have no idea what i'm doing.

[accesskid]

From that information i dont really know what your looking for but start by going into design view and typing your fields in and selecting the data types.

May i advice if you to a very useful book i have used called 'Access 2010 Inside Out'

Will give you basic and advanced knowledge about access databasing.

[TrKent]

Ok, I am setting up the following tables.
Vehicle info: for all vehicles from cars to road tractors.
Equipment info: for all equipment, skid steers, loaders, pavers, etc.
Company info: for the three companies the database is for.
Employee info:
Vendor info:
Inventory details: Part Number, Type, Target Stock, Restock level...
Inventory Transactions:
Equipment services:
Vehicle services:

a few questions:
Should I have lookup fields between my tables? i.e. should i have a field that has the driver of a vehicle that comes from the Employee table? and in the inventory table should i have a lookup field for the vendors

are there other tables i should set up?

[TrKent]

...Anyone?

[TrKent]

Next question, if I have a table of the inventory information. Such as a Filter Part Number, Part Type (oil filter, air filter, etc.) How would I go about creating a way to organize what the application is for each item and the stock quantity for each of the three companies?

[ndtownsend]

I'm not sure if you need more tables yet..... but let's take a step into the overall design....

First identify as you did briefly in your first post each item or action you wish to track. These will become tables.


Then decide on what you need to know about that item or action and list it. ( these will become your columns.

Table 1: Company
columns: uniqueid, Name, address1, address 2, city, state , zip, phone1, email1, fax1, date added etc.

Table 2: Employees
Columns: EmpId, LastName, FirstName, address1, city, state, zip, phone, email, dateadded

Table 3: EquipAssignment
Columns: EAID, EmpId, EquipID, DateAssign, DateBack etc.

Table 4: Vehicles
Columns: VehicleId, Name, Mfgr, tag, DateAdded, Cost, CompId

Table 5: VAID, empId, vehicleID, DateAssign, DateBack etc.

*** you could ombine tables 3 and 5 *****

Table 6: Vendor
Columns: VendorID, Name, Address, etc

Etc....

[ndtownsend]

Inventory: This can be a whole science in itself....

If you have the same part that you get from 2 different vendors it needs to be in your table as 2 different items.....this will allow you to generate purchase orders for each vendor and track them.

Does the 3 companies have the same inventory items and are they kept as separate inventories? Does each company generate their own vendor orders?