counting items

[Guest]

Hi,

Sorry if it's already mentionned I didn't found it.

I have a form where the users manualy enter some items numbers (itemsID
(numeric only)), witch are associated with spare parts that was buyed in our
inventory.

Since the beginning, we have mabye 100 differents items number entered.

I would like to generate query that show the total of items number that was
entered.

Let's say I just want to do TOP 100 of most entered items numbers.

Like :

items number Nbr of times entered in the table
4567 10
4536 9
2234 6
4567 4
etc...

Can you help me ?
Thanks

 

[Guest]

SELECT [YourTable].[YourNumberField], Count([YourTable].[YourNumberField]) AS
[Nbr of times entered in the table]
FROM [YourTable]
GROUP BY [YourTable].[YourNumberField];

 

[Guest]

Thanks, it worked perfectly, another thing is that I have another field [qty]
in the table, so users enter their item number and the quantity they took.
Exemple :

Item number quantity
2234 10
2234 100
2234 200
1111 8

is it possible to do the same query as your answer but to print :

Item number Nbr of times entered in the table
2234 310
1111 8

Many thanks

SELECT [YourTable].[YourNumberField], Count([YourTable].[YourNumberField]) AS
[Nbr of times entered in the table]
FROM [YourTable]
GROUP BY [YourTable].[YourNumberField];

Hi,

Sorry if it's already mentionned I didn't found it.

I have a form where the users manualy enter some items numbers (itemsID
(numeric only)), witch are associated with spare parts that was buyed in our
inventory.

Since the beginning, we have mabye 100 differents items number entered.

I would like to generate query that show the total of items number that was
entered.

Let's say I just want to do TOP 100 of most entered items numbers.

Like :

items number Nbr of times entered in the table
4567 10
4536 9
2234 6
4567 4
etc...

Can you help me ?
Thanks

 

[Guest]

UNTESTED --
SELECT [YourTable].[YourNumberField], Sum([YourTable].[YourQuanityField]) AS
[Total Taken], Count([YourTable].[YourNumberField]) AS [Nbr of times entered
in the table]
FROM [YourTable]
GROUP BY [YourTable].[YourNumberField];

Thanks, it worked perfectly, another thing is that I have another field [qty]
in the table, so users enter their item number and the quantity they took.
Exemple :

Item number quantity
2234 10
2234 100
2234 200
1111 8

is it possible to do the same query as your answer but to print :

Item number Nbr of times entered in the table
2234 310
1111 8

Many thanks

SELECT [YourTable].[YourNumberField], Count([YourTable].[YourNumberField]) AS
[Nbr of times entered in the table]
FROM [YourTable]
GROUP BY [YourTable].[YourNumberField];

Hi,

Sorry if it's already mentionned I didn't found it.

I have a form where the users manualy enter some items numbers (itemsID
(numeric only)), witch are associated with spare parts that was buyed in our
inventory.

Since the beginning, we have mabye 100 differents items number entered.

I would like to generate query that show the total of items number that was
entered.

Let's say I just want to do TOP 100 of most entered items numbers.

Like :

items number Nbr of times entered in the table
4567 10
4536 9
2234 6
4567 4
etc...

Can you help me ?
Thanks

 

[Guest]

Worked perfectly,

Thank you very much for the solution.

UNTESTED --
SELECT [YourTable].[YourNumberField], Sum([YourTable].[YourQuanityField]) AS
[Total Taken], Count([YourTable].[YourNumberField]) AS [Nbr of times entered
in the table]
FROM [YourTable]
GROUP BY [YourTable].[YourNumberField];

Thanks, it worked perfectly, another thing is that I have another field [qty]
in the table, so users enter their item number and the quantity they took.
Exemple :

Item number quantity
2234 10
2234 100
2234 200
1111 8

is it possible to do the same query as your answer but to print :

Item number Nbr of times entered in the table
2234 310
1111 8

Many thanks

SELECT [YourTable].[YourNumberField], Count([YourTable].[YourNumberField]) AS
[Nbr of times entered in the table]
FROM [YourTable]
GROUP BY [YourTable].[YourNumberField];


:

Hi,

Sorry if it's already mentionned I didn't found it.

I have a form where the users manualy enter some items numbers (itemsID
(numeric only)), witch are associated with spare parts that was buyed in our
inventory.

Since the beginning, we have mabye 100 differents items number entered.

I would like to generate query that show the total of items number that was
entered.

Let's say I just want to do TOP 100 of most entered items numbers.

Like :

items number Nbr of times entered in the table
4567 10
4536 9
2234 6
4567 4
etc...

Can you help me ?
Thanks