|
 |
access thread: COMPLICATED QUERY
Message #1 by "Daniel Bailey" <maxim_ize@h...> on Fri, 27 Sep 2002 18:40:50
|
|
Dear Fellow Programmers:
I am trying to create a query in Access 2000 using QBE or SQL view.
I have a table of client invoices. Each client?s invoice has one or more
negative dollar amounts, but not all clients? invoices have positive
dollar amounts.
I would like to see for each client with a negative dollar amount, by
invoice number:
1) Client name;
2) Invoice number;
3) The negative dollar amount and;
4) The positive dollar amount if it exists.
What I have tried:
To query for only those clients with negative dollar amounts. Then using
this as a subquery, query for those clients with positive dollar amounts.
Result:
This does not work in the case with a client who would have one or more
negative and/or positive dollar amounts per invoice for the same dollar
amount. In that case, I only get one occurrence of the positive and/ or
negative dollar amount. (i.e if the client's invoice showed $10, $10, and
$10, I would get $10 not $30 same with the multiple negative amounts).
I would be satisfied if the query would sum the multiple amounts and it
was equal to the invoice amount, but using sum on the dollar amounts
causes a Cartesian query for those records.
Is this possible?
Thank you for your help.
Regards,
Dan
Message #2 by "Carnley, Dave" <dcarnley@a...> on Fri, 27 Sep 2002 12:50:03 -0500
|
|
can you share a little more about the structure of your table(s)?
-----Original Message-----
From: Daniel Bailey [mailto:maxim_ize@h...]
Sent: Friday, September 27, 2002 1:41 PM
To: Access
Subject: [access] COMPLICATED QUERY
Dear Fellow Programmers:
I am trying to create a query in Access 2000 using QBE or SQL view.
I have a table of client invoices. Each client's invoice has one or more
negative dollar amounts, but not all clients' invoices have positive
dollar amounts.
I would like to see for each client with a negative dollar amount, by
invoice number:
1) Client name;
2) Invoice number;
3) The negative dollar amount and;
4) The positive dollar amount if it exists.
What I have tried:
To query for only those clients with negative dollar amounts. Then using
this as a subquery, query for those clients with positive dollar amounts.
Result:
This does not work in the case with a client who would have one or more
negative and/or positive dollar amounts per invoice for the same dollar
amount. In that case, I only get one occurrence of the positive and/ or
negative dollar amount. (i.e if the client's invoice showed $10, $10, and
$10, I would get $10 not $30 same with the multiple negative amounts).
I would be satisfied if the query would sum the multiple amounts and it
was equal to the invoice amount, but using sum on the dollar amounts
causes a Cartesian query for those records.
Is this possible?
Thank you for your help.
Regards,
Dan
Message #3 by "Daniel Bailey" <maxim_ize@h...> on Fri, 27 Sep 2002 19:40:13
|
|
Yes of course, actually it is one table,tblRePayments. Here is some data:
ACCOUNT_NO ACCT_NAME BILL DATE AMOUNT
1001 SMITH 18 9/23/2002 -59.91
1001 SMITH 18 9/23/2002 -59.91
1001 SMITH 18 9/23/2002 -59.91
1001 SMITH 18 9/23/2002 -49.29
1001 SMITH 18 9/23/2002 142.26
1001 SMITH 18 9/23/2002 142.26
1001 SMITH 18 9/23/2002 142.26
1001 SMITH 18 9/23/2002 142.26
1001 SMITH 18 9/23/2002 142.26
1001 SMITH 18 9/23/2002 -49.29
Here is the subquery named QforNegPMCRs:
SELECT tblRePayments.ENTRY,
tblRePayments.ACCT_NAME,
tblRePayments.DATE,
tblRePayments.AMOUNT,
tblRePayments.ACCOUNT_NO,
tblRePayments.BILL
FROM tblRePayments
WHERE ((tblRePayments.AMOUNT)<0));
Here is the query named QFor_OUTONC using QforNegPMCRs:
SELECT QforNegPMCRs.ACCOUNT_NO,
tblRePayments.BILL,
tblRePayments.DATE,
tblRePayments.ACCT_NAME,
tblRePayments.AMOUNT AS AMOUNT
FROM QforNegPMCRs INNER JOIN tblRePayments ON (QforNegPMCRs.ACCOUNT_NO =
tblRePayments.ACCOUNT_NO) AND (QforNegPMCRs.BILL = tblRePayments.BILL)
GROUP BY QforNegPMCRs.ACCOUNT_NO, tblRePayments.BILL, tblRePayments.DATE,
tblRePayments.ACCT_NAME,tblRePayments.AMOUNT
ORDER BY QforNegPMCRs.ACCOUNT_NO, tblRePayments.BILL, tblRePayments.DATE,
tblRePayments.ACCT_NAME;
When I run the query I only get:
1001 SMITH 18 9/23/2002 -59.91
1001 SMITH 18 9/23/2002 -49.29
1001 SMITH 18 9/23/2002 142.26
Runing the query with SUM(AMOUNT):
1001 SMITH 18 9/23/2002 -1391.55
1001 SMITH 18 9/23/2002 3556.50
The Cartesian result:
142.26 * 25 = 3556.50
Thank you for your interest.
Regards,
Dan
> can you share a little more about the structure of your table(s)?
-----Original Message-----
From: Daniel Bailey [mailto:maxim_ize@h...]
Sent: Friday, September 27, 2002 1:41 PM
To: Access
Subject: [access] COMPLICATED QUERY
Dear Fellow Programmers:
I am trying to create a query in Access 2000 using QBE or SQL view.
I have a table of client invoices. Each client's invoice has one or more
negative dollar amounts, but not all clients' invoices have positive
dollar amounts.
I would like to see for each client with a negative dollar amount, by
invoice number:
1) Client name;
2) Invoice number;
3) The negative dollar amount and;
4) The positive dollar amount if it exists.
What I have tried:
To query for only those clients with negative dollar amounts. Then using
this as a subquery, query for those clients with positive dollar amounts.
Result:
This does not work in the case with a client who would have one or more
negative and/or positive dollar amounts per invoice for the same dollar
amount. In that case, I only get one occurrence of the positive and/ or
negative dollar amount. (i.e if the client's invoice showed $10, $10, and
$10, I would get $10 not $30 same with the multiple negative amounts).
I would be satisfied if the query would sum the multiple amounts and it
was equal to the invoice amount, but using sum on the dollar amounts
causes a Cartesian query for those records.
Is this possible?
Thank you for your help.
Regards,
Dan
Message #4 by "Carnley, Dave" <dcarnley@a...> on Fri, 27 Sep 2002 14:17:40 -0500
|
|
you are getting Cartesian products because of poorly design key structures.
I don't even see a unique key!
so for this data set you want to see something like
1001 SMITH 18 9/23/2002 -278.31
1001 SMITH 18 9/23/2002 711.30
or are you looking for
1001 SMITH 18 9/23/2002 432.99
?????
for the first guess above you might try something like (assuming account_no,
bill and date are unique keys for an invoice...)
SELECT tblRePayments.ACCOUNT_NO,
tblRePayments.BILL,
tblRePayments.DATE,
tblRePayments.ACCT_NAME,
sum(tblRePayments.AMOUNT) AS AMOUNT
GROUP BY QforNegPMCRs.ACCOUNT_NO,
tblRePayments.BILL,
tblRePayments.DATE,
tblRePayments.ACCT_NAME
WHERE EXISTS
(SELECT * FROM tblRePayments P1
WHERE P1.ACCOUNT_NO = tblRePayments.ACCOUNT_NO
AND P1.BILL = tblRePayments.BILL
AND P1.DATE = tblRePayments.DATE
AND P1.AMOUNT < 0)
UNION
SELECT tblRePayments.ACCOUNT_NO,
tblRePayments.BILL,
tblRePayments.DATE,
tblRePayments.ACCT_NAME,
sum(tblRePayments.AMOUNT) AS AMOUNT
GROUP BY QforNegPMCRs.ACCOUNT_NO,
tblRePayments.BILL,
tblRePayments.DATE,
tblRePayments.ACCT_NAME
WHERE EXISTS
(SELECT * FROM tblRePayments P1
WHERE P1.ACCOUNT_NO = tblRePayments.ACCOUNT_NO
AND P1.BILL = tblRePayments.BILL
AND P1.DATE = tblRePayments.DATE
AND P1.AMOUNT > 0)
is this close to what you are looking for???
-----Original Message-----
From: Daniel Bailey [mailto:maxim_ize@h...]
Sent: Friday, September 27, 2002 2:40 PM
To: Access
Subject: [access] RE: COMPLICATED QUERY
Yes of course, actually it is one table,tblRePayments. Here is some data:
ACCOUNT_NO ACCT_NAME BILL DATE AMOUNT
1001 SMITH 18 9/23/2002 -59.91
1001 SMITH 18 9/23/2002 -59.91
1001 SMITH 18 9/23/2002 -59.91
1001 SMITH 18 9/23/2002 -49.29
1001 SMITH 18 9/23/2002 142.26
1001 SMITH 18 9/23/2002 142.26
1001 SMITH 18 9/23/2002 142.26
1001 SMITH 18 9/23/2002 142.26
1001 SMITH 18 9/23/2002 142.26
1001 SMITH 18 9/23/2002 -49.29
Here is the subquery named QforNegPMCRs:
SELECT tblRePayments.ENTRY,
tblRePayments.ACCT_NAME,
tblRePayments.DATE,
tblRePayments.AMOUNT,
tblRePayments.ACCOUNT_NO,
tblRePayments.BILL
FROM tblRePayments
WHERE ((tblRePayments.AMOUNT)<0));
Here is the query named QFor_OUTONC using QforNegPMCRs:
SELECT QforNegPMCRs.ACCOUNT_NO,
tblRePayments.BILL,
tblRePayments.DATE,
tblRePayments.ACCT_NAME,
tblRePayments.AMOUNT AS AMOUNT
FROM QforNegPMCRs INNER JOIN tblRePayments ON (QforNegPMCRs.ACCOUNT_NO =
tblRePayments.ACCOUNT_NO) AND (QforNegPMCRs.BILL = tblRePayments.BILL)
GROUP BY QforNegPMCRs.ACCOUNT_NO, tblRePayments.BILL, tblRePayments.DATE,
tblRePayments.ACCT_NAME,tblRePayments.AMOUNT
ORDER BY QforNegPMCRs.ACCOUNT_NO, tblRePayments.BILL, tblRePayments.DATE,
tblRePayments.ACCT_NAME;
When I run the query I only get:
1001 SMITH 18 9/23/2002 -59.91
1001 SMITH 18 9/23/2002 -49.29
1001 SMITH 18 9/23/2002 142.26
Runing the query with SUM(AMOUNT):
1001 SMITH 18 9/23/2002 -1391.55
1001 SMITH 18 9/23/2002 3556.50
The Cartesian result:
142.26 * 25 = 3556.50
Thank you for your interest.
Regards,
Dan
> can you share a little more about the structure of your table(s)?
-----Original Message-----
From: Daniel Bailey [mailto:maxim_ize@h...]
Sent: Friday, September 27, 2002 1:41 PM
To: Access
Subject: [access] COMPLICATED QUERY
Dear Fellow Programmers:
I am trying to create a query in Access 2000 using QBE or SQL view.
I have a table of client invoices. Each client's invoice has one or more
negative dollar amounts, but not all clients' invoices have positive
dollar amounts.
I would like to see for each client with a negative dollar amount, by
invoice number:
1) Client name;
2) Invoice number;
3) The negative dollar amount and;
4) The positive dollar amount if it exists.
What I have tried:
To query for only those clients with negative dollar amounts. Then using
this as a subquery, query for those clients with positive dollar amounts.
Result:
This does not work in the case with a client who would have one or more
negative and/or positive dollar amounts per invoice for the same dollar
amount. In that case, I only get one occurrence of the positive and/ or
negative dollar amount. (i.e if the client's invoice showed $10, $10, and
$10, I would get $10 not $30 same with the multiple negative amounts).
I would be satisfied if the query would sum the multiple amounts and it
was equal to the invoice amount, but using sum on the dollar amounts
causes a Cartesian query for those records.
Is this possible?
Thank you for your help.
Regards,
Dan
Message #5 by "Daniel Bailey" <maxim_ize@h...> on Mon, 30 Sep 2002 16:18:38
|
|
This was an Monarch extract from a report exported to Access, that is why
there are no primaries and or structure. Actually it is a flat file w/o
relationships.
I think I got it to work (meaning w/o running a calculator tape on the
orginal report, it looks good) by creating a query on the negative amounts
with a sum([AMOUNT])AS NEG_AMOUNT and creating a query on the positive
amounts sum([AMOUNT])AS POS_AMOUNT.
Then I put the two queries together where the negative query using the
ACCOUNT_NAME and BILL looks for one-to-one match in the positive query. It
seems to work. The report shows the ACCOUNT_NAME, BILL, DATE, NEG_AMOUNT,
POS_AMOUNT.
A note on your query below. I cut and pasted it into my database but
Access complains about the SELECT statement. I'm interested in how it
works.
Thanks for your help.
Regards,
Dan
> you are getting Cartesian products because of poorly design key
structures.
I don't even see a unique key!
so for this data set you want to see something like
1001 SMITH 18 9/23/2002 -278.31
1001 SMITH 18 9/23/2002 711.30
or are you looking for
1001 SMITH 18 9/23/2002 432.99
?????
for the first guess above you might try something like (assuming
account_no,
bill and date are unique keys for an invoice...)
SELECT tblRePayments.ACCOUNT_NO,
tblRePayments.BILL,
tblRePayments.DATE,
tblRePayments.ACCT_NAME,
sum(tblRePayments.AMOUNT) AS AMOUNT
GROUP BY QforNegPMCRs.ACCOUNT_NO,
tblRePayments.BILL,
tblRePayments.DATE,
tblRePayments.ACCT_NAME
WHERE EXISTS
(SELECT * FROM tblRePayments P1
WHERE P1.ACCOUNT_NO = tblRePayments.ACCOUNT_NO
AND P1.BILL = tblRePayments.BILL
AND P1.DATE = tblRePayments.DATE
AND P1.AMOUNT < 0)
UNION
SELECT tblRePayments.ACCOUNT_NO,
tblRePayments.BILL,
tblRePayments.DATE,
tblRePayments.ACCT_NAME,
sum(tblRePayments.AMOUNT) AS AMOUNT
GROUP BY QforNegPMCRs.ACCOUNT_NO,
tblRePayments.BILL,
tblRePayments.DATE,
tblRePayments.ACCT_NAME
WHERE EXISTS
(SELECT * FROM tblRePayments P1
WHERE P1.ACCOUNT_NO = tblRePayments.ACCOUNT_NO
AND P1.BILL = tblRePayments.BILL
AND P1.DATE = tblRePayments.DATE
AND P1.AMOUNT > 0)
Is this close to what you are looking for???
Message #6 by "Carnley, Dave" <dcarnley@a...> on Mon, 30 Sep 2002 10:24:04 -0500
|
|
I'm sure my syntax is probably off a little bit, I just wrote that query in
the email I didn't test it out. It's really 2 queries UNIONED together, the
first sums the negative amounts the second sums the positive amount.
-----Original Message-----
From: Daniel Bailey [mailto:maxim_ize@h...]
Sent: Monday, September 30, 2002 11:19 AM
To: Access
Subject: [access] COMPLICATED QUERY
This was an Monarch extract from a report exported to Access, that is why
there are no primaries and or structure. Actually it is a flat file w/o
relationships.
I think I got it to work (meaning w/o running a calculator tape on the
orginal report, it looks good) by creating a query on the negative amounts
with a sum([AMOUNT])AS NEG_AMOUNT and creating a query on the positive
amounts sum([AMOUNT])AS POS_AMOUNT.
Then I put the two queries together where the negative query using the
ACCOUNT_NAME and BILL looks for one-to-one match in the positive query. It
seems to work. The report shows the ACCOUNT_NAME, BILL, DATE, NEG_AMOUNT,
POS_AMOUNT.
A note on your query below. I cut and pasted it into my database but
Access complains about the SELECT statement. I'm interested in how it
works.
Thanks for your help.
Regards,
Dan
> you are getting Cartesian products because of poorly design key
structures.
I don't even see a unique key!
so for this data set you want to see something like
1001 SMITH 18 9/23/2002 -278.31
1001 SMITH 18 9/23/2002 711.30
or are you looking for
1001 SMITH 18 9/23/2002 432.99
?????
for the first guess above you might try something like (assuming
account_no,
bill and date are unique keys for an invoice...)
SELECT tblRePayments.ACCOUNT_NO,
tblRePayments.BILL,
tblRePayments.DATE,
tblRePayments.ACCT_NAME,
sum(tblRePayments.AMOUNT) AS AMOUNT
GROUP BY QforNegPMCRs.ACCOUNT_NO,
tblRePayments.BILL,
tblRePayments.DATE,
tblRePayments.ACCT_NAME
WHERE EXISTS
(SELECT * FROM tblRePayments P1
WHERE P1.ACCOUNT_NO = tblRePayments.ACCOUNT_NO
AND P1.BILL = tblRePayments.BILL
AND P1.DATE = tblRePayments.DATE
AND P1.AMOUNT < 0)
UNION
SELECT tblRePayments.ACCOUNT_NO,
tblRePayments.BILL,
tblRePayments.DATE,
tblRePayments.ACCT_NAME,
sum(tblRePayments.AMOUNT) AS AMOUNT
GROUP BY QforNegPMCRs.ACCOUNT_NO,
tblRePayments.BILL,
tblRePayments.DATE,
tblRePayments.ACCT_NAME
WHERE EXISTS
(SELECT * FROM tblRePayments P1
WHERE P1.ACCOUNT_NO = tblRePayments.ACCOUNT_NO
AND P1.BILL = tblRePayments.BILL
AND P1.DATE = tblRePayments.DATE
AND P1.AMOUNT > 0)
Is this close to what you are looking for???
|
|
|
View Cart
