Hi, Thanks in advance for help any of you could provide!
To start off, I am fairly new to Access but have been working with Excel for years. I am also a mechanical engineer so problem solving is right up my alley!
The issue I have is that I want to create an Access database from multiple excel files so that I can use queries and create reports.
At our company we have multiple designers as well as the purchasing department. The designers will create an Excel file, for each NEW project, which contains two sheets. One sheet is for Purchased items and another sheet is for Manufactured items.
Each sheet will be fully formatted so all columns,data types, etc will match the Access tables. Also, all excel files can be saved in the same folder.
The data from these two sheets needs to be added to the access database.
I will then create a User Interface where the Purchasing department can perform queries and reports.
My major obstacle (from my limited knowledge of Access) is the fact that new jobs are created up to 5 or 6 times a month AND (to make things more difficult) existing excel files need to be periodically updated with new data .(existing data never changes, only new data is added) but that data also needs to be updated in the database.
So to sum up my questions:
1 - How would you approach this issue?
2 - How would you modify my existing process to simplify the solution?
I am very open to suggestions here.
The important part is that I need to make the process as easy as possible for the designers and purchasers. If not, they will never use the system or worse yet, screw it up! lol
Again, thanks is advance for any help! I know you are all very busy with your own projects so any answers are VERY appreciated!
To start off, I am fairly new to Access but have been working with Excel for years. I am also a mechanical engineer so problem solving is right up my alley!
The issue I have is that I want to create an Access database from multiple excel files so that I can use queries and create reports.
At our company we have multiple designers as well as the purchasing department. The designers will create an Excel file, for each NEW project, which contains two sheets. One sheet is for Purchased items and another sheet is for Manufactured items.
Each sheet will be fully formatted so all columns,data types, etc will match the Access tables. Also, all excel files can be saved in the same folder.
The data from these two sheets needs to be added to the access database.
I will then create a User Interface where the Purchasing department can perform queries and reports.
My major obstacle (from my limited knowledge of Access) is the fact that new jobs are created up to 5 or 6 times a month AND (to make things more difficult) existing excel files need to be periodically updated with new data .(existing data never changes, only new data is added) but that data also needs to be updated in the database.
So to sum up my questions:
1 - How would you approach this issue?
2 - How would you modify my existing process to simplify the solution?
I am very open to suggestions here.
The important part is that I need to make the process as easy as possible for the designers and purchasers. If not, they will never use the system or worse yet, screw it up! lol
Again, thanks is advance for any help! I know you are all very busy with your own projects so any answers are VERY appreciated!