Converting existing Excel Project Database over to Access

[petro62]

So after asking for help in the Excel forums the overall consensus was that going to Access may be a better option. I have very limited experience with Access. So I imported in my excel sheet. Currently it is 150 rows long by ~60 columns wide. I set each columns data type. Other than the fact that it converted all my percentages into 100% it appears the information transferred fine.

The goal I had with the excel sheet was trying to make a user friendly user form so that the project managers could easily go in and pull up the form and add a new project or pull up an edit form that let them filter and find their project and then it would load all of the information into the same original user form for them to edit. I was slowly getting there with Excel, but I think maybe it might not be the best option in the long run. So I need help creating these forms in Access. I currently created the 7 forms that I break the information down into. I was hoping to combine them into one tabbed form. Eventually I will pull this information into powerbi just for simple dispay purposes.

So I guess my question is this.

1. Can I combine my 7 forms into 1 form with 7 tabs.
2. Can I put an easy next button or cancel button at the bottom?
3. Can I put a save button on the last page of the form to save all that information to the table?
4. Can I create an opening form that lets them pick add new or edit existing?
5. In the edit existing form can they filter via their name or via the project status (active or inactive etc..)?
6. I assume this is obvious, but if I put this file on Microsoft teams to store in the cloud I assume everyone should be able to access it or do I want to create something separate for them to add and edit from?

I know it is a lot of questions, but any help to get going would be great.

 

[Larry Haydn]

So after asking for help in the Excel forums the overall consensus was that going to Access may be a better option. I have very limited experience with Access. So I imported in my excel sheet. Currently it is 150 rows long by ~60 columns wide. I set each columns data type. Other than the fact that it converted all my percentages into 100% it appears the information transferred fine.

The goal I had with the excel sheet was trying to make a user friendly user form so that the project managers could easily go in and pull up the form and add a new project or pull up an edit form that let them filter and find their project and then it would load all of the information into the same original user form for them to edit. I was slowly getting there with Excel, but I think maybe it might not be the best option in the long run. So I need help creating these forms in Access. I currently created the 7 forms that I break the information down into. I was hoping to combine them into one tabbed form. Eventually I will pull this information into powerbi just for simple dispay purposes.

So I guess my question is this.

1. Can I combine my 7 forms into 1 form with 7 tabs.
2. Can I put an easy next button or cancel button at the bottom?
3. Can I put a save button on the last page of the form to save all that information to the table?
4. Can I create an opening form that lets them pick add new or edit existing?
5. In the edit existing form can they filter via their name or via the project status (active or inactive etc..)?
6. I assume this is obvious, but if I put this file on Microsoft teams to store in the cloud I assume everyone should be able to access it or do I want to create something separate for them to add and edit from?

I know it is a lot of questions, but any help to get going would be great.

1. Can I combine my 7 forms into 1 form with 7 tabs.
   Yes, there is a form container, and you can add tabs and build your forms.
   
   
2. Can I put an easy next button or cancel button at the bottom?
   Sure. Command buttons can do just about anything you program them to do.
   
   
3. Can I put a save button on the last page of the form to save all that information to the table?
   Of course. But unlike Excel, you need to specify what data in your controls (eg. TextBox) you want to save.
   
   
4. Can I create an opening form that lets them pick add new or edit existing?
   Yes. You can use a form to control which track to take.
   
   
5. In the edit existing form can they filter via their name or via the project status (active or inactive etc..)?
   Yes, if you set up your tables properly.
   
   
6. I assume this is obvious, but if I put this file on Microsoft teams to store in the cloud I assume everyone should be able to access it or do I want to create something separate for them to add and edit from?
   If you want multiple users to access the database, then a client-server set-up would be best.
   First, you store the database (tables, queries, etc) in Access to a shareable location.
   Next you build another Access application to work with the database.
   

 

[petro62]

1. Can I combine my 7 forms into 1 form with 7 tabs.
   Yes, there is a form container, and you can add tabs and build your forms.
   
   
2. Can I put an easy next button or cancel button at the bottom?
   Sure. Command buttons can do just about anything you program them to do.
   
   
3. Can I put a save button on the last page of the form to save all that information to the table?
   Of course. But unlike Excel, you need to specify what data in your controls (eg. TextBox) you want to save.
   
   
4. Can I create an opening form that lets them pick add new or edit existing?
   Yes. You can use a form to control which track to take.
   
   
5. In the edit existing form can they filter via their name or via the project status (active or inactive etc..)?
   Yes, if you set up your tables properly.
   
   
6. I assume this is obvious, but if I put this file on Microsoft teams to store in the cloud I assume everyone should be able to access it or do I want to create something separate for them to add and edit from?
   If you want multiple users to access the database, then a client-server set-up would be best.
   First, you store the database (tables, queries, etc) in Access to a shareable location.
   Next you build another Access application to work with the database.

Thanks for the answers. It seems I have a lot to learn/study. Are there any good resources with samples?

 

[Larry Haydn]

Google is my teacher

 

[Micron]

I can virtually guarantee that your spreadsheet type table is going to be a problem. If you design an Access db with an Excel brain you will continually struggle from the beginning. First thing you need to do is study and understand db normalization. Here's a few of what I think are the most important subjects to review:

Normalization Parts I, II, III, IV, and V

What Is Normalization, Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

and/or

Database Normalization – Holowczak.com Tutorials

holowczak.com

Entity-Relationship Diagramming: Part I, II, III and IV

Entity-Relationship Diagramming: Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

How do I Create an Application in Microsoft Access?

How do I Create an Application in Microsoft Access?

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

Important for success:
Naming conventions - General: Commonly used naming conventions

MS Access Naming Conventions

_________________________________________________________ Naming Conventions. This document is aimed at the user who is unfamiliar with any naming convention. It is based upon personal experience and the interaction I have had with others. It is not a hard and fast rule. Why use a...

www.access-programmers.co.uk

What not to use in names
- Microsoft Access tips: Problem names and reserved words in Access

About Auto Numbers
- General: Use Autonumbers properly

The evils of lookup fields - The Access Web - The Evils of Lookup Fields in Tables
Table and PK design tips - Microsoft Access Tables, Primary Key Tips and Techniques
About calculated table fields - Microsoft Access tips: Calculated Fields
About Multi Value Fields - http://www.mendipdatasystems.co.uk/multivalued-fields/4594468763