Report Design Question

[Stew]

I need to make a report for training stats. There are roughly 30 training fields that I have. I'm trying to determine the best way to generate this report. I need total required, total completed, and percentages. For completed I have a yes/no field set up, same with required. Trying to figure out if I can set up a report from a query perhaps, that allows the user to pick the events they want a report on. Any help in the design of this would be appreciated. I'm going to start work on it later tonight. If I can't come up with a solution I'll post what I have and see if anyone can help me work from there. Just trying to get the design idea around my head.

[HansV]

We already discussed the problem of letting the user select fields in an earlier thread - see Post=23236.

To calculate the number of records for which Completed = True, place a text box in the report footer with control source

=Abs(Sum([Completed]))

To calculate the percentage of records with Completed = True, place a text box in the report footer with control source

=Abs(Sum([Completed]))/Count(*)

and set the Format property of this text box to Percent and Decimal Places to the desired number (default is 2 decimal places). Similar for Required.

[Stew]

I don't see how this will be possible. If I have it make the report from a query I have the user build as we did in that post, how can I make the totals come up at the bottom. Using that code =Abs(Sum([Completed])) I must know which field to check Completed against. I don't have a training table set up with 5 fields (ID, Name, Req, Complete, Date) like I'm starting to think I should. I have my training hard coded in my main table with thier own unique fields. I can go back and set it up like that if it is not possible the way I have it. Also I tried that code and got a #error. I just made an unbound textbox and put that code right in.

[HansV]

I just used the name Completed as an example, you should use the name of the relevant field, and that field should be in the record source of the report.

[Stew]

Attached is my trimmed down database. In tblMain all my training items are stored (not all have required, as they are required by all). Every attempt I have made to make a training report with sums and percentages have failed miserably. Is it not possible the way I have it set up? I tried to run a report using the code you gave me in the footer for any of the completed fields (such has Saar), however I get a #name error when I do that. Could you take a look at it, possible set it up for one training event so I can use it as a template to set up the other 30+. Due to the size I think making a form to view the stats may be better due to the amount of events I have.

[HansV]

I can't look at it right now, I'll do so later today.

[Stew]

Thanks alot whenever you get a chance, I've appreciated your help more then you could understand on this project I've been assigned to do.

[HansV]

The current table design is unwieldy. You should create a separate table, with

SSN to link to tblMain
TrainingID to link to a table that lists the available training items.

Plus whatever extra info you need, for example DateCompleted, Score, ...

[Stew]

I was hoping you wouldn't say that. I didn't want to have to reformat that at the last minute. So I need two more tables, one with a list of all the training events and one storing the records based on the SSN. If I reformat the table to look like this, can I create records automatically when a SSN is put in. I don't want to give use the power to add and delete records for the training, just edit. So default everything would be required and I can have the section change that.

[HansV]

See the sample database attached to Post=8097. It's about a questionnaire, but the idea is the same - the code behind the OK button on frmMain creates a record for each question in the questionnaire.

[Stew]

I think I understand how that works, I'll give it a shot. Time to start reformatting a good chunk of my database 24 hours before due date. Think I can make this work quick though. Thanks alot, I'll post how this turns out in a couple of hours.

[Stew]

Attached is what I have so far. Building the rts and SQL string is throwing for a loop. Don't quite get it. I'm running a form (frmS3Add) to create a record for each training event for the person. The training events are listed in tblTrainingEvents, and the records are stored in tblTraining. This code is a tad above my head and need it explained in greater depth. I am getting different errors no matter how I work this. Could you take a look at it and point me in the right direct here. Thanks again.

[HansV]

Some of the fields that you have place in tblTrainingEvents belong in tblTraining, as far as I can tell. EventDate, EventCompleted and EventScore are not properties of a generic training, but of a specific training by a specific marine. I don't know about EventClass since I don't know what it means.

In the SQL string, the first part "INSERT INTO tblTraining (TrainingID, SSN) " specifies the table into which you will insert records, and the fields you are going to fill. tblTraining doesn't have a field SSN, it is - for better or for worse - named Marine here. So it should be "INSERT INTO tblTraining (TrainingID, Marine) ".

The second part, "SELECT " & Me.cmbMarine & " ID FROM tblTrainingEvents" specifies the values you are going to insert. You must specify them in the same order as in the INSERT INTO part, i.e. first the value for TrainingID and then the value for Marine. They must be separated by a comma. So this part should be "SELECT ID, " & Me.cmbMarine & " FROM tblTrainingEvents".

The entire string is now

Code: Select all

    strSql = "INSERT INTO tblTraining (TrainingID, Marine) " & _
        "SELECT ID, " & Me.cmbMarine & " FROM tblTrainingEvents"

and this will insert the records you need.

[Stew]

Once I changed the strSql to your code I get this error:

Index or primary key cannot contain a Null value. The primary key for tblTraining is an autonumber.

Figured it out, I accidently changed my primary key off autonumber so it was coming up with the error.

[HansV]

Oops, yes, I should have mentioned that. In the database that you attached, the ID field is not an AutoNumber field but a text field, so it does not get populated automatically.
Open tblTraining in design view.
Delete the ID field (yes, you're removing the primary key).
Select the TrainingID AND Marine fields.
Click the Primary Key button in the Tools section of the Design tab of the ribbon.
You have now made the combination of TrainingID and Marine the primary key.
Close and save the table design.
The code should now work OK.

[Stew]

Would it be better if I leave the primary key an autonumber, or should I switch it to what you said Hans? Or will it have very little impact on my end product either way?

[HansV]

You can also change the ID field to AutoNumber and leave it as the primary key. The difference is that this will allow you to enter the same training - marine combination more than once into tblTraining, whereas if you make the combination of those two fields the primary key, duplicate combinations wouldn't be allowed.