Lookup data across tables using next-highest value (1 Viewer)

littlespoon · Oct 7, 2015

Hi,

I hope you guys can help me with this. (I also hope I can explain it clearly!)

So. I have a master database (TABLE 1), and a database that I need to check for accuracy (TABLE 2).

One of the things I need to check is price, but Table 2 uses price tiers, whereby the prices from Table 1 are 'rounded up' to the next value in the price tier list and then converted into the corresponding tier.

I want to write a query that takes the price of each record, compares it to the tier lookup, increases it to the next price in the lookup table if necessary, and then returns the corresponding tier.

For example, if a record is priced at £1.75, I need it to check the tier lookup, see that the next tier is £1.99, and return the corresponding tier.

So far I have been able to show each record's price tier from Table 2, pull the price that that corresponds to, and subtract that from the Table 1 price. Where do I go from here? I think I should use DLookup, or DMin, but I don't understand how those functions work.

Thank you!

arnelgp · Oct 7, 2015

select A.[price], (Select Top 1 [price] From (SELECT [price] FROM Table2 ORDER BY [price] ASC) Where [price] > A.[price]) As [Next Tier] From Table1 As A

littlespoon · Oct 7, 2015

Just to be annoying, are you able to show me how to do that in Design view?

Oh -- and I'm using Access 2013.

arnelgp · Oct 7, 2015

what are the real names of your tables (table1 and table2) and the real field name for price on both table, which other field do you want to include in your query and which table does this belong?

littlespoon · Oct 7, 2015

Table 1 is called Master, and the price is called 'Price'.
Table 2 is called Pricing. The price is called 'list price' and the tier is called 'price tier'.
The lookup table is called Tier Lookup, and the field I want to include which corresponds to [Master].[Price] is called 'tier'.

arnelgp · Oct 7, 2015

correct me if im doing the wrong steps:
1. get price (a) from master table
2. find (a) in pricing table and return price tier (b)
3. find (b) in your lookup table and finally return value from 'tier' field.

what are the fields in your lookup table again.

littlespoon · Oct 7, 2015

The steps are:
1) Get price (a) from master table
2) Look up price (a) in lookup table (which is called Tier Lookup. Price field is called 'GBP' and tier is called 'Tier'). Round up if necessary.
3) Pull corresponding tier from Tier Lookup.

You can ignore Pricing table -- I can pull in the tiers from there and do a 'where' clause for where they aren't identical.

arnelgp · Oct 7, 2015

SELECT A.[price], (Select Top 1 [tier] From (SELECT [tier] FROM [Tier Lookup]ORDER BY [price] ASC) Where [GBP] >= A.[price]) As [Next Tier] From MasterA;

littlespoon · Oct 7, 2015

I tried to do that but Access gave an error with the "A.[price]" and "MasterA" parts -- why are the 'A's there?

And there's no Group By in that code -- surely the table it generates will be empty?

If you can describe how to do it using Design View I'll be incredibly grateful!

arnelgp · Oct 8, 2015

it should be:
Master As A

littlespoon · Oct 8, 2015

Hi,

Thank you -- but it's still not working!

Access asks me to enter parameter value for 'price' and for 'gbp'. I've tried changing the code you wrote so that it says '[Master].[price]' rather than just 'price', but that doesn't help.

Any ideas? Thank you!

littlespoon · Oct 8, 2015

And do I do any kind of join between the tables?

arnelgp · Oct 8, 2015

no need for Joins since you are using sub-query.
if it is not to much for you to upload your db, so i may take a look, if its okay.

littlespoon · Oct 8, 2015

Hi,

Please see attached!!

arnelgp · Oct 8, 2015

you are using text field on your numeric values (price, gbp, tier), please try:

littlespoon · Oct 8, 2015

That worked!! Thank you so much

Lookup data across tables using next-highest value (1 Viewer)

littlespoon

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

littlespoon

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

littlespoon

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

littlespoon

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

littlespoon

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

littlespoon

Registered User.

littlespoon

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

littlespoon

Registered User.

Attachments

arnelgp

..forever waiting... waiting for jellybean!

Attachments

littlespoon

Registered User.

Similar threads

Users who are viewing this thread