Converting attendance data for reporting

[Yoplumpy]

Hi All

I think I am out of my depth here - but desperately need to get reports created out of a database that captures dates and attendance marks.

Currently the table was 12 weeks so there is:

ID
12WeekProgramme.Week 1 Date
12WeekProgramme.Week 1 Attendance
12WeekProgramme.Week 2 Date
12WeekProgramme.Week 2 Attendance

And so on.

Marks are AA/UA or /


Is there a way I can export the dates/marks to a new table with only the 2 columns? Then I could use that to tally types of attendance within a specified date range?

Any assistance would be really, really appreciated!

Paul

 

[jzwp22]

Having fields such as week1date, week2date... are an example of a repeating group which indicates that your table is not normalized. For more on normalization check out this link. Basically the information for each week should be a record in a table not a field. What would happen if you had to track information for more than 12 weeks? You would have to redesign your table and all your forms, queries and reports!

To be able to help you further, we need to get a better understanding of what your application is intended to do. Could you please provide more details about your application?

 

[Galaxiom]

Use a Union query

SELECT [Week 1 Date] AS Datefield, [Week 1 Attendance] as Attendance FROM [12WeekProgramme]
UNION ALL
SELECT [Week 2 Date] AS Datefield, [Week 2 Attendance] as Attendance FROM [12WeekProgramme]
UNION ALL
etc

And yes, your data structure is inappropriate.

 

[Yoplumpy]

Hello - thanks for replying.

You are absolutely right about the normalization issue. I wasn't 100% sure how to achieve this so had to go with the horribly rigid structure. I understand the theory just not how to achieve it!

Basically its a database of young people who attend various session, education, mentoring etc.

There is a main "core" table that holds the person details and this is linked to the various attendance tables via subforms. All tables have an Primary key that matches the Primary key on the "core" table.

If you could provide any help on how to normalise this it would be great.

Paul

 

[jzwp22]

I'll give it a try based on what info you provided:

A table to hold the info about the people
tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName
-dteBirth (date of birth)
other fields

A table to hold the session info
tblSessions
-pkSessionID primary key, autonumber
-txtSessionName
-dteSession (date of the session)

Finally a table to hold the people attending a session
tblSessionPeople
-pkSessionPeopleID primary key, autonumber
-fkSessionID foreign key to tblSessions
-fkPeopleID foreign key to tblPeople

...attend various session, education, mentoring etc

Are these sessions, education(classes?) and mentoring events one-time events or are they like courses that meet for several days within a prescibed time period?

 

[Yoplumpy]

They are classes and they would attend a few times a week usually.

thinking about the potential for problems in the future what I may do is just create a separate attendance table that is viewed as a data sheet and then can just add the dates through the subform.

I can then report on the table - although can't seem to get a query to count the marks are the moment

 

[jzwp22]

If you don't have the proper table structure, you will constantly struggle with your queries and reports, so I would strongly recommend designing an appropriate table stucture.

I have helped some others on the forum with similar applications that may help; this thread may be of use. This one also.

 

[Yoplumpy]

Thanks very much for your help.

Paul

 

[jzwp22]

You're welcome.

If you decide to redesign your DB and run into questions, please do not hesitate to ask for help!