I need to create a query of records in one table along with a count of how often those records occur in 2 other tables and the difference between the counts.

What tables are you querying and what columns are you comparing?

I am looking for something like:

SELECT Detail.No, Count(Detail.No), Count(Billing.No)  FROM Detail, Billing WHERE Detail.No *= Billing.No GROUP BY Detail.No

But the data returned by both Count(Detail.No) and Count(Billing.No) the product of Count(Detail.No) times(X) Count(Billing.No). For example, assume that Count(Detail.No) for Detail.No=1 should be 19 and Count(Billing.No) should be 4, the returned record would be

1, 76, 76

instead of
1, 19, 4

The ultimate result will eventually be Count(Detail.No) MINUS Count(Billing.No)
1, 19, 4, 15
but I thought I would take it step by step

is your join proper ?

This query works:

SELECT Stock.lStockId, Model.Name, Count(*) FROM amPortfolio WHERE (lStockId > 0) AND (lPortfolioItemId NOT IN (SELECT lPortfolioItemId FROM amReservation)) GROUP BY Stock.lStockId, Model.Name
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.