Table relationship question

[ISTodd]

Hi,
I am new to this so I hope I am clear with my question.... Every year my school gets testing info (achievement scores) from the state on a spreadsheet. There are about 70 fields including name, gender, school name, test score, etc... I was able to easily import one year's spreadsheet into an access table and create a query, and form to sort and filter the data, but is there an "easy" way to import any number of previous year's data so I can track a student's progress longitudinally or compare average test scores by school, grade, etc? If I just import each year's test data into a seperate tables ie (tbl2003, tbl2004, etc); would I be able to query the data together even if there really isn't a relationship established?

Thanks for any help you can offer. Todd

 

[PHV]

Take a look at UNION query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

A common approach is to include the year in the table name that was imported from your spreadsheet. PHV's solution would then work for this approach where you use a union query when working with different years (assuming same format).

Another approach is to include the year in the table design. In this case, you can append the data to open table where the records for each year are easily identified by the "year" field. The "year" field can either be added to the spreadsheet prior to the import, or added to the imported tabled, or used during an append query when moving the data from the imported table to the "master" table.

 

[JeremyNYC]

Having a table for each year is a really bad idea. I've got a great paper on the topic on my old website at

http://www.abcdataworks.com,

in the developers' section (written by Paul Litwin).

---
Jeremy Wallace
METRIX Project Coordinator
Fund for the City of New York

 

[BNPMike]

Having a table for each year is a really bad idea.

In industry this is quite common - partitioning tables into years, months, regions etc etc. Some databases allow you to partition a table automatically and stick bits on different media. As PHV says, UNION provides the same external schema to programs as if you held all the data in one single tablespace.

Why partition? the normal reason is you are carrying out most activity on perhaps this month's/last month's data so accessing x years everytime is wickedly inefficient.

 

[JeremyNYC]

Mike,

Take a look at the initial post. This is a new person, with a very small set of data. I'll stand by the line you quoted. Yes, there are times when it makes sense to break the rules of normalization. But this doesn't even come close to being one of those times.

Jeremy

---
Jeremy Wallace
METRIX Project Coordinator
Fund for the City of New York

 

[BNPMike]

And which rule of normalisation were you thinking about?

 

[willir]

Jeremy -- Good to hear from you -- I have not seen a post from you in eons.

Hmmmm...
Personally, I prefer to use one table for transactions, and include the date field - I agree with Jeremy on this.
Pro: Makes queries, form design etc much easier.
Con: When importing, have to develop a mechanism for adding the date, and appending to a table, not over-writing -- not a tough issue, but still has to be dealt with.

Using the year as part of the table name is a common approach, especially if importing from a spreadsheet -- more of a convenience thing at the front end when importing, but later work becomes more complex because of the need of Union quries, etc.
Pro: Simple way of importing. Keeps transaction files smaller - may be an issue for large amounts of data.
Con: More complex queries -- very do-able, but you could be forever using Union ("longish") queries. Using the same form for multiple tables requires some thought and skill.

Lastly, using two tables -- one for current transactions, and the other for archived transactions, is another approach.
Pro: "Smallish" tables where you work with only current data, faster access.
Con: If you need to compare "archived" data and current data requries some work.

Which rule??
All information in the database should be represented in one and only one way -- as values in a table.

This line can be interpretted in favour of either one or more than one table. One table -- have to add the date, but you probably still want to incldue the date field in the table design regardless of the final design.

Richard

 

[BNPMike]

Which rule??
All information in the database should be represented in one and only one way -- as values in a table.

That's a design feature of the relational model and is not connected with normalisation. We know by the way that it is incorrect as all aspects of the relational model require the name of the table which therefore opens up the possibility of representing data in terms of which table a row is in - which is what we are discussing.

There is an urban myth that one-to-one relationships are evil and incorrect. There is nothing at all to say they are a bad design. I agree you don't require them in the model under discussion but for the same reason (low cardinality) it is no problem to split tables and UNION them back into required contiguous views