Is there a better approach?

[peciam]

Hi to all,

I am working with student performance data by school district for grade 3-8. The data is both for English and math.

The data came to me in one complete table. I had to break it out by school district, type of test, english or math, and then by grade level. So I have 6 tables for english and six for math, all linked by school district.

I then run a query that allows the user to select a school district and the query pulls in demographic data, risk indicators, and grade 3-8 english and math data.

My question is about performace. Should I run the query each time that pulls the english and math data or create a table with all the data by school district for all grades?

Thanks,

TC

 

[GingerR]

Is this a one-shot deal? you get the data and crunch some numbers for them and that's it? If so I'd leave it all in one table.

Why do you have 6 tables for English and 6 for math? for each 'grade' 3-8?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[dhookom]

A properly normalized table structure might have tables for Districts, subjects, and scores. This would depend on your actual values you are tracking.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[peciam]

Hey Ginger,

No it's not a one-shot deal, if I undersatand your statement. The user(s) will run the queries when ever they need a school district profile. The english and math is only a part of the whole profile.

The reason I have six tables for each test, the original data came in one table.
Dist code, Name, Subject, # tested, % L1, %L2, %L3, %L4,
I couldn't pull in the data for just one district, I was getting 6 record for each district, when I linked that to the other records I was getting thousands of records, instead of the 702 districts.

 

[GingerR]

Not understanding your last post. Not sure what %L1, %L2 etc are. If you have things set up properly, you won't get multiple duplicate records, if that's what you mean.

What I meant was are people going to be entering data? Or you only imported data one time (or may import the data other times)? Are users going to simply be "pulling" data (running queries) and not entering data?



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[peciam]

Hi,

%L1, %l2, etc... they are the field names from the original file. That's part of the question(s), what does set up properly mean? I have read the normalization paper, and many others, taken courses on it. Still not clear when you get a file from the state that has thousands of records, the same field names, many records for the same school district.

Users are only going to be pulling data in reports, no updates from them.

 

[lespaul]

I couldn't pull in the data for just one district, I was getting 6 record for each district, when I linked that to the other records I was getting thousands of records, instead of the 702 districts.

That has to do with the joins into the other tables.

Can you give some sample data from your original file and explain the tables that you are inserting this information into and what results you need to be able to report, maybe we would have a better idea of how to help you.

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[GingerR]

Hi--what I mean is "What does L1, L2 etc MEAN?" I'm trying to help sort out your table design. Obviously you have things joined oddly or set up not as well as they could be, as Leslie says. Please help us help you by giving us sample data and the table structures of any tables you currently have!!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[peciam]

Hi,

I hope I can explain. I am able to do the task assigned, but I have started to wonder if I am doing it right, is there a better way.

The profile(report) I create has many data parts. Of which come from many files. My link is the school district code, ie. Albany SD is code 010100. I link files based on that code. I have just received a 5000 record file from the state that has grade 3, 4, 5, 6, 7, and 8th grade english and math results for each grade, many records for each district, ie. Albany SD has 12 records, each grade for enlish and math.

I could not find a way to pull records by district from that file. So I created sepreate files, thaey contain district name, code(link), Grade="3rd"(etc), number tested, Percent at level 1, percent at level 2, so on up to level 4.

Once I had the grade levels broken out I then created a query linking on school district code to each grade level.
That query is then linked to the other profile data files based on school district code.

I hope this is clear. My question was should I create one file instead of running the query? I don't have a performance hit, running it local now, but will push it up to the network as some time.

Thanks,

TC

 

[GingerR]

Put it on the network right now and test it out and see if there's a performance problem.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html