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

[Chris101]

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!

 

[petertenthije]

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.

 

[Chris101]

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.

 

[petertenthije]

just the old fashioned way with a printscreen

 

[Micron]

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

Relationships window?

 

[Chris101]

Thank you.

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

Best Regards.