469,307 Members | 2,181 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,307 developers. It's quick & easy.

Multi-table Value Query Help!!

natalie99
Hi All :)

I am trying to stipulate rules for the return of a pricing value in Access.

I know this should be very easy, I simply cannot seem to make my expressions work!

The two tables have the following (ignoring all irrelevant fields)

TABLE 1

Minimum Bandwidth, eg. 0 , 40.01 , 80.01
Maximum Bandwidth, eg. 40 , 80 , 138
End Price, eg. 150.26 , 327.18 , 459.87

TABLE 2

Bandwidth 26.4 , 48 , 64.1 , etc


I want to place an expression in the query stating that the end price for a record is defined by the bandwidth, ie.

Minimum Bandwith<=Bandwidth and Maximum Bandwidth>Bandwidth

then display the End Price

How do I type this in the design view so that the value of field Bandwidth in TABLE 2 is used, and the expression does not interperet it as the word bandwidth, which is not an integer!

In the criteria for field Minimum Bandwidth:

<="Bandwidth"

Thanks for any help with my most likely simple problem :)

Natalie
Feb 29 '08 #1
4 1286
Stewart Ross
2,545 Expert Mod 2GB
...How do I type this in the design view so that the value of field Bandwidth in TABLE 2 is used, and the expression does not interperet it as the word bandwidth, which is not an integer!

In the criteria for field Minimum Bandwidth:

<="Bandwidth"
...
Hi Natalie. Assuming that you have put Table 2 into your query's design view you refer to the field within brackets:
Expand|Select|Wrap|Line Numbers
  1.  <=[Bandwidth]
-Stewart
Feb 29 '08 #2
Assuming that you have put Table 2 into your query's design view you refer to the field within brackets:

Code: (text)
<=[Bandwidth]


Hi

Thanks for the reply!

I have tried this and it says

'Data Type Mismatch in Criteria Expression

However, both fields are set to Number in the Table 1 and Table 2 design views.

Also, the query design view automatically changes the criteria from:



<=(Bandwidth)

to

<=("Bandwidth")

Thanks!
Feb 29 '08 #3
right, sqaure brackets, duh, SORRY! and thanks for the help!
Feb 29 '08 #4
Stewart Ross
2,545 Expert Mod 2GB
right, sqaure brackets, duh, SORRY! and thanks for the help!
You're very welcome, Natalie. I'm sure you'll find it hard to forget which kind of bracket to use from now on too!!

-Stewart
Feb 29 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Xenophobe | last post: by
reply views Thread by kernal | last post: by
1 post views Thread by Hought, Todd | last post: by
1 post views Thread by Wayne | last post: by
5 posts views Thread by Samik2003 | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.