Complex SELECT Statement

[willkr]

I'm still working on an old app. I have a select statement to find the total of all credit card payments for all credit cards. The statement that I currently have is:



SELECT DISTINCTROW Sum(tblCreditCardPayment.[CP Amount]) AS CCPmt, tblCreditCardPayment.[CC Account]
FROM tblCreditCardPayment
GROUP BY tblCreditCardPayment.[CC Account];

This works fine, but I now want to find the total of all "active" credit cards. The active flag is in tblCreditAccounts.CCActive.

Any ideas on how I can accomplish this?

[June7]

What are the primary and foreign key linking fields that would join these tables?

SELECT Sum([CP Amount]) AS CCPmt, Sum(IIf(CCActive, [CP Amount], 0)) AS CCPmtAct, tblCreditCardPayment.[CC Account]
FROM tblCreditCardPayment
INNER JOIN tblCreditAccounts
ON tblCreditCardPayment.[CC Account] = tblCreditAccounts.[CC Account]
GROUP BY tblCreditCardPayment.[CC Account];


DISTINCTROW (or DISTINCT) should not be needed with GROUP BY - it's redundant

[ArviLaanemets]

Junes query will return totals for all credit cards, but in field CCPmtAct leaves out payments, which are done after the card was deactivated.

To get totals of currently active cards only:

SELECT Sum(ccp.[CP Amount]) AS CCPmt, ccp.[CC Account]
FROM tblCreditCardPayment ccp
INNER JOIN tblCreditAccounts cca
ON ccp.[CC Account] = cca.[CC Account]
WHERE cca.CCActive = True
GROUP BY ccp.[CC Account];

[willkr]

Arvil,
I"m not sure what you mean by ccp and cca. I think you are referring to the tables. So, I think that when it is expanded out it looks like this:

SELECT Sum(tblCreditCardPayment.[CP Amount]) AS CCPmt, tblCreditCardPayment.[CC Account] FROM tblCreditCardPayment INNER JOIN tblCreditAccounts ON tblCreditCardPayment.[CC Account] = tblCreditAccounts.[CC Account] WHERE tblCreditAccounts.CCActive = True GROUP BY tblCreditAccounts.[CC Account];

SELECT Sum(tblCreditCardPayment.[CP Amount]) AS CCPmt, tblCreditCardPayment.[CC Account] FROM tblCreditCardPayment INNER JOIN tblCreditAccounts ON tblCreditCardPayment.[CC Account] = tblCreditAccounts.[CC Account] WHERE tblCreditAccounts.CCActive = True GROUP BY tblCreditAccounts.[CC Account];

Is this what you mean?

[ArviLaanemets]

In FROM clause, when declaring source tables, you can declare also aliases for them, and then use them to refer the tables through whole query.
The reasons to do this:
1. When using aliases shorter than the original file name, querystring will be more compact, and easier to read;
2. In case different tables contain fields having same names, Access don't need to decide, from which one to read the value (and probably to get wrong one);
3. In case you link the same table several times into query, using aliases is mandatory;
4. In case you have some result field of query calculated as (Select ...), and refer there to some table referred in FROM clause of main query too, using aliases is mandatory.

And yes, this was what I ment!