Sorry post was too large with attachment, resent without attachment
----------------------
If you insist that an Invoice report is an easy report for a novice to
design and you want to do everything yourself.
I attached the table design I use for the tblInvoice, tblInvoiceLines and
tblProduct in my application.
The report itself uses query "qryReportInvoice"
Query design for qryReportInvoice is:
SELECT tblInvoice.InvoiceID, tblInvoice.AccountID,
tblInvoice.InvoiceNumber,
tblInvoice.PONumber, tblInvoice.InvoiceDate, tblInvoice.InvoiceDueDate,
tblInvoice.PrintedNotes, tblInvoice.InternalNotes, tblInvoice.TaxPercent,
tblInvoice.BillingFullAddress, tblInvoice.DeliveryFullAddress,
tblInvoiceLine.InvoiceLineID, tblInvoiceLine.ProductID,
tblInvoiceLine.LineNumber, tblInvoiceLine.ProductDescription,
tblInvoiceLine.Qty, tblInvoiceLine.Rate, nz([Rate])*nz([Qty]) AS Amount,
tblInvoiceLine.Taxable, tblProduct.ProductName,
qryReportInvoiceTotals.InvoiceSubTotal,
qryReportInvoiceTotals.InvoiceTaxTotal,
qryReportInvoiceTotals.InvoiceGrandTotal
FROM (tblInvoice INNER JOIN qryReportInvoiceTotals ON
tblInvoice.InvoiceID =
qryReportInvoiceTotals.InvoiceID) INNER JOIN (tblInvoiceLine INNER JOIN
tblProduct ON tblInvoiceLine.ProductID = tblProduct.ProductID) ON
tblInvoice.InvoiceID = tblInvoiceLine.InvoiceID;
Query design for qryReportInvoiceTotal is:
SELECT tblInvoice.InvoiceID, Sum(nz([Qty]*[Rate])) AS InvoiceSubTotal,
Sum(nz([tblInvoice].[TaxPercent]/100)*IIf([tblInvoiceLine].[Taxable]="T",[tblInvoiceLine].[Qty]*[tblInvoiceLine].[Rate],0))
AS InvoiceTaxTotal,
Sum(nz([Qty]*[Rate])+(nz([tblInvoice].[TaxPercent]/100)*IIf([tblInvoiceLine].[Taxable]="T",[tblInvoiceLine].[Qty]*[tblInvoiceLine].[Rate],0)))
AS InvoiceGrandTotal
FROM tblInvoice LEFT JOIN tblInvoiceLine ON tblInvoice.InvoiceID =
tblInvoiceLine.InvoiceID
GROUP BY tblInvoice.InvoiceID;
The report itself is the easy part:
- detail lines in the detail section
- the header/footer of the invoice in page header/footer or use grouping
if
you plan on having multiple invoices per report
- see my CRM template or donation software for an example of running an
Invoice report.
I'm not sure what your sql and reporting skills are at, but maybe it will
help?
Mark
RPT Software
http://www.rptsoftware.com
At least give me information as below :
Table1 : INV_NO, TO
Table2 : LINE_NO, ITEMS
This both tables connected each other by INV_NO
and I have created report. Where are the sections
I have to insert those four fields in report? One invoice
must print on one invoice (INV_NO). I know I have to
do grouping for this. Where I will do this. Please see
this problem with your greate favor. I want to accomplish this.
I take msaccess as a challenge.
:
You could download and look at my CRM template (see website). I give
away a
second CRM template for 2007 for free as well.
You would get lots of source code to look at and steal for your
project.
They both have invoice form and report examples. Invoice forms are a
little
tricky (creating invoice line numbers, associated subform and totals,
product selection to provide default for lines with option to
overwrite
etc....).
It's difficult to get someone to give you exact details for the thing
you
are trying to build.
Newsgroups are usually more for "hey this query doesn't work or how do
I
approach ..."
HTH,
Mark Andrews
RPT Software
http://www.rptsoftware.com
In report design view where I can put respective fields? In what
way I
can
get grouping? I created query and made a report. Then I dont have
clear
idea about grouping in the way you said. Mainly I want to know what
tricks I
can use to get
separate separate invoice on the basis of INV_NO field. uh...sorry
feel
little bit hard in some areas. I wish I were a scholar in this
msaccess
database.
:
First, you need to change the name of your field from DATE, because
date
is
the name of an Access built-in function (which makes it a reserved
word)
and
it can be confusing.
Create a Query, joining table 1 and table 2 on INV_NO, specifying a
sort
of
Ascending for INV_NO, LINE_NO, and QTY.
Use that Query as the Record Source for a Report. In the Report's
Sorting
and Grouping Properties, group on INV_NO, with a header, and a
footer.
Place the fields relating to the invoice as a whole (probably those
in
table1) in the header, and use the Sum function to total the prices
for
the
detail lines in the Group Footer, and set the Force New Page
property
of
the
Footer to "After".
Just for the record, newsgroups are not for general assistance in
how
to
do
common functions, as you seem to have requested, they are for
assisting
you
in getting past or around stumbling blocks or errors you have
encountered.
So, consider that my response is the "favor" you requested (or
implied).
In the future, please explain what you have done, what trouble you
had
or
what errors you encountered, and what help you need. In this case,
that
would have been "How do I accomplish creating a report of my
invoices?"
Larry Linson
Microsoft Office Access MVP
I have created 2 tables for invoce database.
fields in table1 : INV_NO, DATE, TO
fields in table2 : LINE_NO, DESCRIPTION, UNIT_PRICE, QTY,
TOTAL_PRICE
I have connected both tables as primary and foregn keys.
I need to print it as below :
Each invoice has one or multiple pages.
In one invoce line nuber will be increasing in serial number as
much
as
how
many items.
Would appreciate your favor in this matter.
.
.
.
