• 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

    • It was easy enough in Task Manager Performance tab already.
    • GeForce NOW adds support for 25 games in June, including Rematch and Dune: Awakening by Pulasthi Ariyasinghe A new month is here, and Nvidia is starting it off with a big GeForce NOW announcement as usual. The latest reveal has support for 25 games that are incoming in June alone, with some highlights including Rematch, The Alters, FBC: Firebreak, Dune: Awakening, and even the Borderlands trilogy from Gearbox and 2K. Just this week alone, Nvidia is adding support for the following ten games for GeForce NOW subscribers: Symphonia (New release on Xbox, available on PC Game Pass, June 3) Pro Cycling Manager 25 (New release on Steam, June 5) Tour de France 2025 (New release on Steam, June 5) Dune: Awakening – Advanced Access (New release on Steam, June 5) 7 Days to Die (Xbox) Clair Obscur: Expedition 33 (Epic Games Store) Cubic Odyssey (Steam) Drive Beyond Horizons (Steam) Police Simulator: Patrol Officers (Xbox, available on PC Game Pass) Sea of Thieves (Battle.net) Nvidia also has plans to add a bunch more games in the rest of June, which is when most of the biggest new releases are coming: Dune: Awakening (New release on Steam, June 10) MindsEye (New release on Steam, June 10) The Alters (New release on Steam and Xbox, available on PC Game Pass, June 13) Architect Life: A House Design Simulator (New release on Steam, June 19) Crime Simulator (New release on Steam, June 17) FBC: Firebreak (New release on Steam and Xbox, available on PC Game Pass, June 17) Lost in Random: The Eternal Die (New release on Steam and Xbox, available on PC Game Pass, June 17) Broken Arrow (New release on Steam, June 19) REMATCH (New release on Steam and Xbox, available on PC Game Pass, June 19) DREADZONE (New release on Steam, June 26) System Shock 2: 25th Anniversary Remaster (New release on Steam, June 26) Borderlands Game of the Year Enhanced (Steam) Borderlands 2 (Steam and Epic Games Store) Borderlands 3 (Steam and Epic Games Store) Easy Red 2 (Steam) The company has a tendency to add many more games to its cloud gaming service outside of these early announcements, so check back as weeks go by to see what's new. Steam Deck owners recently received a better way of using GeForce NOW too, all thanks to a dedicated app. As summer continues, don't forget that the GeForce NOW 40% off sale is still active too, with Nvidia cutting the price of the Performance membership plan until July. As always, keep in mind that unlike subscription services like Game Pass, a copy of a game must be owned by the GeForce NOW member (or at least have a license via PC Game Pass) to start playing via Nvidia's cloud servers.
    • Thought I'd quote myself as reference to what was happening yesterday. I wasn't getting the pop up then in Firefox, but I did just now using it.
    • With DARPA the military can do their own research and then the tech can enter the public domain and benefit the people. If we use public money to buy privatized tech then we don't get that benefit and even worse we will probably pay higher prices in the end. Unfortunately we are ditching NASA for private tech. If you look at all of the technologies developed by NASA that benefit us, you can see why going private can be a huge loss and jack up prices for consumers when private patents are involved. This could have a detrimental effect on innovation by monopolizing certain advancements and reduce access to advancements for the average person, even though our tax dollars would be funding these advancements.
  • Recent Achievements

    • 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
    • Reacting Well
      James courage Tabla earned a badge
      Reacting Well
  • Popular Contributors

    1. 1
      +primortal
      405
    2. 2
      +FloatingFatMan
      181
    3. 3
      snowy owl
      175
    4. 4
      ATLien_0
      170
    5. 5
      Xenon
      135
  • Tell a friend

    Love Neowin? Tell a friend!