Invoice Report 02-14-10

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.

0
Utf
2/14/2010 6:37:01 AM
access.reports 4434 articles. 0 followers. Follow

12 Replies
1542 Views

Similar Articles

[PageSpeed] 6

>>I have connected both tables as primary and foregn keys.
How are they connected?   I do not see any fields that appear to be common 
to both.
-- 
Build a little, test a little.


"Kutty" wrote:

> 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.
> 
0
Utf
2/14/2010 7:38:01 AM
Connected INV_NO in table1 as primary key 
and INV_NO in table2 as foreign key.  I did not mention to 
you that I have INV_NO in table2 also, sorry.

"KARL DEWEY" wrote:

> >>I have connected both tables as primary and foregn keys.
> How are they connected?   I do not see any fields that appear to be common 
> to both.
> -- 
> Build a little, test a little.
> 
> 
> "Kutty" wrote:
> 
> > 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.
> > 
0
Utf
2/14/2010 8:10:01 AM
Dear Karl,
Please give me response for this question.

"KARL DEWEY" wrote:

> >>I have connected both tables as primary and foregn keys.
> How are they connected?   I do not see any fields that appear to be common 
> to both.
> -- 
> Build a little, test a little.
> 
> 
> "Kutty" wrote:
> 
> > 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.
> > 
0
Utf
2/15/2010 5:51:01 AM
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


"Kutty" <Kutty@discussions.microsoft.com> wrote in message 
news:91F99B4A-6E1B-4CDA-8E23-5AA490E23D3D@microsoft.com...
>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.
> 



0
Larry
2/15/2010 6:26:06 AM
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.

"Larry Linson" wrote:

> 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
> 
> 
> "Kutty" <Kutty@discussions.microsoft.com> wrote in message 
> news:91F99B4A-6E1B-4CDA-8E23-5AA490E23D3D@microsoft.com...
> >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.
> > 
> 
> 
> 
> .
> 
0
Utf
2/15/2010 1:39:01 PM
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

"Kutty" <Kutty@discussions.microsoft.com> wrote in message 
news:F68F8E68-5FD4-441A-BAD3-9DB4A741D0E1@microsoft.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.
>
> "Larry Linson" wrote:
>
>> 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
>>
>>
>> "Kutty" <Kutty@discussions.microsoft.com> wrote in message
>> news:91F99B4A-6E1B-4CDA-8E23-5AA490E23D3D@microsoft.com...
>> >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.
>> >
>>
>>
>>
>> .
>> 
0
Mark
2/15/2010 1:59:12 PM
Sorry to say I am not satisfied with this reply.  I am highly intrested to 
fulfil 
this task with your greate favor.  Please tell me instraight way how can I 
prepare
this simple report.  

I just want the following :
    I want line items with LINE_NO field under INV_NO
Would appreciate.


"Mark Andrews" wrote:

> 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
> 
> "Kutty" <Kutty@discussions.microsoft.com> wrote in message 
> news:F68F8E68-5FD4-441A-BAD3-9DB4A741D0E1@microsoft.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.
> >
> > "Larry Linson" wrote:
> >
> >> 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
> >>
> >>
> >> "Kutty" <Kutty@discussions.microsoft.com> wrote in message
> >> news:91F99B4A-6E1B-4CDA-8E23-5AA490E23D3D@microsoft.com...
> >> >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.
> >> >
> >>
> >>
> >>
> >> .
> >> 
> .
> 
0
Utf
2/16/2010 6:41:01 AM
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.  


"Mark Andrews" wrote:

> 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
> 
> "Kutty" <Kutty@discussions.microsoft.com> wrote in message 
> news:F68F8E68-5FD4-441A-BAD3-9DB4A741D0E1@microsoft.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.
> >
> > "Larry Linson" wrote:
> >
> >> 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
> >>
> >>
> >> "Kutty" <Kutty@discussions.microsoft.com> wrote in message
> >> news:91F99B4A-6E1B-4CDA-8E23-5AA490E23D3D@microsoft.com...
> >> >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.
> >> >
> >>
> >>
> >>
> >> .
> >> 
> .
> 
0
Utf
2/17/2010 9:43:02 AM
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

"Kutty" <Kutty@discussions.microsoft.com> wrote in message
news:A3EFB5A5-1A9A-46E4-9C37-5700E1ECCE8C@microsoft.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.
>
>
> "Mark Andrews" wrote:
>
>> 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
>>
>> "Kutty" <Kutty@discussions.microsoft.com> wrote in message
>> news:F68F8E68-5FD4-441A-BAD3-9DB4A741D0E1@microsoft.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.
>> >
>> > "Larry Linson" wrote:
>> >
>> >> 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
>> >>
>> >>
>> >> "Kutty" <Kutty@discussions.microsoft.com> wrote in message
>> >> news:91F99B4A-6E1B-4CDA-8E23-5AA490E23D3D@microsoft.com...
>> >> >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.
>> >> >
>> >>
>> >>
>> >>
>> >> .
>> >>
>> .
>> 
0
Mark
2/17/2010 10:45:07 PM
Dear Mark,
Please help.
I have my invoice in my PC created by myself in msaccess.  I dont know 
anything
about sql.  So I am totally confused about your below reply.  Let me put my 
invoice 
as follows :
________________________________
Report Header
INV_NO         DATE       TO                each invoice can be 1 or 
multiple pages  
                                                       because line items 
sometimes will be more.
_________________________________
Page Header
LINE_NO        DESCRIPTION        PRICE              
_________________________________

"Mark Andrews" wrote:

> 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
> 
> "Kutty" <Kutty@discussions.microsoft.com> wrote in message
> news:A3EFB5A5-1A9A-46E4-9C37-5700E1ECCE8C@microsoft.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.
> >
> >
> > "Mark Andrews" wrote:
> >
> >> 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
> >>
> >> "Kutty" <Kutty@discussions.microsoft.com> wrote in message
> >> news:F68F8E68-5FD4-441A-BAD3-9DB4A741D0E1@microsoft.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.
> >> >
> >> > "Larry Linson" wrote:
> >> >
> >> >> 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
> >> >>
> >> >>
> >> >> "Kutty" <Kutty@discussions.microsoft.com> wrote in message
> >> >> news:91F99B4A-6E1B-4CDA-8E23-5AA490E23D3D@microsoft.com...
> >> >> >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.
> >> >> >
> >> >>
> >> >>
> >> >>
> >> >> .
> >> >>
> >> .
> >> 
> .
> 
0
Utf
2/19/2010 3:51:02 PM
If you don't want to learn sql or hire someone to do the work for you or buy 
something and steal some code it is going to be very tough to make an 
Invoice report (which has some tricky items).  Try and group by Inv_no and 
have the top part of the invoice in the grouping and the invoice lines in 
the detail and the bottom of the invoice in the group footer (that's general 
advise).

Perhaps start another newsgroup post, maybe someone else can help you and 
walk you through the process,
Mark

"Kutty" <Kutty@discussions.microsoft.com> wrote in message 
news:2CA39A72-C8B0-4FE2-9DD4-8DAA92C991CC@microsoft.com...
> Dear Mark,
> Please help.
> I have my invoice in my PC created by myself in msaccess.  I dont know
> anything
> about sql.  So I am totally confused about your below reply.  Let me put 
> my
> invoice
> as follows :
> ________________________________
> Report Header
> INV_NO         DATE       TO                each invoice can be 1 or
> multiple pages
>                                                       because line items
> sometimes will be more.
> _________________________________
> Page Header
> LINE_NO        DESCRIPTION        PRICE
> _________________________________
>
> "Mark Andrews" wrote:
>
>> 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
>>
>> "Kutty" <Kutty@discussions.microsoft.com> wrote in message
>> news:A3EFB5A5-1A9A-46E4-9C37-5700E1ECCE8C@microsoft.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.
>> >
>> >
>> > "Mark Andrews" wrote:
>> >
>> >> 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
>> >>
>> >> "Kutty" <Kutty@discussions.microsoft.com> wrote in message
>> >> news:F68F8E68-5FD4-441A-BAD3-9DB4A741D0E1@microsoft.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.
>> >> >
>> >> > "Larry Linson" wrote:
>> >> >
>> >> >> 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
>> >> >>
>> >> >>
>> >> >> "Kutty" <Kutty@discussions.microsoft.com> wrote in message
>> >> >> news:91F99B4A-6E1B-4CDA-8E23-5AA490E23D3D@microsoft.com...
>> >> >> >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.
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >> >> .
>> >> >>
>> >> .
>> >>
>> .
>> 
0
Mark
2/19/2010 6:03:25 PM
Dear Mark,
Finally I got idea to group the LINE_NO.
Thanks for your great cooperation and the effort you took for this.
I hope for valuable favor in future also.

"Mark Andrews" wrote:

> If you don't want to learn sql or hire someone to do the work for you or buy 
> something and steal some code it is going to be very tough to make an 
> Invoice report (which has some tricky items).  Try and group by Inv_no and 
> have the top part of the invoice in the grouping and the invoice lines in 
> the detail and the bottom of the invoice in the group footer (that's general 
> advise).
> 
> Perhaps start another newsgroup post, maybe someone else can help you and 
> walk you through the process,
> Mark
> 
> "Kutty" <Kutty@discussions.microsoft.com> wrote in message 
> news:2CA39A72-C8B0-4FE2-9DD4-8DAA92C991CC@microsoft.com...
> > Dear Mark,
> > Please help.
> > I have my invoice in my PC created by myself in msaccess.  I dont know
> > anything
> > about sql.  So I am totally confused about your below reply.  Let me put 
> > my
> > invoice
> > as follows :
> > ________________________________
> > Report Header
> > INV_NO         DATE       TO                each invoice can be 1 or
> > multiple pages
> >                                                       because line items
> > sometimes will be more.
> > _________________________________
> > Page Header
> > LINE_NO        DESCRIPTION        PRICE
> > _________________________________
> >
> > "Mark Andrews" wrote:
> >
> >> 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
> >>
> >> "Kutty" <Kutty@discussions.microsoft.com> wrote in message
> >> news:A3EFB5A5-1A9A-46E4-9C37-5700E1ECCE8C@microsoft.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.
> >> >
> >> >
> >> > "Mark Andrews" wrote:
> >> >
> >> >> 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
> >> >>
> >> >> "Kutty" <Kutty@discussions.microsoft.com> wrote in message
> >> >> news:F68F8E68-5FD4-441A-BAD3-9DB4A741D0E1@microsoft.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.
> >> >> >
> >> >> > "Larry Linson" wrote:
> >> >> >
> >> >> >> 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
> >> >> >>
> >> >> >>
> >> >> >> "Kutty" <Kutty@discussions.microsoft.com> wrote in message
> >> >> >> news:91F99B4A-6E1B-4CDA-8E23-5AA490E23D3D@microsoft.com...
> >> >> >> >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.
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >> .
> >> >> >>
> >> >> .
> >> >>
> >> .
> >> 
> .
> 
0
Utf
2/20/2010 10:51:01 AM
Reply:

Similar Artilces:

Cell Reference 01-12-10
What I am looking for is that when I enter a formula (In cell B1) to pick up value in A1. Now I need value from cell A5 in cell B2. Next value I need in B3 is A9. Everytime I have to change the cell values manually in column B. Formula I use : - =IF(OR(AK64<$D$4,AK64=$D$4),H331,0) Now I need value from cell H336 and I manually change H331 to H336 shown below =IF(OR(AK64<$D$4,AK64=$D$4),H336,0) Any way to make this automated. Thanks Ankur Bhateja ankur.bhateja@hotmail.com Instead of =IF(OR(AK64<$D$4,AK64=$D$4),H331,0) you could say =IF(AK64<=$D$4,H331,0) ...

Import Report in Customization Maintenance Error
When I attempt to import a report package file in the Customization Maintenance Window I get an error. "Unable to open customizations dictionary" I did this in the evening so users were out of the system. I did have to delete a few logins prior to the import. So I do not think that is the issue, but I could be wrong. Does anyone know what to do in this situation? Hi, If the report/s dictionary is shared to GP users from a central repository (server), you have to manually delete all active file (GP dictionaries) sessions from that server. This can be done fro...

outlook 2003 02-18-10
Can not send mail from a distribution list created in Outlook 2003 - why? I get an error message that reads - could not be delivered to the following receive 501 5.5.2 RCPT TO syntax error Can someone help me. "pj jakobsen" <pj jakobsen@discussions.microsoft.com> wrote in message news:A0D15814-B5C5-4153-91EF-F3D36CD3F581@microsoft.com... > Can not send mail from a distribution list created in Outlook 2003 - why? > I get an error message that reads - could not be delivered to the following > receive 501 5.5.2 RCPT TO syntax error > Can someone help me. ...

Record Count 10-02-07
Experts, I'm trying to dynamically stored the record count from a subform into a field on my form. Now I have to look at the record count on the subform and manually entered the count into a field on form. There must be a better way. Please help! In the Control Source property of a text box on the main form: =[SubFormControlName].[Form].[recordset].[RecordCount] Where SubFormControlName is the name of the subform control, Not the name of the form that is the Source Object of the subform control. -- Dave Hargis, Microsoft Access MVP "Shiller" wrote: > Experts, >...

Can Update Office 11.0
I am trying to give Office a whirl on my powerbook however I need to upgrade to 11.1 for it to work with my ofices Lotus Notes e-Mail. When I try upgrading I get the 11002:2,-14 error I've heard talked about in this newsgroup. I've tried all the suggestions but still no luck. I tried repairing permisions, re-installing, turning of Entourage notifications. Nothing works! Please help. Haven't a clue. Previous threads didn't offer anything? Try re-downloading the update. Maybe previous updates haven't been run yet. Use Help | Check for Updates and let it tell you what...

Compare records in multiple sheet -> report
Hi! I have a workbook consisting of ten sheets. To simplify my question let�s say that the three first columns of every sheet denotes the spatial coordinates x,y,z and the fourth column is a scalar value. Some x,y,z-triplets exist in all 10 sheets, some exists in only a few sheets, if the triplet exists, then also the scalar value of the 4th column exists. What I would like to do is to find all unique x,y,z-triplets and show them in the first column of a new sheet. In columns 2-11, I would like to show the scalar value(from the corresponding x,y,z-triplet of course) in column 4 in s...

Problems changing password in Crystal Reports after CRM 1.2 installation
When attempting to change the password in the Crystal Reports Password Tool (leaving the old password blank as per the CRM installation instructions), I receive the following error: Unable to change password. Error returned is : The remote server returned an error: (404) Not Found. This installation is on Win2K3 SBS if that makes any difference. Thanks in advance, Carson F. Ball Technician The Alexander Group ...

MRP not creating planned orders in version 10.0
I'm testing version 10.0 on a test system and using a copy of my live database. Version 8.0 creates a planned PO when QTY Available falls below Order Point Qty. It's not working in 10.0. I've checked all my settings from 8.0 to 10.0 and they all look the same. Is there a setting I'm missing? Thanks, Ken Have you rebuilt your low level codes after the upgrade? -- Tim Foster "KRoy" wrote: > I'm testing version 10.0 on a test system and using a copy of my live > database. Version 8.0 creates a planned PO when QTY Available falls below > Ord...

How to create web-based reports from RMS database.
The reports available in RMS is not specific enough for my apparel retail store. I need sell the through rate, average sale, sales/sf, return rate, discount analysis, all broken down by date, week, month, year, category, supplier, and then compare against last year's numbers. I want to create my own web-based reports accessing RMS's SQL database. Does anyone have a template of data access web page so all I have to do is change the SQL statement in getting to the right database table? mas wrote: > The reports available in RMS is not specific enough for my apparel retail > ...

Error while trying to access reports
Hi all, I have a user who is Admin and gets this error when she clicks on the report icon: Runtime Error Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine. Details: To enable the details of this specific error message to be viewable on remote machines, please create a <customErrors> tag within a "web.config" configuration file located in the r...

Upgrading #10
I am currently using MS Money 99 and recently purchased Money 2006. I want to upgrade and keep using the same file in the new version of the software. (This way I still have all my financial data in tact.) When I go to open the 99 file or restore the 99 backup in the 2006 version, I get an error message that says it cannont locate the file or that is has been corrupted. I know neither of these are true because I can still use the file in Money 99. I don't think Money 2006 knows how to recognize the 99 file. How do I use my Money 99 file in the 2006 version of the software? M...

Date question 01-14-10
Can anyone help with this function: =IF[Date Parts Ordered Completed]+ [Date Part Completed]+[Date Purchasing Completed],not blank,=Date The goal is that is all three fields have a date in them, then put in today's date in the final field. If one of the dates is missing, then the final date is blank. The only problem is if I open up that form I do not want it to change the date to today. I want it to stay the date of the actual completion. Any help is greatly appreciated. Thanks, P-Chu -- Message posted via http://www.accessmonster.com Try this -- Completion_...

Create Pivot Table Reports
I just finished installing Analysis Cube on the server, everything seemed working fine until when I try to create the Pivot Table Report (Tools->Analysis Cubes->Create Pivot Table Reprots). After I called up the pre-defined 'Definition ID' and click on the Excel icon, a message popped up saying " The WHTemplate.XLT file was not found." Anybody have any idea what that is, and how to resolve it?? Thanks. 1.) There are two pieces of software. Did you install both the server piece, and the client piece? 2.) Is Excel installed on the machine, on which you'r...

Accpac DOS with Adagio Migration to Dynamics 10
Anybody still do these migrations.... ...

OWA Question #10
Hello, we're running Exchange 2003 and I have the following situation. Several employees in our company decided to start a different branch. Instead of creating a separate domain we just created a new account in AD with a different domain suffix. So let's say they used to have account@companyA.com. But now they have account@companyB.com. They've been able to access email via Outlook just fine but when accessing it via OWA, they are unsuccessful. We've tried logging them in with both companyA.com and companyB.com domains in the login screen. companyA.com just retur...

Money 2001 #10
when using the portfolio I entered a new transaction under a new acount and an error message appeared "M Money has encountered a problem and needs to close". The computer then shuts down or locks up. This happens whenever I click on portfolio. This problem has occured twice over the past month. The first time I went back one month and reentered all my transactions before the error accured and was going along fine but then it happened again while entering a transaction under a new account. ...

Reports not availble on ssms
Hello Gurus, Here is a situation I have versions of SQL from 2000 to 2008 in both the 2005 and 2008 version of ssms I can not see the report section when right clicking on a database in SSMS. However I can view these reports when I RDC into the server using the exact same windows credentials?? Is there something missing on my client tool that would cause this? or What?? help! Wisord *** Sent via Developersdex http://www.developersdex.com *** ...

Date range for a report
Hi I am trying to run a query and can't seem to nail down the right code. We use the access database to track files, incoming/outgoing correspondence dates etc. I am trying to run a query that show me files with dates in a follow up field of -60 days to +7 days, so essentially any follow ups missed in the last 2 months and up coming in the next week. This report is run on a weekly basis. Appreciate any help Thanks Assuming that the follow up field is actually a date/time data type, try this in the criteria: Between Date() - 60 and Date() + 7 -- Jerry Whittle, ...

Report Server Error 09-28-07
After uninstalling MSCRM from my server, I tried installing it again. This has led me to the problem of the report server. I'm using the default server name http://<servername>/reportserver and it's still not realizing that it is actually there and fully running. I've checked the Services section of my server and also checked the individual server status. Both are running within normal parameters. Before the uninstall, the report server was running properly and CRM recognised that it was actually there. Any ideas? ....reed Is this SQL Server 2000 or 2005 SRS? 1. Fo...

Purchasing Invoice Inquiry Zoom
Where is the Apply button on the Purchasing Invoice Inquiry Zoom so we can see that the invoice has been paid??? Anybody have any insight into this. It's a major workaround to have to go through each payment to determine this. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the me...

not repeating text boxes in reports with columns
Hello, I am trying to create a report with columns without repeating certain text boxes. Here is an example of what I would like to create: [Date] "Month1" [Date] "Month2" [Date] "Month3" [Product] "Product1": [quantity] [value] [quantity] [value] [quantity] [value] [Product] "Product2": [quantity] [value] [quantity] [value] [quantity] [value] [Product] "Product3": [quantity] [value] [quantity] [value] [quantity] [value] [Product] "Product4": [q...

Crystal Reports help
We are currently using GP 8.0 and Crystal Reports 10. We will be running most of our reports through Crystal because you can do so much more than with the Report Writer. I am looking for a good reference book for Crystal 10 (this is the first version I have ever used) any suggestions? You can get books on Crystal 10 in any large bookseller. One thing you won't get is how to use Crystal with Great Plains. There was some training materials available. You will want to consult the Great Plains SDK for help on the tables to use. JO wrote: > We are currently using GP 8.0 and Crystal R...

Trouble with Expression 02-19-10
This expression counts the number of records where a tick box is YES TxtSoleYes: Count(IIf(txtSole,1,Null)) Why doesn't this work to give me the number of No's TxtSoleYes: Count(IIf(txtSole,0,Null)) Thanks Tony -- Why don't my grey cells communicate with each as fast as they used to? I hate getting old! Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1 Tony - Try Sum instead of Count. Count gives the number of non-nulls, and zero is non-null. -- Daryl S "TonyWilliams via AccessMonster.com&...

Payroll GP 10.0
Got this from a client and not sure what they are doing, but never heard of this any help would be great. As I am reviewing the Payroll Deduction Journal from the payroll I just ran yesterday, I have identified 5 employees whose deductions & benefits did not calculate. I happen to be one of the 5. There is $$$$$ that did not come off my pay as deductions; including my 401K. The common denominator seems to be when employee changes are made, deductions & benefits are made inactive; even though those benefits & deductions have no part in the changes being made. Pardon me, ...

Access SSRS Throug SSMS not Report Manager
Without giving someone membership into the "BUILTIN\Administrators" group, how would one enable a user to be able to access SSRS through SSMS? SSMS >> Connect >> Reporting Services >> {Server Name} I'm currently getting the error below: TITLE: Connect to Server ------------------------------ Cannot connect to {servername}. (removed the server name) ------------------------------ ADDITIONAL INFORMATION: A connection to the computer cannot be established. (Microsoft.SqlServer.Management.UI.RSClient) ------------------------------ Acces...