Monthly Consumption Query (1 Viewer)

KINGOFCHAOS17

Member
Local time
Today, 19:50
Joined
Mar 20, 2020
Messages
31
Good Morning and thank you for previous assistance, I'm fairly new to Microsoft Access and struggling to write a query. I have a simple table containing all issues made to the customer (sample data attached). What I'm trying to achieve is a query which displays the number of times a part is issued over the past 36 month period along with the total consumed for that month. I'm attaching an example that I quickly put together in Excel that I'd like to see. Where zero consumption (issues) occurred in the month I would the field to display a zero (0). Month 1 or M1 in the attached example would only include the last full month. So if I were to run the query today (7 April 2020) then Month 1 would look at anything that occurred in March 2020.
 

Attachments

  • Outcome Example.pdf
    38.1 KB · Views: 102
  • Example Data.pdf
    36.4 KB · Views: 94

KINGOFCHAOS17

Member
Local time
Today, 19:50
Joined
Mar 20, 2020
Messages
31
Thank you, but I have no idea how to achieve that. Can you assist me further?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:50
Joined
May 7, 2009
Messages
19,169
you can use Crosstab query, if you want.
see Query1.
 

Attachments

  • monthlyConsumption.zip
    22.9 KB · Views: 88

KINGOFCHAOS17

Member
Local time
Today, 19:50
Joined
Mar 20, 2020
Messages
31
you can use Crosstab query, if you want.
see Query1.
Thank you, can you explain how/what the queries qryDatesFromMsysObjects and qryNumbersFromMsysObjects are doing? Trying to educate myself here.
 

Users who are viewing this thread

Top Bottom