IMPORT EXCEL AND QUERY

[Guest]

This is what I imported from excel into access:
user name, module, edit, query, add, delete, review... (65 columns in all
with Y&N )
pmayes....... bkin .....Y...... Y........ Y ......N .......Y.........
jsmith .........staff .....N...... N....... Y...... Y.......Y........
jdoe............staff ......Y...... Y....... Y....... N ......N..........
kadams.......bkin....... Y......Y.........Y.......N........Y .......
I have 1720 records of users, for each user name there is a module, and
there are 65 Y or N places, I need to group all the users that have the same
"Y" or "N" together. The Y and N could all be in different places, but there
are alot that match. THANKS
I hope this better explains, and thank you.

 

[John Spencer]

So you want to order the records by the 65 Y/N columns. You should be able
to do so.

Add all your columns to the query.
Sort descending (or ascending) on all the Y/N columns.

If that is not what you want, then you need to expand on what you mean by
"..group all users that have the same Y or N together". Doing so on the
limited set of records you have displayed will order them as follows.

pmayes....... bkin .....Y...... Y........ Y ......N .......Y.........
kadams.......bkin....... Y......Y.........Y.......N........Y .......
jdoe............staff ......Y...... Y....... Y....... N ......N..........
jsmith .........staff .....N...... N....... Y...... Y.......Y........


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Hi Pammy

Add an extra column to your imported table called Label

Assuming userid is unique, create a make table query that selects all yes/no
columns and groups them all and then min(userid) like...

select yesno1, yesno2, ..., yesno65, min(userid) as [NewLabel]
group by yesno1, yesno2, ..., yesno65

Then create a query that uses the above table and your imported table,
Tedious bit, join all 65 yes/no columns in the above table to their
corresponding yes/no column in the imported table and update the added Label
column to [NewLabel]

Now all identical combinations will have the min(userid) as their label

HTH

regards

Andy Hull