Traffic Database Design Question

[Collins]

Beginner here. I have a pretty go understanding of tables, records, fields. Things start to get a little grey with relationships and forms and then totally out of my element with queries and reports.

Every year we collect crash data, traffic volumes and other information and crunch it in an excel spreadsheet. We base our studies off the last 3 years of data. So for this year we are entering crash data for 2014 and our report will reflect 2012,2013 and 2014. Some of this information gets added together or calculated to determine crash rates and warrants and so on.

So would I have a table with all different volume and crash data we track sorted by the year as the primary key?

Then, how do we pull that information for the last 3 years? Queries??? Or Reports?

Any thoughts would be greatly appreciated.



Collins

[JoeM]

Tables, themselves, usually don't have any order attached to them. Someone once made a great metaphor and said think of a database table as a "bag or marbles". The records are all jumbled around in there, and order relative to other records in the table really has no meaning.

All sorts and selections you would do in queries. I would highly recommend educating yourselves on them (book, course, tutorials, etc). They are probably one of the most common and useful things in Access (and other database programs). Tables store records, but whenever you have a "task" to do, queries usually come into play.

Queries are often use as the Data Sources for things such as Forms, Reports, and Exports (many noobies are often mistaken and think only tables can be used as Data Sources for these things).

So, all sorting and record selection (criteria) will be done in your queries.

[Collins]

Tables, themselves, usually don't have any order attached to them. Someone once made a great metaphor and said think of a database table as a "bag or marbles". The records are all jumbled around in there, and order relative to other records in the table really has no meaning.

All sorts and selections you would do in queries. I would highly recommend educating yourselves on them (book, course, tutorials, etc). They are probably one of the most common and useful things in Access (and other database programs). Tables store records, but whenever you have a "task" to do, queries usually come into play.

Queries are often use as the Data Sources for things such as Forms, Reports, and Exports (many noobies are often mistaken and think only tables can be used as Data Sources for these things).

So, all sorting and record selection (criteria) will be done in your queries.

Queries will do the math too right? I have several equations that the data has to be run though as well. and then ranked in order of priority which I'm guessing would be a report at that point correct? or would a query still be the answer. Also Joe... Any book recommendations?

[JoeM]

Yes, most calculations will be done at the query level. A general rule of thumb is to never store anything in a table which can be calculated/derived from other fields.

There are a ton of good books out there. Back in the day, I got started using the Microsoft Step-by-Step guide to Access 2000. Amazon is always a good place to check out book reviews.

[nick404]

Lending credence to the Microsoft Access 2000 book, I used that as well, and well after the year 2000. Reports will be most useful for displaying your data from queries, tables, forms, etc. I would create a general query of the fields you need for your uses, and use date parameters (so you can type in 1/1/12 through 12/31/14) for running the query. When you create the report make sure you select whatever query(ies) you use as the data source so that they are link.

[ssanfu]

So would I have a table with all different volume and crash data we track sorted by the year as the primary key?

"Year" is not a good choice for a primary key (PK). I would use an autonumber as the PK field.
"Year" is also a reserved word in Access and shouldn't be used as an object name.

Problem names and reserved words in Access
http://www.allenbrowne.com/AppIssueBadWord.html



Here are a couple of sites about autonumbers/PK fields

Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers


Use Autonumbers properly
http://access.mvps.org/access/general/gen0025.htm