Here's a question i've been wondering about for some time. i can work around it, but it isn't very efficient.
Say that i want to calculate a field, or just rename one, and then use it in a further column in a calculated field in Design View. For example, i have a table that lists the number of adults by city, and i have another table that lists the total number of people of all ages by city. (Don't get me started about how it doesn't require multiple tables; i didn't design the setup and it cannot be modified). In each table the field with the population is called "VALUE". i want to combine the two tables into one query and calculate the percentage of adults in the population.
Normally i would first rename the VALUE in the "Adults" table to "Adults: VALUE", and renamed the VALUE field in the "Total Population" table to "Total: VALUE". This will make sense and be better formatted for viewers.
Next i want to create a calculated field that is "Percentage Adults: [Adults]/[Total]*100". But when running the query, i would be prompted for both [Adults] and [Total]. If i save the query before running it, sometimes it will run without prompting me, but sometimes it still doesn't work. Of course i can simply calculate it based on "[Adults].[VALUE]/[Total Population].[VALUE]*100" and it works, but if [Adults] or [Total] are calculated fields (say there's an IIf statement that calculates the number of adults based on their age group from a different column), i have to copy the entire calculation into the other percentage calculation column, and Access must do the calculation twice, which may make things run slowly.
Why does it do this, and what is the better way to solve it without the clumsy work-around above?
Say that i want to calculate a field, or just rename one, and then use it in a further column in a calculated field in Design View. For example, i have a table that lists the number of adults by city, and i have another table that lists the total number of people of all ages by city. (Don't get me started about how it doesn't require multiple tables; i didn't design the setup and it cannot be modified). In each table the field with the population is called "VALUE". i want to combine the two tables into one query and calculate the percentage of adults in the population.
Normally i would first rename the VALUE in the "Adults" table to "Adults: VALUE", and renamed the VALUE field in the "Total Population" table to "Total: VALUE". This will make sense and be better formatted for viewers.
Next i want to create a calculated field that is "Percentage Adults: [Adults]/[Total]*100". But when running the query, i would be prompted for both [Adults] and [Total]. If i save the query before running it, sometimes it will run without prompting me, but sometimes it still doesn't work. Of course i can simply calculate it based on "[Adults].[VALUE]/[Total Population].[VALUE]*100" and it works, but if [Adults] or [Total] are calculated fields (say there's an IIf statement that calculates the number of adults based on their age group from a different column), i have to copy the entire calculation into the other percentage calculation column, and Access must do the calculation twice, which may make things run slowly.
Why does it do this, and what is the better way to solve it without the clumsy work-around above?
