Help with creating queries and reports!

[oliverme]

I have a database I created for class registration for a small group. It is used to put registration information in there as well as pull queried reports to show attendance as well as if they registered or were walk-ins.

Here are the tables I created:

Language
Instructor
Session
Subsession (subdatasheet of Session)
Classes (holds all the data)

Here is what my table fields are in Classes:

ID
Language (linked to Language table)
Instructor (linked to Instructor table)
Folder Permissions
Session (linked to Session table)
Subsession(linked to subsession table)
Date
Day
Time
Attended (Yes/No)
Registered (Yes/No)
Walk-In (Yes/No)

I created the Walk-In field because it is needed for the reports I need to create.

I have created a query and can get it to pull by date range

Between [Forms]![test]![RegBegDt] And [Forms]![test]![RegEndDt]

but I can't get it to bring up the absolute value for each of the 3 YES/NO fields so that they are not negative values by typing in

SUM(Abs(FIELD))

This criteria does not work.

As well, I created a form where a user can input the date range and it opens the query, but how do I get the command button to just run the query and then automatically open the report? Is it something I can create as a macro?

The report needs to pull the data by session, subsession, and then date. I need the totals for Attended, Registered, and Walk-In for each session, and then a complete total for all sessions combined.

Does this make sense to anyone??? I am really new at this and trying to figure this out! I am definitely a novice! Please help?!?!

 

[rac2]

I think maybe Yes/No columns are Boolean values, True or False. As such they do not have absolute values. If you wish to see just those rows for Registered classes you would enter the value True in the Criteria: line under the Registered column in the Design view; or you would write this condition in SQL

...
WHERE (((Registered)=True));

If you wish to count the number of classes with Registered equal to True you could write this expression

SELECT SUM( IIf(Registered, 1, 0) ) AS "NumberedRegistered"
...

A general comment about designing databases. Usually a table contains descriptions of things and the name of the table shows us what kind of thing is described in that table. When I see a table named Classes, I think it might be describing some things like a class led by an instructor meeting in room 120A for 90 minutes starting at 10:10AM covering the subject Indo European Languages and Peoples. But it is difficult for me to see how that class could be Registered or Walk-in or Attendance. Possibly you need two tables, one to describe the Classes, and another to record Attendance. In the Attendance table you might have Number columns for Registered and Walk-in attendance.

 

[oliverme]

Thanks for your help, rac2! Each class a person can either be registered or a walk-in to attend the class. Should I still create 2 tables? I need to know the sum of attendees, plus if they are registered or walk-ins for each class.

 

[flyover789]

oliverme,

in my opinion, you are missing a table to store your attendees (persons) data. or is it just the classes table, where you enter a new record for each person, either attended or not / registered or not / walk-in or not?
If that is so, you can create a query on your classes table like this, to now how many persons have attended and have been registered and how many have not (equivalent for the attended and the walk-in property and any combination):

SELECT count(ID), attended, registered FROM classes GROUP BY  attended, registered;

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Shared Database_Systems and _Applications across all Business_Areas[/blue]

 

[oliverme]

The first 2 codes didn't work. The first doesn't like that expression at all. And, the second still brought up a negative value. The number is correct but it's still negative.

As well, I need the tally sums for each class for a particular date range. I do have another table that has the list of instructor names. And another table that has a list of the sessions (classes) that are offered.

Can I email my database to someone to take a look at it? Maybe I just completely did it wrong. There is already data in it to work with.

Thanks,
oliverme