Replies

In the appropriate report section (probably the report footer), place an unbound textbox with the following formula in the Control Source: =Count([your fieldname])

Have you tried printing the report to PDFWriter; that is as a PDF file? It can be sent as an email attachment and I have found it is the least frustrating way to retain Access report formatting. (Although I haven’t used a texbox BackColor, so post back if it doesn’t work)

The way that I do this is on the path of Patt’s suggestion. Having a table that holds each of the 50 reponses as different fields is going to be cumbersome, as well as not properly normalized. I have one table for the general Survey identifying data, a second table for Responses and a third table for Question information (QuestionID and the QuestionStatement). Their relationships are one to many. So the Response table has just 3 fields, SurveyID, QuestionID and the Response itself. You can calculate statistics from this table much more easily. I believe that I once posted a structure for this type of application on the forum. If you do a search on “Survey”, you will find it.

I remember it also – I saw it in a Logical Operations Training manual for Access. I looked through my manuals, but it must have been before v97, because I no longer have those older ones. None the less, Patt explained how it is done. That new field uses the Left function to get the first letter of the Last Name in the group header. Format it large and bold.

Is the Department Name in the Group Header? If so, in the Report design view, on the ToolBar, click on the Sorting and Grouping icon and Enter the Department Name in the first row of the Field/Expression column. The Sort Order is the second column. Click OK and the sort order will take effect.

It seems natural and correct to me because of the need to add records to the many side of one to many relationships. I understood that it may feel like a lot of work at the onset, but it seems perfectly reasonable to me – probably an indication of how many times I’ve done it.

The use of a Main Form and subforms to represent the one to many relationships, as others have suggested, is your best approach. I understand that this may feel like a lot of work, but in order to represent those relationships and be able to add data, I can’t think of a more straight-forward approach. Linking these tables together in a select query is one thing. You can “see” the data just fine, but being able to add records on the many side of a relationship is quite another. It will all display on one form in the end, as the subforms are linked to the main form. Why don’t you do a quick test and see if this accomplishes your goal.

I use it for statistical reports. If I calculate the average for a set of data, I may also need to calculate the standard deviation. The standard deviation indicates how closely the data points relate to the average.

This may be more than you want to know, but this reference explains it quite well.

When I used the following WHERE clause, all records for August 29th of any year were returned:
WHERE (((DatePart(“m”,[tblA].[ApprovalDate]))=(DatePart(“m”,Date()))) AND ((DatePart(“d”,[tblA].[ApprovalDate]))=(DatePart(“d”,Date())));
If that is what you wanted, you don’t need to address the year, but you do need to address the day along with the month in your clause.

Try the VB Round function. It returns a number rounded to a specified number of decimal places. The syntax is:
Round(expression [,numdecimalplaces])

You want to Set Warnings Off. Set it back to On at the end of your macro or you will not receive warnings after the macro completes.

The relationship between Project and Employee will be many to many, therefore you will need an intersection table between these. This intersection table will hold the key to the Project table, the key to the Employee table and other data specific to that Employee’s work on that Project, such as the time data for each “block” of work.

If a Project has many SubProjects, but a SubProject belongs to only one Project, the relationship between Project and SubProject is one to many. Hold the key to Project in the SubProject table.

If there is a place in which an Employee’s total time is held (let’s say on a weekly basis; as in 40 hours per week), you should be able to use queries against this design to get the information you want.

Is this enough to get you started?

It did not occur to me that you might be trying to save a value in a table that was the same table referenced by your ComboBox. If this is the case, please refer to Mark’s comment and post back. We can help you find a way to do this that doesn’t break normalization rules.

I’m not clear on the logic that makes this decision, but On the AfterUpdate Event of the ComboBox, place an If statement that reads something like:

If Me!ComboBoxName = choice1(the value that determines a .25 should be placed) Then
Me!TextboxName = .25
Else If Me!ComboBoxName = choice2 (the value that determines a 1should be placed) Then
Me!TextboxName = 1
.. other choices continue here
End If

If your TextBox is bound to the field in the table, the value will be stored.
If there are numerous choices, you may want to consider a Case statement instead.
Post back with a few more details if this isn’t helpful enough.

There are probably more graceful solutions, but on the OnClick Event of that button Count the number of records returned from your parameter query. If the Count = 0, give the user a message (use MsgBox) and do not open the form. When the Count >0, open the form as usual.