A DB-Design question/problem

[MarionD]

Hi there,

I really need a bit of advice on design. I have faced this problem in various Databases and although I have always made a “workaround” – I’m sure there must be better ways. Although I have no immediate problem I have to get this right in the future. I hope someone has a bit of time to answer!

This is the general problem:

Part of my DB is an “Attendance Register – direct to Accounting”
I have a table with possible Attendence codes (A=Absent; P=Present, E=Excused etc..)
I have a table of Students.
I want to be able to capture the Data for one month in a cross tab form with the Student name in a row and the days of the month as columns.

I’m sure the right way is to have multiple records with Student ID, Date and Attendence Code in an AttendenceTBL but then it’s not possible to create kind of input form I need (?).

Another way is to have a record for each Month with 31 Fields (Days). This way it’s easy to create the form but difficult to extract the Data in the way I need it for accounting purposes.
(P Pays full fee, Absent without being excused 75%, Excused 50 %)
e.g. In one month a student could pay 15 * Full fee + 10 * 75% + 2 * 50%

I would really appreciate it someone could point me in the right direction!
Thanks
Marion

 

[RichMorrison]

Marion,

This is an interesting problem. I only wish I had a good solution.

I would go for option #2; a table with 31 code fields. You could make a module to roll up the charges at the end of the month. The module would loop through the table and for each record loop through the attendence code fields, accumulate a total charge, and then update some total field in the record.

Not elegant but should work. I hope someone else will post a better idea.

RichM

 

[MarionD]

Thanks for the reply Rich. Thats the way I'm doing it at the moment but as you say - not elegant. I thought there must be a better way!

The problem that I encounter is this:

At the moment I have 5 different attendance codes. I loop through the records - through the fields an update a total field (of which I have 5 in the Table - Total Absent without leave, total Present, total excused etc...) Now if they add a code to the Codes Table - I have to add a field to the Attendance Table to be able to store the total Information. This is naturally a pain!

Thanks for the time taken . I really appreciate it!

Marion

 

[MarionD]

Thanks Pat , I was hoping to hear from you!

I'm afraid my client is adamant about the way the data capture is to be made! I'll try the 7 sub forms and when I have it working perfectly, I'll aproach them with it and a number of "nice looking" reports to strengthen my case!

Until then they will have to continue as at present.

Thanks again for the time

Marion

 

[RichMorrison]

Here's one more idea (?)

Create an un-normalized table for data entry/update. This is the one with 31 attendence code fields.

Write a function to "unload" each row into a normalized structure for reporting. I assume this function would be run at the end of the month. Use the normalized table for reporting.

HTH,
RichM

 

[The_Doc_Man]

Sometimes people tell me I tend to state the obvious. But perhaps it will help for me to do so. Because by stating the obvious it might help to clarify yours (or someone else's) thinking enough for a good solution to pop up.

One problem is that you are describing this as "like a cross-tab." It is not. When you wish to show student name on one axis and day of the month on another axis, with an attendance code in the intersection, this is NOT a cross-tab. It is a detail form. Actually, it is a spreadsheet. This is an important conceptual distinction.

You don't say how comfortable you are with VBA, so I hesitate to suggest it as the first option. Yet the idea of creating a spreadsheet like an Excel application, using that as your input form, has its merits. Also you could use the spreadsheet as your primary output detail report. (Probably would print pretty will in landscape mode if you kept the columns as narrow as possible and used a small font.)

Perhaps if Excel is also available to you, you could try this:

Never mind Access. Enter the raw data to Excel. Make the first row a header with field names. Make the first column the student name. Then make each remaining column something like "D01", "D02" etc. Make a new spreadsheet each month. This is such a natural amount of data that no one should have a problem with it conceptually.

When you have a month worth of data to work on, import the spreadsheet to a table or just (temporarily) link Access to the spreadsheet. (I vote for the import because it is easier to control the name of the table for subsequent queries, but there is definitely more than one way to skin this cat.)

Make a detail table normalized exactly as you suggested, with name, date, and code as three columns. This is where your REAL dataset will be kept.

Make another table in which you keep some monthly things. (Maybe only ONE monthly thing, but maybe not.) The most important aspect of this table is a column in which you store the date of the last day for which you have completely processed your official monthly data. This column gets updated or a new row gets appended once per month.

For example, if you are now in July processing the complete June data, the last day in this oddball table is 31-May of whatever year. When you finish processing June data, this table would be updated to indicate the last process-complete date is now 30-Jun of the current year.

OK, now you have a way for Access to see the datasheet and a place to put the data. You now need to run 31 queries, one at a time. A macro will probably do nicely to drive it all, and you can build the first query by hand. But then cut-and-paste can be used to make the other 30 queries.

Each query will be EXACTLY the same except for the column and day of the month that is being processed. Append a record for each row in the (imported) datasheet table where the code in the current column isn't blank. Each query focuses on a separate column. (Might need the NZ function here to prevent null issues from rearing their ugly head. Excel will be HAPPY to pass you nulls just when you don't want them.)

Now, the question is, WHAT do you append?

In the queries, you append the person's name, their attendance code for the day, and this formula:

DateAdd( "d", n, DMax("[LastDate]","OddballTable"))

Where n = 1 if column is Day01, 2 if Day02, etc. One query for each day. The way that works is that the [LastDate] is supposed to be the day that ends the last month processed. So one day later than that is the date of the first day of the month you are processing now.

In SQL, this query for the first day of the month might look like

INSERT INTO [AttendTable]
( [StudentName], [AttendCode],[AttendDate])
SELECT [StName], [D01], DateAdd("d", 1, DMax("[LastDate]","OddballTable"))
FROM [ImportedSheet]
WHERE Len(Nz([AttendCode],"")) > 0;

Then [D02] and DateAdd with 2 for the second day, etc. etc. etc.

Now, I'm sure there are ways to further automate this data gathering process, but if you only do it once a month, there is a law of diminishing returns here. It might be enough to just run the append macros.

You could really get kinky and make four macros, one for months with 28 days, one for leap-year months with 29 days, one for 30 days, and one for 31 days. Then just run the right macro for the month you are processing. Manually update that oddball table's last date field when you are done.

As long as you are thinking inside the Access box, this problem is ugly. But if you step out of Access and think into Excel, it is a perfectly good (and perfectly integrable) Office product that fits a part of your customer's requirement. So use it if you have it.

 

[MarionD]

Wow! Thanks Rich, Doc-Man

I think it's amazing that while I'm in Germany (just back from an hour long bike ride) my problem is being "researched" all over the world!

I have printed this all out and am going to read it in Bed.
I am pretty efficient with Visual Basic, not so clued up in Excel but always willing to learn!

I knew I should be keeping the Table "normalised" but creating the form turned into a nightmare of sub forms etc... I can't wait to study these answers!

Goodnight Guys!

Marion

 

[rpadams]

Make a clickable calendar

I have developed a system that records the attendance for 600 students per month. The underlying table has the following fields:

StudentID
DateAbsence
TypeAbsence
ChargeAbsence

On my form I have a combobox to get the student number and an option box to click on the absence type (which by default is usually just "A" unless we know differently) and another option box for the charge (which by default is usually "1" - ie full day.)

I then open up a calendar Active/X, have the user select the TypeAbsence (if not default) and charge (if not default) and then click on the Date for DateAbsence. The OnClick event populates my table. The operator just clicks away for all the absence dates. I also have a control to change subsequent Type and Charge but it is surprising how little use it gets. Operators usually do all the A-1 first and then go back and fill in the non-standard absence later. Data entry is surprisingly fast (more time to describe than actually perform) and the normalized table is very easy to work with. It is straightforward to write reports with summing and grouping depending on your needs. Having 7 or 31 fields/month seems very awkward to me.

I put in a "dummy" student who is absent each day of the month to get my date headings for the crosstab queries. I can give you more detail but because the attendance piece is only a small part of a much larger school wide application, I can't send the actual program. For a special report, I also send the table to Excel and do manual pivottables to get the level of subdivision. I use Access97 but this is probably not necessary with 2000+

 

[MarionD]

Pat,

Unfortuneately not by the hour. I get paid a fixed amount for each update according to a maintenance contract I have with the customer. It's not specified clearly in the contract if I HAVE to include changes the customer suggests, but I try to keep my customers happy! This is not really a change as the system is running the way I described above but because the customer would like extra reports/analysis I decided I must try and solve this issue once and for all.

RPAdams, Thanks for the reply - your system sounds super. But as I said my customer insists on keeping the data entry as is. "Spreadsheet View". I actually populate the table automatically when he selects the month and year. It's a little involved as a Student does not necessarily have to attend each day. I have a Table with fields STudent ID . Monday,Tuesday,Wednesday,Thursday,Friday. When the Student is originally entered, I click the days he is supposed to attend. Then depending on which day the 1st of the month is I populate the table with "P"(present) on the days the Student should be there. This way, the clerk only has to change the days when the Student is not there (to whatever code) In my Table of absence codes I have a field which holds payment %. If a student is not absent at all - no changes have to be made and I can bill the Student.

I like Rich's idea of "unrolling" the Data into a normalised Table and I'll try this first.

Thanks again ALL for the time taken to answer - I know it's valuble!

Marion