Help needed builing a simple Expression to calculate Wean Index

[GrasshopperKing]

I need an expression to take the AvgADA on the qryAvgADA/Year query to a new column on the qryWeanIndex query so that I can calculate a Wean Index which is ADA/AvgADA X 100. It needs to take that years AvgADA and match it to the Birth Year, you’ll notice 1996 has two births, so don’t get misled by those years that have identical ADA & AvgADA, I am only using a small sample of data but it will be used to calculate many births per year.

I have tried Iif statements, Switch statements, and all kinds of things. I am a NOOB and not a very bright one!



Be most grateful for any help.

[moke123]

wont ADA/AvgADA X 100 always = 100?

You may also want to change your field name from year to something else as year is a reserved word.

[GrasshopperKing]

Hello and thank you for the reply.

Yes & No. In this small data set most of them will have an Index of 100 but if you look at 1996 you will see that calculating their index gives you 94 and 105. This is why I mentioned that it would be slightly misleading because of the lack of births, I am expecting to have many hundreds of births a year.

I need a simple expression to match the years AvgADA with the birth year

I will certainly change the reserved word to something else.

Thanks

[June7]

Consider building report and use its Sorting & Grouping features with aggregate calcs.

[GrasshopperKing]

Hello and thank you for your reply,

I think a report would be useful but only later. I really need it to show up on the subform of each animal performance over its lifetime, 1 birth a year over 8 to 13+ years

Thanks

[June7]

To accomplish in form options:

1. An aggregate query for the annual average. Then join that query to the raw data by linking on the year fields. However, this will result in a non-editable dataset.

2. Use DAvg to calculate the annual average. Domain aggregate can cause slow performance but the dataset will be editable.