Need help created new database

[rvil460]

I was recently moved into an equipment tracking/dispatching position. I was asked to develop and implement a tracking system from scratch. Since I have Access 2007 on my computer, I figured I could use that. I have never used Access before, and am new to this. I have messed around with it for the past few days and am able to create a very simple database that can do what they want. I would like to create a database that will track equipment number, description, pick up location, pick up job number, drop off location, and drop off job number. They also want me to keep track of the dates, so they know when the equipment moved and how long they stay at job sites.

What I am trying to find out is what the best method of accomplishing this is. Should I make a simple table with the different fields and a form that I could just input the information in, or is there a better, more efficient way of doing this?

I would really appreciate any help and input.

Thank you

 

[spikepl]

First see if there is any suitable template you could alter http://office.microsoft.com/en-us/templates/CT010152732.aspx?av=ZAC

And your data most definitely does not sound like belonging in ONE table. You can make something work, undoubtedly, but it WILL come back and bite you eventually, if you don't get a reasonable data/table structure set up from the outset.

 

[rvil460]

I agree this database will come back to haunt me if I make it as simple as it is right now. Do you think I could create one database that has our whole equipment list, including equipment number, description, and rent/own. Then somehow link that to a drop down list on a form that I can update everyday? The form would have to include date, pick up location, pick up job number, drop off location, and drop off job number. That information will change constantly. Is this possible, or maybe a better way?

 

[spikepl]

You should structure your data in appropriate tables, otherwise all manipulation (and inevitable changes/extensions) will be a pain. From the very beginning. Access is not Excel.


Read up on normalization http://support.microsoft.com/kb/283878

have a look at some data models http://www.access-programmers.co.uk/forums/showthread.php?t=195190

You can stuff everything in one table, but it WILL hound you from day one. Find a similar template or look at the data models.

Come up with some suggestions for which tables, containing what, and connected how, and post it here, then the readers here will help you along.

some instructions are here:
http://office.microsoft.com/en-us/training/CR010158283.aspx

 

[rvil460]

This is what I'm working on...it may be a deadend. I'm not sure if I'm on the right path or not.

 

[rvil460]

spikepl, Thank you for all your help. After a lot of reading, testing, and frustration I think I have something worth using. Now my new question is, will previous information be deleted, or unable to produce reports, if I delete information from a related table?

 

[spikepl]

depends on how you made the relations. Did you set the options for Enforce Referential Integrity and Cascade Delete ?

The major issue is always to preserve the referential integrity, so that no "orphans" are created by accident

 

[rvil460]

I have not created a table for this yet. I wanted to make sure I could update the table without ruining data. This table will be our Job Number table that will have to be updated about every month, but I would like to still be able to report equipment information related to old/deleted job numbers. What would be the best method to do this?

Thank you

 

[GinaWhipp]

Peeking in...

I see you have a table Equipment Category but you do not have a field in the Equipment List to link to. Will all Equipment have a Category? If not, what will that table be used for?

Also, I am removing all symbols and spaces from field names... They become a pain to deal with when writing code.

 

[rvil460]

Hi GinaWhipp,

That is actually a database I was testing on. I will attach the one I am planning on using. Sorry for not updating that. I really appreciate you looking at it.

 

[GinaWhipp]

Hmmm, still needs some work... and if you could explain the Equipment Category table it would really help out.

 

[rvil460]

The Equipment Category table is to quickly identify what type of equipment it is, rather than a full description. I'm hoping I will be able to create reports that will show, for example, these Loaders with these equipment numbers moved from here to there on this date. People out in the field will be calling me to let me know they are going to move equipment, we have about 8 remote offices, and work all over southeastern New Mexico...so our equipment is constantly moving and our job numbers are no longer needed when we finish a job, but we still need to report certain equipment was at those jobs even after the fact. I hope this helps you out. Also I have attached a newer file...I pretty much just edited the Form to look better. I hope this information helps, and I appreciate you greatly.

Thank you.

 

[GinaWhipp]

I'm not even looking at the form just at the tables. I will not pick out the drapes before the foundation is even built. And yes that does help because it tells me that a field should be included in the EquipmentList table.

 

[GinaWhipp]

Okay, here you go. Have a look at the tables and the relationships...

 

[GinaWhipp]

Oops, forgot, you might want to have a gander at...

Jeff Conrad's resources page...
http://www.accessmvp.com/JConrad/accessjunkie/resources.html
The Access Web resources page...
http://www.mvps.org/access/resources/index.html
A free tutorial written by Crystal (MS Access MVP)...
http://allenbrowne.com/casu-22.html
MVP Allen Browne's tutorials...
http://allenbrowne.com/links.html#Tutorials
Sample data models...
http://www.databasedev.co.uk/table-of-contents.html

 

[rvil460]

Thank you very much. This will definitely help.

 

[GinaWhipp]

You're welcome! Come back if you need more help...