comparing data in Access database
mads - 22/3/11 at 06:18 PM
hi all,
I have a database with various fields in. One field is the product code, the second is a description of the product and the third is the site (of
which there are four). The product code will be the same for all sites, if they have it. What I want to do is compare each description code for a
product and determine if the product description is different for said product at any of the sites. The principle is to have the same product
description for each product code regardless of site. Once this is determined, I can then go into the actual software and amend the product
descriptions for any anamolous product codes.
Does that make sense?
Cheers,
mads
[Edited on 22/3/11 by mads]
Xtreme Kermit - 22/3/11 at 06:48 PM
Sounds like you want a bit of SQL.
I don't know how many product codes you have, but if it is in the hundreds, and all your data is in one table, then you want something like
Select product code, product description, count (*) from table
Group by product code, product description
Sort by product code, product description
This will group all combinations of code and descriptions together. In a list of hundreds, the anomalies will be obvious.
I am sure a more sophisticated query can be used to show anomalies only, but it is some time since I have done this kind of thing 
mads - 22/3/11 at 06:54 PM
yep, data is all in one table.
i did something similar to what you said Kermit but there are thousands of product codes and with potentially the same code for all four sites, there
is still a lot of data to sift through.
Xtreme Kermit - 22/3/11 at 06:58 PM
How about a correlated sub query mads? Tried one of those yet?
beagley - 22/3/11 at 07:51 PM
I know you said you have around 1000 records in this table, but would it be easier to segment that data into 3rd normal form? You have 1 table for
acceptable sites and 1 table for acceptable products. Then to view the data you are wanting its a 3rd table that is keyed by the combination of Site
+ Product.
I know this picture isn't great, but it kinda explains my point.
Table Layouts
This would help standardize your data going forward so that you only have one place to edit a product's description. Its a PITA to get those
thousand records lined up correctly, but once its set its much easier to maintain. I too have to deal with data management problems like this.... ugh
can be frustrating.
quote:
Posted by Mads
Select product code, product description, count (*) from table
Group by product code, product description
Sort by product code, product description
Other than the query that mads suggested I can't really think of something that would provide you with what you're wanting.
[edit]
I was thinking about this a little more and I may have had a neuron or two fire. If your site key values are all consistent AND your product code key
values are all consistant in the main table you are wanting to edit the descriptions you can go ahead and set up the two tables I spoke of earlier.
Then simply run an update to pull the descriptions from the newly created PRODUCT table to update your SITE + PRODUCT table. This would ensure that
you have a consistent product description for all of your thousand(s) of records...... clear as mud?
[/edit]
Beags
[Edited on 3/22/2011 by beagley]
[Edited on 3/22/2011 by beagley]
jonrotheray - 22/3/11 at 09:33 PM
You can make a query - select the table twice and make a query like this one.
It will list records with differrent descriptions but same code.
Sorry this is huge - it wouldn't display when I resized it!
MikeR - 22/3/11 at 10:18 PM
Firstly - Why are you not using the DM&D descriptions??????
If you send me the db i'll have a play, i used to do something similar years ago when i developed hospital systems (prior to the introduction of
the DM&D)
What codes are you linking to? Can you not pull standard descriptions from the DM&D and replace your mixture with those?
(DM&D - standardised description of all drugs and medical devices used within the NHS. its also traverable so you can start with generic concepts
eg analgesic and end up with paracetamol 500mg pack of 30)
mads - 23/3/11 at 07:14 AM
quote:
Originally posted by Xtreme Kermit
How about a correlated sub query mads? Tried one of those yet?
nope - never done one. will google about them.
Beagley - I have thousands of product codes so there would be a LOT of data to sift through particularly as each code can be four times. Other
problem is is that the database I use is the output from a system at the end of each month. To amend the product descriptions, I need to go into the
actual software and manual edit the description for each product that is not in the correct format.
Jon - will give your query a go, it looks like it might work.
Mike - DM&D isn't used by the system supplier, hence the product descriptions are created manually. Plus it's primarily used in
community systems because it doesn't always have codes for some of the more unusual, unlicensed medicines we use. If I don't have any joy
with Jon's query, I will send you the database 
MikeR - 23/3/11 at 08:43 AM
Community, community ....... should be using DM&D ....... grrrr, grumble, money wasted ....... grrr
Is there any industry standard coding available or is it all local trust coding? (have you looked what your purchasing dept. has? They may have a
cross reference between trust codes and industry codes. IF you do have that, then its quite easy to just replace your non standard descriptions with
standardised descriptions via a lookup table).
