Lookup Query Criteria vs Return Value

[mackc557]

I'm trying to create a Lookup Query for a tax percentage. My Table contains a AutoNum for the purpose of order, a Number named StoreID, and a Number named TaxRate. There are other things, but they aren't relevant to this. I'm trying to get the most recently stored TaxRate (using the AutoNum) that the StoreID had. For example: Store1 January rate is 0.875, Store1 February rate is 0.88, Store1 March rate is 0.7, when entering Store1 April the box should show 0.7 since it's the most recent. Any ideas on how to set up a query for this? I really don't want to use SQL.

 

[Joe4]

You should really NOT use the AutoNum field in any sort of calculations. The sole purpose of an AutoNum field is to give a record a unique identifier. Order cannot be guaranteed with it.
It would be better to work off of your Store ID field, and use some sort of date field so each record can be ordered properly.
Then you could do an Aggregate Query, selecting the Max Date field for each store, and then link that query back to your original table (based on BOTH the Store ID and Date fields) to return the Tax Rate for that date.

By the way, when you use the Query Builder, you actually are creating SQL code (behind the scenes).

 

[mackc557]

By the way, when you use the Query Builder, you actually are creating SQL code (behind the scenes).

I am aware of this, I just would prefer not to do much of the SQL coding myself. I've taken classes on it and have trouble with it.

You should really NOT use the AutoNum field in any sort of calculations. The sole purpose of an AutoNum field is to give a record a unique identifier. Order cannot be guaranteed with it.
It would be better to work off of your Store ID field, and use some sort of date field so each record can be ordered properly.
Then you could do an Aggregate Query, selecting the Max Date field for each store, and then link that query back to your original table (based on BOTH the Store ID and Date fields) to return the Tax Rate for that date.

I do understand that the AutoNum can't guarantee order. That being said, I can't seem to properly get the expected results, so I will trust your judgement and use a date field.

 

[Joe4]

If you cannot get it to work out, post a sample of your updated data (with dates), and I can help you come up with it.