Access Querry Seeking Records that has 0 usage in Three Consecutive Months

Chris101

New Member
Joined
Feb 17, 2023
Messages
28
Office Version
  1. 365
Platform
  1. Windows
I am very new to working with Access Database and may not understand all of the lingo used.

What I am trying to do is manage a company's wireless account(s) and each of the device's usage on that account. I am currently having trouble creating a QRY that gives me all of the Unit Numbers that has 0 usage over three consecutive months.

I created a select QRY that has the following Fields: Unit Number, Bill Date, Service Name, and Data Used. That QRY will give me all the units, say between Jan and March that has at least one month with 0 usage in the Data Used field. My issue is that some Unit Numbers may have 0 usage in Jan and March but usage in February. In that example the unit will appear twice Jan and March. Some units may have 0 usage in Jan and February but usage in March, resulting in that unit number appearing in Jan and Feb. some units may have 0 Usage in Jan but no usage in February and March resulting in one record for that unit number in Jan.

Is there a way to write a QRY that gives me only the Unit Numbers that has 0 usage in all three consecutive months based on the dates that I put in the QRY criteria and exclude those unit numbers that may have 0 usage in one or two of the three months?

I hope I have not confused anyone.

Your time and knowledge are very much appreciated. Thank you!
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
without having a look at layout of your tables it’s hard to give a specific answer. With that said, my first approach would be:

Make a sum of the usage of each device for the previous 90 days.
Filter the result to only show values 0 and null.
 
Upvote 0
Solution
That was a great idea. I created a second sum QRY where I added a second date field. I made the first date a "Where" and the second date a count. the second date field gave me how many each number appeared over that 90-day period. 1 month, 2 month and 3 month. I then set the criteria for "3" and it worked like a charm.

Thank you very much for your help. I really appreciate your suggestions.

By the way, is there a way to show the layout of tables?

Thank you.
 
Upvote 0
Thank you.

I will keep that in mind when I have another issue, which I am sure I will.

Best Regards.
 
Upvote 0

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top