Analysing Data from List Box (1 Viewer)

[Uncle Ned]

Hi Everyone,


Although I've used this forum for help over the years, this is my first time joining as a memeber in the hope of getting some help.


I'm using Access 2016, am self taught and picked up a reasonable understading of VB over the years.



At the moment I am working on a shipping schedule and the main interface has a list box with all pending orders. Each order has plenty of data displayed in the listbox, however, I want to further distill this further to give me a summary of the number of orders in each week and the number of items (in this case, blades), and display it again in another listbox.


I've added an image showing what I currently have and then, in red, what I would like to achieve.


Thanks in advance

 

[Gasman]

Use the Count() function for your Total Orders
Use Sum() function for Total item

Group by week number

Make that query the source for your list box and bring in all 3 columns.?
Criteria would be the week number from the main listbox. Columns start at 0.

HTH

 

[Uncle Ned]

Use the Count() function for your Total Orders
Use Sum() function for Total item

Group by week number

Make that query the source for your list box and bring in all 3 columns.?
Criteria would be the week number from the main listbox. Columns start at 0.

HTH

I've tried to Count() and Sum(), but I can't figure out how to get the week numbers listed. For example, there chould be 200 orders on the screen spread out over 10 weeks - maybe 30 orders this week and 2 orders on the furthest out week. Also, as each week goes by, the previous week will fall of the list.

I can't figure out how to summarise 200 orders into, say 10 week numbers, where I singlulary list each week number that has an order - regardless of the quantity of orders, or in some cases, where there is a week with no order

 

[arnelgp]

first thing to look into is the RowSource of the list you show.
copy the rowsource, and make a Query out of it (say Query1).

from Query1 make the "final" query that will be shown in the soon coming list2.
the "final" query is something like:

select [ship wk], count([ship wk]) as [total orders], 
       sum([blades]) as [total blades] 
from Query1 group by [ship wk];

 

[Uncle Ned]

first thing to look into is the RowSource of the list you show.
copy the rowsource, and make a Query out of it (say Query1).

from Query1 make the "final" query that will be shown in the soon coming list2.
the "final" query is something like:

select [ship wk], count([ship wk]) as [total orders], 
       sum([blades]) as [total blades] 
from Query1 group by [ship wk];

That sounds like a good place to start. Thanks for your help, I'll give it a try and see how it works out

 

[Uncle Ned]

first thing to look into is the RowSource of the list you show.
copy the rowsource, and make a Query out of it (say Query1).

from Query1 make the "final" query that will be shown in the soon coming list2.
the "final" query is something like:

select [ship wk], count([ship wk]) as [total orders], 
       sum([blades]) as [total blades] 
from Query1 group by [ship wk];

That worked perfectly well, screen shot of implemented resolution attached.


Thanks for your help

 

[Uncle Ned]

Maybe just a little help on sorting - ref attached screen shot



In the large list all the orders are sorted starting with the cloest at the top and furthest away at the bottom. The ship date is entered on the order and then formatted to "week number" in the SQL argument. Therefore the orders appear in the correct manner in the main list.


However, in the summary list the orders are group by Week Number, but becuase it is no longer using the raw date, week 2 (2020) is appearing at the top of the sumamry list, when it should follow after week 52 (2019)


Any ideas?

 

[arnelgp]

you can try to add a Calculated field on your query.
on your first post there is Age column there:

Field:     Format(Age, "yyyymm")
Table:
Total:     Group
Show:     no
Sort:      Ascending

 

[Uncle Ned]

you can try to add a Calculated field on your query.
on your first post there is Age column there:

Field:     Format(Age, "yyyymm")
Table:
Total:     Group
Show:     no
Sort:      Ascending

I had considered that, but ran aground becuase orders have different status. First status is when the order is placed by the customer - these are the furthest out orders. Once the customer decides when they want the product the status chages to "carded" (as in a production card is created), this is when the clock starts ticking for the age column. So the far out orders don't have a value in this column.


I've also tried to convert the week no back to yyyymm, but it goes back to 1900 and preseumes that the weeks are all in the same year.

 

[Uncle Ned]

you can try to add a Calculated field on your query.
on your first post there is Age column there:

Field:     Format(Age, "yyyymm")
Table:
Total:     Group
Show:     no
Sort:      Ascending

My last post made me think of somthing. I changed the [Ship Wk] date format in the query for the main list to "yy-ww". When I then group these in the sumamry list it all fell into the correct order.


Thanks for your help