Info in subform showing up multiple times..

  • Thread starter Thread starter stephendeloach via AccessMonster.com
  • Start date Start date
S

stephendeloach via AccessMonster.com

I have created a report that will summarize all of my invoices that are in
the Invoice form. My problem is that in the report it is showing all of the
seperate charges on the same invoice. Say if there is 3 charges on the one
invoice, that invoice comes up 3 times on the report. I can get the report to
show the invoices only one time, but I need the netamount in the report also.
In order for the netamount to be shown I need the quantity and unitprice in
the report, causing the problem of the charges coming up more than once...
Can someone please help me?
 
So you have a report based on a query. The query uses the Invoice table
(invoice header information) and the InvoiceDetail table (invoice line
items.) The problem is that if an invoice has 3 line items, this query
produces 3 rows, and so the invoices appears 3 times on the report.

If you want the totals only on the report (not listing the line items), you
can do that by changing the query to a Totals query:
1. Open the query in design view.

2. Depress the Total button on the toolbar.
Access adds a Total row to the design grid.

3. In the total row, accept Group By under the fields from the Invoice
table, but change it for the fields from the InvoiceDetail row. For example,
choose Sum under the Amount field to get the net amount.

4. After verfiying the query gives the results you want, open the report in
design view, and delete the controls bound to fields from the InvoiceDetail
table. Replace them with controls bound to the new field names, such as
SumOfAmount.

If you do want to show the line items in the report, the other approach is
to leave the query the way it was, and use the Sorting And Grouping box in
the report:
1. With the report open in design view, open this dialog (View menu.)

2. Choose the InvoiceID field, and in the lower pane of the dialog, set
Group Header to Yes and Group Footer to Yes. Access adds a header section
above the Detail, and a footer section below.

3. Move the appropriate controls into the group header section (e.g. the
invoice number, date, and address panel.)

4. In the group footer section, add a text box with its control source set
to something like this:
=Sum([Amount])
substituting your field name for Amount.

It is also possible to perform the grouping in the report instead of the
query, suppressing the line items by setting the Visible property of the
report's Detail section to No.
 
I have changed my query to group by SumofUnitPrice and SumofQuantity. When I
run the query everything is great. When I run the report everything looks
good, but i cant get the netamount to come out right. I dont have the
netamount in the form because everyone says it is bad to have a calculation
in a form. So what I have been doing is =Sum([Quantity]*[UnitPrice]) but now
it isnt calculating right because the quantity and unitprice are added
together... Any suggestions on what calculation should be in the NetAmount
field?? Thanks for getting me this far!

Stephen

Allen said:
So you have a report based on a query. The query uses the Invoice table
(invoice header information) and the InvoiceDetail table (invoice line
items.) The problem is that if an invoice has 3 line items, this query
produces 3 rows, and so the invoices appears 3 times on the report.

If you want the totals only on the report (not listing the line items), you
can do that by changing the query to a Totals query:
1. Open the query in design view.

2. Depress the Total button on the toolbar.
Access adds a Total row to the design grid.

3. In the total row, accept Group By under the fields from the Invoice
table, but change it for the fields from the InvoiceDetail row. For example,
choose Sum under the Amount field to get the net amount.

4. After verfiying the query gives the results you want, open the report in
design view, and delete the controls bound to fields from the InvoiceDetail
table. Replace them with controls bound to the new field names, such as
SumOfAmount.

If you do want to show the line items in the report, the other approach is
to leave the query the way it was, and use the Sorting And Grouping box in
the report:
1. With the report open in design view, open this dialog (View menu.)

2. Choose the InvoiceID field, and in the lower pane of the dialog, set
Group Header to Yes and Group Footer to Yes. Access adds a header section
above the Detail, and a footer section below.

3. Move the appropriate controls into the group header section (e.g. the
invoice number, date, and address panel.)

4. In the group footer section, add a text box with its control source set
to something like this:
=Sum([Amount])
substituting your field name for Amount.

It is also possible to perform the grouping in the report instead of the
query, suppressing the line items by setting the Visible property of the
report's Detail section to No.
I have created a report that will summarize all of my invoices that are in
the Invoice form. My problem is that in the report it is showing all of
Click to expand...
[quoted text clipped - 8 lines]
the report, causing the problem of the charges coming up more than once...
Can someone please help me?
Click to expand...
Click to expand...
 
In the query, enter this expression into a new column in the Field row:
Amount: [Quantity] * [UnitPrice]
and in the Total row under this, choose Sum.

You should now be able to use this Amount field in your report, and sum it.

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

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

stephendeloach via AccessMonster.com said:
I have changed my query to group by SumofUnitPrice and SumofQuantity. When
I
run the query everything is great. When I run the report everything looks
good, but i cant get the netamount to come out right. I dont have the
netamount in the form because everyone says it is bad to have a
calculation
in a form. So what I have been doing is =Sum([Quantity]*[UnitPrice]) but
now
it isnt calculating right because the quantity and unitprice are added
together... Any suggestions on what calculation should be in the
NetAmount
field?? Thanks for getting me this far!

Stephen

Allen said:
So you have a report based on a query. The query uses the Invoice table
(invoice header information) and the InvoiceDetail table (invoice line
items.) The problem is that if an invoice has 3 line items, this query
produces 3 rows, and so the invoices appears 3 times on the report.

If you want the totals only on the report (not listing the line items),
you
can do that by changing the query to a Totals query:
1. Open the query in design view.

2. Depress the Total button on the toolbar.
Access adds a Total row to the design grid.

3. In the total row, accept Group By under the fields from the Invoice
table, but change it for the fields from the InvoiceDetail row. For
example,
choose Sum under the Amount field to get the net amount.

4. After verfiying the query gives the results you want, open the report
in
design view, and delete the controls bound to fields from the
InvoiceDetail
table. Replace them with controls bound to the new field names, such as
SumOfAmount.

If you do want to show the line items in the report, the other approach is
to leave the query the way it was, and use the Sorting And Grouping box in
the report:
1. With the report open in design view, open this dialog (View menu.)

2. Choose the InvoiceID field, and in the lower pane of the dialog, set
Group Header to Yes and Group Footer to Yes. Access adds a header section
above the Detail, and a footer section below.

3. Move the appropriate controls into the group header section (e.g. the
invoice number, date, and address panel.)

4. In the group footer section, add a text box with its control source set
to something like this:
=Sum([Amount])
substituting your field name for Amount.

It is also possible to perform the grouping in the report instead of the
query, suppressing the line items by setting the Visible property of the
report's Detail section to No.
I have created a report that will summarize all of my invoices that are
in
the Invoice form. My problem is that in the report it is showing all of
Click to expand...
[quoted text clipped - 8 lines]
the report, causing the problem of the charges coming up more than
once...
Can someone please help me?
Click to expand...
Click to expand...
Click to expand...
 
Works great! Now, next question.. on my form I have a check box. When the
checkbox (Taxable) is checked in the single invoice report, it knows to add
in the tax. How can I can make the InvoiceSummary report know to add in the
Tax? These are the formulas in the single invoice report...

Non-Taxable =Sum(IIf([taxablechk]=False,[quantity]*[UnitPrice],0))

Taxable =Sum(IIf([taxablechk]=True,[quantity]*[UnitPrice],0))

and for Tax to be added in...

Tax =Sum(IIf([taxablechk]=True,[quantity]*[unitprice],0))*[Tax]


Thanks!

Allen said:
In the query, enter this expression into a new column in the Field row:
Amount: [Quantity] * [UnitPrice]
and in the Total row under this, choose Sum.

You should now be able to use this Amount field in your report, and sum it.
I have changed my query to group by SumofUnitPrice and SumofQuantity. When
I
Click to expand...
[quoted text clipped - 64 lines]
Click to expand...
 
Again, move the Tax field into the query instead of trying to do everything
in the report.

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

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

stephendeloach via AccessMonster.com said:
Works great! Now, next question.. on my form I have a check box. When the
checkbox (Taxable) is checked in the single invoice report, it knows to
add
in the tax. How can I can make the InvoiceSummary report know to add in
the
Tax? These are the formulas in the single invoice report...

Non-Taxable =Sum(IIf([taxablechk]=False,[quantity]*[UnitPrice],0))

Taxable =Sum(IIf([taxablechk]=True,[quantity]*[UnitPrice],0))

and for Tax to be added in...

Tax
=Sum(IIf([taxablechk]=True,[quantity]*[unitprice],0))*[Tax]


Thanks!

Allen said:
In the query, enter this expression into a new column in the Field row:
Amount: [Quantity] * [UnitPrice]
and in the Total row under this, choose Sum.

You should now be able to use this Amount field in your report, and sum
it.
I have changed my query to group by SumofUnitPrice and SumofQuantity.
When
I
Click to expand...
[quoted text clipped - 64 lines]
once...
Can someone please help me?
Click to expand...
Click to expand...
Click to expand...
 
So I would put Tax : Sum(IIf([taxablechk]=True,[quantity]*[unitprice],0))*
[Tax] ??


Allen said:
Again, move the Tax field into the query instead of trying to do everything
in the report.
Works great! Now, next question.. on my form I have a check box. When the
checkbox (Taxable) is checked in the single invoice report, it knows to
Click to expand...
[quoted text clipped - 27 lines]
Click to expand...
 
If you choose Expression in the Group By row, in query design,that might
work.

Else omit the Sum() part.

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

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

stephendeloach via AccessMonster.com said:
So I would put Tax : Sum(IIf([taxablechk]=True,[quantity]*[unitprice],0))*
[Tax] ??


Allen said:
Again, move the Tax field into the query instead of trying to do
everything
in the report.
Works great! Now, next question.. on my form I have a check box. When
the
checkbox (Taxable) is checked in the single invoice report, it knows to
Click to expand...
[quoted text clipped - 27 lines]
once...
Can someone please help me?
Click to expand...
Click to expand...
Click to expand...
 
I have Tax, Table1, Expression, and for criteria i have IIf([taxablechk]=True,
[quantity]*[unitprice],0)*[Tax] and i get a "You tried to execute a query
that does not include the specified expression "tax" as part of an aggregate
function" ???

Allen said:
If you choose Expression in the Group By row, in query design,that might
work.

Else omit the Sum() part.
So I would put Tax : Sum(IIf([taxablechk]=True,[quantity]*[unitprice],0))*
[Tax] ??
Click to expand...
[quoted text clipped - 9 lines]
Click to expand...
 
And with the Sum() part?

Perhaps you could post the entire SQL statement.

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

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

stephendeloach via AccessMonster.com said:
I have Tax, Table1, Expression, and for criteria i have
IIf([taxablechk]=True,
[quantity]*[unitprice],0)*[Tax] and i get a "You tried to execute a query
that does not include the specified expression "tax" as part of an
aggregate
function" ???

Allen said:
If you choose Expression in the Group By row, in query design,that might
work.

Else omit the Sum() part.
So I would put Tax :
Sum(IIf([taxablechk]=True,[quantity]*[unitprice],0))*
[Tax] ??
Click to expand...
[quoted text clipped - 9 lines]
once...
Can someone please help me?
Click to expand...
Click to expand...
Click to expand...
 
SELECT Sum(Charges.UnitPrice) AS SumOfUnitPrice, Sum(Charges.Quantity) AS
SumOfQuantity, Charges.[Invoice#] AS [Charges_Invoice#], Charges.TaxableChk,
Table1.[BillTo:], Table1.WellName, Table1.[Parish/County], Table1.InvoiceDate,
Table1.[Invoice#] AS [Table1_Invoice#], Table1.NetAmount, Table1.CompanyFrom,
Table1.Tax, Charges.Expendable, Table1.RigName, Table1.[TSWSin#], Table1.Date,
Table1.AmtPaid, Table1.Balance, Charges.netamt, Table1.Summary, Sum([Quantity]
*[UnitPrice]) AS Amount
FROM Table1 INNER JOIN Charges ON Table1.[Invoice#] = Charges.[Invoice#]
GROUP BY Charges.[Invoice#], Charges.TaxableChk, Table1.[BillTo:], Table1.
WellName, Table1.[Parish/County], Table1.InvoiceDate, Table1.[Invoice#],
Table1.NetAmount, Table1.CompanyFrom, Table1.Tax, Charges.Expendable, Table1.
RigName, Table1.[TSWSin#], Table1.Date, Table1.AmtPaid, Table1.Balance,
Charges.netamt, Table1.Summary
HAVING (((Table1.Tax)=Sum(IIf([taxablechk]=True,[quantity]*[unitprice],0))*
[Tax]));


thanks for the fast reply!

Allen said:
And with the Sum() part?

Perhaps you could post the entire SQL statement.
I have Tax, Table1, Expression, and for criteria i have
IIf([taxablechk]=True,
Click to expand...
[quoted text clipped - 14 lines]
Click to expand...
 
Presumably TaxableChk is the yes/no field in the Charges table, so the
calculated field would be something like this query:

SELECT Sum(Charges.UnitPrice) AS SumOfUnitPrice,
Sum(Charges.Quantity) AS SumOfQuantity,
Charges.[Invoice#] AS [Charges_Invoice#],
Charges.TaxableChk,
Table1.[BillTo:],
Table1.WellName,
Table1.[Parish/County],
Table1.InvoiceDate,
Table1.[Invoice#] AS [Table1_Invoice#],
Table1.NetAmount,
Table1.CompanyFrom,
Table1.Tax,
Charges.Expendable,
Table1.RigName,
Table1.[TSWSin#],
Table1.Date,
Table1.AmtPaid,
Table1.Balance,
Charges.netamt,
Table1.Summary,
Sum([Quantity] * [UnitPrice]) AS Amount
Sum(IIf(Charges.TaxableChk, Charges.Quantity *
Charges.UnitPrice * Table1.Tax, 0)) AS TaxAmount
FROM Table1 INNER JOIN Charges
ON Table1.[Invoice#] = Charges.[Invoice#]
GROUP BY Charges.[Invoice#],
Charges.TaxableChk,
Table1.[BillTo:],
Table1.WellName,
Table1.[Parish/County],
Table1.InvoiceDate,
Table1.[Invoice#],
Table1.NetAmount,
Table1.CompanyFrom,
Table1.Tax,
Charges.Expendable,
Table1.RigName,
Table1.[TSWSin#],
Table1.Date,
Table1.AmtPaid,
Table1.Balance,
Charges.netamt,
Table1.Summary;

Note that the field named Date in Table1 is likely to give problems. It's
not a factor in the problem you are wrestling with, but it would be a good
idea to change it anyway. For an (extensive) list of the field names to
avoid, see:
Problem names and reserved words
at:
http://allenbrowne.com/AppIssueBadWord.html

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

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

stephendeloach via AccessMonster.com said:
SELECT Sum(Charges.UnitPrice) AS SumOfUnitPrice, Sum(Charges.Quantity) AS
SumOfQuantity, Charges.[Invoice#] AS [Charges_Invoice#],
Charges.TaxableChk,
Table1.[BillTo:], Table1.WellName, Table1.[Parish/County],
Table1.InvoiceDate,
Table1.[Invoice#] AS [Table1_Invoice#], Table1.NetAmount,
Table1.CompanyFrom,
Table1.Tax, Charges.Expendable, Table1.RigName, Table1.[TSWSin#],
Table1.Date,
Table1.AmtPaid, Table1.Balance, Charges.netamt, Table1.Summary,
Sum([Quantity]
*[UnitPrice]) AS Amount
FROM Table1 INNER JOIN Charges ON Table1.[Invoice#] = Charges.[Invoice#]
GROUP BY Charges.[Invoice#], Charges.TaxableChk, Table1.[BillTo:], Table1.
WellName, Table1.[Parish/County], Table1.InvoiceDate, Table1.[Invoice#],
Table1.NetAmount, Table1.CompanyFrom, Table1.Tax, Charges.Expendable,
Table1.
RigName, Table1.[TSWSin#], Table1.Date, Table1.AmtPaid, Table1.Balance,
Charges.netamt, Table1.Summary
HAVING
(((Table1.Tax)=Sum(IIf([taxablechk]=True,[quantity]*[unitprice],0))*
[Tax]));


thanks for the fast reply!

Allen said:
And with the Sum() part?

Perhaps you could post the entire SQL statement.
I have Tax, Table1, Expression, and for criteria i have
IIf([taxablechk]=True,
Click to expand...
[quoted text clipped - 14 lines]
once...
Can someone please help me?
Click to expand...
Click to expand...
Click to expand...
 
I still cant seem to get it to work... Could I sent the db to you and you
take a look at it??

Allen said:
Presumably TaxableChk is the yes/no field in the Charges table, so the
calculated field would be something like this query:

SELECT Sum(Charges.UnitPrice) AS SumOfUnitPrice,
Sum(Charges.Quantity) AS SumOfQuantity,
Charges.[Invoice#] AS [Charges_Invoice#],
Charges.TaxableChk,
Table1.[BillTo:],
Table1.WellName,
Table1.[Parish/County],
Table1.InvoiceDate,
Table1.[Invoice#] AS [Table1_Invoice#],
Table1.NetAmount,
Table1.CompanyFrom,
Table1.Tax,
Charges.Expendable,
Table1.RigName,
Table1.[TSWSin#],
Table1.Date,
Table1.AmtPaid,
Table1.Balance,
Charges.netamt,
Table1.Summary,
Sum([Quantity] * [UnitPrice]) AS Amount
Sum(IIf(Charges.TaxableChk, Charges.Quantity *
Charges.UnitPrice * Table1.Tax, 0)) AS TaxAmount
FROM Table1 INNER JOIN Charges
ON Table1.[Invoice#] = Charges.[Invoice#]
GROUP BY Charges.[Invoice#],
Charges.TaxableChk,
Table1.[BillTo:],
Table1.WellName,
Table1.[Parish/County],
Table1.InvoiceDate,
Table1.[Invoice#],
Table1.NetAmount,
Table1.CompanyFrom,
Table1.Tax,
Charges.Expendable,
Table1.RigName,
Table1.[TSWSin#],
Table1.Date,
Table1.AmtPaid,
Table1.Balance,
Charges.netamt,
Table1.Summary;

Note that the field named Date in Table1 is likely to give problems. It's
not a factor in the problem you are wrestling with, but it would be a good
idea to change it anyway. For an (extensive) list of the field names to
avoid, see:
Problem names and reserved words
at:
http://allenbrowne.com/AppIssueBadWord.html
SELECT Sum(Charges.UnitPrice) AS SumOfUnitPrice, Sum(Charges.Quantity) AS
SumOfQuantity, Charges.[Invoice#] AS [Charges_Invoice#],
Click to expand...
[quoted text clipped - 30 lines]
Click to expand...
 
Sorry, Stephen. We can't accept everyone's databases, or we would never get
any work done.

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

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

stephendeloach via AccessMonster.com said:
I still cant seem to get it to work... Could I sent the db to you and you
take a look at it??

Allen said:
Presumably TaxableChk is the yes/no field in the Charges table, so the
calculated field would be something like this query:

SELECT Sum(Charges.UnitPrice) AS SumOfUnitPrice,
Sum(Charges.Quantity) AS SumOfQuantity,
Charges.[Invoice#] AS [Charges_Invoice#],
Charges.TaxableChk,
Table1.[BillTo:],
Table1.WellName,
Table1.[Parish/County],
Table1.InvoiceDate,
Table1.[Invoice#] AS [Table1_Invoice#],
Table1.NetAmount,
Table1.CompanyFrom,
Table1.Tax,
Charges.Expendable,
Table1.RigName,
Table1.[TSWSin#],
Table1.Date,
Table1.AmtPaid,
Table1.Balance,
Charges.netamt,
Table1.Summary,
Sum([Quantity] * [UnitPrice]) AS Amount
Sum(IIf(Charges.TaxableChk, Charges.Quantity *
Charges.UnitPrice * Table1.Tax, 0)) AS TaxAmount
FROM Table1 INNER JOIN Charges
ON Table1.[Invoice#] = Charges.[Invoice#]
GROUP BY Charges.[Invoice#],
Charges.TaxableChk,
Table1.[BillTo:],
Table1.WellName,
Table1.[Parish/County],
Table1.InvoiceDate,
Table1.[Invoice#],
Table1.NetAmount,
Table1.CompanyFrom,
Table1.Tax,
Charges.Expendable,
Table1.RigName,
Table1.[TSWSin#],
Table1.Date,
Table1.AmtPaid,
Table1.Balance,
Charges.netamt,
Table1.Summary;

Note that the field named Date in Table1 is likely to give problems. It's
not a factor in the problem you are wrestling with, but it would be a good
idea to change it anyway. For an (extensive) list of the field names to
avoid, see:
Problem names and reserved words
at:
http://allenbrowne.com/AppIssueBadWord.html
SELECT Sum(Charges.UnitPrice) AS SumOfUnitPrice, Sum(Charges.Quantity)
AS
SumOfQuantity, Charges.[Invoice#] AS [Charges_Invoice#],
Click to expand...
[quoted text clipped - 30 lines]
once...
Can someone please help me?
Click to expand...
Click to expand...
Click to expand...
 
Understandable. So back to square one. Where would I need to put Sum(IIf
(Charges.TaxableChk, Charges.Quantity * Charges.UnitPrice * Table1.Tax, 0))
AS TaxAmount ??


Allen said:
Sorry, Stephen. We can't accept everyone's databases, or we would never get
any work done.
I still cant seem to get it to work... Could I sent the db to you and you
take a look at it??
Click to expand...
[quoted text clipped - 60 lines]
Click to expand...
 
Back
Top