Can you calculate w/in a crosstab query

[goslincm]

I am running a crosstab query that looks like:

[OfficeID] [Year] [Population]
GroupBy GroupBy Sum
Row Heading ColumnHeading Value

What I then need to do is compute differences in the populations from one year to the next and come up with a score of 1 is there is a decrease in population or a score of 0 if there is a population increase.

Can this be done in a crosstab or am I doing about it incorrect?

 

[dhookom]

You can do this in a query, form, or report based on a crosstab query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[goslincm]

Okay,I created a form based on this crosstab query butI'm stuck again. I created a new control to subtract one year from another to get the difference that I need. However where I'm stuck is in getting that different in that new control to then show either a 1 or a 0 based on it being an increase or decrease in the population.

I truly appreciate your patience out here I'm a new access person who is struggling but trying desperately.

 

[dhookom]

You can set a control source to something like:
=IIf([2006]>[2005],1,0)


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[goslincm]

Thanks, that worked great for me.