Report/query provides too much info!!

[jwr]

I have created a query and then a report from that query. Within the query
are numerous tables. When I, for instance, want to see the payments made
against an invoice, I get the total amount paid on that invoice listed as if
that total amount was paid for each
line item rather than one total amount paid by invoice.

How do I handle this problem? This needs to be a report and not a form.

 

[Duane Hookom]

Please share a little about your table structure and report structure in the
future. Also some sample data would be nice.

I assume you can
-set a running sum on a text box in the report section containing unique
total amount paid
-use subreports for detail information
-divide the sum of amount paid by the number of line items

 

[jwr]

Following is the SQL

SELECT Payments.PaymentDate, Contracts.ContractNum, SIN.SIN,
Products.ProductCode, Products.ProductName, [Order Details].Quantity, [Order
Details].UnitPrice, Customers.ControlNum, Payments.PaymentAmount
FROM ((Customers INNER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID) INNER JOIN (((Contracts INNER JOIN ContractSIN_XRef ON
Contracts.ContractID = ContractSIN_XRef.ContractID) INNER JOIN (SIN INNER
JOIN Products ON SIN.SINID = Products.SINID) ON (SIN.SINID =
ContractSIN_XRef.SINID) AND (Contracts.ContractID = Products.ContractID))
INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID) ON Orders.OrderID = [Order Details].OrderID) LEFT JOIN
Payments ON Orders.OrderID = Payments.OrderID
ORDER BY Contracts.ContractNum DESC;


I want to see several things that I have not been able to accomplish.

In the info below, you will see that Payment amount of $10,500 is listed 3
times because ControlNum 52074 has 3 line items on their sales invoice. I
want to see the actual amount paid per invoice; i.e. $10,500 in lieu of
$31,500; by date paid by SIN.

I would also like to be able to have a total amount per invoice as well as
individual line items.

72A Query Payment Date Contract SIN Order Code Product Name Quantity Unit
Price ControlNum Payment Amount

GS-07F-9957H 251-01 1973W DEMO MILITARY GATOR 1 $7,000.00 52031

8/7/2005 GS-07F-9957H 251-01 1004 run flat tire upgrade 5 $210.00
52074 $10,500.00
8/7/2005 GS-07F-9957H 251-01 123456 Military Gator Utility Vehicle 1
$14,003.86 52074 $10,500.00

GS-07F-8721D 341-700 BM20923 WINDSHIELD 3 $122.74 52034


GS-07F-8721D 341-100 0062TC/1001/2000 1420 SERIES2 FRONT MOWER/4WD 1
$13,416.32 10788


GS-07F-8721D 341-700 BM20454/BM20988/BM21380 2 BAG COLLECTION SYS/48"
MOWER 1 $638.73 10788

8/7/2005 GS-07F-8721D 341-100 0062TC/1001/2000 1420 SERIES2 FRONT
MOWER/4WD 5 $15,000.00 52074 $10,500.00


Please let me know if I have provided information requested.

Thank you very much in advance.
Joy

 

[Duane Hookom]

I would use subreports and remove the Order Details from your main report.

--
Duane Hookom
MS Access MVP
--

Following is the SQL

SELECT Payments.PaymentDate, Contracts.ContractNum, SIN.SIN,
Products.ProductCode, Products.ProductName, [Order Details].Quantity,
[Order
Details].UnitPrice, Customers.ControlNum, Payments.PaymentAmount
FROM ((Customers INNER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID) INNER JOIN (((Contracts INNER JOIN ContractSIN_XRef ON
Contracts.ContractID = ContractSIN_XRef.ContractID) INNER JOIN (SIN INNER
JOIN Products ON SIN.SINID = Products.SINID) ON (SIN.SINID =
ContractSIN_XRef.SINID) AND (Contracts.ContractID = Products.ContractID))
INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID) ON Orders.OrderID = [Order Details].OrderID) LEFT JOIN
Payments ON Orders.OrderID = Payments.OrderID
ORDER BY Contracts.ContractNum DESC;


I want to see several things that I have not been able to accomplish.

In the info below, you will see that Payment amount of $10,500 is listed 3
times because ControlNum 52074 has 3 line items on their sales invoice. I
want to see the actual amount paid per invoice; i.e. $10,500 in lieu of
$31,500; by date paid by SIN.

I would also like to be able to have a total amount per invoice as well as
individual line items.

72A Query Payment Date Contract SIN Order Code Product Name Quantity Unit
Price ControlNum Payment Amount

GS-07F-9957H 251-01 1973W DEMO MILITARY GATOR 1 $7,000.00 52031

8/7/2005 GS-07F-9957H 251-01 1004 run flat tire upgrade 5 $210.00
52074 $10,500.00
8/7/2005 GS-07F-9957H 251-01 123456 Military Gator Utility Vehicle 1
$14,003.86 52074 $10,500.00

GS-07F-8721D 341-700 BM20923 WINDSHIELD 3 $122.74 52034


GS-07F-8721D 341-100 0062TC/1001/2000 1420 SERIES2 FRONT MOWER/4WD 1
$13,416.32 10788


GS-07F-8721D 341-700 BM20454/BM20988/BM21380 2 BAG COLLECTION SYS/48"
MOWER 1 $638.73 10788

8/7/2005 GS-07F-8721D 341-100 0062TC/1001/2000 1420 SERIES2 FRONT
MOWER/4WD 5 $15,000.00 52074 $10,500.00


Please let me know if I have provided information requested.

Thank you very much in advance.
Joy

 

[jwr]

Duane --
Please excuse my ignorance.
Are you suggesting that I NOT use the query?
Or are you suggesting that I create new reports?

Can you show me or direct me with some examples?
i.a. -- what subreports would I create to eliminate the duplication?

Thanks,
Joy

I would use subreports and remove the Order Details from your main report.

--
Duane Hookom
MS Access MVP
--

Following is the SQL

SELECT Payments.PaymentDate, Contracts.ContractNum, SIN.SIN,
Products.ProductCode, Products.ProductName, [Order Details].Quantity,
[Order
Details].UnitPrice, Customers.ControlNum, Payments.PaymentAmount
FROM ((Customers INNER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID) INNER JOIN (((Contracts INNER JOIN ContractSIN_XRef ON
Contracts.ContractID = ContractSIN_XRef.ContractID) INNER JOIN (SIN INNER
JOIN Products ON SIN.SINID = Products.SINID) ON (SIN.SINID =
ContractSIN_XRef.SINID) AND (Contracts.ContractID = Products.ContractID))
INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID) ON Orders.OrderID = [Order Details].OrderID) LEFT JOIN
Payments ON Orders.OrderID = Payments.OrderID
ORDER BY Contracts.ContractNum DESC;


I want to see several things that I have not been able to accomplish.

In the info below, you will see that Payment amount of $10,500 is listed 3
times because ControlNum 52074 has 3 line items on their sales invoice. I
want to see the actual amount paid per invoice; i.e. $10,500 in lieu of
$31,500; by date paid by SIN.

I would also like to be able to have a total amount per invoice as well as
individual line items.

72A Query Payment Date Contract SIN Order Code Product Name Quantity Unit
Price ControlNum Payment Amount

GS-07F-9957H 251-01 1973W DEMO MILITARY GATOR 1 $7,000.00 52031

8/7/2005 GS-07F-9957H 251-01 1004 run flat tire upgrade 5 $210.00
52074 $10,500.00
8/7/2005 GS-07F-9957H 251-01 123456 Military Gator Utility Vehicle 1
$14,003.86 52074 $10,500.00

GS-07F-8721D 341-700 BM20923 WINDSHIELD 3 $122.74 52034


GS-07F-8721D 341-100 0062TC/1001/2000 1420 SERIES2 FRONT MOWER/4WD 1
$13,416.32 10788


GS-07F-8721D 341-700 BM20454/BM20988/BM21380 2 BAG COLLECTION SYS/48"
MOWER 1 $638.73 10788

8/7/2005 GS-07F-8721D 341-100 0062TC/1001/2000 1420 SERIES2 FRONT
MOWER/4WD 5 $15,000.00 52074 $10,500.00


Please let me know if I have provided information requested.

Thank you very much in advance.
Joy

Please share a little about your table structure and report structure

in
the

future. Also some sample data would be nice.

I assume you can
-set a running sum on a text box in the report section containing unique
total amount paid
-use subreports for detail information
-divide the sum of amount paid by the number of line items

--
Duane Hookom
MS Access MVP


I have created a query and then a report from that query. Within the
query
are numerous tables. When I, for instance, want to see the payments made
against an invoice, I get the total amount paid on that invoice

listed
as

if
that total amount was paid for each
line item rather than one total amount paid by invoice.

How do I handle this problem? This needs to be a report and not a
form.

 

[Duane Hookom]

I would not use your query as the source for my main report. Consider these
tables

tblFamilies
===============
FamID autonumber primary key
FamAddress
FamMonthlyIncome Currency
FamCity

tblFamilyMembers
=================
FamMemID
FamID link to tblFamilies!FamID
FirstName
LastName
.....

If you create a report with both of these tables and want to total the
FamMonthlyIncome in a FamCity footer, you will end up with an inflated total
since a family generally has more than one member.

You can create a report of just tblFamilies and your totals would be
correct. However, you might want to see all the family members. To do this,
create a subreport based on tblFamilyMembers. Add this subreport to your
main report and set the Link Master/Child properties to FamID. This will not
affect your FamMontlyIncome totals in your main report.

--
Duane Hookom
MS Access MVP

Duane --
Please excuse my ignorance.
Are you suggesting that I NOT use the query?
Or are you suggesting that I create new reports?

Can you show me or direct me with some examples?
i.a. -- what subreports would I create to eliminate the duplication?

Thanks,
Joy

I would use subreports and remove the Order Details from your main
report.

--
Duane Hookom
MS Access MVP
--

Following is the SQL

SELECT Payments.PaymentDate, Contracts.ContractNum, SIN.SIN,
Products.ProductCode, Products.ProductName, [Order Details].Quantity,
[Order
Details].UnitPrice, Customers.ControlNum, Payments.PaymentAmount
FROM ((Customers INNER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID) INNER JOIN (((Contracts INNER JOIN ContractSIN_XRef ON
Contracts.ContractID = ContractSIN_XRef.ContractID) INNER JOIN (SIN INNER
JOIN Products ON SIN.SINID = Products.SINID) ON (SIN.SINID =
ContractSIN_XRef.SINID) AND (Contracts.ContractID = Products.ContractID))
INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID) ON Orders.OrderID = [Order Details].OrderID) LEFT JOIN
Payments ON Orders.OrderID = Payments.OrderID
ORDER BY Contracts.ContractNum DESC;


I want to see several things that I have not been able to accomplish.

In the info below, you will see that Payment amount of $10,500 is
listed 3
times because ControlNum 52074 has 3 line items on their sales invoice. I
want to see the actual amount paid per invoice; i.e. $10,500 in lieu of
$31,500; by date paid by SIN.

I would also like to be able to have a total amount per invoice as well as
individual line items.

72A Query Payment Date Contract SIN Order Code Product Name Quantity Unit
Price ControlNum Payment Amount

GS-07F-9957H 251-01 1973W DEMO MILITARY GATOR 1 $7,000.00 52031

8/7/2005 GS-07F-9957H 251-01 1004 run flat tire upgrade 5 $210.00
52074 $10,500.00
8/7/2005 GS-07F-9957H 251-01 123456 Military Gator Utility Vehicle 1
$14,003.86 52074 $10,500.00

GS-07F-8721D 341-700 BM20923 WINDSHIELD 3 $122.74 52034


GS-07F-8721D 341-100 0062TC/1001/2000 1420 SERIES2 FRONT MOWER/4WD
1
$13,416.32 10788


GS-07F-8721D 341-700 BM20454/BM20988/BM21380 2 BAG COLLECTION SYS/48"
MOWER 1 $638.73 10788

8/7/2005 GS-07F-8721D 341-100 0062TC/1001/2000 1420 SERIES2 FRONT
MOWER/4WD 5 $15,000.00 52074 $10,500.00


Please let me know if I have provided information requested.

Thank you very much in advance.
Joy



Please share a little about your table structure and report structure in
the
future. Also some sample data would be nice.

I assume you can
-set a running sum on a text box in the report section containing unique
total amount paid
-use subreports for detail information
-divide the sum of amount paid by the number of line items

--
Duane Hookom
MS Access MVP


I have created a query and then a report from that query. Within
the
query
are numerous tables. When I, for instance, want to see the payments
made
against an invoice, I get the total amount paid on that invoice listed
as
if
that total amount was paid for each
line item rather than one total amount paid by invoice.

How do I handle this problem? This needs to be a report and not a
form.