Create a MS Access Database with query/return results function, list related informa

[L227196]

Hi,

I am trying to standardize our process in security in terms of creating default application access for new hires based on their job title.

1. I receive report from HR with current list of users every month. list is on a spreadsheet with location codes, job code, job title, first and last name, employee number etc


2. I created a spreadsheet with the basic access for a user based on the job title

what i need access to do is:
1. needs to have search function that can query in multiple tables and list results base on the input string.
ex: input string is a 'job code'
- i need access to return results with names of users with that job code as well as the corresponding locations where that user is located.
- base on the result, it needs to supply additional result showing what applications that user have access to.

3. i also need access if possible to connect to active directory to verify information for each user from the list hr provided.

sorry i am VERY VERY new to access and just trying to take the initiative to clean the mess our old analysts did.

Thank You

[ItsMe]

Hi,

what i need access to do is:
1. needs to have search function that can query in multiple tables and list results base on the input string.


3. i also need access if possible to connect to active directory to verify information for each user from the list hr provided.

Item one; You will need to import the spreadsheet from HR. You can use the import Wizard to create a macro that you call on later as long as the spreadsheet structure remains the same. Work on your existing spreadsheets by having them inside the Access database.

Build search forms that use queries to find the specific criteria with the Access data tables.

Item three; not sure where Item two went. What within AD do you expect to discover that is relative to staff being current or not?

[L227196]

Hi, thank you for your prompt response.

1. How do i create the search forms in access? Do you have a sample code that I can try or if you can help me with instructions that I can follow please.

2. Sorry item 2 was the "I created spreadsheet with access matrix"

3. For the AD part, I need to see the group membership that user have in order to determine any other access they have that so I can do data analysis on what application access falls under standard access and exception.

I apologize if I'm blabbering, I'm trying to provide as much information as I can

Thank you!

[ItsMe]

Hi, thank you for your prompt response.
1. How do i create the search forms in access? Do you have a sample code that I can try or if you can help me with instructions that I can follow please.

Every form is based on a query. You may be able to get the functionality you need from a basic form and the built in toolbar's search function.

2. Sorry item 2 was the "I created spreadsheet with access matrix"

Not sure what this means exactly. Table structure within Access is very different than a spreadsheet. If you successfully build an Application it is rare you will export your data to a spreadsheet. Ideally you keep your data inside the database. A database is a data management tool. Exporting your data means you no longer have control over it and you are unable to manage it.

If you have something that is your own that you want to incorporate into the design, then once it is incorporated you manage it within the DB. You can take a spreadsheet and import it into Access as a new data table. It is up to you to then make this table function within the DB. Tables are what make up a database. How you query the tables is the Application part that the user sees. You present a GUI via forms that depend on queries and VBA code.

3. For the AD part, I need to see the group membership that user have in order to determine any other access they have that so I can do data analysis on what application access falls under standard access and exception.

I believe this is possible. However, it is not something that is built into Access. You would have to figure out how to get the information, maybe from command line, and then import it into the DB.

Access is a data management tool. If you have data, you want to put it inside your DB. Access will help you create an Application the User can interface with and manage the data. You need to create a Relational Database and an Application in order for your objectives to be met.

[L227196]

Every form is based on a query. You may be able to get the functionality you need from a basic form and the built in toolbar's search function.

Ok, thank you.

Not sure what this means exactly. Table structure within Access is very different than a spreadsheet. If you successfully build an Application it is rare you will export your data to a spreadsheet. Ideally you keep your data inside the database. A database is a data management tool. Exporting your data means you no longer have control over it and you are unable to manage it.

If you have something that is your own that you want to incorporate into the design, then once it is incorporated you manage it within the DB. You can take a spreadsheet and import it into Access as a new data table. It is up to you to then make this table function within the DB. Tables are what make up a database. How you query the tables is the Application part that the user sees. You present a GUI via forms that depend on queries and VBA code.

My previous answer was in response to your first question, "what happen to item 2"

I believe this is possible. However, it is not something that is built into Access. You would have to figure out how to get the information, maybe from command line, and then import it into the DB.

Access is a data management tool. If you have data, you want to put it inside your DB. Access will help you create an Application the User can interface with and manage the data. You need to create a Relational Database and an Application in order for your objectives to be met.

Thank you for the information. Based on what you said, I'm assuming a Relational Database is a database that can determine the relationship of data in a supplied table and its relevance, correct?

I was able to export the HR list spreadsheet into access and use the wizard to this. I'm still playing with access on creating a form.

[ItsMe]

I was able to export the HR list spreadsheet into access and use the wizard to this. I'm still playing with access on creating a form.

It is good that you were able to import the HR spreadsheet into your data base. You might want to spend some time researching RDMBS fundamentals and practicing building relations.

Forms are mostly about writing code. In order for your to develop a working RDMBS you will need skills as a developer. Writing code is not enough. The form building wizard can help introduce you to the form aspect after you have your table structure in place.

I would approach it in this order: Tables, Relationships, Queries, and then Forms.