reusing a query field

[k2tile]

Is it possible to SELECT AS into more than one field? I'm writing a large query where many of the output fields happen to the be calculated with the same formula.

Any idea if this can be done directly within the query?

 

[vbaInet]

I don't understand your question. Please explain.

 

[boblarson]

where many of the output fields happen to the be calculated with the same formula.

What formula? That sounds strange to have many fields calculated with the same formula. Or are you saying the formula would be the same but with different fields making up the values for the calculations?

And no, there is no simple way to do that.

 

[k2tile]

The values are literally exactly the same. This is for import into sales/inventory software. The software has several different fields for alternate product numbers (internal SKU, vendor code, product code), but we just want to use the same value for all these fields.

 

[boblarson]

Create a query with the single field calculated and any other fields necessary except the other calculated ones. Then bring that query into another query and then you can just use the same field as many times as necessary.

 

[the_net_2.0]

another thing you could do as well is write a one line function with arguments. Something to the effect of:

SELECT myformula([field1]) as sku, 
      myformula([field1]) as vendorcode,
      myformula([field1]) as prodcode, etc, etc...

and, as the call:

public function myformula(myinput as double) as string

     myformula = myinput * 12

end function

that is of course simplified, but it is another alternative to what already has been mentioned.

 

[vbaInet]

It would work but using a function in this case will cause the query to run twice (everytime it's called) because the function will be returning different results. Double execution.

 

[boblarson]

The function would run once for every field and for every record. My solution would be the most processor efficient.

 

[vbaInet]

That's the one Bob.

 

[the_net_2.0]

The function would run once for every field and for every record. My solution would be the most processor efficient.

Perhaps I didn't understand what was going on here. If that's the case, good points you two. Sorry about that! I'll quit while I'm ahead in this one...

 

[k2tile]

Thank you everyone for your help. I'm going to try writing a function. That seems like a really nice concise way of handling this.

 

[boblarson]

Thank you everyone for your help. I'm going to try writing a function. That seems like a really nice concise way of handling this.

My way is more efficient and would run faster.

 

[the_net_2.0]

My way is more efficient and would run faster.

k2,

Bob is right. either works, but in a technical sense, Access will like you much more with his way. good luck!