I'm querying a spreadsheet for data to be inserted into a database. Some "genius" thought it'd be great to put all possible names for a particular
CAS Number in the spreadsheet. The DISTINCT query works in that sense, but how do I get a single result for duplicate CAS Numbers?
Here is my unedited raw list:
Code:
00 Cadmium 7440-43-9
01 Cadmium oxide 1306-19-0
02 Cadmium sulfide 1306-23-6
03 Cadmium chloride 10108-64-2
04 Cadmium sulfate 10124-36-4
05 Chromium (VI) oxide * 1333-82-0
06 Barium chromate 10294-40-3
07 Calcium chromate 13765-19-0
08 Chromic acetate 1066-30-4
09 Chromium trioxide * 1333-82-0
10 Lead (II) chromate 7758-97-6
11 Sodium chromate 7775-11-3
12 Sodium dichromate 10588-01-9
13 Strontium chromate 7789-06-2
14 Potassium dichromate 7778-50-9
15 Potassium chromate 7789-00-6
16 Zinc chromate 13530-65-9
17 Mercury 7439-97-6
18 Mercuric chloride 33631-63-9
19 Mercury (II) chloride 7487-94-7
20 Mercuric sulfate 7783-35-9
21 Mercuric nitrate 10045-94-0
22 Mercuric (II) oxide 21908-53-2
23 Mercuric sulfide 1344-48-5
24 2-Bromobiphenyl 2052-07-5
25 3-Bromobiphenyl 2113-57-7
26 4-Bromobiphenyl 92-66-0
27 Bromobiphenyl ether 101-55-3
28 Decabromobiphenyl 13654-09-6
29 Decabromobiphenyl ether 1163-19-5
30 Dibromobiphenyl 92-86-4
31 Dibromobiphenyl ether 2050-47-7
32 Heptabromobiphenylether 68928-80-3
33 Hexabromobiphenyl 59080-40-9
34 hexabromo-1,1’-biphenyl 36355-01-8
35 Firemaster FF-1 67774-32-7
36 Hexabromobiphenyl ether 36483-60-0
37 Nonabromobiphenylether 63936-56-1
38 Octabromobiphenyl 61288-13-9
39 Octabromobiphenyl ether 32536-52-0
40 Pentabromobidphenyl ether 32534-81-9
41 Polybrominated Biphenyls 59536-65-1
42 Tetrabromobipheny 40088-45-7
43 Tetrabromobipheny ether 40088-47-9
44 Tribromobiphenyl ether 49690-94-0
45 Perchlorates 7791-03-9
46 Lead 7439-92-1
47 Lead (II) sulfate 7446-14-2
48 Lead (II) carbonate 598-63-0
49 Lead hydrocarbonate * 1319-46-6
50 Lead acetate 301-04-2
51 Lead (II) acetate, trihydrate 6080-56-4
52 Lead phosphate 7446-27-7
53 Lead selenide 12069-00-0
54 Lead (IV) oxide 1309-60-0
55 Lead (II,IV) oxide 1314-41-6
56 Lead (II) sulfide 1314-87-0
57 Lead (II) oxide 1317-36-8
58 Lead (II) carbonate basic * 1319-46-6
59 Lead hydroxidcarbonate 1344-36-1
60 Lead (II) phosphate 7446-27-2
61 Lead (II) chromate 7758-97-6
62 Lead (II) titanate 12060-00-3
63 Lead sulfate, sulphuric acid, lead salt 15739-80-7
64 Lead sulphate, tribasic 12202-17-4
65 Lead stearate 1072-35-1
And after I run the query w/Distinct:
Code:
00 2-Bromobiphenyl 2052-07-5
01 3-Bromobiphenyl 2113-57-7
02 4-Bromobiphenyl 92-66-0
03 Barium chromate 10294-40-3
04 Bromobiphenyl ether 101-55-3
05 Cadmium 7440-43-9
06 Cadmium chloride 10108-64-2
07 Cadmium oxide 1306-19-0
08 Cadmium sulfate 10124-36-4
09 Cadmium sulfide 1306-23-6
10 Calcium chromate 13765-19-0
11 Chromic acetate 1066-30-4
12 Chromium (VI) oxide * 1333-82-0
13 Chromium trioxide * 1333-82-0
14 Decabromobiphenyl 13654-09-6
15 Decabromobiphenyl ether 1163-19-5
16 Dibromobiphenyl 92-86-4
17 Dibromobiphenyl ether 2050-47-7
18 Firemaster FF-1 67774-32-7
19 Heptabromobiphenylether 68928-80-3
20 hexabromo-1,1’-biphenyl 36355-01-8
21 Hexabromobiphenyl 59080-40-9
22 Hexabromobiphenyl ether 36483-60-0
23 Lead 7439-92-1
24 Lead (II) acetate, trihydrate 6080-56-4
25 Lead (II) carbonate 598-63-0
26 Lead (II) carbonate basic * 1319-46-6
27 Lead (II) chromate 7758-97-6
28 Lead (II) oxide 1317-36-8
29 Lead (II) phosphate 7446-27-2
30 Lead (II) sulfate 7446-14-2
31 Lead (II) sulfide 1314-87-0
32 Lead (II) titanate 12060-00-3
33 Lead (II,IV) oxide 1314-41-6
34 Lead (IV) oxide 1309-60-0
35 Lead acetate 301-04-2
36 Lead hydrocarbonate * 1319-46-6
37 Lead hydroxidcarbonate 1344-36-1
38 Lead phosphate 7446-27-7
39 Lead selenide 12069-00-0
40 Lead stearate 1072-35-1
41 Lead sulfate, sulphuric acid, lead salt 15739-80-7
42 Lead sulphate, tribasic 12202-17-4
43 Mercuric (II) oxide 21908-53-2
44 Mercuric chloride 33631-63-9
45 Mercuric nitrate 10045-94-0
46 Mercuric sulfate 7783-35-9
47 Mercuric sulfide 1344-48-5
48 Mercury 7439-97-6
49 Mercury (II) chloride 7487-94-7
50 Nonabromobiphenylether 63936-56-1
51 Octabromobiphenyl 61288-13-9
52 Octabromobiphenyl ether 32536-52-0
53 Pentabromobidphenyl ether 32534-81-9
54 Perchlorates 7791-03-9
55 Polybrominated Biphenyls 59536-65-1
56 Potassium chromate 7789-00-6
57 Potassium dichromate 7778-50-9
58 Sodium chromate 7775-11-3
59 Sodium dichromate 10588-01-9
60 Strontium chromate 7789-06-2
61 Tetrabromobipheny 40088-45-7
62 Tetrabromobipheny ether 40088-47-9
63 Tribromobiphenyl ether 49690-94-0
64 Zinc chromate 13530-65-9
So there is only ONE instance of both the CAS Number and Material Name being identical, BUT as you can see via the asterisks, there are still duplicates of CAS Number. How do I ONLY get one instance of CAS Number and Material Name? I don't care about the different names, that will be managed later. I just need ONE UNIQUE CAS Number from the list.
Any insight/thoughts?
dBforums
>
Generic DISTINCT question, but I'm querying XLS
01-13-10, 16:48
Linear Mode
