Results 1 to 9 of 9
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding Unique records (Access 2000)

    I'm a novice with Access but know Oracle and SQL fairly well (haven't used it in two years though).

    I have an urgent request from a co-worker to help find unique records of company names. The original data files are in multiple Excel spreadsheets some of which are over 50,000 rows. I've imported two of the spreadsheets into Access to practice but can't figure out how to run a simple SQL statement against this table.

    The table, AllCities, has one column for each City. Each City has 'n' records of company names, such as:

    ATLANTA, DENVER, MIAMI
    CompanyA, CompanyAA, CompanyAAA
    CompanyB, CompanyBB, CompanyBB

    My goal is to determine the UNIQUE companies for all cities with something like:

    <pre>Select distinct atlanta, denver, miami from AllCities</pre>

    How do you run SQL in Access, this is SO simple in Oracle. The wizard is useless as far as I can figiure out and since I'm in a rush I need the super brains of The Lounge to get me out of this bind for now.

    Oh, Wise Ones.... What's the easy way to do this query?

    Deb <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23> <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Unique records (Access 2000)

    Try creating a query with your company field.

    Then in design view right click on the field and select totals.

    This will give you all of the company names once.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding Unique records (Access 2000)

    Are companyA and CompanyAA and/or CompanyAAA related somehow? Are they branches of the same organization in different cities?

    If not, you need to create (or re-create) the table in the form:

    City / Company
    Atlanta, CompanyA
    Denver, CompanyAA
    Miami, CompanyAAA

    etc

    Then
    <pre>SELECT DISTINCT tblCityTable.City, tblCityTable.Company
    FROM tblCityTable;
    </pre>

    Will extract only the unique combinations of City and Company. As a shortcut, if you define the table with City and Company combined to form the Primary Key (before you start adding records to the file) then Access will not add duplicate city/company combinations - this means that the table will already be in the output form you need, if that helps.

    If the companyA, CompanyAA, CompanyAAA are related, then
    <pre>SELECT DISTINCT tblCityTable.Miami, tblCityTable.Atlanta, tblCityTable.Denver
    FROM tblCityTable;
    </pre>

    will extract unique combinations of CoA, CoAA, CoAAA - but if you have 'missing' entries (a null for 'Miami' in one row, and the right names for Denver and Atlanta, for instance) you will extract the Denver and Atlanta information twice - once with Miami, and once without. It will get you part of the way there, anyway. Again, if you define the combined Miami/Atlanta/Denver fields as a Primary key before you add data, Access will eliminate any duplicate entries on its own

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Unique records (Access 2000)

    No, the company names are not related. I need unique company names no matter what city they're located in. The table has one field per city and 'n' records. For example if Blockbuster is listed in 5 cities, I only want it counted once.

    Fields: Detroit, Miami, Cleveland
    Records:
    Burger King, Macys, Burger King
    Taco Bell, Taco Bell, Wendys
    Wendys, JC Penny, Safeway

    So above is a list of three cities and each record/row has company names, totally unrelated to each other.

    The output should be unique names, so it's:
    Burger King, Macys, Taco Bell, Wendys, JC Penny, Safeway

    I still can't get Access to run a query manually (no stupid wizards). I can see the query view with the raw SQL but when I edit it, how can I get it to run. This is so trivial in Oracle with SQL Plus.

    Actually I think I'll just export the .xls files as .csv and telnet them to my Unix box and then do a sort -u on the data. No database needed.

    Thanks for the info, Deb <img src=/S/duck.gif border=0 alt=duck width=23 height=23>

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Finding Unique records (Access 2000)

    This is pretty easy to do if you get each company name into it's own record. Therein lies the challenge, and it will be one in SQL Server or Oracle too.

    So how to get rid of the City info, and end up with just the Company names. You could always write a little VBA routine to take the records and manipulate them, which I consider too much work. What I do instead is take the data into a good text editor, and replace the delimiters with a CR/LF. Then I import it into Access and run the unique values query. BTW, I would get rid of the City names in XL by just deleting that column. Hope this alternative is useful.
    Wendell

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Unique records (Access 2000)

    The data is 90 degrees from what you're saying. Each Excel column is a city as I mentioned in the previous post. Actually the data is too big for Excel to handle (more than 64K rows) so we've broken it down into one city per .xls file and some of those are 50K+ rows.

    Columns = CityA, CityB, CityC
    Rows:
    CompA, CompB, CompA
    CompC, CompA, CompB
    CompD, CompA, CompE

    So the unique companies are: CompA, CompB, CompC, CompD, CompE

    I ended up just telneting the exported files (after saving as .csv) to my Unix server and doing 'sort -u' . Works fine, no database or pgramming needed. I never was able to figure out how to manually do an SQL statement. I only mentioned Oracle beause it's so much easier to do with their SQL Plus utility, no stupid wizards to "help" you.

    Thanks anyway,
    Deb

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Finding Unique records (Access 2000)

    Sorry I misinterpreted your data arrangement, but the basic trick is to get all city names into a single field in Access. Once that's done, you use the builder and set the query property unique values to Yes, or you can simply type in the following SQL statement in the SQL View of a new query:
    <font color=blue>SELECT DISTINCT CityTable.CityName FROM CityTable;</font color=blue>
    The bottom line however is that unless you do this sort of thing regularly, the quickest way to do something is nearly always the way you already know how. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> Glad you got it to work without too much pain.
    Wendell

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Finding Unique records (Access 2000)

    Sorry to have come in so late, but you can do this simply from A2000 with the following query.

    SELECT distinct Cities.CityA
    FROM (SELECT Cities.CityA from Cities UNION SELECT Cities.CityB from Cities UNION SELECT Cities.CityC from Cities)

    Better late than never. I have included a sample database as well.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding Unique records (Access 2000)

    You can't honestly compare a database server (Oracle, SQL Server, etc) with Access when it comes to queries. Access queries have to run through the Jet query engine and the query grid is the interface for creating them. SQL in a database server can run from within a stored procedure, etc., which is a far cry from an Access query. Besides, flat files are not really easier to query in a database server, you just have the advantage of having more muscle and stored procedures to do the dirty work.
    Charlotte

Posting Permissions

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