Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2009
    Posts
    67

    Unanswered: Generic DISTINCT question, but I'm querying XLS

    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?
    Last edited by LAYGO; 01-13-10 at 16:51.

  2. #2
    Join Date
    Oct 2009
    Posts
    67
    For some reason this got me there!
    access queries DISTINCT vs GROUPBY ?

    Simply using a pointless aggregate on MaterialName & Group By CAS Number worked. Yeehaw.

    Anything I need to keep my eye on?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •