Contacts and Invoicing

[.:RoKsTaR:.]

Hey There

I'm tired of quickbooks and excel so I think it's about time to move over to
a database. I'd like to create a database for 50 clients whom I invoice each
month with one or more of my services.

My question is, where should I start? should I start out with a template
for the contacts or do it from scratch? How much of the contact info do I
separate?

I've already checked out the info on these sites and a series of videos by a
woman on you tube.

http://allenbrowne.com/tips.html
http://www.mvps.org/access

 

[John W. Vinson]

Hey There

I'm tired of quickbooks and excel so I think it's about time to move over to
a database. I'd like to create a database for 50 clients whom I invoice each
month with one or more of my services.

My question is, where should I start? should I start out with a template
for the contacts or do it from scratch? How much of the contact info do I
separate?

I've already checked out the info on these sites and a series of videos by a
woman on you tube.

If you're not pleased with Quickbooks (or Peachtree or MS Money or the like),
bear in mind that they are the product of many year - innumerable
person-years! - of programming and design and user feedback specifically
devoted to coming up with an accounting program.

If you feel that you can take a good but nonspecialized toolkit such as Access
and construct a program which not only does everything Quickbooks does but
ALSO does the additional things that you want it to accomplish... well, more
power to you! Just don't expect to have it up, running and debugged this month
(or probably this year), unless your expectations are markedly low or your
genius level exceptionally high.

On the other hand, if you're *JUST* trying to manage a modest number of
customers, services and invoices, and are willing to forgo a lot of the useful
things that Quicken handles (bank account reconciliation, online banking, tax
calculations, etc. etc.), and you're willing to start simple and then add to
it over the months and years... well, it can certainly be done and we'll be
glad to help. I'm ambivalent about starting from templates; they can be useful
guides or they can be straitjackets, depending on how they were created and
how you work with them; but it might be worth a try checking MS's website for
one that's at least pointing you in the right direction.

Good luck!

 

[.:RoKsTaR:.]

I've only ever used quickbooks for invoicing and opted for excel to manage my
books; it was just faster that way. However, quickbooks isn't worth it if
you're not using the whole thing and right now it has a lot of issues in W7.
I figured if I went the database route, it would give me more room in the
long run to expand and manage customer data.

My business is small, 50 students is about all I can handle from a time
perspective. I like the idea of using a database as it'll force me to
organize my resources better as I get more experience.

So any thoughts on the best way to start out?

 

[John W. Vinson]

I've only ever used quickbooks for invoicing and opted for excel to manage my
books; it was just faster that way. However, quickbooks isn't worth it if
you're not using the whole thing and right now it has a lot of issues in W7.
I figured if I went the database route, it would give me more room in the
long run to expand and manage customer data.

My business is small, 50 students is about all I can handle from a time
perspective. I like the idea of using a database as it'll force me to
organize my resources better as I get more experience.

So any thoughts on the best way to start out?

If you've already seen Crystal's video and the tutorials, all I can really say
is "dive in and try it". There are some good books - Rebecca Riordan's
_Designing Relational Database Systems_ is topnotch, there are many others.

Start by identifying the Entities - real-life persons, things or events - that
you need to manage. Make a list of the Attributes (atomic, distinct chunks of
information) that you need to know about each entity. Each type of Entity (a
student, an encounter with a student, a project) will get its own table; each
Attribute of that entity (a last name, an address, etc.) will be a field in
that table. An invoice might or might not be an entity in its own right; an
actual printed invoice will typically be generated by an Access Report,
incorporating data from several tables.

Feel free to post your proposed data model for suggestions and discussion.

 

[Mark Andrews]

You could download demos of my CRM Template or donation software, they both
are basic CRM systems that do invoicing.
The back-end databases are unlocked and you could steal the table design.
Use shift-Open to open the MDE for the CRM demo.

I designed the invoice screens/reports to be just like Quicken.

CRM systems are usually either "contact" based or "account" based. That is
most things are related directly to a contact or directly to an account (and
optionally a contact at that account).

A rough design would be tables for:
- Contacts (your customers, the humans)
- Companies (the companies contacts might work at)
- Invoices (the header of the invoice)
- Invoice lines (individual lines on an invoice)
- Products (what you are selling, user select a product/service when filling
out an invoice line and that supplies the defaults for that invoice line,
however the user can override the defaults)
- a few misc tables for lookups (states, countries, statuses etc...).
- you could also track payments made on those invoice or other things like
quotes, estimates etc....

Hope this helps give you some ideas,
Mark Andrews
RPT Software
http://www.rptsoftware.com
http://www.donationmanagementsoftware.com

 

[.:RoKsTaR:.]

Ok this is what I have so far, I figure each one of the quotations words
would be a table.

"Student" - Name, Gender, Birthday

"Addresses" - Address 1, Address 2, City, Province, Postal Code, Country

"Phone" - Number, Extension, Location

"Email" - Address, Location

"Item List" - Product, Price, Tax Status


Not sure where to put the names of Parents/Guardians, but I think it looks
good so far. At some point I may use this to track attendance too, but for
now I just want to invoice.

How's it look so far?

 

[.:RoKsTaR:.]

Thanks Mark, I'll take a look

 

[David W. Fenton]

However, quickbooks isn't worth it if
you're not using the whole thing and right now it has a lot of
issues in W7.

I didn't know QB was said to have problems with Win7, so I Googled
it. There's a FAQ here:

http://tinyurl.com/yfv6bl4 =>
http://support.quickbooks.intuit.com/support/Pages/KnowledgeBaseArtic
le/898435

The only things listed there as being a problem in Win7 are these:

- QuickBooks does not offer an option to email with Windows Live
Mail

- QuickBooks does not open after turning off Internet Explorer 8
(IE8)

- Google Desktop stops responding

- Error: [QB's PDF] Printer not activated error -20

- Script error appears when using Live Community search

- Google Desktop does not index files

All of these are things that I think oughtn't be a problem for
really using QB. I have always hated the way QB continually adds
more and more junk to the installation and here's perfect evidence
of why it's a bad idea to become dependent on any of that crap,
because Intuit doesn't control it.

I think Intuit is remarkably ineffective at creating a
properly-designed product (the whole run-with-elevated-permissions
thing is prima facie evidence of colossal incompetence on their
part), and their attitude about Win7 really annoys me. Properly
programmed applications don't break just because the version of
Windows changes.

This is one of the things I constantly advocate for in creating
Access apps -- don't make your app dependent on outside components
you can't count on being there. Bascially, that means using late
binding instead of adding references, and never using add-in ActiveX
controls (which are almost always just wrappers around functionality
available via the Windows API). Intuit doesn't seem to understand
this principle, and that's why their flagship app breaks with a new
version of Windows.

I wonder if it's possible for anybody to knock QB out of 1st place
as small business accounting package. It used to be simple, back in
the day, but it has gotten increasingly complex and larded up with
attempts to sell things. My clients who use it have gotten pretty
annoyed with it over the last few years and grumble about it, but
don't see any realistic alternative.

 

[John W. Vinson]

Ok this is what I have so far, I figure each one of the quotations words
would be a table.

"Student" - Name, Gender, Birthday

Split the name into FirstName and LastName so you can display them separately
or concatenate them on demand into a fullname; and store the date of birth
(e.g. #5/16/1946#) in a date/time field. You can easily calculate the birthday
anniversary and/or the age from this.

"Addresses" - Address 1, Address 2, City, Province, Postal Code, Country

"Phone" - Number, Extension, Location

"Email" - Address, Location

Unless your students will routinely have multiple addresses, phones, or emails
- or you will routinely have multiple students at an address - it's probably
not necessary to have separate tables for these; just include the fields in
the Students table.

"Item List" - Product, Price, Tax Status

EVERY table should - Must!! - have a Primary Key, e.g. StudentID, ProductID.
Access will try to persuade you that this should be an Autonumber; that's in
fact not obligatory but in this case it's probably satisfactory.

You will need at least one more table, assuming that you provide products to
your students and want to track sales:

Sales
SalesID <autonumber primary key>
StudentID <link to Students.StudentID, Long Integer if that field is an
autonumber>
SaleDate <date/time, default value =Now() is handy>
SalePrice <in case it's different from the list price, e.g. bulk or hardship
discount, price as sold if the item price changes>

Not sure where to put the names of Parents/Guardians, but I think it looks
good so far. At some point I may use this to track attendance too, but for
now I just want to invoice.

My church membership database has a Families table (with a FamilyID, address,
phone, email) related one to many to a Members table (without those fields but
just a FamilyID link).

How's it look so far?

Good start!

 

[.:RoKsTaR:.]

Yes it supposed to be easy to use, but really it's just time consuming and we
all know how valuable our time is My QB won't email or print to pdf and
after all the troubleshooting it still doesn't. QB doesn't offer support
unless you pay for it and the support they give is the same as what you can
google

I like the organization of a database and how you can keep building on it,
so as scary as access seems I'm gonna try it

 

[.:RoKsTaR:.]

Thanks John I have a few students with 2 addresses because of divorced
parents, but maybe I can just choose one as the main contact. I'll have to
keep phone and email separate as each one has lots of those

Your sales part when over my head a bit, but I get why i need a table for
that. I'm gonna try to start setting some of this up and see what happens.
Pretty scary! This is very new to me

 

[.:RoKsTaR:.]

So am I right in assuming that my students table will have the primary key
value and the other tables relating to that student will hold a foreign key
value?

How do I set these?

 

[John W. Vinson]

So am I right in assuming that my students table will have the primary key
value and the other tables relating to that student will hold a foreign key
value?

That's true of just about ANY relational database tables. Every table, without
exception, needs a Primary Key; tables which contain logically related
information should have relationships, linking the Primary Key of the "parent"
table to the corresponding foreign key in the "child" table.

How do I set these?

The Primary Key is set during table design, using the key icon. It can be just
one field or it can be multiple fields; in your case (so far) I don't see a
need for any multifield keys. A Foreign Key isn't a special kind of field and
it is not defined as such in any visible way in table design view; instead
it's defined by how it's used. You would use the Relationships window to drag
the primary key (student ID for example) from the Students table to the
corresponding StudentID field in the Addresses table. Note that the field
*name* is irrelevant; it's perfectly proper to have the foreign key fieldname
different than the primary key fieldname. For instance, some developers use a
naming convention prefixing the fieldnames with the name of the table (perhaps
abbreviated); they'd use (say) adrStudentID or adrStudentIDfk as the Foreign
Key field name in the Address table linked to the StudentID field.

THe datatype must match however - if the Primary Key is a 25 byte text field,
so must the foreign key be. An Autonumber is a special type of long integer so
its corresponding Foreign key fields must also be Long Integer (not
autonumber).

 

[Steve]

As a matter of convention, begin the name of all tables with "Tbl". For
example, TblStudent. Down the road you will be Very glad you did! Then as a
matter of convention, name your primary key the same as the table name
without the Tbl, ie, StudentID. Don't use any spaces in the table names or
primary key names. For foreign keys, always use the same field name as its
assosiated primary key, ie StudentID. An example of tables for you would be:
TblStudent
StudentID
<student fields>

TblStudentInvoice
StudentInvoiceID
StudentID (foreign key)
<student invoice fields>


Steve
(e-mail address removed)

 

[.:RoKsTaR:.]

OK, so my next step is learning how to control all these things in access.
Should I start with a book or DVD or is there another way? I can get a hold
of the Trainsignal Certification DVD's from a friends office if that helps

 

[John W. Vinson]

OK, so my next step is learning how to control all these things in access.
Should I start with a book or DVD or is there another way? I can get a hold
of the Trainsignal Certification DVD's from a friends office if that helps

<g> Trainspotting is a whole different specialty...

Try some of these resources. Crystal's video is good, and there are a bunch of
other tutorials and links:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[.:RoKsTaR:.]

Hey John,

So am I right in thinking that the Student table is the parent table of all
my other tables so far?

 

[John W. Vinson]

Hey John,

So am I right in thinking that the Student table is the parent table of all
my other tables so far?

I don't know, because I don't know your business model or data model. I'd
guess it is NOT, however. In a traditional student/course model (which may or
may not apply here!) a Student is one type of entity, a Course is a different
type of entity; neither is a child of the other. Rather there would be an
Enrollment table, related one to many to both Students and Courses.

Utility lookup tables would likewise not be "children" of the student table.

Perhaps you could post a bit more detail about your current table structures
and the real-life entities that they're intended to model.

 

[.:RoKsTaR:.]

So far it's this:

"Student"
Name
Gender
Birthday

"Address"
Gaurdian
Address
City
Province
Postal Code
Country

"Phone"
Number
Extension
Location

"Email"
Address
Location

I'm a private guitar teacher, so my database will only have about 50
contacts to start. So I'm assuming all of these are linked to the primary
key in the student database which would have the fields studentid, namefirst,
namelast, gen, bobmo, dobda, dobyr.

Do the other tables relating to that one just have the fields that I listed
above and a foreign key relating back to the parent student table?

 

[.:RoKsTaR:.]

Actually now that I think of it, I may have messed this up....I have to
somehow accommodate students with 2 separate residences (divorced parents)

So maybe I need a guardian table that links to address, phone, and email and
then finally back to Student.


Soo confusing