Quotation & Invoicing Database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello All,

I am attempting to create a simple database that produces quotations and
invoices. I have started with a customer table and the job table. i dont need
a products table becuase i just want to use a description for the work that
is being carried out, so i have put this in the jobs table. I have created a
"Quote, "Invoiced" and "Paid" fields in the jobs table and these are YES/NO
datatypes. Therefore this can be used to tell me which jobs i have quoted,
invoiced and which jobs have been paid for.

i can put customers in, jobs for them customers in and i have got these on
the same form but i dont know how i can create a quote, and an invoice. i
have tried to use reports by clicking a command box which will display the
record that is being displayed but i had not luck and i dont know what i can
do or what i am doing wrong.

Can some one help me or guide me to what i should be doing. I hope what i
have stated is clear. Please help
 
There's quite a bit to setting up a database like this.

You need a Customer table seprate from your Quote table, becuase one
customer can have several quotes over the years.

Then you need a QuoteDetail table, because one quote can have many line
items.

For an example of how to put this kind of thing together, open the Northwind
sample database that installs with Access, and choose Relationships on the
Tools menu. The Customers, Orders and OrderDetail tables are a similar kind
of idea.

Once you have that sorted out, I suggest you need a Job and JobDetail table
as well. The actual job can be different from the original quote, so when a
quote succeeds you need to be able to retain the quote, but actually charge
something different for the job (e.g. where the client accepts only part of
the quote, or asks for extras.)

Invoices and payments received are another issue again. Your structure needs
to cope with:
- Multiple invoices for one job (e.g. big job with progress payments);
- Multiple payments for one invoices (client pays over time);
- Multiple invoices in one payment (client pays several invoices at once);
- Prepayments (client gives you a check before you write an invoice.)

You may also need to track your purchases for jobs, but that's another story
again.
 
Moody

The best thing to do is start the design of your database all over. ask
yourself what data you want to see on your forms and reports. Translate the
required data into fields of 1 or more tables.
Make queries and forms to select and combine the fields on your reports or
form.

Nanne
 
Thanks for the reply but i have got a little confused, maybe i havent
understood something.

I think i know what you are trying to tell me and i agree with you but i
cant get my head around to doing so, it seems very difficult.

Really sorry to sound like an idiot but could you try to explain it a bit
more. I like your idea and would like it to be implemented, preferably with
out the use of backend SQL.

the line items that you mentioned are not really an issue in this database
becuase the work being carried out for the customer is not a specfic item
that has been stated, i would just use a description field for this and set
the price i choose for this..

I really appreciate your help and i look forward to your reply.

Thanks
 
Okay, we will assume there will never, ever be any invoice that needs to
list 2 items, and no quote will ever need to list 2 alternatives.

Consider these four tables:

Customer table:
CustomerID AutoNumber primary key
Surname Text
FirstName Text
Address Text
City Text
Zip Text
...

Quote table:
QuoteID AutoNumber primary key
CustomerID Number The customer this quote is for
QuoteDate Date/Time When quote was given.
Duration Number number of days the quote is valid
Descrip Text (or memo) what this is for

Invoice table:
InvoiceID AutoNumber primary key
CustomerID Number who is to pay this invoice
InvoiceDate Date/Time when the invoice was sent
Terms Number number of days before due.
Descrip Text (or memo) what this is for
AmountEx Currency amount of this invoice, without tax.
TaxRate Number (Double) Percent tax rate to add.
QuoteID Number A way to identify the quote this came
from.
Comment Memo Comments not printed on invoice

Receipt table:
ReceiptID AutoNumber primary key
CustomerID Number who paid this amount
ReceiptDate Date/Time when received.
Amount Currency amount received.
Comment Memo description of what for.

This is the simplest solution that copes with everything, because the
receipts are not tied to invoices. Therefore anyone can pay you any amount
any time: partial invoices, multiple invoices, parts of multiples, and even
uninvoiced amounts. You then calculate how much as customer owes as:
DSum("[AmountEx] * (1+[TaxRate])", "Invoice", "[CustomerID] = 99")
- DSum("Amount", "Receipt", "[CustomerID] = 99")

That will actually work for years with many thousands of invoices per
client, so is suitable for many databases where Access is appropriate.
Beyond that, you are getting into double-entry accounting, with periods that
you close off with closing and opening balances, which may be adding
complexity you don't need.
 
PS: Obviously the Quote table needs the Amount and TaxRate fields as well.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen Browne said:
Okay, we will assume there will never, ever be any invoice that needs to
list 2 items, and no quote will ever need to list 2 alternatives.

Consider these four tables:

Customer table:
CustomerID AutoNumber primary key
Surname Text
FirstName Text
Address Text
City Text
Zip Text
...

Quote table:
QuoteID AutoNumber primary key
CustomerID Number The customer this quote is for
QuoteDate Date/Time When quote was given.
Duration Number number of days the quote is valid
Descrip Text (or memo) what this is for

Invoice table:
InvoiceID AutoNumber primary key
CustomerID Number who is to pay this invoice
InvoiceDate Date/Time when the invoice was sent
Terms Number number of days before due.
Descrip Text (or memo) what this is for
AmountEx Currency amount of this invoice, without tax.
TaxRate Number (Double) Percent tax rate to add.
QuoteID Number A way to identify the quote this came
from.
Comment Memo Comments not printed on invoice

Receipt table:
ReceiptID AutoNumber primary key
CustomerID Number who paid this amount
ReceiptDate Date/Time when received.
Amount Currency amount received.
Comment Memo description of what for.

This is the simplest solution that copes with everything, because the
receipts are not tied to invoices. Therefore anyone can pay you any amount
any time: partial invoices, multiple invoices, parts of multiples, and
even uninvoiced amounts. You then calculate how much as customer owes as:
DSum("[AmountEx] * (1+[TaxRate])", "Invoice", "[CustomerID] = 99")
- DSum("Amount", "Receipt", "[CustomerID] = 99")

That will actually work for years with many thousands of invoices per
client, so is suitable for many databases where Access is appropriate.
Beyond that, you are getting into double-entry accounting, with periods
that you close off with closing and opening balances, which may be adding
complexity you don't need.

Moody said:
Thanks for the reply but i have got a little confused, maybe i havent
understood something.

I think i know what you are trying to tell me and i agree with you but i
cant get my head around to doing so, it seems very difficult.

Really sorry to sound like an idiot but could you try to explain it a bit
more. I like your idea and would like it to be implemented, preferably
with
out the use of backend SQL.

the line items that you mentioned are not really an issue in this
database
becuase the work being carried out for the customer is not a specfic item
that has been stated, i would just use a description field for this and
set
the price i choose for this..

I really appreciate your help and i look forward to your reply.

Thanks
Click to expand...
Click to expand...
 
Hello Allen, thanks for the reply,

I cant thank you enough fopr this information, at the moment, i am
celebrating my christmas with family and i will look at this and defnantly
get back to you. i am just replying, so that you dont think that i dont
appreciate your help.

Thanks and speak to you soon.

Allen Browne said:
PS: Obviously the Quote table needs the Amount and TaxRate fields as well.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen Browne said:
Okay, we will assume there will never, ever be any invoice that needs to
list 2 items, and no quote will ever need to list 2 alternatives.

Consider these four tables:

Customer table:
CustomerID AutoNumber primary key
Surname Text
FirstName Text
Address Text
City Text
Zip Text
...

Quote table:
QuoteID AutoNumber primary key
CustomerID Number The customer this quote is for
QuoteDate Date/Time When quote was given.
Duration Number number of days the quote is valid
Descrip Text (or memo) what this is for

Invoice table:
InvoiceID AutoNumber primary key
CustomerID Number who is to pay this invoice
InvoiceDate Date/Time when the invoice was sent
Terms Number number of days before due.
Descrip Text (or memo) what this is for
AmountEx Currency amount of this invoice, without tax.
TaxRate Number (Double) Percent tax rate to add.
QuoteID Number A way to identify the quote this came
from.
Comment Memo Comments not printed on invoice

Receipt table:
ReceiptID AutoNumber primary key
CustomerID Number who paid this amount
ReceiptDate Date/Time when received.
Amount Currency amount received.
Comment Memo description of what for.

This is the simplest solution that copes with everything, because the
receipts are not tied to invoices. Therefore anyone can pay you any amount
any time: partial invoices, multiple invoices, parts of multiples, and
even uninvoiced amounts. You then calculate how much as customer owes as:
DSum("[AmountEx] * (1+[TaxRate])", "Invoice", "[CustomerID] = 99")
- DSum("Amount", "Receipt", "[CustomerID] = 99")

That will actually work for years with many thousands of invoices per
client, so is suitable for many databases where Access is appropriate.
Beyond that, you are getting into double-entry accounting, with periods
that you close off with closing and opening balances, which may be adding
complexity you don't need.

Moody said:
Thanks for the reply but i have got a little confused, maybe i havent
understood something.

I think i know what you are trying to tell me and i agree with you but i
cant get my head around to doing so, it seems very difficult.

Really sorry to sound like an idiot but could you try to explain it a bit
more. I like your idea and would like it to be implemented, preferably
with
out the use of backend SQL.

the line items that you mentioned are not really an issue in this
database
becuase the work being carried out for the customer is not a specfic item
that has been stated, i would just use a description field for this and
set
the price i choose for this..

I really appreciate your help and i look forward to your reply.

Thanks

:

There's quite a bit to setting up a database like this.

You need a Customer table seprate from your Quote table, becuase one
customer can have several quotes over the years.

Then you need a QuoteDetail table, because one quote can have many line
items.

For an example of how to put this kind of thing together, open the
Northwind
sample database that installs with Access, and choose Relationships on
the
Tools menu. The Customers, Orders and OrderDetail tables are a similar
kind
of idea.

Once you have that sorted out, I suggest you need a Job and JobDetail
table
as well. The actual job can be different from the original quote, so
when a
quote succeeds you need to be able to retain the quote, but actually
charge
something different for the job (e.g. where the client accepts only part
of
the quote, or asks for extras.)

Invoices and payments received are another issue again. Your structure
needs
to cope with:
- Multiple invoices for one job (e.g. big job with progress payments);
- Multiple payments for one invoices (client pays over time);
- Multiple invoices in one payment (client pays several invoices at
once);
- Prepayments (client gives you a check before you write an invoice.)

You may also need to track your purchases for jobs, but that's another
story
again.


I am attempting to create a simple database that produces quotations
and
invoices. I have started with a customer table and the job table. i
dont
need
a products table becuase i just want to use a description for the work
that
is being carried out, so i have put this in the jobs table. I have
created
a
"Quote, "Invoiced" and "Paid" fields in the jobs table and these are
YES/NO
datatypes. Therefore this can be used to tell me which jobs i have
quoted,
invoiced and which jobs have been paid for.

i can put customers in, jobs for them customers in and i have got
these on
the same form but i dont know how i can create a quote, and an
invoice. i
have tried to use reports by clicking a command box which will display
the
record that is being displayed but i had not luck and i dont know what
i
can
do or what i am doing wrong.

Can some one help me or guide me to what i should be doing. I hope
what i
have stated is clear. Please help
Click to expand...
Click to expand...
Click to expand...
 
Thanks again Allen,

How do i know which quotes have been paid and possibly move them to another
storage.

could it be possible to move the quote, so that it can not be manipulated
once paid. so i want the quotes table to trigger once the invoice for that
quote has been paid.

i have decided not to use the function of part payment becuase i am going to
assume that customers will always pay me in full and not in installments. how
would i do a query to tell me which quotes need paying and which quotes got
invoiced.

quotes means that the customer asked for a job and is unsure if he wants
the job but invoices means that i did the job.

thank you very much for your help and i look forward to your reply, hope i
dont sound silly.

Allen Browne said:
PS: Obviously the Quote table needs the Amount and TaxRate fields as well.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen Browne said:
Okay, we will assume there will never, ever be any invoice that needs to
list 2 items, and no quote will ever need to list 2 alternatives.

Consider these four tables:

Customer table:
CustomerID AutoNumber primary key
Surname Text
FirstName Text
Address Text
City Text
Zip Text
...

Quote table:
QuoteID AutoNumber primary key
CustomerID Number The customer this quote is for
QuoteDate Date/Time When quote was given.
Duration Number number of days the quote is valid
Descrip Text (or memo) what this is for

Invoice table:
InvoiceID AutoNumber primary key
CustomerID Number who is to pay this invoice
InvoiceDate Date/Time when the invoice was sent
Terms Number number of days before due.
Descrip Text (or memo) what this is for
AmountEx Currency amount of this invoice, without tax.
TaxRate Number (Double) Percent tax rate to add.
QuoteID Number A way to identify the quote this came
from.
Comment Memo Comments not printed on invoice

Receipt table:
ReceiptID AutoNumber primary key
CustomerID Number who paid this amount
ReceiptDate Date/Time when received.
Amount Currency amount received.
Comment Memo description of what for.

This is the simplest solution that copes with everything, because the
receipts are not tied to invoices. Therefore anyone can pay you any amount
any time: partial invoices, multiple invoices, parts of multiples, and
even uninvoiced amounts. You then calculate how much as customer owes as:
DSum("[AmountEx] * (1+[TaxRate])", "Invoice", "[CustomerID] = 99")
- DSum("Amount", "Receipt", "[CustomerID] = 99")

That will actually work for years with many thousands of invoices per
client, so is suitable for many databases where Access is appropriate.
Beyond that, you are getting into double-entry accounting, with periods
that you close off with closing and opening balances, which may be adding
complexity you don't need.

Moody said:
Thanks for the reply but i have got a little confused, maybe i havent
understood something.

I think i know what you are trying to tell me and i agree with you but i
cant get my head around to doing so, it seems very difficult.

Really sorry to sound like an idiot but could you try to explain it a bit
more. I like your idea and would like it to be implemented, preferably
with
out the use of backend SQL.

the line items that you mentioned are not really an issue in this
database
becuase the work being carried out for the customer is not a specfic item
that has been stated, i would just use a description field for this and
set
the price i choose for this..

I really appreciate your help and i look forward to your reply.

Thanks

:

There's quite a bit to setting up a database like this.

You need a Customer table seprate from your Quote table, becuase one
customer can have several quotes over the years.

Then you need a QuoteDetail table, because one quote can have many line
items.

For an example of how to put this kind of thing together, open the
Northwind
sample database that installs with Access, and choose Relationships on
the
Tools menu. The Customers, Orders and OrderDetail tables are a similar
kind
of idea.

Once you have that sorted out, I suggest you need a Job and JobDetail
table
as well. The actual job can be different from the original quote, so
when a
quote succeeds you need to be able to retain the quote, but actually
charge
something different for the job (e.g. where the client accepts only part
of
the quote, or asks for extras.)

Invoices and payments received are another issue again. Your structure
needs
to cope with:
- Multiple invoices for one job (e.g. big job with progress payments);
- Multiple payments for one invoices (client pays over time);
- Multiple invoices in one payment (client pays several invoices at
once);
- Prepayments (client gives you a check before you write an invoice.)

You may also need to track your purchases for jobs, but that's another
story
again.


I am attempting to create a simple database that produces quotations
and
invoices. I have started with a customer table and the job table. i
dont
need
a products table becuase i just want to use a description for the work
that
is being carried out, so i have put this in the jobs table. I have
created
a
"Quote, "Invoiced" and "Paid" fields in the jobs table and these are
YES/NO
datatypes. Therefore this can be used to tell me which jobs i have
quoted,
invoiced and which jobs have been paid for.

i can put customers in, jobs for them customers in and i have got
these on
the same form but i dont know how i can create a quote, and an
invoice. i
have tried to use reports by clicking a command box which will display
the
record that is being displayed but i had not luck and i dont know what
i
can
do or what i am doing wrong.

Can some one help me or guide me to what i should be doing. I hope
what i
have stated is clear. Please help
Click to expand...
Click to expand...
Click to expand...
 
Answers in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Moody said:
Thanks again Allen,

How do i know which quotes have been paid and possibly move them to
another
storage.
Click to expand...

You can only charge the customer if they agree to the quote. That means you
need to create an *invoice*, and the question is which invoices have been
paid. (Even if you do charge for quotes, a rejected quote would still need
an invoice for the quote charge, not the full amount in the quote.)

You don't want to move the quotes into an archive table once they have an
invoice, because it makes them too hard to track/query. Additionally, it
doesn't solve the issue of rejected quotes (which never get invoiced). If
you want to know how to do it anyway, see:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html
could it be possible to move the quote, so that it can not be manipulated
once paid. so i want the quotes table to trigger once the invoice for that
quote has been paid.
Click to expand...

You can lock the form so that the quote record is no longer editable after
an invoice has been created. To to that, you would use the Current event of
your quotes form to DLookup() the Invoice table and see if there is a match.
If so, set the AllowEdits property of the form to No.

The event procedure code would be something like this:
Private Sub Form_Current
Dim bAllow as Boolean
Dim strWhere As String
If Not Me.NewRecord Then
strWhere = "QuoteID = " & Me.QuoteID
bAllow = IsNull(DLookup("QuoteID", "Invoice", strWhere)
If Me.AllowEdits <> bAllow Then
Me.AllowEdits = bAllow
End If
End If
End Sub

Note that this locks the quote once there is an invoice (regardless of
whether the invoice is paid or not.)
i have decided not to use the function of part payment becuase i am going
to
assume that customers will always pay me in full and not in installments.
how
would i do a query to tell me which quotes need paying and which quotes
got
invoiced.
Click to expand...

If you can assume that there will *never* be part payments, prepayments, or
payment of multiple invoices together, you could get away with adding 2
extra fields to your Invoice table:
Received Currency Amount customer has paid for this invoice.
RecDate Date/Time When the client paid this invoice.

You can then find the invoice, and enter the amount received when the client
pays you. The invoice is fully paid, when the Received equals the amount of
the invoice (including any tax.)
quotes means that the customer asked for a job and is unsure if he wants
the job but invoices means that i did the job.

thank you very much for your help and i look forward to your reply, hope i
dont sound silly.
Click to expand...

Not at all. These are really important questions. Too many people just ask,
"How can I get this report?" without asking the crucial questions about how
the data should be stored.
Allen Browne said:
Okay, we will assume there will never, ever be any invoice that needs
to
list 2 items, and no quote will ever need to list 2 alternatives.

Consider these four tables:

Customer table:
CustomerID AutoNumber primary key
Surname Text
FirstName Text
Address Text
City Text
Zip Text
...

Quote table:
QuoteID AutoNumber primary key
CustomerID Number The customer this quote is for
QuoteDate Date/Time When quote was given.
Duration Number number of days the quote is
valid
Descrip Text (or memo) what this is for

Invoice table:
InvoiceID AutoNumber primary key
CustomerID Number who is to pay this invoice
InvoiceDate Date/Time when the invoice was sent
Terms Number number of days before due.
Descrip Text (or memo) what this is for
AmountEx Currency amount of this invoice, without tax
TaxRate Number (Double) Percent tax rate to add.
QuoteID Number The quote this came from.
Comment Memo Comments not printed on invoice

Receipt table:
ReceiptID AutoNumber primary key
CustomerID Number who paid this amount
ReceiptDate Date/Time when received.
Amount Currency amount received.
Comment Memo description of what for.

This is the simplest solution that copes with everything, because the
receipts are not tied to invoices. Therefore anyone can pay you any
amount
any time: partial invoices, multiple invoices, parts of multiples, and
even uninvoiced amounts. You then calculate how much as customer owes
as:
DSum("[AmountEx] * (1+[TaxRate])", "Invoice", "[CustomerID] = 99")
- DSum("Amount", "Receipt", "[CustomerID] = 99")

That will actually work for years with many thousands of invoices per
client, so is suitable for many databases where Access is appropriate.
Beyond that, you are getting into double-entry accounting, with periods
that you close off with closing and opening balances, which may be
adding
complexity you don't need.

Thanks for the reply but i have got a little confused, maybe i havent
understood something.

I think i know what you are trying to tell me and i agree with you but
i
cant get my head around to doing so, it seems very difficult.

Really sorry to sound like an idiot but could you try to explain it a
bit
more. I like your idea and would like it to be implemented, preferably
with
out the use of backend SQL.

the line items that you mentioned are not really an issue in this
database
becuase the work being carried out for the customer is not a specfic
item
that has been stated, i would just use a description field for this
and
set
the price i choose for this..

I really appreciate your help and i look forward to your reply.

Thanks

:

There's quite a bit to setting up a database like this.

You need a Customer table seprate from your Quote table, becuase one
customer can have several quotes over the years.

Then you need a QuoteDetail table, because one quote can have many
line
items.

For an example of how to put this kind of thing together, open the
Northwind
sample database that installs with Access, and choose Relationships
on
the
Tools menu. The Customers, Orders and OrderDetail tables are a
similar
kind
of idea.

Once you have that sorted out, I suggest you need a Job and JobDetail
table
as well. The actual job can be different from the original quote, so
when a
quote succeeds you need to be able to retain the quote, but actually
charge
something different for the job (e.g. where the client accepts only
part
of
the quote, or asks for extras.)

Invoices and payments received are another issue again. Your
structure
needs
to cope with:
- Multiple invoices for one job (e.g. big job with progress
payments);
- Multiple payments for one invoices (client pays over time);
- Multiple invoices in one payment (client pays several invoices at
once);
- Prepayments (client gives you a check before you write an invoice.)

You may also need to track your purchases for jobs, but that's
another
story
again.


I am attempting to create a simple database that produces
quotations
and
invoices. I have started with a customer table and the job table. i
dont
need
a products table becuase i just want to use a description for the
work
that
is being carried out, so i have put this in the jobs table. I have
created
a
"Quote, "Invoiced" and "Paid" fields in the jobs table and these
are
YES/NO
datatypes. Therefore this can be used to tell me which jobs i have
quoted,
invoiced and which jobs have been paid for.

i can put customers in, jobs for them customers in and i have got
these on
the same form but i dont know how i can create a quote, and an
invoice. i
have tried to use reports by clicking a command box which will
display
the
record that is being displayed but i had not luck and i dont know
what
i
can
do or what i am doing wrong.

Can some one help me or guide me to what i should be doing. I hope
what i
have stated is clear. Please help
Click to expand...
Click to expand...
Click to expand...
 
Thanks again Allen,

I have taken on board your information and i will try to put it into affect.

P.s. the wensite is very helpful, in my favourites now.

thanks again.

Allen Browne said:
Answers in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Moody said:
Thanks again Allen,

How do i know which quotes have been paid and possibly move them to
another
storage.
Click to expand...

You can only charge the customer if they agree to the quote. That means you
need to create an *invoice*, and the question is which invoices have been
paid. (Even if you do charge for quotes, a rejected quote would still need
an invoice for the quote charge, not the full amount in the quote.)

You don't want to move the quotes into an archive table once they have an
invoice, because it makes them too hard to track/query. Additionally, it
doesn't solve the issue of rejected quotes (which never get invoiced). If
you want to know how to do it anyway, see:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html
could it be possible to move the quote, so that it can not be manipulated
once paid. so i want the quotes table to trigger once the invoice for that
quote has been paid.
Click to expand...

You can lock the form so that the quote record is no longer editable after
an invoice has been created. To to that, you would use the Current event of
your quotes form to DLookup() the Invoice table and see if there is a match.
If so, set the AllowEdits property of the form to No.

The event procedure code would be something like this:
Private Sub Form_Current
Dim bAllow as Boolean
Dim strWhere As String
If Not Me.NewRecord Then
strWhere = "QuoteID = " & Me.QuoteID
bAllow = IsNull(DLookup("QuoteID", "Invoice", strWhere)
If Me.AllowEdits <> bAllow Then
Me.AllowEdits = bAllow
End If
End If
End Sub

Note that this locks the quote once there is an invoice (regardless of
whether the invoice is paid or not.)
i have decided not to use the function of part payment becuase i am going
to
assume that customers will always pay me in full and not in installments.
how
would i do a query to tell me which quotes need paying and which quotes
got
invoiced.
Click to expand...

If you can assume that there will *never* be part payments, prepayments, or
payment of multiple invoices together, you could get away with adding 2
extra fields to your Invoice table:
Received Currency Amount customer has paid for this invoice.
RecDate Date/Time When the client paid this invoice.

You can then find the invoice, and enter the amount received when the client
pays you. The invoice is fully paid, when the Received equals the amount of
the invoice (including any tax.)
quotes means that the customer asked for a job and is unsure if he wants
the job but invoices means that i did the job.

thank you very much for your help and i look forward to your reply, hope i
dont sound silly.
Click to expand...

Not at all. These are really important questions. Too many people just ask,
"How can I get this report?" without asking the crucial questions about how
the data should be stored.
Okay, we will assume there will never, ever be any invoice that needs
to
list 2 items, and no quote will ever need to list 2 alternatives.

Consider these four tables:

Customer table:
CustomerID AutoNumber primary key
Surname Text
FirstName Text
Address Text
City Text
Zip Text
...

Quote table:
QuoteID AutoNumber primary key
CustomerID Number The customer this quote is for
QuoteDate Date/Time When quote was given.
Duration Number number of days the quote is
valid
Descrip Text (or memo) what this is for

Invoice table:
InvoiceID AutoNumber primary key
CustomerID Number who is to pay this invoice
InvoiceDate Date/Time when the invoice was sent
Terms Number number of days before due.
Descrip Text (or memo) what this is for
AmountEx Currency amount of this invoice, without tax
TaxRate Number (Double) Percent tax rate to add.
QuoteID Number The quote this came from.
Comment Memo Comments not printed on invoice

Receipt table:
ReceiptID AutoNumber primary key
CustomerID Number who paid this amount
ReceiptDate Date/Time when received.
Amount Currency amount received.
Comment Memo description of what for.

This is the simplest solution that copes with everything, because the
receipts are not tied to invoices. Therefore anyone can pay you any
amount
any time: partial invoices, multiple invoices, parts of multiples, and
even uninvoiced amounts. You then calculate how much as customer owes
as:
DSum("[AmountEx] * (1+[TaxRate])", "Invoice", "[CustomerID] = 99")
- DSum("Amount", "Receipt", "[CustomerID] = 99")

That will actually work for years with many thousands of invoices per
client, so is suitable for many databases where Access is appropriate.
Beyond that, you are getting into double-entry accounting, with periods
that you close off with closing and opening balances, which may be
adding
complexity you don't need.

Thanks for the reply but i have got a little confused, maybe i havent
understood something.

I think i know what you are trying to tell me and i agree with you but
i
cant get my head around to doing so, it seems very difficult.

Really sorry to sound like an idiot but could you try to explain it a
bit
more. I like your idea and would like it to be implemented, preferably
with
out the use of backend SQL.

the line items that you mentioned are not really an issue in this
database
becuase the work being carried out for the customer is not a specfic
item
that has been stated, i would just use a description field for this
and
set
the price i choose for this..

I really appreciate your help and i look forward to your reply.

Thanks

:

There's quite a bit to setting up a database like this.

You need a Customer table seprate from your Quote table, becuase one
customer can have several quotes over the years.

Then you need a QuoteDetail table, because one quote can have many
line
items.

For an example of how to put this kind of thing together, open the
Northwind
sample database that installs with Access, and choose Relationships
on
the
Tools menu. The Customers, Orders and OrderDetail tables are a
similar
kind
of idea.

Once you have that sorted out, I suggest you need a Job and JobDetail
table
as well. The actual job can be different from the original quote, so
when a
quote succeeds you need to be able to retain the quote, but actually
charge
something different for the job (e.g. where the client accepts only
part
of
the quote, or asks for extras.)

Invoices and payments received are another issue again. Your
structure
needs
to cope with:
- Multiple invoices for one job (e.g. big job with progress
payments);
- Multiple payments for one invoices (client pays over time);
- Multiple invoices in one payment (client pays several invoices at
once);
- Prepayments (client gives you a check before you write an invoice.)

You may also need to track your purchases for jobs, but that's
another
story
again.


I am attempting to create a simple database that produces
quotations
and
invoices. I have started with a customer table and the job table. i
dont
need
a products table becuase i just want to use a description for the
work
that
is being carried out, so i have put this in the jobs table. I have
created
a
"Quote, "Invoiced" and "Paid" fields in the jobs table and these
are
YES/NO
datatypes. Therefore this can be used to tell me which jobs i have
quoted,
invoiced and which jobs have been paid for.

i can put customers in, jobs for them customers in and i have got
these on
the same form but i dont know how i can create a quote, and an
invoice. i
have tried to use reports by clicking a command box which will
display
the
record that is being displayed but i had not luck and i dont know
what
i
can
do or what i am doing wrong.

Can some one help me or guide me to what i should be doing. I hope
what i
have stated is clear. Please help
Click to expand...
Click to expand...
Click to expand...
 
Back
Top