Finding Unique records (Access 2000)

Topic

New Reply

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:

Select distinct atlanta, denver, miami from AllCities

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

Reply | Quote

Replies

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.

Reply | Quote

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

SELECT DISTINCT tblCityTable.City, tblCityTable.Company
FROM tblCityTable;

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

SELECT DISTINCT tblCityTable.Miami, tblCityTable.Atlanta, tblCityTable.Denver
FROM tblCityTable;

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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:
SELECT DISTINCT CityTable.CityName FROM CityTable;
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. Glad you got it to work without too much pain.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote