Help for an access Newbie

[Chackett]

I have an .MDB set up with two tables. One table is the general ledger, with three fields that I am interested in
1. Entity
2. Account
3. Cost Center
The Second has two fields I am interested in:
1. Entity
2. product type

There are several entities in each of 4 product types, I esentially want a query that will tell me how many times an account/cost center combo appears in the general ledger for each product type.

Can anyone help? Thanks!

 

[pritcham]

Hi

Are there multiple entries for the same "Entity" in the ledger table? What about in the second table?

Martin

 

[Chackett]

In the ledger there are multiple account/cost center strings used for each entity. The second table relates an entity to a product type. Each entity is associated with only one product type, each product type will contain multiple entities.

Thanks for the help!

 

[pritcham]

Hi

Does this do the job for you?

SELECT DISTINCTROW SecondTable.ProductType, ledger.Entity, ledger.Account, Count(ledger.CostCentre) AS CountOfCostCentre
FROM SecondTable LEFT JOIN ledger ON SecondTable.Entity = ledger.Entity
GROUP BY SecondTable.ProductType, ledger.Entity, ledger.Account, SecondTable.Entity;

Martin