loop?

[SB]

Hopefully someone can help me with this. I'm new to Access and am having trouble.
I have data in a table for a number of cities. For instance;

Montreal 52
Ottawa 71
Toronto 46
Winnipeg 33
Vancouver 60

In my query I need to perform an operation on all the data and then divide each record by the base city value, in this case Toronto. Is their a simple way to combine the two operations? Perhaps some looping macro or an array function? I have to do this many times so eliminating a step would be much better.

Thanks

 

[Philem]

Hullo. This is rather simple, and can be done in the query. Depending on the operation you need to perform, simply have a new field, with a formula as such:

 NEWFIELD: [tblYourTable].[CityCode]/[operation result]

That should get you rolling.

HTH

P

 

[SB]

You had suggested;

NEWFIELD: [tblYourTable].[CityCode]/[operation result]

where the "[tblYourTable].[CityCode]" would be the initial mathematical operation and as I understand you are suggesting to enter the actual value to be divided by in "[operation result]".

This obviously works fine. However, when the intial data is updated the value calculated from the first operation will change and so must the "[operation result]". I need some sort of way to reference that calculated value without creating a circular reference. Or else I will have to alter the values manually every time I update the info.

Thanks though, any other ideas?

 

[Russell Hauf]

Your last post is quite different from your first. Perhaps you could go into more detail about what you want to do? When you say,

However, when the intial data is updated the value calculated from the first operation will change and so must the "[operation result]".

It gets confusing. Perhaps show us what you want your end result to look like, and describe the "changes" you talk about (I do understand that you are trying to work off of numbers that are being calculated from your fields, but this could mean just about anything).

Thank you,

Russell

 

[SydneyGeek]

If you always want to divide by the Toronto value, you can use a DLOOKUP function in the query formula to grab Toronto's current value. If Toronto changes, the divisor will change too.
Check the help files for DLOOKUP syntax.

If your base city won't always be Toronto, it gets a bit trickier -- please clarify if that is the case.

Hope that helps.

 

[SB]

My data table looks somehting like this;

Table 1 Column 1

• City Name
  Montreal
  Ottawa
  Toronto
  Winnipeg
  Vancouver

Table 1 Column 2

• Costs
  52
  71
  46
  33
  60

Table 1 Column 3

• Inflation
  1.03
  0.98
  1.22
  1.11
  1.10

1) I need to multiply the costs by the inflation values to give me a new cost.
2) Then I need to search throught those results and find the Toronto result.
3) Then I need to divide all results by the Toronto result to give an indice where everything is related to the Toronto cost;

Query 1 Column 1

• City Name
  Montreal
  Ottawa
  Toronto
  Winnipeg
  Vancouver

Query 1 Coulmun 2

• Cost indices
  ?
  ?
  1.00
  ?
  ?

I would like to do this all in one step. I don't know how to do this in one step because the Toronto (or whatever base city) result is not available until the entire first calculation is complete.

 

[Russell Hauf]

Here's one way:

Start a new query - select your table, and the 3 fields that you provided in your example. Then in a new column, put something like this:

• Toronto: DLookUp("[Costs]","tbl081103","[City Name] = 'Toronto'")*DLookUp("[Inflation]","tbl081103","[City Name] = 'Toronto'")

Then in the next column, put something like this:

• Final: ([Costs]*[Inflation])/[Toronto]

Note that my table name is tbl081103 - change this to your table name. Also, you can eliminate the "Toronto" column and just use the DLOOKUP's in the "Final" (Cost Index) column.

HTH,

Russell