duplicates

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

Guest

I have a table with user names and security modules. There are 1700 users and
65 security modules that will have a Y or N depending on if they have that
module. I would like to be able to run a query that will group all the users
with the same security . I cannot figure out how to do this. The Duplicate
wizard will not allow over 10 columns, and as noted I have 65 columns.
Thanks,
 
First of all you need to revise your table structure and not have the 65
columns.
Have a table with the user information. Next a table listing the 65
security modules and associated information. Third build a junction table
having a foreign key from the user table and the security table. Create a
one-to-many relationalship from the primary keys to the foreign keys.
Use a form/subform (user/security module) to assign modules to users. In
the junction table you can also have additional information such as
AssignDate, Completed, etc.
 
I don't understand what a junction table is. Sorry, I am not a proficent in
access.

Thanks
 
A junction table is as I described. In this case it relates people to
security modules in a one(user)-to-many(security modules).
Instead of having 65 fields for the user they would have up to 65 records in
the junction table.
John Brown -- user - record in the user table
John Brown - Security Module01 - records in the junction table
John Brown - Security Module03
John Brown - Security Module07
John Brown - Security Module15
Bill Jones -- user
Bill Jones - Security Module01
John Brown - Security Module01
John Brown - Security Module02
John Brown - Security Module05
John Brown - Security Module11
 
I have a table with user names and security modules. There are 1700 users and
65 security modules that will have a Y or N depending on if they have that
module. I would like to be able to run a query that will group all the users
with the same security . I cannot figure out how to do this. The Duplicate
wizard will not allow over 10 columns, and as noted I have 65 columns.
Thanks,
Click to expand...

You're "committing spreadsheet upon a database", a venial sin punishible by
being made to read about Normalization:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

especially the Database Design 101 links on Jeff's page.

In short, "fields are expensive, records are cheap". You need THREE tables:

Users
UserID <primary key>
LastName
FirstName
<other bio info about the user>

SecurityModules
ModuleID <primary key>
ModuleName
Description
... <other fields about the module itself>

UserModules
UserID <link to Users>
ModuleID <link to Modules>
<any fields about THIS user and THIS module, e.g. date acquired, etc.>

Rather than 65 *fields* with Y or N, you would have between zero and 65
*records* in the SecurityModules table for each user. Now you will have only
one field to search!

You can migrate the data from your spreadsheet table into the properly
normalized table using a "normalizing Union query". If you'ld care to post
back with a description of your table (some representative fieldnames perhaps)
I'd be glad to explain how to set one up.

John W. Vinson [MVP]
 
This is what I imported from excel into access:
user name, module, edit, query, add, delete, review...etc.
pmayes bkin Y Y Y N Y.........
jsmith staff N N Y N Y........
jdoe staff Y Y Y N Y..........
I have 1720 records of users, for each user name there is one 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.
I hope this better explains, and thank you.
 
This is what I imported from excel into access:
user name, module, edit, query, add, delete, review...etc.
pmayes bkin Y Y Y N Y.........
jsmith staff N N Y N Y........
jdoe staff Y Y Y N Y..........
I have 1720 records of users, for each user name there is one 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.
Click to expand...

I'd suggest using three tables:

Users
UserName <Text, Primary Key>
Module <Text>

Steps
Step <Text, Primary Key>

StepsDone
UserName <Text, Primary Key>
Step <Text, Primary Key>
Completed <Yes/no>

Manually fill the Steps table with your fieldnames - Edit, Query, Add, Delete,
Review, etc. etc.

Use the Relationships window to define a relationship from Users to StepsDone,
and also from Steps to StepsDone.

Run an Append query:

INSERT INTO Users (UserName, Module)
SELECT [User Name], [Module]
FROM yourtable;

Then go into the SQL window to create a new UNION query:

SELECT [User Name], [Module], "Edit" AS Step, [Edit] = "Y" AS Completed
FROM yourtable
UNION ALL
SELECT [User Name], [Module], "Query" AS Step, [Query] = "Y" AS Completed
FROM yourtable
UNION ALL
SELECT [User Name], [Module], "Add" AS Step, [Add] = "Y" AS Completed
FROM yourtable

<etc. etc. through all 65 fields>

The expressions [Edit] = "Y" will return True (-1) for the Y values, and False
(0) for the N (or actually for anything other than Y).

Base a new Append query on your UNION query and append the data into the new
StepsDone table.

With this properly normalized structure, you'll be able to create a Query
joining StepsDone to itself by Step and Completed to easily identify which
users have completed (or not completed) each step.

It will be nightmarishly difficult to do this with your current wide-flat
design. You *do* need to normalize as I've suggested!

John W. Vinson [MVP]
 
Back
Top