SELECT Payments.Payment,jwrnana said:This is the SQL for the Query that I am using. I tried to remove the
Payments table, but got errors when I typed in the SQL as you suggested in
a
fresh field in my query. I get syntax error. I changed your suggestion
to
read with the correct spelling, etc.
Allen Browne said:There's a fair bit going on in that query. Suggestions:
1. To get the subquery working correctly, create a new query into just the
Orders table. Type the subquery into the field, and get it working. Once it
is working, you can copy the expression into the more involved query.
2. Now you have the subquery working, replace the bad one in your original
query with this one. In the Total row of this subquery field, change Group
By to Expression.
3. Remove the Payments table from this query. This will cause it to drop out
the payment number, amount, essettdate, and essettnumber fields. Presumably
you don't need these if you are wanting the total value of payments instead
of the individual payments.
4. It looks like payments are credited against the OrderID, yet OrderID is
not one of the fields your query is grouping on. The query should show the
correct total payments for the order, but if it is not showing which order
that is, I'm not sure the data has any meaning.
5. If you do add the OrderID field to the output grid and Group By this
field, the query should should the correct total payments received for the
order. However, if the OrderID is duplicated (because of grouping on the
other fields) and you sum the amounts, you will still have duplicated
totals.
6. The HAVING clause is not going to work like that. If you are actually
trying to go the other direction, and group by payments, you could use the
subquery on the order information.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
SELECT Payments.Payment,jwrnana said:This is the SQL for the Query that I am using. I tried to remove the
Payments table, but got errors when I typed in the SQL as you suggested in
a
fresh field in my query. I get syntax error. I changed your suggestion
to
read with the correct spelling, etc.
Contracts.ContractNum,
SIN.SIN,
Products.ProductCode,
Products.ProductName,
[Order Details].Quantity,
Sum([Order Details].UnitPrice) AS SumOfUnitPrice,
Customers.ControlNum,
Payments.PaymentAmount,
Payments.EssettDate,
Payments.EssettNumber,
(SELECT CCur(Nz(Sum(Payments.PaymentAmount).0))
FROM Payments WHERE Payments.OrderID = Orders.OrderID) AS Received
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)
INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID)
ON (SIN.SINID = ContractSIN_XRef.SINID) AND (Contracts.ContractID =
Products.ContractID))
ON Orders.OrderID = [Order Details].OrderID) LEFT JOIN Payments
ON Orders.OrderID = Payments.OrderID
GROUP BY Payments.PaymentDate,
Contracts.ContractNum,
SIN.SIN,
Products.ProductCode,
Products.ProductName,
[Order Details].Quantity,
Customers.ControlNum,
Payments.PaymentAmount,
Payments.EssettDate,
Payments.EssettNumber,
(SELECT CCur(Nz(Sum(Payments.PaymentAmount).0))
FROM Payments WHERE Payments.OrderID = Orders.OrderID)
HAVING (((Payments.PaymentDate)>=[forms]![Report Date Range]![Beginning
Order Date]
And (Payments.PaymentDate)<=[forms]![Report Date Range]![Ending Order
Date]))
ORDER BY Contracts.ContractNum DESC;
jwrnana said:My apologies for my ignorance --
1. I attempted to create a query using only the orders table. I typed
in
the information "Received: (SELECT
CCur(Nz(Sum(Payment.Amount),0)) FROM Payments WHERE Payments.OrderID =
Orders.OrderID).
When I run the query, I get parameter box for Payment Amount and Order ID.
Naturally, I get zero payment in that field.
Question: How can I use only the Orders Table and have the query pull
information from the Payments Table?? I don't understand.
2. I then went to the original query and changed only one thing -- the
addition of OrderID. I do get the OrderID, but of course still have
duplicate info.
3. I do need this information.
4. I did add OrderID, group by. Info is duplicated because of grouping
on
other fields. I cannot change the query where there is grouping only on
the
OrderID, can I?
Please let me explain what I am attempting to do.
1. Customer places order == I am using OrderID to also represent Invoice
No. instead of having another number.
2. Customer pays - sometimes in installments against an OrderID(Invoice
Number).
3. When customer pays me, I have 7 days to pay my vendor. However,
payment
is not due until payment has been received in full.
a. I still need to see individual payments.
4. When I pay vendor, the information is inserted into the Payments form
(selecting command button on customer orders screen); i.e. - the essett
date, amount, essett number.
a. This allows me to see if I have received a payment from the
customer
and neglected to pay the vendor.
5. I need to see the total of each payment against each OrderID(Invoice
Number).
I am going in a circle. Do I need to create numerous queries and then a
query using those queries? I have attempted on several occasions to add a
query to another query without any success.
Your assistance is greatly appreciated.
Thank you for your patience.
JR
Allen Browne said:There's a fair bit going on in that query. Suggestions:
1. To get the subquery working correctly, create a new query into just
the
Orders table. Type the subquery into the field, and get it working. Once it
is working, you can copy the expression into the more involved query.
2. Now you have the subquery working, replace the bad one in your
original
query with this one. In the Total row of this subquery field, change
Group
By to Expression.
3. Remove the Payments table from this query. This will cause it to drop out
the payment number, amount, essettdate, and essettnumber fields. Presumably
you don't need these if you are wanting the total value of payments instead
of the individual payments.
4. It looks like payments are credited against the OrderID, yet OrderID
is
not one of the fields your query is grouping on. The query should show
the
correct total payments for the order, but if it is not showing which
order
that is, I'm not sure the data has any meaning.
5. If you do add the OrderID field to the output grid and Group By this
field, the query should should the correct total payments received for
the
order. However, if the OrderID is duplicated (because of grouping on the
other fields) and you sum the amounts, you will still have duplicated
totals.
6. The HAVING clause is not going to work like that. If you are actually
trying to go the other direction, and group by payments, you could use
the
subquery on the order information.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
SELECT Payments.Payment,jwrnana said:This is the SQL for the Query that I am using. I tried to remove the
Payments table, but got errors when I typed in the SQL as you suggested in
a
fresh field in my query. I get syntax error. I changed your
suggestion
to
read with the correct spelling, etc.
Contracts.ContractNum,
SIN.SIN,
Products.ProductCode,
Products.ProductName,
[Order Details].Quantity,
Sum([Order Details].UnitPrice) AS SumOfUnitPrice,
Customers.ControlNum,
Payments.PaymentAmount,
Payments.EssettDate,
Payments.EssettNumber,
(SELECT CCur(Nz(Sum(Payments.PaymentAmount).0))
FROM Payments WHERE Payments.OrderID = Orders.OrderID) AS Received
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)
INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID)
ON (SIN.SINID = ContractSIN_XRef.SINID) AND (Contracts.ContractID =
Products.ContractID))
ON Orders.OrderID = [Order Details].OrderID) LEFT JOIN Payments
ON Orders.OrderID = Payments.OrderID
GROUP BY Payments.PaymentDate,
Contracts.ContractNum,
SIN.SIN,
Products.ProductCode,
Products.ProductName,
[Order Details].Quantity,
Customers.ControlNum,
Payments.PaymentAmount,
Payments.EssettDate,
Payments.EssettNumber,
(SELECT CCur(Nz(Sum(Payments.PaymentAmount).0))
FROM Payments WHERE Payments.OrderID = Orders.OrderID)
HAVING (((Payments.PaymentDate)>=[forms]![Report Date Range]![Beginning
Order Date]
And (Payments.PaymentDate)<=[forms]![Report Date Range]![Ending Order
Date]))
ORDER BY Contracts.ContractNum DESC;
There are a couple of ways to solve this.
Presumably you have tables like this:
- Invoice (one record for each invoice);
- InvoiceDetail (the line items on the invoice);
- Payment (one record for each payment received.)
I take it your Payment table has a foreign key to the Invoice table,
so
you identify a payment with an invoice.
You have created a total query to Group By the invoice number, and Sum
the
values from InvoiceDetail and Payment. The problem is that if an invoice
has 2 line items you end up with double the payment amount.
To solve that, remove the Payment table from the query. Instead type a
subquery into a fresh column in the Field row in query design. The
line
will look something like this:
Received: (SELECT CCur(Nz(Sum(Payment.Amount),0))
FROM Payment WHERE Payment.InvoiceID = Invoice.InvoiceID)
It is possible that when you create a report on this query, you could end
up with "Multi-level group-by not allowed" error. Post back if you
strike that issue.
I posted this message previously and Allen Brown was replying. I am at
another computer and cannot see his reply. Can anyone assist in this?
I have a report based upon payments received by customers. If, for
instance, the invoice that the customer is paying has several line
itmes on it and they make several payments, my report lists the
line
items over and over again as being paid.
Example: Invoice = $500
line Items Purchased = 2
Payments $100
$400
Report shows they paid $1000 (2 payments x 2 line items)
How do I prevent this? My report is based upon a query. I am
using
XP Pro and Access 2003.
Allen Browne said:Your table seems to be named Payments (with an S), so you will need to use
Payments.Amount (not Payment.Amount) to get the subquery to work.
For an explanation of how subqueries work, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
The only way you are going to get this solved is to break the problem down
into smaller chunks, and build up from there. Start with a query that
contains only the Order and Order Detail tables. Group by OrderID. Sum the
amount. Get the payments from a subquery.
Once you have that working, you can then use that query as a source "table"
into another query that picks up the other information you need to look up
as well.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
jwrnana said:My apologies for my ignorance --
1. I attempted to create a query using only the orders table. I typed
in
the information "Received: (SELECT
CCur(Nz(Sum(Payment.Amount),0)) FROM Payments WHERE Payments.OrderID =
Orders.OrderID).
When I run the query, I get parameter box for Payment Amount and Order ID.
Naturally, I get zero payment in that field.
Question: How can I use only the Orders Table and have the query pull
information from the Payments Table?? I don't understand.
2. I then went to the original query and changed only one thing -- the
addition of OrderID. I do get the OrderID, but of course still have
duplicate info.
3. I do need this information.
4. I did add OrderID, group by. Info is duplicated because of grouping
on
other fields. I cannot change the query where there is grouping only on
the
OrderID, can I?
Please let me explain what I am attempting to do.
1. Customer places order == I am using OrderID to also represent Invoice
No. instead of having another number.
2. Customer pays - sometimes in installments against an OrderID(Invoice
Number).
3. When customer pays me, I have 7 days to pay my vendor. However,
payment
is not due until payment has been received in full.
a. I still need to see individual payments.
4. When I pay vendor, the information is inserted into the Payments form
(selecting command button on customer orders screen); i.e. - the essett
date, amount, essett number.
a. This allows me to see if I have received a payment from the
customer
and neglected to pay the vendor.
5. I need to see the total of each payment against each OrderID(Invoice
Number).
I am going in a circle. Do I need to create numerous queries and then a
query using those queries? I have attempted on several occasions to add a
query to another query without any success.
Your assistance is greatly appreciated.
Thank you for your patience.
JR
OnceAllen Browne said:There's a fair bit going on in that query. Suggestions:
1. To get the subquery working correctly, create a new query into just
the
Orders table. Type the subquery into the field, and get it working.
itdropis working, you can copy the expression into the more involved query.
2. Now you have the subquery working, replace the bad one in your
original
query with this one. In the Total row of this subquery field, change
Group
By to Expression.
3. Remove the Payments table from this query. This will cause it to
outsuggestedthe payment number, amount, essettdate, and essettnumber fields. Presumably
you don't need these if you are wanting the total value of payments instead
of the individual payments.
4. It looks like payments are credited against the OrderID, yet OrderID
is
not one of the fields your query is grouping on. The query should show
the
correct total payments for the order, but if it is not showing which
order
that is, I'm not sure the data has any meaning.
5. If you do add the OrderID field to the output grid and Group By this
field, the query should should the correct total payments received for
the
order. However, if the OrderID is duplicated (because of grouping on the
other fields) and you sum the amounts, you will still have duplicated
totals.
6. The HAVING clause is not going to work like that. If you are actually
trying to go the other direction, and group by payments, you could use
the
subquery on the order information.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
This is the SQL for the Query that I am using. I tried to remove the
Payments table, but got errors when I typed in the SQL as you
incoulda
fresh field in my query. I get syntax error. I changed your
suggestion
to
read with the correct spelling, etc.
SELECT Payments.Payment,
Contracts.ContractNum,
SIN.SIN,
Products.ProductCode,
Products.ProductName,
[Order Details].Quantity,
Sum([Order Details].UnitPrice) AS SumOfUnitPrice,
Customers.ControlNum,
Payments.PaymentAmount,
Payments.EssettDate,
Payments.EssettNumber,
(SELECT CCur(Nz(Sum(Payments.PaymentAmount).0))
FROM Payments WHERE Payments.OrderID = Orders.OrderID) AS Received
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)
INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID)
ON (SIN.SINID = ContractSIN_XRef.SINID) AND (Contracts.ContractID =
Products.ContractID))
ON Orders.OrderID = [Order Details].OrderID) LEFT JOIN Payments
ON Orders.OrderID = Payments.OrderID
GROUP BY Payments.PaymentDate,
Contracts.ContractNum,
SIN.SIN,
Products.ProductCode,
Products.ProductName,
[Order Details].Quantity,
Customers.ControlNum,
Payments.PaymentAmount,
Payments.EssettDate,
Payments.EssettNumber,
(SELECT CCur(Nz(Sum(Payments.PaymentAmount).0))
FROM Payments WHERE Payments.OrderID = Orders.OrderID)
HAVING (((Payments.PaymentDate)>=[forms]![Report Date Range]![Beginning
Order Date]
And (Payments.PaymentDate)<=[forms]![Report Date Range]![Ending Order
Date]))
ORDER BY Contracts.ContractNum DESC;
There are a couple of ways to solve this.
Presumably you have tables like this:
- Invoice (one record for each invoice);
- InvoiceDetail (the line items on the invoice);
- Payment (one record for each payment received.)
I take it your Payment table has a foreign key to the Invoice table,
so
you identify a payment with an invoice.
You have created a total query to Group By the invoice number, and Sum
the
values from InvoiceDetail and Payment. The problem is that if an invoice
has 2 line items you end up with double the payment amount.
To solve that, remove the Payment table from the query. Instead type a
subquery into a fresh column in the Field row in query design. The
line
will look something like this:
Received: (SELECT CCur(Nz(Sum(Payment.Amount),0))
FROM Payment WHERE Payment.InvoiceID = Invoice.InvoiceID)
It is possible that when you create a report on this query, you
endamup with "Multi-level group-by not allowed" error. Post back if you
strike that issue.
I posted this message previously and Allen Brown was replying. I
atanother computer and cannot see his reply. Can anyone assist in this?
I have a report based upon payments received by customers. If, for
instance, the invoice that the customer is paying has several line
itmes on it and they make several payments, my report lists the
line
items over and over again as being paid.
Example: Invoice = $500
line Items Purchased = 2
Payments $100
$400
Report shows they paid $1000 (2 payments x 2 line items)
How do I prevent this? My report is based upon a query. I am
using
XP Pro and Access 2003.
jwrnana said:Thank you. I got my first query built and working -- with one question.
If
I have more than one payment date, how do I list each payment separately
so
that I can see each payment date (my payment to vendor is due 7 days after
paydate) rather than totaling all payments together?
JR
Allen Browne said:Your table seems to be named Payments (with an S), so you will need to
use
Payments.Amount (not Payment.Amount) to get the subquery to work.
For an explanation of how subqueries work, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
The only way you are going to get this solved is to break the problem
down
into smaller chunks, and build up from there. Start with a query that
contains only the Order and Order Detail tables. Group by OrderID. Sum
the
amount. Get the payments from a subquery.
Once you have that working, you can then use that query as a source "table"
into another query that picks up the other information you need to look
up
as well.
jwrnana said:My apologies for my ignorance --
1. I attempted to create a query using only the orders table. I
typed
in
the information "Received: (SELECT
CCur(Nz(Sum(Payment.Amount),0)) FROM Payments WHERE Payments.OrderID =
Orders.OrderID).
When I run the query, I get parameter box for Payment Amount and Order ID.
Naturally, I get zero payment in that field.
Question: How can I use only the Orders Table and have the query pull
information from the Payments Table?? I don't understand.
2. I then went to the original query and changed only one thing --
the
addition of OrderID. I do get the OrderID, but of course still have
duplicate info.
3. I do need this information.
4. I did add OrderID, group by. Info is duplicated because of
grouping
on
other fields. I cannot change the query where there is grouping only
on
the
OrderID, can I?
Please let me explain what I am attempting to do.
1. Customer places order == I am using OrderID to also represent Invoice
No. instead of having another number.
2. Customer pays - sometimes in installments against an
OrderID(Invoice
Number).
3. When customer pays me, I have 7 days to pay my vendor. However,
payment
is not due until payment has been received in full.
a. I still need to see individual payments.
4. When I pay vendor, the information is inserted into the Payments form
(selecting command button on customer orders screen); i.e. - the essett
date, amount, essett number.
a. This allows me to see if I have received a payment from the
customer
and neglected to pay the vendor.
5. I need to see the total of each payment against each
OrderID(Invoice
Number).
I am going in a circle. Do I need to create numerous queries and then
a
query using those queries? I have attempted on several occasions to
add a
query to another query without any success.
Your assistance is greatly appreciated.
Thank you for your patience.
JR
There's a fair bit going on in that query. Suggestions:
1. To get the subquery working correctly, create a new query into just
the
Orders table. Type the subquery into the field, and get it working. Once
it
is working, you can copy the expression into the more involved query.
2. Now you have the subquery working, replace the bad one in your
original
query with this one. In the Total row of this subquery field, change
Group
By to Expression.
3. Remove the Payments table from this query. This will cause it to drop
out
the payment number, amount, essettdate, and essettnumber fields.
Presumably
you don't need these if you are wanting the total value of payments
instead
of the individual payments.
4. It looks like payments are credited against the OrderID, yet
OrderID
is
not one of the fields your query is grouping on. The query should show
the
correct total payments for the order, but if it is not showing which
order
that is, I'm not sure the data has any meaning.
5. If you do add the OrderID field to the output grid and Group By
this
field, the query should should the correct total payments received for
the
order. However, if the OrderID is duplicated (because of grouping on the
other fields) and you sum the amounts, you will still have duplicated
totals.
6. The HAVING clause is not going to work like that. If you are actually
trying to go the other direction, and group by payments, you could use
the
subquery on the order information.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
This is the SQL for the Query that I am using. I tried to remove
the
Payments table, but got errors when I typed in the SQL as you suggested
in
a
fresh field in my query. I get syntax error. I changed your
suggestion
to
read with the correct spelling, etc.
SELECT Payments.Payment,
Contracts.ContractNum,
SIN.SIN,
Products.ProductCode,
Products.ProductName,
[Order Details].Quantity,
Sum([Order Details].UnitPrice) AS SumOfUnitPrice,
Customers.ControlNum,
Payments.PaymentAmount,
Payments.EssettDate,
Payments.EssettNumber,
(SELECT CCur(Nz(Sum(Payments.PaymentAmount).0))
FROM Payments WHERE Payments.OrderID = Orders.OrderID) AS Received
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)
INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID)
ON (SIN.SINID = ContractSIN_XRef.SINID) AND (Contracts.ContractID =
Products.ContractID))
ON Orders.OrderID = [Order Details].OrderID) LEFT JOIN Payments
ON Orders.OrderID = Payments.OrderID
GROUP BY Payments.PaymentDate,
Contracts.ContractNum,
SIN.SIN,
Products.ProductCode,
Products.ProductName,
[Order Details].Quantity,
Customers.ControlNum,
Payments.PaymentAmount,
Payments.EssettDate,
Payments.EssettNumber,
(SELECT CCur(Nz(Sum(Payments.PaymentAmount).0))
FROM Payments WHERE Payments.OrderID = Orders.OrderID)
HAVING (((Payments.PaymentDate)>=[forms]![Report Date
Range]![Beginning
Order Date]
And (Payments.PaymentDate)<=[forms]![Report Date Range]![Ending Order
Date]))
ORDER BY Contracts.ContractNum DESC;
There are a couple of ways to solve this.
Presumably you have tables like this:
- Invoice (one record for each invoice);
- InvoiceDetail (the line items on the invoice);
- Payment (one record for each payment received.)
I take it your Payment table has a foreign key to the Invoice
table,
so
you identify a payment with an invoice.
You have created a total query to Group By the invoice number, and Sum
the
values from InvoiceDetail and Payment. The problem is that if an
invoice
has 2 line items you end up with double the payment amount.
To solve that, remove the Payment table from the query. Instead
type a
subquery into a fresh column in the Field row in query design. The
line
will look something like this:
Received: (SELECT CCur(Nz(Sum(Payment.Amount),0))
FROM Payment WHERE Payment.InvoiceID = Invoice.InvoiceID)
It is possible that when you create a report on this query, you could
end
up with "Multi-level group-by not allowed" error. Post back if you
strike that issue.
I posted this message previously and Allen Brown was replying. I am
at
another computer and cannot see his reply. Can anyone assist in
this?
I have a report based upon payments received by customers. If, for
instance, the invoice that the customer is paying has several line
itmes on it and they make several payments, my report lists the
line
items over and over again as being paid.
Example: Invoice = $500
line Items Purchased = 2
Payments $100
$400
Report shows they paid $1000 (2 payments x 2 line items)
How do I prevent this? My report is based upon a query. I am
using
XP Pro and Access 2003.
Allen Browne said:If you want to list each payment separately, without duplicating the values
where you have repeating Order numbers in the main query, then I think you
better try creating a subreport to show the payments.
The main report will show the order, and in the OrderID footer, place the
subreport to show the payments.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
jwrnana said:Thank you. I got my first query built and working -- with one question.
If
I have more than one payment date, how do I list each payment separately
so
that I can see each payment date (my payment to vendor is due 7 days after
paydate) rather than totaling all payments together?
JR
OrderAllen Browne said:Your table seems to be named Payments (with an S), so you will need to
use
Payments.Amount (not Payment.Amount) to get the subquery to work.
For an explanation of how subqueries work, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
The only way you are going to get this solved is to break the problem
down
into smaller chunks, and build up from there. Start with a query that
contains only the Order and Order Detail tables. Group by OrderID. Sum
the
amount. Get the payments from a subquery.
Once you have that working, you can then use that query as a source "table"
into another query that picks up the other information you need to look
up
as well.
My apologies for my ignorance --
1. I attempted to create a query using only the orders table. I
typed
in
the information "Received: (SELECT
CCur(Nz(Sum(Payment.Amount),0)) FROM Payments WHERE Payments.OrderID =
Orders.OrderID).
When I run the query, I get parameter box for Payment Amount and
ID.andNaturally, I get zero payment in that field.
Question: How can I use only the Orders Table and have the query pull
information from the Payments Table?? I don't understand.
2. I then went to the original query and changed only one thing --
the
addition of OrderID. I do get the OrderID, but of course still have
duplicate info.
3. I do need this information.
4. I did add OrderID, group by. Info is duplicated because of
grouping
on
other fields. I cannot change the query where there is grouping only
on
the
OrderID, can I?
Please let me explain what I am attempting to do.
1. Customer places order == I am using OrderID to also represent Invoice
No. instead of having another number.
2. Customer pays - sometimes in installments against an
OrderID(Invoice
Number).
3. When customer pays me, I have 7 days to pay my vendor. However,
payment
is not due until payment has been received in full.
a. I still need to see individual payments.
4. When I pay vendor, the information is inserted into the Payments form
(selecting command button on customer orders screen); i.e. - the essett
date, amount, essett number.
a. This allows me to see if I have received a payment from the
customer
and neglected to pay the vendor.
5. I need to see the total of each payment against each
OrderID(Invoice
Number).
I am going in a circle. Do I need to create numerous queries and then
a
query using those queries? I have attempted on several occasions to
add a
query to another query without any success.
Your assistance is greatly appreciated.
Thank you for your patience.
JR
There's a fair bit going on in that query. Suggestions:
1. To get the subquery working correctly, create a new query into just
the
Orders table. Type the subquery into the field, and get it working. Once
it
is working, you can copy the expression into the more involved query.
2. Now you have the subquery working, replace the bad one in your
original
query with this one. In the Total row of this subquery field, change
Group
By to Expression.
3. Remove the Payments table from this query. This will cause it to drop
out
the payment number, amount, essettdate, and essettnumber fields.
Presumably
you don't need these if you are wanting the total value of payments
instead
of the individual payments.
4. It looks like payments are credited against the OrderID, yet
OrderID
is
not one of the fields your query is grouping on. The query should show
the
correct total payments for the order, but if it is not showing which
order
that is, I'm not sure the data has any meaning.
5. If you do add the OrderID field to the output grid and Group By
this
field, the query should should the correct total payments received for
the
order. However, if the OrderID is duplicated (because of grouping on the
other fields) and you sum the amounts, you will still have duplicated
totals.
6. The HAVING clause is not going to work like that. If you are actually
trying to go the other direction, and group by payments, you could use
the
subquery on the order information.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
This is the SQL for the Query that I am using. I tried to remove
the
Payments table, but got errors when I typed in the SQL as you suggested
in
a
fresh field in my query. I get syntax error. I changed your
suggestion
to
read with the correct spelling, etc.
SELECT Payments.Payment,
Contracts.ContractNum,
SIN.SIN,
Products.ProductCode,
Products.ProductName,
[Order Details].Quantity,
Sum([Order Details].UnitPrice) AS SumOfUnitPrice,
Customers.ControlNum,
Payments.PaymentAmount,
Payments.EssettDate,
Payments.EssettNumber,
(SELECT CCur(Nz(Sum(Payments.PaymentAmount).0))
FROM Payments WHERE Payments.OrderID = Orders.OrderID) AS Received
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)
INNER JOIN [Order Details] ON Products.ProductID = [Order
Details].ProductID)
ON (SIN.SINID = ContractSIN_XRef.SINID) AND (Contracts.ContractID =
Products.ContractID))
ON Orders.OrderID = [Order Details].OrderID) LEFT JOIN Payments
ON Orders.OrderID = Payments.OrderID
GROUP BY Payments.PaymentDate,
Contracts.ContractNum,
SIN.SIN,
Products.ProductCode,
Products.ProductName,
[Order Details].Quantity,
Customers.ControlNum,
Payments.PaymentAmount,
Payments.EssettDate,
Payments.EssettNumber,
(SELECT CCur(Nz(Sum(Payments.PaymentAmount).0))
FROM Payments WHERE Payments.OrderID = Orders.OrderID)
HAVING (((Payments.PaymentDate)>=[forms]![Report Date
Range]![Beginning
Order Date]
And (Payments.PaymentDate)<=[forms]![Report Date Range]![Ending Order
Date]))
ORDER BY Contracts.ContractNum DESC;
There are a couple of ways to solve this.
Presumably you have tables like this:
- Invoice (one record for each invoice);
- InvoiceDetail (the line items on the invoice);
- Payment (one record for each payment received.)
I take it your Payment table has a foreign key to the Invoice
table,
so
you identify a payment with an invoice.
You have created a total query to Group By the invoice number,
SumIthe
values from InvoiceDetail and Payment. The problem is that if an
invoice
has 2 line items you end up with double the payment amount.
To solve that, remove the Payment table from the query. Instead
type a
subquery into a fresh column in the Field row in query design. The
line
will look something like this:
Received: (SELECT CCur(Nz(Sum(Payment.Amount),0))
FROM Payment WHERE Payment.InvoiceID = Invoice.InvoiceID)
It is possible that when you create a report on this query, you could
end
up with "Multi-level group-by not allowed" error. Post back if you
strike that issue.
I posted this message previously and Allen Brown was replying.
amat
another computer and cannot see his reply. Can anyone assist in
this?
I have a report based upon payments received by customers. If, for
instance, the invoice that the customer is paying has several line
itmes on it and they make several payments, my report lists the
line
items over and over again as being paid.
Example: Invoice = $500
line Items Purchased = 2
Payments $100
$400
Report shows they paid $1000 (2 payments x 2 line items)
How do I prevent this? My report is based upon a query. I am
using
XP Pro and Access 2003.