Please HELP!!! Is it possible to create a database with

[Bailey]

Can someone please help me set up tables in Access that will give me the
ability to view/change/capture passwords? I have tried so many different
things but nothing is working for me.

I am going to give you a brief description of each column that is needed

UserName - This is the UserID for the Password
DateRequested - This is the Date that the UserID was requested
AccountDisabledDate - This is the Date that the UserID was disabled
Password - This is the password for the UserID
Type - This is the type of ID it is such as a Application ID, Database ID,
Oracle ID ect.
Approved By - This is the person/group that approved the UserID
Owner - This is the person/group that currently owns the UserID
Domain/Server or Machine - This is the machine that the UserID is restricted
to
Description - This is the description of the UserID
Application - This is the application in which the UserID and the Password
is used in

All of the information repeats itself. For instance,
One UserID can have many Applications
One UserID can have many DateRequested
One UserID can have many Types
One UserID can have many Domain/Server/Machine
One UserID can have many Approvers
One UserID can have many Owners
One UserID can have many Descriptions

One UserID only has one Account DisableDate
One UserId only has one Password

My ultimate goal is to be able to have the user view all information in a
table and be able to use a form to add in new UserIDs along with the new
information for the UserID.

Please help,

If this is too confusing, please let me know.

Thanks!

 

[Pieter Wijnen]

You need a couple of tables
Of the top of my head this is how I'd start out

User
--------------
UserID PK (autonumber)
UserName Text
Password Text
DisableDate Date/Time

ResourceType
---------------
ResourceTypeID (PK)
ResourceType Text (Machine, Domain, Application)

Resource (Application is Reserved in Access & Not reccomended + no need to
have seperate tables for machines & Apps)
------------
ResourceID PK AutoNumber
ResourceTypeID FK
Resource Text (MyDomain, MyMachine, MyApp )

UserResource
--------------
UserID (FK)
ResourceID (FK)
ApprovedID (FK -> User.UserID *))

Owner
--------
UserID (FK)
OwnerID (FK -> User.UserID *))

*) No reason not to store Owners & Approvers in the User Table as well, even
if they don't have any resources

HTH

Pieter

 

[Bailey]

Thanks Pieter for your help in this matter but I have a few questions.

1. Is the ResourceTypeID a autonumbe, is not where do I get this info from
2. For the ResourceType did you want me to put all three fields in one column
3.What do you mean by (Application is Reserved in Access & Not reccomended +
no need to have seperate tables for machines & Apps)
4. In the UserResource table what do u mean by User.userID*)). Is the
approvedID the name of the person/group or a number
5. In the Owner table what do you mean by by User.userID*)). Is the OwnerID
a number or text

Please forgive me but I am new to Access

Are u suggesting I create 5 tables?

Thanks,

 

[Bailey]

The main goal is to be able to have the user enter a new UserID using one
form and thus the new information populates all tables.

Thanks,

 

[John W. Vinson]

The main goal is to be able to have the user enter a new UserID using one
form and thus the new information populates all tables.

Sorry, but that's WRONG.

You do not need to or want to "populate all tables" until there is data to put
into those tables!

You would use your subforms to add new data to the tables (inheriting the
UserID from the main form) *when there is data to put into those tables* - not
before.

John W. Vinson [MVP]

 

[Bailey]

Thanks John,

So I am going in all the wrong directions.

Let me just start all over cause I seem to be missing something. First of
all I need help creating tables. I am importing a spreadsheet from Excel
into Access and I can't seem to find a unique identifier for any of my
columns because everything repeats. The tables I have so far are:

tblMaster
-----------
MasterId - Autonumber - pk
UserName
Type
Application
Domain/Server/Machine
Approved By
Owner
Password
Date Requested
DisableDate
Description

tblUsers
----------
Username - pk

tblApplications
-----------------
Applications - pk


When using this setup I am unable to add in a new id using the subform

Please Help

Thanks

 

[John W. Vinson]

Thanks John,

So I am going in all the wrong directions.

Let me just start all over cause I seem to be missing something. First of
all I need help creating tables. I am importing a spreadsheet from Excel
into Access and I can't seem to find a unique identifier for any of my
columns because everything repeats. The tables I have so far are:

tblMaster
-----------
MasterId - Autonumber - pk
UserName
Type
Application
Domain/Server/Machine
Approved By
Owner
Password
Date Requested
DisableDate
Description

tblUsers

Is the user name unique? People's names aren't - you might have two people
named Jim Smith. Is this what's in Username, or is it something guaranteed
unique like JSMITH3? Are you at all interested in getting the person's real
name?

tblApplications

So each record in tblMaster should have one and only one application?

How are the tables related? I presume each record in tblUser may be related to
one or more records in tblMaster, and similarly each record in tblApplications
to multiple records in tblMaster? If someone has 11 applications on each of
three Domain/Server/Machines, you want 33 records in tblMaster?

If so, you don't need subforms. You just need a couple of very simple
maintenance forms to add new Users and new Applications, and a basic form
bound to tblMaster, with combo boxes to select UserName and Application.
You'll probably also want a table of machines (to keep from having to type
them in each time), and perhaps also for Owners.

The Autonumber MasterID won't repeat; you can link to your spreadsheet and run
an Append query to add all the fields EXCEPT the MasterID, and Access will
assign autonumbers for you. You don't say where the Applications and Usernames
come from - how are they represented in your spreadsheet? You may be able to
use an Append query with its "Unique Values" property set in order to create a
tblUsers with only one record per user.

It might help if you could post a couple of sample records from your
spreadsheet.

John W. Vinson [MVP]

 

[Bailey]

Below is a sample of the data that I am working with. My columns are
User, Type, Application, Description, Approved By, Owner, Date Requested,
DisableDate, Password and Domain/Server/Machine

Please let me know if the table is legible or not

User Type Appl. Desc. Approve Owner DateReq. DisaDate Password
Domain
ATB Oracle Psoft Sched JSmith JSmith
ATB DB Wales Integr JWalk JWalk
web NT Comp Admin THall THall 2/26/06
PeterPan hinkle
web NT Comp Admin THall THall 2/26/06
SusieQ samm
web NT Comp Admin BCarr BCarr 8/15/05
hinkle
Corp NT Office N/A ADavid ADavid 11/1/02
Boss tcorp
Cred NT Office Credit ADavid ADavid 11/01/02
Boss tcorp
Finan NT Office Financ ADavid ADavid 11/01/02 Boss
tcorp
Bank Unix Struct Unix JFrost NickJr
Bank DB Wales login JHowel JHowel

 

[Bailey]

I can see that the table is not legible at all. Is there another way I can
send you a sample table?

To answer your questions earlier:
Is the user name unique?
No the username is not unique.

So each record in tblMaster should have one and only one application?
This is how I look at it.

One UsedID can be in many applications
One UserID can have many dates that it was requested
One UserID can have many passwords
One UserID can have many types
One UserID can be on many machines/server/machines
One UserID can have many approvers
One UserID can have many owners
One UserID can have many descriptions
One UserID can have many disabledates

 

[Bailey]

Resending the table - hopefully you can read it now

User Type Appl. Desc. Approve Owner DateReq. DisaDate Pass Domain
ATB Oracle Psoft Sched JSmith JSmith
ATB DB Wales Integr JWalk JWalk
web NT Comp Admin THall THall 2/26/06
PeterPan hinkle
web NT Comp Admin THall THall 2/26/06 SusieQ
samm
web NT Comp Admin BCarr BCarr 8/15/05
hinkle
Corp NT Office N/A ADavid ADavid 11/1/02 Boss
tcorp
Cred NT Office Credit ADavid ADavid 11/01/02 Boss
tcorp
Finan NT Office Financ ADavid ADavid 11/01/02 Boss
tcorp
Bank Unix Struct Unix JFrost NickJr
Bank DB Wales login JHowel JHowel

 

[John W. Vinson]

Resending the table - hopefully you can read it now

User Type Appl. Desc. Approve Owner DateReq. DisaDate Pass Domain
ATB Oracle Psoft Sched JSmith JSmith
ATB DB Wales Integr JWalk JWalk
web NT Comp Admin THall THall 2/26/06
PeterPan hinkle
web NT Comp Admin THall THall 2/26/06 SusieQ
samm
web NT Comp Admin BCarr BCarr 8/15/05
hinkle
Corp NT Office N/A ADavid ADavid 11/1/02 Boss
tcorp
Cred NT Office Credit ADavid ADavid 11/01/02 Boss
tcorp
Finan NT Office Financ ADavid ADavid 11/01/02 Boss
tcorp
Bank Unix Struct Unix JFrost NickJr
Bank DB Wales login JHowel JHowel

Tied up until Sunday... will try to respond then.

John W. Vinson [MVP]

 

[John W. Vinson]

Resending the table - hopefully you can read it now

User Type Appl. Desc. Approve Owner DateReq. DisaDate Pass Domain
ATB Oracle Psoft Sched JSmith JSmith
ATB DB Wales Integr JWalk JWalk
web NT Comp Admin THall THall 2/26/06
PeterPan hinkle
web NT Comp Admin THall THall 2/26/06 SusieQ
samm
web NT Comp Admin BCarr BCarr 8/15/05
hinkle
Corp NT Office N/A ADavid ADavid 11/1/02 Boss
tcorp
Cred NT Office Credit ADavid ADavid 11/01/02 Boss
tcorp
Finan NT Office Financ ADavid ADavid 11/01/02 Boss
tcorp
Bank Unix Struct Unix JFrost NickJr
Bank DB Wales login JHowel JHowel

I guess I don't understand the problem...

You can base a Form on this table. You should have a table of (what I'd call)
UserID's, such as ADavid, THall, etc. Your User field is something else - are
"ATB", "Web", "Corp" all different Users? Does ADavid have a password? or does
user Bank have one password for Unix and a different password for DB?

And what specific problem are you having assigning passwords?

John W. Vinson [MVP]