Duplication in queries - how to eliminate

[permit]

I want to compare two prices to find te variance between the two. Each price is distinguised by a sales code; one for 2008 and one for 2009.

Originally I had specified these code in criteria but this eliminated all new poducts that didn't have a 2008 price from the comparison making it unusable overall as we want to include our 2009 products.

I then tried to make it so that if the price doesn't have the 2008 sales code it uses the 2009 sales code through this expression in the criteria field - IIf([salescode]=2008,2008,2009) (simplified)

now this works for new products which never had a 2008 sales code but if the product had a 2008 price code and has a 2009 price code it duplicates the record...obviously.

Now my question is, how can I stop it from duplicating? Is there a more robust expression I can use?

I also tried to put 2008 in the criteria and then put 2009 in the or field but when I try to run the query it comes up with a 0 division error

Thanks for your help

 

[Fozi]

Are the sales codes in the same table? Pleas post database if possible and I'll have a look at it.

 

[Fozi]

The attached assumes respective tables for each year.

 

[neileg]

You have a design problem. Instead of holding each years price as a new column, they should be held as separate records in a related table. You need to read up on nomalisation. Wikipedia is a good starting point.

 

[boblarson]

Here's a good article on Normalization:
http://www.allenbrowne.com/bin/Access_Basics_Crystal_080220_Chapter_03.pdf

 

[boblarson]

The attached assumes respective tables for each year.

As mentioned by Neil - you shouldn't have tables for each year. That flies against the strengths of a relational database. It's like taking the handle off a hammer and trying to hammer in nails by only using the head without the handle. You lose most of the strength of the hammer.

 

[Rabbie]

This link also gives an sight into normalisation

 

[permit]

Thanks for all your help so far.

The tables are imported through an obcd from a Navision table so normalisation of the tables is not really an option I think.

I'm also using only one table for the prices in my query it's just that all the prices are in the same table, including codes. So to be able to draw the different prices with codes out I drag them in my query multiple times.

Here's an example dataset to illustrate my problem:

Product Sales Code Price
(Product 1) 2008 £10
(Product 1) 2009 £12
(Product 2) 2009 £30


Now I need to get that data into this format:

2008 RRP 2009 RRP Variance
(Product One) £10 £12 +£2
(Product Two) £30 £30 £0

This is off course really simplified as each product is sorted by product category, product family, brand and then has up to 20 sales codes for different countries etc...

Now the sales data is in a table layed out much like in the illustration. So when I put an IIf statement in the criteria it applies that to each line (obviously) and so gives a duplication in the case of product one and works perfectly for product two, if I just use the sales code as a criteria it obviously omits product two (no applicable sales code).

I've tried to write an expression in the heading but that resulted for every sales code and associated price to show up, effectively repeating it up to 20 times!

No after tinkering with it for a few hours....I've managed to get the thing to work by splitting each sales code in a seperate table and then drawing them back in through a 'master' query which did all the other stuff. So in a way I've done it but I'm sure there must be a better way to do this!

I've also noticed that there is a way to play around with the joins of the tables to make it work but unfortuanely there is an unlinked parameter table in there which is necessary for all the reports to run accurately.