How do I make a custom field in a Query?

[Leopardfist]

I have a query in my database, and I have recently added new possible entries for area. My report tells me how many male chickens vs. how many female chickens I have. Now with the new entries for area, I want my breakdown to EXCLUDE any chickens that live in a specific area.

For instance:

Pen A has 15 males & 15 females.
Pen B has 12 Males & 13 Females.
Pen C has 50 males & 50 females.

Pen C is the recent addition...

Right now my report shows: 77 Males & 78 Females...

I WANT it to EXCLUDE Pen C, so it would report: 27 Males & 28 Females.

Can anyone tell me how best to do this? I figured I would add a custom field to the query that the criteria for is "Pen = C" and then on my report I would change the data source from: =Count([Chicken Query]![Male] --- and make it =Count([Chicken Query]![Male] - Count([Pen="C"]) --- or something like that.

Can anyone help me? And if so could you help with the proper code for the second part that I am adding new? I know the Count([Pen="C"]) isn't right, but until I know exactly how to add a custom field to the current query, with the critieria of Pen="C", I don't know how to write the -Count([NewField]) code, if that would even work.

Thanks!

 

[neileg]

Nope, not that way.

Assuming you have a field called [Pen] which shows A, B or C, then add a criterion to [Pen] of Not "C". This will exclude any records where then pen is C so your totals will only include A and B.

 

[Leopardfist]

Nope, not that way.

Assuming you have a field called [Pen] which shows A, B or C, then add a criterion to [Pen] of Not "C". This will exclude any records where then pen is C so your totals will only include A and B.

Awesome! That was SO much easier than what I was trying to cook up, and it works perfectly!

Thank You Neil!