Linking Tables when one has duplicates

[Welshbird]

I've just spotted an odd thing:

I had two tables designed as follows:

table_rx
Name                   
---------------------
CALMNTH                
Site_CODE   
PRODUCT_NAME       
RX                     
QTY                    
UNITS
                  
table_sales
Name                           
-------------------------
CALMNTH                        
CEG_PROD_CODE                  
TVF                            
TYPE_ETA                       
SITE_CODE           
GEO                            
WHS                            
UNITS                          
VALUE                          
CAL

Now one of these contained prescribed data, and one sales data (yip - that's prescriptions!)

I needed to join these two together, as the table_sales contains lots more sites than the table_rx, and I needed to know all sales, and sales to the smaller group.

BUT: Table_RX is not summarised, as the product_name could be written in different ways, (Month and Site_Code are not a unique key) and this gave an artificially inflated answer.

I created a summarised version of table_rx, grouped by month, site_code and then linked and the result was fine.

I just wondered if someone could explain how / why this happens.

Thanks Guys.


Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[LKBrwnDBA]

The summarized version will elliminate duplicates.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Welshbird]

Yeah - it did - I just thought it seemed like odd behavour to me, and I still can't really understand why the original table (with correct links in) would not have given me the correct answer.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]