combining Count and GroupBy and Join?

[Geoff Greene]

I've hit a deadend trying to figure out a "count distinct" type query, that
also includes a join, to add fields from a second table to the distincts
found in the first. My table look like this, and a many-to-one from
tbl.Quote to tbl.Company on Accountno

tbl.Company
Accountno | Company | etc
1111 | ABC Co |
1112 | DEF Co |
1113 | GHI Co |

tbl.Quote
Accountno | Quote | Status | etc
1111 | 001 | Closed |
1113 | 002 | Open |
1111 | 003 | Open |
1113 | 004 | Open |
1112 | 005 | Closed |

I'm trying to generate a list of companies that have one or more quotes with
a status of "Open", and a count of how many "Open" quotes each company has.
I can write a single-table query against tbl.Quote, that returns each
Accountno with a count of it's "Open" quotes, like this-

select Accountno, count(*) from tbl.Quote
where Status = "Open"
group by Accountno

which would return-

Accountno | Count
1111 | 1
1113 | 2

Now I want to pull in the Company field from tbl.Company, so I can display
the company names instead of their account numbers. But everything I've
tried to pull in Company causes an error in either the Count of GroupBy.
I've searched through related posts, but couldn't find anything that seemed
to fit. Any suggestions would be much appreciated.

Thanks,
Geoff

 

[Jeff Boyce]

Geoff

Are you attempting to do this all in one query? Have you considered
building more than one query and "chaining" them together to get what you're
after?

Good luck

Jeff Boyce
<Access MVP>

 

[Geoff Greene]

I'm not sure how to "chain" multiple queries together -- but I suspect that
because of other restrictions, I need to make this as a single query.

 

[Geoff Greene]

Wow! Thanks, John. It looks so easy when you do it. <g>

If I can impose on your knowledge for just a moment more -- in the part --
"Count(Q.Status) as Opencount" -- what's the "as Opencount" do for the
query? Taking it out makes the query fail - and that seems to be the key to
your query working and my attempts not


And an odd question -- in a lot of query examples, I've noticed people using
"C" as their first alias, and "Q" as their second alias. Do you know if
there's some meaning behind those particular letters? Or is it just an
unofficial convention?

finally - FYI - no, my tables don't actually have "tbl." as part of the
name -- that's just nomenclature I picked up somewhere to clarify
pseudocode.

thanks again,
Geoff

Do you really have a period in your table names? I would recommend against it
since you will always have to have [] around the tablename, but that is your
choice. I would use a query like the following. Notice the aliases for the
company and quote tables. The aliases made it easier for me to enter the SQL

SELECT C.AccountNo, C.Company, Count(Q.Status) as Opencount
FROM [tbl.Company] as C INNER JOIN [tbl.Quote] as Q
ON C.AccountNo = Q.AccountNo
WHERE Q.Status = "Open"
GROUP BY C.AccountNo, C.Company

I've hit a deadend trying to figure out a "count distinct" type query, that
also includes a join, to add fields from a second table to the distincts
found in the first. My table look like this, and a many-to-one from
tbl.Quote to tbl.Company on Accountno

tbl.Company
Accountno | Company | etc
1111 | ABC Co |
1112 | DEF Co |
1113 | GHI Co |

tbl.Quote
Accountno | Quote | Status | etc
1111 | 001 | Closed |
1113 | 002 | Open |
1111 | 003 | Open |
1113 | 004 | Open |
1112 | 005 | Closed |

I'm trying to generate a list of companies that have one or more quotes with
a status of "Open", and a count of how many "Open" quotes each company has.
I can write a single-table query against tbl.Quote, that returns each
Accountno with a count of it's "Open" quotes, like this-

select Accountno, count(*) from tbl.Quote
where Status = "Open"
group by Accountno

which would return-

Accountno | Count
1111 | 1
1113 | 2

Now I want to pull in the Company field from tbl.Company, so I can display
the company names instead of their account numbers. But everything I've
tried to pull in Company causes an error in either the Count of GroupBy.
I've searched through related posts, but couldn't find anything that seemed
to fit. Any suggestions would be much appreciated.

Thanks,
Geoff

 

[John Spencer (MVP)]

I just an lazy and when using aliases for table names I often use the first
letter of the table or query. I also use tmp or T (for temporary). I don't
know of any convention on this.

"Count(Q.Status) as Opencount" -- It gives the calculation a named column to
store the results.