Report/query provides too much info!!

  • Thread starter Thread starter jwr
  • Start date Start date
J

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.
 
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
 
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
 
I would use subreports and remove the Order Details from your main report.

--
Duane Hookom
MS Access MVP
--

jwr said:
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
Click to expand...
 
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

Duane Hookom said:
I would use subreports and remove the Order Details from your main report.

--
Duane Hookom
MS Access MVP
--

jwr said:
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 said:
Please share a little about your table structure and report structure
Click to expand...
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
Click to expand...
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.
Click to expand...
Click to expand...
Click to expand...
 
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


jwr said:
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

Duane Hookom said:
I would use subreports and remove the Order Details from your main
report.

--
Duane Hookom
MS Access MVP
--

jwr said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top