Compare two versions of the same table

[whenthegeeseinvade]

Hello

I have a supplier who emails me a pricelist on a monthly basis. This consists of 200,000 products, each with a product group and an associated discount.

ie:

Product Group Item Discount

HK52 Copper Pipe 15mm 35%
HK52 Copper Elbow 22mm 35%
AJ07 Grohe Eurostyle Shower 28%

The product groups have the discount assocoated with them rather than any individual product so really the product is irrelevant here.

I want to be able to compare a table of this month's download with a table of last month's and to determine the following;

Have any existing product group discounts been changed
Have any new product groups been added
Have any old product groups been removed.

I've tried various ways of achieving this but I'm just not getting it. Any pointers would be really appreciated.

Thanks to all,

Larry

 

[John Big Booty]

Have a look at Find Duplicates and Find Unmatched queries in the query builder wizard.

 

[whenthegeeseinvade]

Thanks John. I've tried those but I can't seem to get the result I'm after. Can you give me any further guidance? Thanks.

 

[John Big Booty]

You could the following to determine if there is a discount on a particular line;

[URL="http://www.techonthenet.com/access/functions/string/right.php"]Right[/URL]([YourTextField], 1) = "%"

You could use the following to select all products with the same name regardless of discount value

[URL="http://www.techonthenet.com/access/functions/string/left.php"]Left[/URL]([YourTextField],[URL="http://www.techonthenet.com/access/functions/string/instrrev.php"]InstrRev[/URL]([YourTextField]," "))

From there you should be able to compare the discount values on the various lines.

 

[whenthegeeseinvade]

Thanks for your help John

I think I've got it now.

Larry