table design, a different view is required to get my head around this!!!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Norman Fritag - 11 Feb 2004 12:59 GMT

Hi there,

I am looking for an answer to the following senario:

For a schools, I record the number of students in the Year and student
participation in Activities.
The number of students who are currently in the Year (eg: 6,7, or 8), become
know to me only on a first visit. Some activity data are collected and sent
to me on follow up visits. This is the method how I receive the data, before
they get entered into the database and I have to bear with this situation.
Eg: Year can range from 0 -12.

In the first year of this study I managed to collect data of 1/3 of the
targeted schools.

This year a new school year has started. The students form last year have
moved up 1 year grade. In this example year 12 from last year has dropped of
and last year 11 in now in year 12. Year 0 is filled with new students and
previous year 0 is now in year 1 and soforth.

I will consciously receive data, which predominantly will be of type
follow-up visits by grade. I can't tell which proportion of the activity
data (eg on Year grade 8) reported are from previous year grades (eg: last
year 7) and which are new to this grade. My only initial fix point for the
number of students in the year grade was give during the first visit.

If I continue recording and entering data as I have for last year, the
number of students who participate will rise above the number of students in
the year grade, which would create a lot of meaning less data. (I don't have
and can get this years number of the students grades)

Now for the sake of reporting and producing reports on valid data as a
percentage of students in each year (eg 6,7,8) for 2 or more school terms, I
am looking for a solution that allows me proper data entry as well as proper
reporting???

What other binding nominator could I consider, which would allow me to
report over 2 or may be more school terms, whilst I continually enter new
date to the system????
Could I in accounting bring forward the count from one term to the next term
by one grade?

I would appreciate any thoughts

regards

Norman

Reply to this Message

Jeff Boyce - 11 Feb 2004 13:29 GMT

Norman

I am not able to visualize your data structure from the description you
provided. It sounds a little like you might be using multiple tables or
repeating fields to handle the fact that at different points in time,
students are "in" a different Year -- if so, you will want to consider
further normalizing of your data.

Please post back with a description of your tables and fields...

Jeff Boyce
<Access MVP

Reply to this Message

Norman Fritag - 11 Feb 2004 20:05 GMT

Jeff,

Tblactivitydata
ActivityId = autocount
SchoolID = numeric,
LocalArea = text, 20
Year grade = numeric,( 0-12 grades)
Students in a Grade = Numeric
Students pract in Activity = numeric
dateof visit = date
type of visit = yes/ no ( first or followup visit)

thats the table in a nutshell

let me know if you require further information.

regards

Norman

> Norman
>
[quoted text clipped - 8 lines]
> Jeff Boyce
> <Access MVP

Reply to this Message

Jeff Boyce - 12 Feb 2004 13:00 GMT

Norman

Given the table structure you provided, I don't see how you could report
anything. Let's back up a step. Before talking about your data, please
describe what you want to be able to report. For example, you may wish to
report "which students participate in which activity(ies) during which
"grade" year. Or you may wish only to report how many students are
participating, not which ones.

Is there a reason you've decided to use Access, rather than a spreadsheet to
keep your information?

Signature

Good luck

Jeff Boyce
<Access MVP

Reply to this Message

Norman Fritag - 16 Feb 2004 12:06 GMT

Jeff,

Thanks for your response.

I think the issues are in the multi latitude of the time the Project could
run and or which way (flexibility) is required for reporting.

a) I would like to report on all students, which at the time when the
project started fall into the Grade / year 9 - 12.
Eg: the same student who was in the starting year in Grade 9 may not be
in the system any longer after 4 years, as he has out grown this category.
I don't have any names. I assume that students move classes from one year to
the next. How many of them move or don't move I don't know. The point is
that I report on there participation through out the entire project only
once. As students move to the next higher grade in subsequent years, I
believe that I will pick up only on the students that I have not recorded,
having participated.
I further assume that the fluctuation of students form year to year is
about 2.5% of the students in the grade at reporting time.

b)I would like to report on all students from the point now and or other
times back to the start of the project to see which student have
participated, base on today's group (9 - 12 year / grades).

c)I want to report by Grade and Year as well.

these are the most critical areas that I can think of.

regards Norman

> Norman
>
[quoted text clipped - 7 lines]
> Is there a reason you've decided to use Access, rather than a spreadsheet to
> keep your information?

Reply to this Message

Jeff Boyce - 16 Feb 2004 13:30 GMT

Norman

I will try paraphrasing your descriptions, as I am still somewhat unclear
what you are trying to do (see in-line comments below)

> I think the issues are in the multi latitude of the time the Project could
> run and or which way (flexibility) is required for reporting.

To make the application maximally flexible for reporting, you need to
capture the most-detailed information possible. You can then aggregate for
reporting. If you only capture summary level information, you will never be
able to report "detail" level information.

> a) I would like to report on all students, which at the time when the
> project started fall into the Grade / year 9 - 12.
[quoted text clipped - 6 lines]
> believe that I will pick up only on the students that I have not recorded,
> having participated.

This is a lot of assumptions. If you are only looking at counts of students
in a grade (and not individual identifiers), how can you tell that you "pick
up only on the students that I have not recorded"?

> I further assume that the fluctuation of students form year to year is
> about 2.5% of the students in the grade at reporting time.

Does this mean you expect the same students (minus 2.5%) to be in the next
grade each year? Your system doesn't have more students moving in/moving
out?

> b)I would like to report on all students from the point now and or other
> times back to the start of the project to see which student have
> participated, base on today's group (9 - 12 year / grades).

You said you didn't have student names ... so I assume you don't have studen
t IDs, either. You CAN'T report on "which students" if you don't record
individual students.

> c)I want to report by Grade and Year as well.

What is it that you want to report "by Grade and Year"?

Signature

Good luck

Jeff Boyce
<Access MVP

Reply to this Message

Norman Fritag - 16 Feb 2004 20:02 GMT

Jeff,

I see that I have manage to lift some serious concerns!

> Norman
>
[quoted text clipped - 24 lines]
> in a grade (and not individual identifiers), how can you tell that you "pick
> up only on the students that I have not recorded"?

Yeah! lot of assumptions! ??? but I don't get access to any ditails.
(confidentiallyty)
At the first visit I record the total number of students in the grade plus
the any students who participarte int he activity. To be more specific, they
are given a form,which they hand in, only those students which are returning
the form are recorded. Hence I think that over time I pick up only the
students not recorded.

> > I further assume that the fluctuation of students form year to year is
> > about 2.5% of the students in the grade at reporting time.
>
> Does this mean you expect the same students (minus 2.5%) to be in the next
> grade each year? Your system doesn't have more students moving in/moving
> out?

Yes I assume the same students (which i dont know of cause) plus minus in
the next year grade.
The only new students are each year the Kindergarden "students", all other
will fall into the category +- 2.5%.

> > b)I would like to report on all students from the point now and or other
> > times back to the start of the project to see which student have
[quoted text clipped - 3 lines]
> t IDs, either. You CAN'T report on "which students" if you don't record
> individual students.

No studentIds. can't report on students induvially, Its though of more as
statisticlly.

> > c)I want to report by Grade and Year as well.
>
> What is it that you want to report "by Grade and Year"?

In a and b I am asked to report on group "year 9 to 12" form diffent view
points.
In point c i am asked to report on year grades as a percentage participation
over the whole duration of the project, meaning who many students in the
grade have over time participated.

Given the few details that I 've got you may now understand why I am looking
for some ways to setup the system though I have a bit more flexibillity in
reporting.

regards

Norman

Reply to this Message

Jeff Boyce - 16 Feb 2004 22:22 GMT

Norman

As I understand what you are trying to do, I don't see a way for you to do
it with the data I understand you are collecting.

Is there a reason why you have chosen Access, rather than a spreadsheet like
Excel, to maintain your data?

Signature

Good luck

Jeff Boyce
<Access MVP

Reply to this Message

Norman Fritag - 17 Feb 2004 12:39 GMT

Jeff,

thanks for your reply.

The project was started with a spreadsheet. The people who designed the
spreadsheet don't want to use it anymore as it become unmanageable! It takes
up to 10 minutes to load!!!

Jeff, I appreciate talking to you about it. The fact that your asking me the
question to describe what that system should achieve has already helped
tremendously!!

Now I am still looking for a result, without having to use code to much!

Regards

Norman

> Norman
>
[quoted text clipped - 3 lines]
> Is there a reason why you have chosen Access, rather than a spreadsheet like
> Excel, to maintain your data?

Reply to this Message