Generating a field in a Report

[bwgreen]

Hi!

I apologize in advance for being somewhat vague about the contents of the database - security requirements where I am dictate it!

I have a database with a bunch of related tables. One holds information on (let's say) types of food. A field in that table tells whether the food is HOT or COLD. Foods are grouped by MEALS in a related table - the PK of the foods table is stored as an field in the MEALS table. What people here are looking for is a report that lists all the foods in each MEAL, the temperature of the food, and whether the meal is HOT, COLD, or MIXED - foods are both HOT and COLD. I've got a report that lists all the FOODs, grouped by MEALs - it's just the overall temperature of the MEAL that I am missing right now.

Any idea if this can be done in Access, and if it can how to do it?

Thanks!

 

[vbaInet]

The average temperature of the meal in each group?

 

[bwgreen]

Not specifically the average, but one of three options -

--> if all the FOODs are HOT, then HOT;
--> if all the FOODs are COLD, then COLD;
--> if there are both HOT and COLD FOODs, then MIXED

Temperature is a text field.
Hope this helps!

 

[vbaInet]

I suppose "temperature" is a bit misleading

Your first step would be to create a count of just the Cold foods. Call this textbox txtColdCount:

=Count(IIF([Temperature]="Cold", 1, Null))

Get this working and we'll move to the next step.

 

[bwgreen]

OK, I have the COUNT working in the report section footer (not the overall report footer - that shows the total number of COLD foods, not just the ones in each MEAL!).

 

[vbaInet]

I don't understand what you wrote in brackets. Can you please rephrase.

Are you talking about the Meal's Group Footer section?

 

[bwgreen]

Yep - each MEAL is to be handled distinctly.

 

[vbaInet]

Alright.

To get the overall "temperature" you need to create another textbox and put this in the Control Source:

=Switch(txtColdCount=0, "Hot", txtColdCount=Count("*"), "Cold", txtColdCount<>0, "Mixed")

 

[bwgreen]

Thanks - that did it!

 

[vbaInet]

No problem!