Design help for item location and testing date database

[asoe209]

Hello all im new to the forum, hoping for some advise about designing a database in access 2007 to use at work on access 2010.

hope this is the right thread.

I have been reading a watching reviews on the subject but cant seem to get it to work how I want it to with what I have tried so far.

I work for a constriction company, and my job is to keep track of safety equipment. this involves insuring that all equipment is up to date on the safety inspections and certificated this is done 6 monthly or yearly depending on item.

I also hire out the same equipment to users so I have to keep record of who has hired and the date it went out and came back.

at present this is all recorded on an excel sheet, with the shear number of items i believe a data base would be better suited.

i have just tried working out table relations again on paper as this seems to be my main problem right now.

the information that i need are as follows,
fleet name
fleet number


serial number
last test date
next test date
current location-on hire, in store, at calibration
user collected
date out
date in
supervisor details- name, email, phone number
job number
600 number

the idea behind it would be so that, i can quickly search fleet, see its location and book out if required. then update its location if it out for calibration and see if it requires calibration.

Also i would like to be able to quickly run reports to sent to supervisors that currently have fleet booked out to them so it can be returned for inspection.

at present this is how my tables look and the relations ships.

fleet table
fleet name
PK - fleet number - linked to fleet location table, fleet name
serial number
last calibration date
next calibration date
Calibrators. - linked to calibrators table, calibrators ID

Calibrators table
PK-ID
company name
date calibrated
date returned

Supervisors table
PK-ID - linked to fleet location table, supervisor
name
email
phone number
job number
600 number

Fleet location table
Fleet Name
fleet number
on hire
off hire
date out
date in
collected
supervisor

Can any one help or advise, i would be really grateful thanks

see attached copy of sheet im trying to convert to access.

[June7]

Not seeing an attachment to your post.

Maintenance and inventory type databases have been discussed. Did you search forum? or Google? What you have appears to be a good start.

Also, there is a template database called Lending Library that you might find interesting for the check in/out aspects of your project.

[asoe209]

Morning, thanks for the reply.

Sorry for got to attach file but i cant attach from work pc. will have to attach later

Well I keep tryinig and I just cant seem to get my relationships to work. I want every thing to be on a for for quick ref, and easy data entrey. when I have ried making the forms its not making it the way i want it to.

so im now thinking do i just have to split every thing up.

Also to add, do you have to add all you data in the tables before you make the relationships and the form?

And yes I have been searching google and not found any thing that matches what I want just yet. I do need to search the forums through,.

[June7]

No data required for setting relationships and creating forms. However, a few dummy records can help with testing.

You will probably need to use form/subform arrangements.

If you want to provide db for analysis, follow instructions at bottom of my post.

[asoe209]

I will upload once im on with every thing I have done so far. thanks.

[asoe209]

Hello again, ok so the data base is attached as well as the original excel sheet that I am working off and trying to improve.

The main problem is the relationships. I have done some reading and from what I have found out you cant get multiply tables in one form even with sub forms. So I was thinking now to go with one from still but using a tab base form. So at the top of the form are the main details. (fleet name, fleet number, S/N and there dates.) then with each tab having the other relevant info. which would be current location. this in a drop down box.

on hire would then link to the hire details

hire details- supervisor, collected by, date collected, date returned. from the hire details the supervisor should link to the supervisors details as this is where i will need to pull there contact information from. in the hire im not sure if supervisor can be selected as a combo box to link or if the name will have to be typed and linked,

not asking any one to do it for me but more help in getting the right relations to work so all data can be added as this changes daily.

reports will be my next big thing which im hoping should not be a problem one i ask the right question in my query.

thanks all.

[June7]

Not sure what you mean by 'cant get multiply tables in one form even with sub forms'. There can certainly be multiple subforms, each bound to a different table.

Primary and foreign key fields must be the same data type. You have an autonumber field as PK in SupervisorDetails table but the FK field in HireDetails is text. Use combobox to select supervisor and save the PK.

Not showing a form with the tab structure you describe.

[asoe209]

Hi,

that was just some thing that I read about tables that said you could not do multi table forms for entering data. Ad es I have not se the tabs up on that form yet.

What yo said here. "Use combobox to select supervisor and save the PK." do i need to add the supervisiors details to the supervisiors table 1st and then build the combo box in the for using the details fro the supervisiors table?

or do i build the combo box in the hiredetails table?

other than the relation you mentioned how are the other relations looking?

[asoe209]

I belive I have done what you suggested and now have a dropdown lined.

Also got rid of the location table and aded location to my fleet table.

ow tryig to make a quary that will show items out on hire, bu not show one that have been erturned.

[June7]

If supervisor is not in the table and therefore not in the combobox list, there are ways to handle adding the supervisor to the table during data entry. Requires VBA code. Explore the NotInList event of combobox.

You might explore the Lending Library template for ideas on the data structure and handling items out/in tracking.

[asoe209]

ill try and have a look at that.

i have been playing around with it most of today and its getting to where it needs to be, as im a noob when it comes to this i don't want to puch thing to far I just need it working to get reports out. as long as its a a workable manor it can always be improved.

i have manged to get queries that show over due fleet now and fleet due for the month. I got to work on getting ones that show wish supervisors have fleet booked to them that have not been bought back.

so far on test records with 1 supervisor the query is pulling through all hires linked to the fleet even if its got a return date. so that tonight plan sort that.