trying to set up a database for billing customers and tracking ser

[seashell0304]

I am trying to set up a database for my husband's lawnmowing business. I
have a customers table. I would like to set another table that would track
the date, customers, and amount charged. This one I would like to be simple,
to where he could just enter in the lawns he did on that date, and the amount
charged.
Then a third table for invoicing. I am completely stumped. I have used
the wizards, but I can't figure out how to make the second table that will
record what was done on each date.
Could someone please give me some advise to get going. I am wondering what
I should use as the primary key for the second one- the date? or the
customer Id?

 

[Evi]

I presume you have some system for recording expenses - some of these can be
raked back when you pay your tax so they need to be itemised - The sort of
structure that you may want to use for jobs is

A Customer table - containing only customer details - name address
telephone, how to reach the address, billing name/address - if different
from the actual address
CustID (Primary Key field)
CFName
CSurName
etc

TblJob
JobID
AppointMade (date appointment was made - Default value is Date()
AppointDate
AppointTime
CustID - linked from TblCustomer
JobDetails
Completed (date completed)


TblInvoice
InvID
JobID (linked from TblJob)
InvNumber (invoice number)
Charged (amount invoiced)
Paid (amount paid - which sadly, may not be the amount invoiced)
HrsWorked
RcptNo (receipt number given to customer for payment)

TblInvoiceDetails
InvDetID
InvID (linked from TblInvoice
InvDetail eg mowed lawn, tidied borders

Evi

 

[Evi]

My personal recommendation is that you use an Autonumber primary key for
your tables - they look after themselves and you can always hide them in
your reports and forms - other people hate this and prefer all sorts of
other solutions but the Autonumber PK keeps things simple and is easy to
refer to if you later need to code anything.
BTW I've assumed in my structure that you would present one invoice per job.
If this isn't true then the structure will be slightly different
Evi