Count totals

[Guest]

Hello. I am a novice with Access so please bear with me. I have created a
query which allows me to look at all stock numbers within a given time frame
i.e. between 10 am and 11 pm. There are times when stock numbers are entered
in more than once. I need to be able to get a total of how many times each
stock number is entered. Example, if stock number 555111 is entered in 10
different times that night, I need a report, or query which will return just
the stock number and the total of how many times it has been entered. There
is new information entered everynight, so there is an infinate number of
possibilities for combinations of stock numbers. Please walk me through this
if possbile. As always, I thank everyone in advance for thier help!

 

[Guest]

Hi

You need to use a Totals Query.

Create a new query
Drag the field stock number int the grid
Drage it in again (you will now now have this field in twice)
Click the Totals Query icon at the top of the screen
Below your 2 fields you will see Group By
In the righthand column click the group by row and select count

View the query and you will see a count of how many times each stock number
has been used. The reason for bringing th field in twice is so you still have
a colum showing the stock number and it will mean something to a user -
rather than just a list of numbers.

Hope this helps

 

[Guest]

Thank you for your quick response! I have done as you suggested, but instead
of a total count for each stock number, it is showing "1" in each column next
to the stock number.
display example:
Stock Number Count ofStock Number
55521 1
42551 1
42551 1
42551 1
31122 1
31122 1

I would like for the total count to be displayed. Example:
Stock Number Count ofStock Number
55521 1
42551 3
31122 2

Please advise. Thanks!

 

[Guest]

Hi

I just tried the answer I gave and it worked for me.

Oh well try it this way.

Open a new query. Bring the table containing the stocknumber into the top
section of the screen.
Click View (at the top of the screen)
Select SQL

Insert this

SELECT TableName.StockNumber, Count(TableName.StockNumber) AS
CountOfStockNumber
FROM TableName
GROUP BY Table1.StockNumber;

Change
"TableName" (3 times) to the real name of the table in your DB
"StockNumber" (4 times) to the name of the field

Let me know if this helps

 

[Guest]

Ooops - typo

GROUP BY Table1.StockNumber;

Should be

GROUP BY TableName.StockNumber;


Don't forget to change the TableName to what it is

 

[Guest]

Hello and thank you agin for your quick respons. I tried this one too and it
did not work. It asked me to enter in the stock number as a parameter value.
I did however, try your first solution, taking out the time/date parameter
and it worked!! But I do need to be able to look up by date time. this is the
criteria I am using for time field >=[Enter From what date and time] And
<=[to:]
Please advise and thanks again!!