Inventory Database needed for School District...please help! (1 Viewer)

[Tech_Girl]

I am trying to design a database that will allow me to keep up with all of the teachers in our school district and their administrative computer and printer. It has taken me nearly a year to collect all of this data into 36 separate excel spreadsheets and now I want to compile all of it into an Access Database. I have read your site, tutorials, and books…and well….I am wayyy past frustrated....i am fairly new to Access..... Not sure which way to go…..so I decided to start small and ask for help.



I have 36 schools and 1500+ teachers....

Here is what a typical Excel spreadsheet would look like... these are the column headings....

Unique School Number
School Name
Room #
First Name
Last Name
PC Model
Dell S/N
Asset Tag
Warranty Expiration Date of Computer
Printer
Printer Model


So far….i have this

School table……SchoolID (does this have to be an autonumber or can it be the unique school ID number that our district uses?) and School Name

Teacher table....TeacherID (autonumber),First and last name, room number, and SchoolID

At this point, it all gets fuzzy. What I would like to be able to do is enter the asset information on a form into an Asset Table. I found a template and tried to modify it…but ran into problems.

It suggested a….

Category table……Category ID…..2 items….Computer and Printer

Asset Table
AssetID
SchoolID
EmployeeID
CategoryID
Computer Model-----this would always be Dell
Model Number---this will vary…..we have about 8 to 10 different models in our district at this time
Dell S/N
District Asset Tag#
Warranty Expiration of Computer….there would be no warranty expiration for the printer

I think that the ASSET Table…would be my main one…..i would like a form to enter this data into…..

I thought about a Computer Table…..but couldn’t figure out if that was logical or not…..i could do make and model……would have about 10 entries but all Serial numbers and asset tags would have to be entered somewhere else, since they are all unique to the machine.

My job is to see that all teachers have a computer and printer....I am trying to get a yearly replacement rotation started based on warranty expiration.........I copied and pasted all this Excel date into ONE spreadsheet so that I could do a basic query....but i know that there has to be an easier way to do this.....and keep the data current as computers and printers are replaced and new teachers come into the district

I would appreciate any direction…and certainly hope this makes sense….

 

[David Eagar]

I'm not sure, but if a teacher can only have 1 computer & 1 printer, do you need the Asset table? If the computer information went into the Teacher table, it would give you less tables to manage.

 

[David Eagar]

On further thought, this might be a better starting point

 

[Tech_Girl]

David,

This is a great starting point....i am looking at what you have created....it appears that we are somewhat on the same page.....and u have given me something to think about.....i would like for you to look at my database that i have been working on.....i want to incorporate your ideas into it....would you mind looking at it and telling me what u think?

and again, thanks for your hard work....this can be quite frustrating to newbies...

 

[David Eagar]

Happy to help

 

[Sean O'Halloran]

Need to normalize

I'm not the best person to help with this - "The Doc Man" is great with advice on analyzing which "entities" you need to assign to their own table, and I would strongly suggest you search for his name and read some of his numerous posts (try searching on these: entities, normalize, Doc Man) - but I think you should rethink your tables along these lines: teachers work with equipment in rooms in schools: to me that suggests four main "entities": teachers, equipment, rooms and schools.

The way you started kind of mushes together teachers and rooms, which may cause trouble later. One rule of table normalization that often helps keep me straight is this, and I'm paraphrasing wildly: Each field in the table should describe only one entity. If that is Teachers, you want first name, last name, date of birth, gender, etc.; each of those describes the person/entity. Room number does not, it describes something else so should be in another table.

Another question to think about: What processes will this database support? I assume you want to be able to track where your equipment is, and in whose control? If equipment is assigned to a teacher, and that teacher goes on leave, does the equipment get assigned to a substitute teacher? Does it stay in the same room? And if you change the equipment assignment to another room or person, would you need to know WHEN that re-assignment occurred? I would want to know, in case the equipment grows legs...So you may need to track the date the equipment is assigned to either person or room, or gets repaired / replaced /breaks down.

I hope that helps, and seriously; search for the Doc Man lectures - and I mean lectures in the most positive way: helpful, incisive, clear.

Good luck -Smilin' Sean

 

[David Eagar]

I hope that helps, and seriously; search for the Doc Man lectures - and I mean lectures in the most positive way: helpful, incisive, clear.

Good luck -Smilin' Sean

Now there's some good advice if ever I saw it!!

 

[Tech_Girl]

yes, i agree.....certainly made me sit up and take notice.....i NOW realize what hard work must go into the design BEFORE the actual creating of the database.....boy, was i clueless

Thanks to both of you guys....you have no idea how much you are helping me....

it is nice to learn from experts....and whoa....do i have a lot to learn!!!

 

[ajetrumpet]

it is nice to learn from experts....and whoa....do i have a lot to learn!!!

Well, I sure hope you learned a lot, however your project got finished. I sure hope the help that you got was worth it. It'll sure make your life a whole lot easier, I'm sure of it!