Counting Individual Numbers

[funkmiesterk]

Hi everyone.

I have a pretty basic database with about 8 tables linked together.

The database is going to be used for keeping an electronic record of claims for upto 5000 people.

I have been asked though to have box on the menu screen which shows both the number of claims and the number of batches (where the claims are put)

All i require really is a way to count each individual number in a coloumn

EG

abc123
abc123
abc124
abc125

number of batches 3

any ideas?

Kev

 

[Guus2005]

SELECT Field1, Count(1) AS Counter
FROM Table1
GROUP BY Table1.field1;

Enjoy!

 

[funkmiesterk]

no joy.

didnt work

below is what i typed:

SELECT "batch number - sta" AS Expr1, Count(1) AS [Counter]
FROM Claims
GROUP BY "batch number - sta";

all i got was the same counter

any ideas?

kev

--

edit

--

SELECT [query how many claims].[CountOfBatch Number - STA] FROM [query how many claims] ORDER BY [CountOfBatch Number - STA];

thats the one i got for how many claims.

any way that could be modified as that works perfectly?

kev

 

[Brianwarnock]

What is "batch number - sta" should that be [batch number - sta] in both cases.

Brian

 

[funkmiesterk]

sorry im no good with access have changed the " to []

now i get a table which shows the various batch names but just has a counter of 1 next to each

eg
expr1 counter
1231 1
1233 1
1331 1

what i need is just the total of counter column

any ideas?

kev

 

[Brianwarnock]

Reading the thread again I understand your batch numbers to be of the format ABC1234, what happens to the alpha? What is STA ? What is the batchnumber field name?

Brian

 

[funkmiesterk]

abc1234 was the example. they will actually be logged under the 4 digit code 1234

there isnt much danger of us reaching 9999 any time soon as we only ever get thru about 1200 a year and this is only going to be used for a maximum of 3 years.

the field is called "batch Number - STA"

cheers for ur help mate

Kev

 

[Brianwarnock]

SELECT [batch number - sta], Count([batch number - sta]) AS Countof[batch number - sta]
FROM Claims
GROUP BY [batch number - sta];

A little unsure of the syntax for Countof as I never have blanks and - in fildnames and thus do not need[]

If you do it in the query grid there should be no problem, select the field twice, select totals query groupby on 1, counnt on second.

Brian

 

[Brianwarnock]

a quick check suggests that it should be

Count([batch number - sta]) AS [Countofbatch number - sta]


Brian

 

[funkmiesterk]

same problem its just counting each line.

I only need it to count one occourence of a number

ie

1234
1234
1233
1235
1234

i would like it to equal 3 but its currently equallying 5

kev

 

[Brianwarnock]

Sorry Christmas hangover, I was counting number of occurences of each batch. You will probably need 2 queries

! groupby batchnumber, 2nd read this and count the field, you might be able to do it in 1 by selecting distinct not sure.

Brian

 

[funkmiesterk]

ahh i see

that would make sense.

Thanks for all the help mate.

Hope ur hangover goes soon! mine starts tmz!

kev

 

[funkmiesterk]

YAY i got it

after using Brians group by SQL query. Then adding another query to count the total number.

Cheers Brian I wont get a P45 as a new years present now!

Kev