Solved Finding and Displaying the Most Used Items (1 Viewer)

[Bean Machine]

Hi Everyone!

I have a database responsible for tracking items coming in and out of the department I am working in. There are a multitude of items that users can sign out and I think a valuable option on the database would be to find out which items are the most popular in order to better understand which items need to be more heavily monitored or ordered. How could I go about reporting on the findings or querying for this data?

 

[Ranman256]

Q1, get the count ,sort backwards:
select ItmName , Count(ItmName) from tData sort CountOfItemName descending.

then if you want to build reports on that , say on the top 10. Set the above query to Top10, then
Q2, use Q1 and the data table to get more info:
select tData.* from Q1,tData where Q1.ItmName = tData.ItmName

 

[Bean Machine]

I'm not the most inclined with Microsoft Access yet so if you don't mind helping me further. My field for item name is simply called "Item" and I am pulling the information for Item name from a query called "qry_ItemMasterNoInput". I'm not sure what tData represents either if you could further explain that. I apologize for my lack of understanding! Thanks for the help!

 

[HiTechCoach]

Without any more details about what your database stores, I will assume you are already storing a transaction record for all activity. This is critical to getting the statistics you want.

Learning the Structure Query Language (SQL) will serve you well. Access queries use SQL to retrieve data.

You might find this helpful: Access SQL: basic concepts, vocabulary, and syntax

 

[Dreamweaver]

I would at minimum have 2 tables one for items and one for transactions
When an item is signed into a department you should store the Id, userid, dateinout, itemnumber, tag to indicate in or out for item.
This way you would be able to ask the question how many of an item did come in between x and y userid ETC.
This is a very basic way of doing it.

 

[Bean Machine]

I would at minimum have 2 tables one for items and one for transactions
When an item is signed into a department you should store the Id, userid, dateinout, itemnumber, tag to indicate in or out for item.
This way you would be able to ask the question how many of an item did come in between x and y userid ETC.
This is a very basic way of doing it.

I have two tables. One is associated with the user information side of things and the other is information pertaining to the dates signed out and items being signed out.

 

[The_Doc_Man]

The "other" table that has dates of sign-out should be a many-to-one based on the user, so if the links are right, you can put together a COUNT() query of dates using GROUP BY user on a JOIN between the user table and the sign-out table on the user ID, whatever it is. That gives you the counts. If you were to add an alias to the COUNT() - say COUNT(SignOutDate) As Usage, then you could have the query include ORDER BY Usage DESC and that would give you most uses on top.

 

[Bean Machine]

Thanks for the help everyone, I came to my own conclusion using the replies you guys gave. I decided I wanted the report to show the most used items AND how much overall quantity of those items has been out of the department just to provide some metrics. Here is what my SQL ended up looking like:

SELECT qry_ItemMasterNoInput.Item, Sum(qry_ItemMasterNoInput.[Qty Out]) AS [SumOfQty Out]
FROM qry_ItemMasterNoInput
GROUP BY qry_ItemMasterNoInput.Item
ORDER BY Sum(qry_ItemMasterNoInput.[Qty Out]) DESC;

I decided to pull from a query in this case and then I ordered the information by the amount of quantity out descending.

 

[The_Doc_Man]

Seems perfectly reasonable to me. But the truth is, if it works for you, that is most important. We can, of course, congratulate you on finding your way to a solution. Using a query as the base of another query is absolutely legit, too. So... good work.

 

[Lightwave]

One thing I would say you might want to investigate which I don't think has been discussed is time period.

Typically in sales/production environments you want to know the popular sold & produced units in the last year / month or week in relation to all other time periods. The most popular of all time is often quite well known by the coalface staff (although nice to be confirmed with evidence)

Both SQL and VBA have native functions for pulling out sub time periods from a date.
eg.

Year(#date#)

&

Month(#date#)

I often use these to group total counts in a query that produces a table of counts showing [year /month/product and count] once sorted by date clients can see the trend from month to month within years.

Such a query would be one of the first summary stats I would normally create for users.

 

[Bean Machine]

One thing I would say you might want to investigate which I don't think has been discussed is time period.

Typically in sales/production environments you want to know the popular sold & produced units in the last year / month or week in relation to all other time periods. The most popular of all time is often quite well known by the coalface staff (although nice to be confirmed with evidence)

Both SQL and VBA have native functions for pulling out sub time periods from a date.
eg.

Year(#date#)

&

Month(#date#)

I often use these to group total counts in a query that produces a table of counts showing [year /month/product and count] once sorted by date clients can see the trend from month to month within years.

Such a query would be one of the first summary stats I would normally create for users.

Actually that would be very helpful. I'll look into it! Thanks for the words of advice!