Excel Grinding to a halt - Looking for an Access Option

[rich_bell20]

Hello all once again!

Your much needed help and guidance is required once again if any of you would be so kind. I currently manage my "Workflow" on a rather detailed spreadsheet, with a number of filters and over 1,800 records.

My excel spreadsheet is shared between 10 people constantly updating records and the whole thing is starting to grind to a halt. I'd love to switch over to a much more secure Access Database, but there's one small problem - I don't have a clue where to begin!

My small team views the current spreadsheet through different filters. Customers' details are entered, and their 'payment status' is updated. The choices are 'unpaid', 'part paid' 'paid', cancelled'. The payment amounts are then entered, and how much is owed. A job status is also assigned to that customers product. 'Not started', 'in progress', 'on hold', 'inform customer', 'completed'

The next person using the excel spreadhseet will only be able to see records that have paid some amount, so only the 'part paid' and 'paid' records will show. They will also be able the records based on their current status, so we can select which records are not started, in progress etc. Notes can be assign to each record also.

I've had a play around with tables and what i guess i need to look at doing first, is created various list boxes/combo boxes that are dependent on each other.

I would greatly appreciate any help so that i can make this dream a reality! Advice along the way, or if somebody could create a basic database that i can work with and add to that would be superb.

Regards,

Richard.

 

[Singh400]

I'd start with a pen and paper mate. Start with the basic table(s), like tblCustomer and tblFinance. Remember to keep different types of data seperate. Payment details shouldn't be in the same table as Customer details. They should be linked in Relationship Manager (via a PK). And then if you need to view a particular set of data, use a frm based on a qry to pull that info out.

Most important thing is table structure, create your basic form to test out the db.

 

[Rabbie]

Excellent advice from Singh. It is all to easy when you come from Excel to Access to keep thinking in spreadsheet mode. Access is not a more advanced version of Excel. It is a relational database system and you need to understand data Normalization. failure to normalise your data will give you problems later on - usually at the most inconvenient times.

Also remember that Access can only tell you what you have already told Access. And if you can't work out how to do it with pencil and paper then you won't be able to do it in Access.

 

[The_Doc_Man]

Rabbie! You're preaching my gospel!

Rich_Bell, you need to do a data analysis and there is no FAST solution to your problem. I.e. no toss-together solution. In Excel, nearly anything goes, but eventually (as you have discovered), you run into issues of sharing.

Let's get it on the table right now - Access and Excel will both have issues with 10 users sometimes. Not everytime, but sometimes. It has to do with where they are working and the fact that Access has locking issues when everyone is working in the same physical vicinity of the same shared database.

I'm going to make some suggestions for research. Both web searchs and this forum's search function (near the top of the displayed page on the page's menu bar, NOT THE WINDOWS MENU BAR) can lead to good things. Don't forget that your first line of help is always Access Help, which (despite its clunkiness) is pretty good in what it says and what it shows you.

Topics you will need to address:

Database normalization - Access Help; Wikipedia.ORG article; several college sites in .EDU domain are all good starting places.

Securing a Database and also Workgroup Security. Search this forum.

Record Locking and also Form Locking. See also keywords "Optimistic" and "Pessimistic" - Access Help and search this forum.

Combo Boxes (as a means to assure that everyone uses the "right" spelling of the statuses). Access Help and search this forum.

As to the "filtration" part, you will need some fairly simple queries. Also, always remember that forms and reports work equally well from tables and queries, but with queries you have more flexibility. And a little-appreciated fact for newcomers to Access: If you use a Report or Form Wizard, odds are it will build a "hidden" query for you anyway even if directly referenced a table when starting the wizard.

From the general discussion you gave us, you will also need to set up some relationships. So read up on Access Relationships in the Access Help arena and then search this forum. Topics that you will need to understand include one-to-many and many-to-one relationships (which you would get from the "Database Normalization" topic) and many-to-many (which you have to piece together by searching this forum for the topic of "Many-to-many" relationships and Junction tables).

That's a lot of reading. Might take you a day or two to digest it. (Hint: Bring antacid tablets...) When done reading, arrange to work on a dry-erase board. Get some different-colored DE markers and a pack of assorted sticky-note pads. Several pads.

When you understand normalization well enough, this next advice will make sense. You are going to simulate the tables you have to build in order to normalize your data sets. So you will draw some table headers on the DE board. Make some sample data for each table on the sticky note pads. Draw lines between the table contents to show various relationships. Here is where the design gets VERY specific:

Now look at the reports and forms you need/want. For each one, look at the board to see your elementary tables. (NEVER populate a query from this board...). See if what you need is present in one table. (Odds are,... NOT.) For each such report/form that requires a data mix, use one of the odd colored DE markers and draw lines from contributing tables to some common point. You've just identified a query that will probably drive whatever form/report you were considering. I might also consider getting a digital camera (borrowing one) and taking pix after each session, then wiping the extra lines for the next form/report and starting over again.

Make a binder. Print your pix on any color-capable printer, even if only in draft mode. Keep the pix as part of your design documentation. This will become a solid-gold reference down the road during serious head-scratch sessions as someone asks, "What were we THINKING when we did this...?"

 

[neileg]

Rabbie! You're preaching my gospel!

We have seen the light, we tread in the path of greatness.

 

[Rabbie]

We have seen the light, we tread in the path of greatness.

Indeed we do. I am always happy to pass on the few pearls of wisdom I have managed to glean from the many cast by the Doc_Man

 

[Singh400]

Securing a Database and also Workgroup Security. Search this forum.

Handy link to this fantastic PDF. Might take a couple of reads to fully understand it, but well worth it.