Multiple count problems involving the first letter of a field 2

[Drisconsult]

To my esteemed colleagues who have helped me on a number of occasions, here is my latest problem:

I have a Media Data Entry form where customers enter a code for DVDs, such D2340, or Games such as G4267 and CDs such as C7345. The first letter defines what type of media it is.

How do I count each of the products on a form showing the count number for each product?

I have called the field MediaID and it contains 5 text characters.

Thank you in advance, knowing that somebody out there will have an immediate answer

Terence
7th July 2005

 

[PHV]

Something like this ?
SELECT Left([MediaID],1) AS TypeOfMedia, Count(*) AS CountOfProducts
FROM yourTable
GROUP BY Left([MediaID],1);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Drisconsult]

Hello PHV

Many thanks for your help in this matter. Would you have a solution that uses a Text Box or a Query rather than VBA? This project was being done by a class of 14 year olds who have no idea about VBA as yet.

 

[PHV]

Where have you seen VBA in my suggestion ?
It's pure SQL code, in fact an aggregate query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[earthandfire]

Create A New Query
Select Your Table

In the Field Row of the First Column type
TypeOfMedia: Left([MediaID],1)
In the Field Row of the Second Column type
CountOfProducts: Count(*)

Click on the Totals button on the Toolbar - it looks like the Greek Sigma

In the Total Row in the First Column
Select Group By
In the Total Row of the Second Column
Select Expression

Save and Run the Query

Hope this helps.

 

[Drisconsult]

Hello PHV and Earthand fire

I must apologise to both of you. It was my 19 year old daughter who communicated her problem to you. She is reading economics at university. However, she is at the stage where her father has become an alien and you don't communicate with aliens do you?

She has howed me how your advice has solved her problem (which she had disguised) and it works well, but ask me - never

This really is Terence saying thank you both, hoping you understand that dealing with 19 year old daughter is far more complex than any Access problem could ever be.