What should I use? - Help

[noneofyo]

Hello,

I have a very simple database.

The fields are:

Product (for the product number)

Retailer (for which retailer we're talking about)

Price

Week of the year (for example: 1-52)

Year

So one entry could be:

Product: 37LCD2

Retailer: Circuit City

Price: 907.99

Week of the year: 10

Year: 2008

We are one of the retailers.

I would like to find any occurrences where we (one of the retailers) are higher in price than any of the other retailers on a specific product in the same week and year.

Basically making sure we are always the lowest price listed.

Hope that's clear.

Would scriptmaker be the best way to do that? Or if there is some other way that would be better, can you point me in the right direction?

Thanks,

- David

[Fitch]

If you had a separate Products table, you could make a relationship to the existing table based on Product, Week, and Year. (In Products, Week and Year would be global fields.) Sort the relationship by price.

With this setup, you can look at a list of products and see who is the lowest price for each one.

[noneofyo]

Thanks so much for the response. There's at least a couple thousand entries though, so I'd like it if there was some way for me to see only the entries where we're higher in price. Going through them all would be a huge task. Plus with every new upload we're wanting to run this report or whatever it would be and see the areas where we're higher than somebody else.

Any ideas?

[comment]

You can do what Tom suggested even without an extra table: define a self-join relationship as:

YourTable::Product = YourTable 2::Product

AND

YourTable::Week of the year = YourTable 2::Week of the year

AND

YourTable::Year = YourTable 2::Year

and add a calculation field (result is Number) =

[color:red]Min ( YourTable 2::Price ) < Price

Search for records with you as the Retailer and 1 in the calculation field.

Edited March 6, 2008 by Guest
fixed a typo

[noneofyo]

Thanks so much for the reply.

When I do that it gives me the following error:

'In a function Average, Count, Extend, GetRepetition, Max, Min, NPV, StDev, Sum, GetSummary, GetNthRecord, an expression was found where a field alone is needed.'

That's when I'm trying to use:

Min ( MainTable 2::Price < Price )

However when I just use

MainTable 2::Price < Price

It works and gives 0's and 1's, but I don't think it's the right calculation. (MainTable is the name of the table)

What do you think?

Thanks again.

[David Jondreau]

I think the calc is supposed to be:

Min(MainTable 2::Price) < Price

You'll still get a bunch of 1s and 0s, but the 1s are the items that are priced lower than yours.

You can also create another calc field that's just

Min(MainTable 2::Price) to see the lowest price.

Edited March 6, 2008 by Guest

[comment]

Oops - sorry, that was a really bad typo. Fixed now.

[noneofyo]

works like a charm.

you guys are seriously awesome.