counting items

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
SELECT [YourTable].[YourNumberField], 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



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


sebgou said:
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
Click to expand...
Click to expand...
 
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];


sebgou said:
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



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


sebgou said:
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
Click to expand...
Click to expand...
Click to expand...
 
Worked perfectly,

Thank you very much for the solution.


KARL DEWEY said:
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];


sebgou said:
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



KARL DEWEY said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top