Hi There
Not being a guru I thought I'd reach out to see if anyone can solve the issue I have.
I have 2 tables. One contains accounts that have a master price code for deliveries to towns and a master town code. Similar to this.
ANum | BName | PriceCode | Town
---------------------------------
1 | MS | AB | AL
---------------------------------
2 | GOO | N5 | BY
---------------------------------
3 | ORC | H7 | KG
---------------------------------
4 | SYM | AB | BY
---------------------------------
5 | VER | U4 | MD
---------------------------------
For most accounts they have just one price code.
I have another table that links with the ANum that holds a list of additional price codes for different towns the account may service. Like this
ANum | PriceCode | Town
---------------------------------
1 | R5 | KG
---------------------------------
1 | E3 | BU
---------------------------------
1 | E4 | FR
---------------------------------
2 | H7 | KO
---------------------------------
4 | U4 | DE
---------------------------------
4 | F2 | AL
---------------------------------
5 | AB | AL
---------------------------------
5 | AB | KO
---------------------------------
I'd like to get a list of all accounts that use a specific price code...say all that use AB. I can use a join but then I end up with 2 rows for the ANum 5 as it has 2 entries in the additional list. I'd like to get the accounts and then maybe count how many additional entries are found so the result would be
ANum | BName | PriceCode | Town | Additional
------------------------------------------
1 | MS | AB | AL | 0
------------------------------------------
5 | VER | U4 | MD | 2
------------------------------------------
is this possible?
Cheers
Peter