Traffic Database Design

[Collins]

In the months and probably years to come I will be attempting to build a database that will collect information that is traffic related. I am learning and have taken some beginners classes. So I have an understanding of how things work together. Just not an understanding of how to make them do it. I would like to make this a working thread that from time to time I can post issues I'm having in the building process. I hope that if you have an idea or suggestion as to how I could make something run smoother or easier that you will please post it.

With that said here are some parameters for the database. Every year we have 5 traffic reports that we have to create. All of them start from an Excel Spreadsheet. Between the 5 reports there are 238 Intersections. Some reports use all 238 records and some do not. So I'm guessing the Main table would be an "Intersections Table" with all the information about that intersection in that table.

Here's where things get tricky. Each year we are given the all the crashes that occurred in our county from the state for the previous year. The current way we use this information is to plug certain parts of information in the spreadsheets, they do some calculations, the order of the intersections are then organized from most important to least important and there is the report. Not all the reports use all of the same data. Some is duplicated from spreadsheet to spreadsheet yet each of them are unique. What happens is that we are entering the same information multiple times depending on the report and mistakes are occurring.

So I need create a database that is centered around Intersections, that stored yearly crash data and volume data, runs some calculations based on the previous 3 years, organize the records by priority and spit out 5 reports at the end of the year.

I can't even figure out where to start. I'm thinking my "intersection table". So I would really appreciate any thought provoking questions or ideas or directions I can go so I can start building this thing.

Thanks

[June7]

Every issue should be its own thread. Ask a question, get an answer, thread is solved.

Here is a good tutorial site that might help you organize http://www.rogersaccesslibrary.com/

Identify data entities and relationships, build tables to fit.

[JamesDeckert]

first, get a list of ALL data which you need in the DB.
A good place to start would be to look at all your Excel sheets.
Then normalize this data into tables for Access.
Then begin building forms/reports/queries

[ssanfu]

You might review this thread....
https://www.accessforums.net/access/...ess-52954.html

[orange]

All info offered to this point is dead on! Review the links in the posts.
Learn some database concepts and some Access "gotchas" before you get too involved.

Now, a few questions about your proposed database which you have named Intersections.

What information will users expect to get out of the database? That is a good indicator of what must go in. You mention Intersection, but you started with Traffic and then referenced crash. So
what exactly relates Traffic, Crashes and Intersections? If you only receive the data at the end of the year, what exactly will be the operational (daily usage) of the database?
If you are doing "what-if" analysis and some statistics and forecasting, then maybe spreadsheet is more appropriate. I'm not saying it is or isn't, I'm just trying to say - understand the task and get the right tool for the task.

Make a list of all of the facts associated with "the business you are trying to support with automation". Identify what you are trying to do, and then relate all of the facts you have derived and build a model of the things in this "business".

Good luck with your project. And don't forget to research the links that others have mentioned.

[Collins]

Awesome guys thanks... I'll do some reading and orange I'll answer your questions later.