Linking Excel to Access

[jimteacher]

Hi!
I'm a teacher at a high school with 1800 students.

The attendance lady types up an excel file each day
listing the tardies and early outs, etc. for each student
who comes in on that day for a pass.

This would end up as 180 excel files a year,
with maybe 100 or so rows for things with different categories.

She would like to be able to search for a particular
student's name throughout all files to generate a
report which would tell her how many early outs, etc.
(stuff that isn't in the regular absence report scanned
in once a day) that one particular student
has had. Now she has to do this manually, looking
through paper files for each student.

Is there a way to link the file to Access so that all
data will go to the database so that she can
search for a particular student name and get a report
on the student?

She isn't into complexities, so it has to be pretty easy.

Thanks for any help you can give us, as it's a royal
pain to go though hundreds of paper files.

Jimteacher

 

[CyberDwarf]

Hi jim

I'm involved in writing software for educational establishments in UK and I
think I can say fairly that any app attempting to achieve what your lady
wants WILL have to tackle some complexity.

1 - You will have to set up the data tables in Access first;

2 - Once these are set up, it will be unnecessary to use Excel at all, if
you design a few Access input forms & reports;

3 - I stress again that this is no trivial task, as there are legal
requirements to adhere to....

Come back to me if you are still interested

Steve

 

[Adam]

Forgive me if I'm missing something, but can't you have your attendance
lady use an Access database, rather than spreadsheets?

 

[Dave F]

If you want to avoid "complexity" I would maintain this data in Excel.

This strategy, of course, introduces its own problems, such as data
integrity issues. However, assuming you're looking for the quickest, most
efficient option for someone averse to learning new technology (which is how
I understand your use of "complexity") then I would do the following:

1) Create a single Excel workbook for the entire year
2) Create a separate tab for each month
3) Add a date column to each table in each tab, so that you can identify
students by date.
4) At the end of every month, run VLOOKUPs or COUNT or SUMPRODUCT functions
to count the various numbers you need to count for attendance records.

As to point 4, if you need help with these functions, a more appropriate
newsgroup would obviously be the XL newsgroups.

Of course, it goes without saying that, in theory, a database is more
appropriate for this task. But a database does involve complexity.

Dave

 

[CyberDwarf]

Sorry, Dave.

I assumed the sheer quantity of data in the worksheets may be a complicating
issue, too.

Maybe not..

 

[Dave F]

Well, the amount of data could complicate it, or it could not. It all
depends on how the XL workbook is structured (which is its own complexity I
assume!)

 

[CyberDwarf]

Interesting discussion....

No response from JimTeacher yet tho'

 

[jimteacher]

Sorry that I didn't get back earlier, but I've been at school all day
and night.

I''ve worked with access before, with textbook lists, so I'm not averse
to complexity
myself. After it's set up, I assume that the lady will just have to put
in names, etc.

As mentioned, the main thing she needs to have is a cumulative list of
early outs, with the ability to put out a report each day, so that the
teachers know who was an earlyout, as opposed to an absence or tardy.

If this can be worked out, with reasonable complexity, I'd like to try
it,
as I like challenges. It's just that the other folks don't like them
too much.

Here's the way the files are set up, with 40 or 50 in each column.

TARDIES Time Type SUSPENSIONS EARLY OUTS
OUT
Axman, KATIE 8:51 TE Biggers, MARIANO BRACKLEY, PATRICK
1:55


Thanks for any help you can give me.
Jim