Get value for field from another query (1 Viewer)

[peng21]

Hello,

It might be a simple question but I don't know how to solve this:

First:
I've created a simple query which gives me the result for a given user input "p".

SELECT TOP 1 val1
FROM tblPerson
WHERE( (val2)<=[p] )
ORDER BY val2 DESC;

Result looks like:

...|..val1...
...| 12345

Now I've a second query.
I would like to get the value for discount from the query above by "putting in p"

Field.......level......... discount
Table.......adbCases...... qryDiscountGet
Total.......expression.....Group By
Criteria...................[p]=[level]

Can anyone help me please how to solve this?

Thanks

 

[pr2-eugin]

Hello peng21, Welcome to AWF

Show a bit more sample data, it is not clear from your description what you want to do.

 

[peng21]

Ok, here we go

Sample data of tblDiscount



Sample data of tblAdbCases



Running (qryDiscountGet)

SELECT TOP 1 discount
FROM tblDiscount
WHERE( (level)<=[p] )
ORDER BY levelDESC;

Should return for [p]=7 "discount = 8.25 USD"


Now I would like to create a query:
"Show me all entries and calculate the discount based on the level"

Access Design View for my query

Field.......level......... discount
Table.......adbCases...... qryDiscountGet
Total.......expression.....Group By

I hope it's now more clear

Thanks