Comparison Query (1 Viewer)

[tmyers]

Is there a way that I can structure a query for the following?

I have a table that contains all sorts of products [tblProducts]. That table holds all info relevant to that item as well as the date it was entered. Is there a way I could design a query to search through a date range (nothing specific, but like between now and 2 months ago) that will check an items price history, and if the price entered falls outside a certain range of others, alert the user?

Example:
The database has a long history of items 1, 2 and 3. Today you enter item 1 and its price. The query runs and checks that newly entered price against that items price between now to 2 months ago. It finds that the newly entered price falls outside a threshold increase (say 10%) and alerts the user. Whereas items 2 and 3 fall within accepted thresholds and dont raise anything to the user.

More or less I thought it would nice to have some kind of system in place to catch possible incorrect pricing and to bring it to the users attention for a potential review.

I see the query having the criteria part number like that entered to narrow it down to that specific item, but don't know if the rest could be done.

 

[June7]

Most anything can be done with enough code.

Will need VBA to determine whether or not to notify user and then do so by MsgBox or opening a form or setting a combobox list to restrict choices.

A query might not even be needed. Possibly a DLookup() or DCount() domain aggregate function can serve. All depends on what you want to present to user.

The real trick often is figuring out what event(s) to put code into.

 

[Guus2005]

It can be done with a query or for clarity sake a few to keep them readable.
There should be a table which defines the range you want to compare against +/- your threshold.
You join the result to your product table and compare the price.
Shouldn't be that difficult.

Post a sample database for a more specific answer.

(use the domain functions like dlookup, dmax, etc on local tables only)

 

[Minty]

You could create a query to give you the min and max values and simply compare the input to that?
If you are using SQL server this could be view, linked to a table with the threshold in it, so that nothing was hard coded.

 

[gemma-the-husky]

Like most things this starts with the data, and not the process.
The data needs to be in the database, and logically structured.

If the data is in there, then it's just a matter of defining what is required, and establishing how to retrieve it.

The actual method might be a query. It might need code. It might be that you need to extract some data to a temporary storage area, and then process the temporary data.

 

[tmyers]

After talking to the boss about this one, he said it wasn't needed and would be extra fluff.
This was mostly one of those afternoon thoughts that popped into my head and wondered if it could be done.
I will experiment and see if I can get it to work just for fun.