IMPORT EXCEL AND QUERY

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
..
 
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
 
Back
Top