Query to return one result, but have a count of other field

I have multiple fields within my SQL table(Microsoft Access) and have created a form that will allow users to search for a serial number. This search should use the value of a text box to search one field(Serial Number), but return the serial number, model of said serial number, description of said serial number and then a count of how many models are the same as the model of the serial number.

Here is the code I’ve been working with, it will run, but returns a logical error.

SELECT Assets.Sticker, Assets.Model, COUNT(*) AS [Count]
FROM Assets
WHERE Assets.[Sticker]=[Forms]![DuplicateSearch]![txtStickerSearch]
GROUP BY Assets.[Sticker], Assets.[Model]
HAVING COUNT(*) > 0;

e.g. I’m searching for the serial number 00220, I hit the search button and it returns. The value of the count should be two(There is a total quantity of two S320 models), but it’s just returning the number of results found and not the total number of models that match.

| Sticker | Model | Count|
|00220    | S320  | 1    |

Any help with this issue would be greatly appreciated!

Edit: Found the solution with the help of user: @GMB

SELECT a.Sticker, a.Model, c.Cnt
FROM Assets AS a INNER JOIN (SELECT Model, COUNT(*) AS Cnt FROM Assets GROUP BY Model)  AS c ON c.Model = a.Model
WHERE a.Sticker=[Forms]![DuplicateSearch]![txtStickerSearch];

Answer

You could JOIN the table with an aggregate subquery that counts the number of occurences of each model, like:

SELECT a.Sticker, a.Model, c.Cnt
FROM Assets a
INNER JOIN (
    SELECT Model, COUNT(*) Cnt FROM Assets GROUP BY Model
) c ON c.Model = a.Model
WHERE ( a.Sticker=[Forms]![DuplicateSearch]![txtStickerSearch] )

Advertisement

Answer