Generating a field in a Report (1 Viewer)

bwgreen · Jul 30, 2014

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 · Jul 30, 2014

The average temperature of the meal in each group?

bwgreen · Jul 30, 2014

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 · Jul 30, 2014

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:

Code:

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

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

bwgreen · Jul 30, 2014

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 · Jul 30, 2014

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

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

bwgreen · Jul 30, 2014

Yep - each MEAL is to be handled distinctly.

vbaInet · Jul 30, 2014

Alright.

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

Code:

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

bwgreen · Jul 30, 2014

Thanks - that did it!

vbaInet · Jul 30, 2014

No problem!

Generating a field in a Report (1 Viewer)

bwgreen

Registered User.

vbaInet

AWF VIP

bwgreen

Registered User.

vbaInet

AWF VIP

bwgreen

Registered User.

vbaInet

AWF VIP

bwgreen

Registered User.

vbaInet

AWF VIP

bwgreen

Registered User.

vbaInet

AWF VIP

Similar threads

Users who are viewing this thread