• 0

Using Access over Excel for database management


Question

So... right now at my job I have 10 different outlook files for different sales people.  Each file has the accounts they handle and the monthly revenue going back - 10 years.

 

The problem - is having to update 10 different files each month...

 

I would love to be able to use access to have every account in one place and then when I update those each excel sheet gets updated for the staff?

 

Is that easily doable in Access?  

 

I consider myself a strong user of excel but have never used access..

 

Thanks in advance! 

12 answers to this question

Recommended Posts

  • 0

sure, but it does require a bit of programming to get the fields to do what you want, and if you want a front end well you will need to design that too. 

 

Might I suggest something like ACT!, it would probably work better or some other customer relations manager (crm).  Each can have their own logon and enter their own data.

  • 0
  On 20/07/2015 at 15:44, sc302 said:

sure, but it does require a bit of programming to get the fields to do what you want, and if you want a front end well you will need to design that too. 

 

Might I suggest something like ACT!, it would probably work better or some other customer relations manager (crm).  Each can have their own logon and enter their own data.

 

Gotta stick with MS suite... since that's what the company uses... 

  • 0

Yeah there are much better contact management systems other than outlook contact files that is for sure..  The mentioned ACT is one of the major players in that area.  http://www.act.com/

  • 0
  On 20/07/2015 at 15:56, Ice_Blue said:

If you give me an outline of what data you need to track, I may be able to give you some guidelines as to how to develop an access database to accomplish this.

 

 

We have 10 sales people who handle different accounts.  Right now I update 10 different excel worksheets every month so they have updated figures of how much those customers spent last month.  I am basically opening/closing 10 different workbooks.  And then lets say someone leaves and we hire someone new I then have to create a new sheet and transfer the accounts.

 

I would love to be able to have ONE master sheet and a field with a salespersons initials and then I update that one sheet monthly it automatically updates 10 individual sheets for the sales people.

 

Hope that makes sense.

  • 0
  On 20/07/2015 at 15:53, Sk8Surfr said:

Sorry, maybe my initial post wasn't clear... 

 

It is more about the DATA than it is the customers.  

 

I'm not concerned with addresses, and contact name etc.

 

It's about revenue 

Fully understand that, however instead of trying to recreate the wheel you could do something that is a bit better in doing what you are trying to do.  A crm like act is a bit more than just a contact list to look up customers in, it helps you manage the account...follow their purchases, alert you when you haven't heard from them in a while, let you know trends as well as their weekly, monthly, quartly, yearly, 5 year, etc totals.  

 

square peg in a round hole, you have a lot of filing to do to make it work, vs getting a solution talored to do what you want.  There are some free/open source crm solutions out there. 

  • 0
  On 20/07/2015 at 16:01, Sk8Surfr said:

We have 10 sales people who handle different accounts.  Right now I update 10 different excel worksheets every month so they have updated figures of how much those customers spent last month.  I am basically opening/closing 10 different workbooks.  And then lets say someone leaves and we hire someone new I then have to create a new sheet and transfer the accounts.

 

I would love to be able to have ONE master sheet and a field with a salespersons initials and then I update that one sheet monthly it automatically updates 10 individual sheets for the sales people.

 

Hope that makes sense.

When you open Microsoft Access 2013 (not sure about 2010) you can choose from a selection of database templates.

The one you are looking for is the "Desktop Northwind 2007 Sample Database".

 

Click on this and enter a name and path for the database. It will be downloaded and opened directly in Access.

 

See if this is something like what you want.

If so, this database can be easily modified to more suit your needs.

 

The Northwind Traders sample database contains the sales data for a fictitious company called Northwind Traders, which imports and exports specialty foods from around the world.

  • 0

Connect an Access database to your workbook

 

It's certainly possible. There are many things to consider though: data integrity, data security, data consistency. There are too many moving and distinct parts. I would consider what sc302 said, and said well: don't reinvent, you probably don't have the time, the support or the experience. Besides, already built solution will sound good when you're about 10-15% into this new, heavy thing and you realize expertise doesn't happen overnight. 

  • 0

Your scenario sounds like a good use of MS Access. Keep all your data in one Access .mdb file, and modify your spreadsheets to query the mdb and refresh the contents.  If you like, you can build forms to make it easier to keep the Access data up to date.  An alternative is to use the free version of MS SQL, which offers a better database (full use of T-SQL) but doesn't have the built-in forms development that Access offers. (However, an Access front-end can use a SQL back-end db.)

 

If the xls is just serving as a report and not being used for calculation, you could use Access Reports or SQL Reports instead of distributing the xls files. That way the reports would always be current and the spreadsheets would not need to have embedded queries.  Reports can be exported to xls if desired.

  • 0

I have to agree with sc302 here, why are you wanting to reinvent the wheel here.  There are lots of companies that do this for their bread and butter.. Something like sugarcrm which does have a FREE community version if you don't want to just license its use.  Salesforce, Zoho crm 2 other major players in the CRM market.

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • Microsoft Store is getting improved recommendations, deeper Windows integration, and more by Taras Buria Microsoft announced several new features coming to the Microsoft Store on Windows 11. The company revealed that 250 million customers use the Microsoft Store each month. With the latest updates, Microsoft is improving the experience with better recommendations, search improvements, deeper Windows integration, Copilot, and more. The store's Home Page is getting personalized recommendations with suggestions based on your recent activities, what is trending in your region, and the recent deals. Microsoft says this change will bring more meaningful and relevant content. Search is getting smarter, and it now uses additional information when ranking apps. It is "intent-aware," and considers additional aspects like app updates, ratings, language-specific nuances, and more. In the United States, the Microsoft Store now has a Copilot button at the bottom of the screen. Clicking it lets you ask questions about an app or game, or compare two products. Speaking of Copilot, apps with AI-powered experiences now have a badge indicating that certain apps work better on Copilot+ PCs. Other changes to the Microsoft Store include a new Discover More section with related apps and deeper Windows integration. The latter lets you find apps in the Microsoft Store using Windows Search, and the "Open With" dialog now includes additional recommendations from the Microsoft Store. Finally, Microsoft made multiple under-the-hood improvements to boost performance (the app launches twice as fast as it did six months ago) and improve installation reliability. In addition to new features coming to the Microsoft Store, the company reminded users that some popular productivity apps are now available in the Store. They include Notion, Perplexity, Docker, and Day One. You can read more about all those changes in a post on the official Windows Blogs website. Last month, at Build 2025, Microsoft announced more improvements for the Microsoft Store, so stay tuned for those.
    • OpenAI exposes secret propaganda campaigns tied to multiple countries by David Uzondu Back in February, OpenAI shut down accounts that were busy developing Chinese surveillance tools aimed at the West. These tools were designed to snoop on social media, look for anti-China sentiment and protests, and report back to Chinese authorities. Now, OpenAI has announced it has disrupted even more shady operations, and not just those tied to China. In a report released Thursday, the company detailed how it recently dismantled ten different operations that were misusing its artificial intelligence tools. One of the China-linked groups, which OpenAI called "Sneer Review," used ChatGPT to churn out short comments for sites like TikTok, X, and Facebook. The topics varied, from U.S. politics to criticism of a Taiwanese game, where players work against the Chinese Communist Party. This operation even generated posts and then replied to its own posts to fake real discussions. What is particularly interesting is that the group also used ChatGPT to write internal performance reviews, describing how well they were running their influence campaign. Another operation with ties to China involved individuals posing as journalists and geopolitical analysts. They used ChatGPT to write social media posts and biographies for their fake accounts on X, translate messages from Chinese to English, and analyze data. OpenAI mentioned that this group even analyzed correspondence addressed to a U.S. Senator. On top of that, these actors used OpenAI's models to create marketing materials, basically advertising their services for running fake social media campaigns and recruiting intelligence sources. OpenAI also disrupted operations, probably originating in Russia and Iran. There was also a spam operation from a marketing company in the Philippines, a recruitment scam linked to Cambodia, and a deceptive job campaign that looked like something North Korea might orchestrate. Ben Nimmo, from OpenAI's intelligence team, noted the wide range of tactics and platforms these groups are using. However, he also said these operations were mostly caught early and did not manage to fool large numbers of real people. According to Nimmo, "We didn't generally see these operations getting more engagement because of their use of AI. For these operations, better tools don't necessarily mean better outcomes."
    • Long ago, I was in a networking class on a lab computer. The guy next to sarcastically told me to SHIFT+DELETE the C:\Windows folder. I said that I was sure Windows wouldn't allow such a thing (Windows 2000), and would either totally block the action or give some kind of dire warning. I was so confident that I tried it...not only was I wrong, but it didn't even give the standard "are you sure" warning, just went to town. I pressed cancel as quick as I could, but it was too late, shortly after, the system blue-screened and never booted again. I had to stay late and reinstall Windows for the teacher, but that ended up being a good thing, had great repour with him for the rest of the year, even got to help him get Active Directory setup in his lab.
    • My best decision: SHIFT+DELETE WINDOWS Then Installed Fedora Linux. Now I am a Happy Person
  • Recent Achievements

    • One Year In
      survivor303 earned a badge
      One Year In
    • Week One Done
      jbatch earned a badge
      Week One Done
    • First Post
      Yianis earned a badge
      First Post
    • Rookie
      GTRoberts went up a rank
      Rookie
    • First Post
      James courage Tabla earned a badge
      First Post
  • Popular Contributors

    1. 1
      +primortal
      419
    2. 2
      snowy owl
      182
    3. 3
      +FloatingFatMan
      182
    4. 4
      ATLien_0
      176
    5. 5
      Xenon
      136
  • Tell a friend

    Love Neowin? Tell a friend!