Converting existing Excel Project Database over to Access

petro62

New Member
Joined
Jul 15, 2013
Messages
46
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.
 
Upvote 0
  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?
 
Upvote 0
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
and/or

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

How do I Create an Application in Microsoft Access?

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

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
 
Upvote 0

Forum statistics

Threads
1,212,929
Messages
6,110,743
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top