to normailise or not?

[Howard]

Hi,
I have the perenial student marks database problem which I'm trying to solve
for the third time in a simple way.

In a nutshell, one table of students, one of student-classes, one of class
assignments and one of students-class-assignment marks
one class = many students, one class = many assignments, one assignment one
mark.

The question is to do with ease of data entry. I'd like it to look like a
spreadsheet with names in col A and each assignmenmt in the other columns

A) If I hold the marks in one table (one per field i.e. unnormailised) and
the students in another, linked by student ID I cannot add new marks into
the table via a form as there are not yet any records in the marks table
that match student IDs in the student table.

B) If I nornalise and have the marks in a table such as MARKS(studentID,
AssignmentID, ClassID, Mark) then the only way I can show all the marks for
each student in a nice grid is using a crosstab query. This looks nice but
also does not allow adding new marks as the dataset is not updatable.

In Excel its easy, one row per student, one column for name, other columns
for marks - How can I duplicate this idea in Access without having to see
only one student at a time with a subform of all their assignments?

Howard

PS The only way I've found before is to have a temp table laid out like a
spreadsheet into which marks were entered ,then using code I extracted marks
and appended them one at at time to a normalised table. Surely there must
be a better way.

 

[Duane Hookom]

I recommend that you normalize. Either
-get used to entering grades in a continuous subform or
-append to a wider temp table like you are doing or
-create a grid-like unbound form that uses code to display and save values

 

[Vincent Johns]

This may involve a small amount of programming, but consider doing it
this way:

Hi,
I have the perenial student marks database problem which I'm trying to solve
for the third time in a simple way.

In a nutshell, one table of students, one of student-classes, one of class
assignments and one of students-class-assignment marks
one class = many students, one class = many assignments, one assignment one
mark.

The question is to do with ease of data entry. I'd like it to look like a
spreadsheet with names in col A and each assignmenmt in the other columns

Have you considered setting up an actual Excel spreadsheet for
collecting/displaying these data, and having Access suck in what it
needs from the spreadsheet for doing its reports?

In Excel, your table will contain several assignments on the same row.
In Access, a Datasheet uses a row for just one record, but the Excel
table would include info from several records (from the
[students_class_assignments] Table) in one row. VBA code could be used
to keep the two versions consistent.

[...]

In Excel its easy, one row per student, one column for name, other columns
for marks - How can I duplicate this idea in Access without having to see
only one student at a time with a subform of all their assignments?

Howard

PS The only way I've found before is to have a temp table laid out like a
spreadsheet into which marks were entered ,then using code I extracted marks
and appended them one at at time to a normalised table. Surely there must
be a better way.

Perhaps there is a better way, but this is how I'd do it, especially
since the meaning of a row in your Excel spreadsheet is so different
from that of a record in an Access Table. And your users would have the
advantage of being able to use some of the convenient data-entry
shortcuts that Excel provides (and Access doesn't).

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Tim Ferguson]

In Excel its easy, one row per student, one column for name, other
columns for marks - How can I duplicate this idea in Access without
having to see only one student at a time with a subform of all their
assignments?

I am with Vincent on this one: why not do the whole lot in Excel? With a
decent normalised design, say

ReceivedMarks (
StudentID long_integer FK references Students,
AssignmentCode NChar(5) FK references Assignments,
ScoredValue integer NOT NULL

primarykey (StudentID, AssignmentCode)
)


then you can use code in the Worksheet_Open event to read in the Students
and Assignments tables to fill in the column and row headings. Then read
in the ReceivedMarks table and fill in the appropriate cells.

To save the values, you could either catch the Worksheet_Change event and
write individual values back to the table, or wait for the user to close
down and catch the Worksheet_Close event. In the latter case, you'd want
a menu or command button to call an explicit Save. Then the code would
need to go through all the cells, and either DELETE a record if the cell
was empty, or INSERT or UPDATE it if it wasn't.

Not too hard to do. Remember that you can then use all Excel's layout
features like decent printing, data validation, sorting, immediate
calculation, and so on. The VBA and data access code (ADO or DAO
according to choice) in Excel is exactly the same as you would use in
Access.

Hope that helps


Tim F

 

[Vincent Johns]

Tim Ferguson wrote:

[...]

Not too hard to do. Remember that you can then use all Excel's layout
features like decent printing, data validation, sorting, immediate
calculation, and so on. The VBA and data access code (ADO or DAO
according to choice) in Excel is exactly the same as you would use in
Access.

Oooo... I forgot about printing! Once your system is working, if you
want really pretty reports, you might consider linking your database
(Excel or Access or a combination) to MS Word, using Word's "Mail Merge"
facility. That allows you to add much fancier graphics and layout,
while Excel and/or Access does the heavy database lifting.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Howard]

The data is in a spreadsheet at the moment. One sheet per course (many
classes per sheet).

If fact I was thinking that once I had it in a database instead I would link
a spreadsheet to it so that staff could see the data and make ad hoc
calculations using excel

Yes I did play around with formatting columns in access and although it can
be done it is a bit of a pain. Easier in Excel.

As for the link to word I knocked up a database to do a mailmerge from my
spreadsheet via access only last week! Doing the complex queries I needed
was easir in access than doing filters in excel.

Good food for thought Thank you. I'd rather normalise the data but it was
the ease of entry bit that was worrying me. What you all suggest seems a
good idea and fun to code!
Howard

 

[Howard]

One point though, This will be used on tablet PCs over a radio network with
the back end database on our server. Is there likely to be any locking
issues if two members of staff have the spreadsheet open at the same time?
Or maybe if I put a separate copy of the spreadsheet on each tablet but two
staff want to make changes at the same time?
Howard

This may involve a small amount of programming, but consider doing it this
way:

Hi,
I have the perenial student marks database problem which I'm trying to
solve for the third time in a simple way.

In a nutshell, one table of students, one of student-classes, one of
class assignments and one of students-class-assignment marks
one class = many students, one class = many assignments, one assignment
one mark.

The question is to do with ease of data entry. I'd like it to look like a
spreadsheet with names in col A and each assignmenmt in the other columns

Have you considered setting up an actual Excel spreadsheet for
collecting/displaying these data, and having Access suck in what it needs
from the spreadsheet for doing its reports?

In Excel, your table will contain several assignments on the same row. In
Access, a Datasheet uses a row for just one record, but the Excel table
would include info from several records (from the
[students_class_assignments] Table) in one row. VBA code could be used to
keep the two versions consistent.

[...]

In Excel its easy, one row per student, one column for name, other
columns for marks - How can I duplicate this idea in Access without
having to see only one student at a time with a subform of all their
assignments?

Howard

PS The only way I've found before is to have a temp table laid out like a
spreadsheet into which marks were entered ,then using code I extracted
marks and appended them one at at time to a normalised table. Surely
there must be a better way.

Perhaps there is a better way, but this is how I'd do it, especially since
the meaning of a row in your Excel spreadsheet is so different from that
of a record in an Access Table. And your users would have the advantage
of being able to use some of the convenient data-entry shortcuts that
Excel provides (and Access doesn't).

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Howard]

Sorry Tim, I thought this was easier than it seems to be. Can you show some
typical code to open an access database and read in some data (and perhaps
the reverse , putting it back. I've got some sample code to do with
northwind but its generating data type errors on lines lke Set Rs =
Db.OpenRecordset("Customers")

Howard

 

[Tim Ferguson]

Sorry Tim, I thought this was easier than it seems to be. Can you show
some typical code to open an access database and read in some data
(and perhaps the reverse , putting it back.

This is air code, but should be enough to give you the picture:

' database objects, early binding for simplicity, need to set
' a reference to DAO in the VB Editor
dim DBEng as new DAO.DBEngine
dim db as dao.database
dim rst as dao.recordset
dim jetSQL as string

' excel objects to navigate the worksheet
dim wks as Worksheet
dim wholeRange as range
dim currentCell as range

' find the database
set db = DBEng.OpenDatabase("h:\data.mdb", false, false)

' handle to current worksheet
set wks = activewindow.worksheet
' get the data area, not sure this is correct...
set wholeRange = wks.cells(1,1).activerange

' don't want the first row or first column
with wholeRange
set wholeRange = .range(.cells(2,2), _
.cells(.colums.count, .rows.count))
end with

' look at each data cell in turn
for each currentCell in wholeRange
' if it's empty, kill it
if currentCell.IsEmpty then
' create the command. Get the Course from the worksheet name,
' get the studentID from the top row, and the Assignment
' code from the first column
jetSQL = "DELETE FROM scores WHERE " &
"Course = """ & wks.Name & """ AND " & _
"StudentID = " & wks.cells(1, currentCell.Column).Value & _
" AND " & _
"AssgCode = """ & wks.Cells(currentCell.Row,1).Value & _
""";"

' run it , you might have to supply the dbFailOnError value
' rather than the name, but you can look it up in the
' object browser
db.Execute jetSQL, dao.dbfailonError

else ' there's a score to record
' create the insert command like above
jetSQL = "INSERT INTO scores " & _
"(Course, StudentID, AssgCode, Score) " & _
"VALUES (" & _
SQLString(wks.Name) & ", " & _
SQLNumber(wks.cells(1, currentCell.Column).Value) & ", " & _
SQLString(wks.Cells(currentCell.Row,1).Value) & ", " & _
SQLNumber(currentCell.Value) & ");"

' run it like above, with safeties on
On Error Resume Next
db.Execute jetSQL, dao.dbfailonerror
if Err.Number <> 0 Then

' if it failed, it's because the record already exists
jetSQL = "UPDATE scores SET " & _
"Score = " & SQLNumber(currentCell.Value) & " " & _
"WHERE " & _
"Course = """ & wks.Name & """ AND " & _
"StudentID = " & wks.cells(1, currentCell.Column).Value & _
" AND " & _
"AssgCode = """ & wks.Cells(currentCell.Row,1).Value & _
""";"

' do it
db.Execute jetSQL, dbFailOnError
end if
On error goto 0

end if

next currentCell



There is a huge number of optimisations and lots of error checking
required, but this should show you roughly what you are aiming for. It
looks a lot but it's really fewer than twenty lines of actual code. Like
all programming, it's just a matter of a clear algorighm and a clear
head.

Hope it helps


Tim F

 

[Howard]

Thanks Tim, that helps a lot. I can see the bits I need now.

Howard

 

[John Vinson]

One point though, This will be used on tablet PCs over a radio network with
the back end database on our server.

BIG RED WARNING:

Access frontend/backend should be run *only* over a fast, stable LAN.
A split database is *very* sensitive to noise on the connection - and
wireless LANs (and worse, WANs) are notoriously noisy. This is a
pretty much certain recipe for frequent corruption of your backend!

John W. Vinson[MVP]