MS Access Newbie

[MNM2903]

Hi all,

Im new to the forums here basically my dad has a business cleaning wheelie bins and has a customer base of 2.6k.

Hes currently using excel spreadsheets and puts all data in manually which is basically Name, Address, Frequency they have the bin cleaned I.E Weekly, Fortnightly, Monthly. It also shows how much they have outstanding and wether they have paid or not. Each clean is £2.50 i was just wondering if anyone could point me in the direction of a tutorial or even if you can get templates of databases to hold this information with options to add new customers etc.

I would love to just give someone the info and what i want but unfortunatly i dont have the funds for that ha so im just learning myself and doing the best i can to help him organise his workload better.

Any advice/help would be greatly appreciated.

Thanks,

 

[mole999]

spend some time working over the northwind sample database, that has customers and other areas to get a feel of

 

[Smitty]

Welcome to the Board!

There are also several pre-built Customer database templates if you goto File-->New.

The biggest thing is to thoughtfully layout your tables will all of the fields you'll ever want (it's easier to structure them now even if you don't need them than it is to add them later). Here's an idea for some tables:

Customers:
CustomerID (auto-number field)
Company Name
Contact Name (Last, First)
Phone
Address
etc.

Bin Types
Bin ID
Bin Size
Price

Frequency
FrequencyID (auto-number)
Frequency (Daily, Weekly, Fortnight, etc.)

Details
DetailID (auto-number)
CustomerID
Bin Size
Bin Count
Frequency

Transactions
TransactionID (auto-number)
CustomerID
Date
BinID
Cost

Once you have your tables set up you can pretty quickly create some bound forms using the Form Wizard and use ComboBoxes for your selections that are based on the ID number. Let's say you want to select company name, you'd use a combo box with a query for the source, add ID & Company name, then the Bound Column would be 1 (ID), and in the display settings you set it for 2 columns with 0, x.x for the width so you only see the customer name, but the ID is what's captured.

HTH,

 

[MNM2903]

thanks, ill look at this tonight. In my head im thinking if i create seperate tables for Customer details, Then tables for his pricing, tables for his rounds (Monday-Friday week 1) then Monday-Friday week 2) etc, etc, ill be able to link the tables somehow and put some drop downs in so i can show wether customers have paid or not paid and also how much they have outstanding then when it comes to creating sheets for the employees to go collecting money in i can simply export all who have not paid to an excel sheet for them.

 

[Smitty]

tables for his rounds (Monday-Friday week 1) then Monday-Friday week 2)

You want to avoid putting transactional details that are separated only by date into separate tables! I see people do this in Excel all the time with a sheet for each week/month, etc. It sounds logical for them, but if you look at the grander scheme of things you lose the ability to functionally analyze your data if it's all over the place. Remember, databases are structured to hold lots of data and they're good with dates, so it's better to keep relevant data in one place.

Take a look at Access MVP Crystal Long's Access Basics tutorial: Access Basics

It's a really good place to start not only understanding Access, but the concepts behind relational database structure.

As for aging reports, that can be done with simple queries. If you have a Paid? field you can simply use that as query criteria.

Do you use any kind of accounting software? If not, it might be worthwhile to look at something like QuickBooks instead of building something new.

 

[MNM2903]

does quickbooks allow me to create rounds and job sheets etc or is it simply for finances? i have been looking at cleaner planner its designed for window cleaners but the same applies with wheelie bin cleaning and its basically an online database and they seem to cover off everything thats required. im very tempted as a novice when it comes to excel and access just going with these. im on a free trial at the moment just waiting for them to upload my customer info i gave them and ill see how well it works.

 

[Smitty]

QuickBooks is pretty much just for finances/invoices/billing/payroll, etc.

If you've found a bespoke system, then I'd see how that goes. Price and functionality are big issues to consider, even if you don't put a price tag on your own time to develop something it's still a cost.