Here is another way to ask my question:
What formula(s) will help me count the days that overlap between two date ranges?

Is it always a single range, or can a resident move in and out several times during the month?

In other words, let's take January. Would it be "Joe" moved in Dec 20 and moved out Jan 15, therefore he's responsible for Jan 1 to Jan 15. Or could it be that he lived there Jan 2 to 5 and then Jan 10 to 12 and then Jan 16 to 20 and you have to figure all those days out?

If it's the former, then you could use some simple math. If it's the latter, we'll need a loop. Either way, this would make a fascinating video. I just need to know which is the case. (And since you're a Platinum member, I'll move this towards the top of the list.)

Wouldn't it be good if Access had INTERSECT, EXCEPT etc

SELECT A,B FROM tbl
INTERSECT
SELECT B,A from tbl

Yeah... not in Access SQL.

Thanks Richard, that is actually a great question...and to be honest, it could be both.  Sometimes it is as simple as just counting the days that one person was in the apartment.  Other times, they may start out in one unit, move out, move back in, and move out again.  So, yes, although is less common for it to be more complicated like that...it does happen!

And each utility company sends their bill for a different date range.  So I need to have a different calculation for each utility bill, per apartment unit, divided fairly between all of the tenants during the date range of each utilities billing cycle.  For example, the electric company recently sent a bill for Oct 27th - Nov 26th, the gas company sent a bill for Nov 13th - Dec 16th, and the water company sent a bill for Nov 1st - Dec 2nd.  So each of those need their own different calculations.  Which is why I would like to build a UTILITY CALCULATION form which will allow me to enter the appropriate info from a utility bill (electricity, gas, water, etc.); such as date range, apartment unit, amount due.  Next, enter as many of our residents that lived in the apartment during that time (1, 2, 3, or more people) and each their info; such as name, and dates lived in unit.  Then have Access count up ONLY days each person lived in that unit during a utilities billing cycle...AND add up ALL of the combined days (Bob = 31 days, Mark = 19 days, Tony = 12 days, TOTAL = 62 days) and divide the full utility bill by total number of days (in this example, 62 days), and THEN take that newly calculated "Cost per Day" and multiply that amount by however many days each person lived in the unit.  Makes sense?

I am assuming I should have a UtilityBillT to enter the info for each utility bill that comes in the mail.  Then, if I make a UtilityCalculationT, I can combine the info from the UtilityBillT with the info from the ResidentT (name, dates lived in unit, etc) and on one form get all of my correct calculations to bill everybody accurately. But, I'm getting stumped in a few places. And this double date range issue is the first hurdle I need to overcome, so THANK YOU for any help you can give me!

OK, I would definitely use a loop for this as the math would get complicated. Loops are easier - but it's a brute force approach. Easier to code, but it might run slightly slower. But if this is something you only run once a month for a handful of records, it won't be a big deal. Give me a little bit to play with it, and I'll make a video.

Awesome! Thank you!

It's trickier than I figured up front. I have a solution for you, but it's going to involve some VBA. Hope you're up to it. :)

Video coming soon.

What do you do if a unit is vacant for a period of time? For example, you get a bill for Jan 1 to Jan 31. Joe lived in the house from Jan 1 to Jan 15, but then it was vacant from Jan 16 to Jan 31?

Could you enter the billing dates and amount for the utility bill in a table/form. The total utility bill amount divided by the number of days in the billing period = utility cost per day. Now loop through the records and count the OccupantIDs for each date, divide the cost per day by the Occupant count each day and simply total each occupant's amount due through the loop. Any amount less than the total utility bill would be due by the facility as no one was in the unit for the difference.

Yeah, that's what I would think. Because if I do it where you add up the total number of "person-days" and let's say there are two people living in a unit for 30 days, that's 60 person days. So if the total utility cost is $600, then they owe (600/60) $10 per person per day, or $300 each - which makes sense.

However, if only one person lived in the unit for 15 days, and the utility cost is still $600, should that one person be billed (600/15) $40 per day, or would you say that he only has to pay (600/30) $20 per day because the cost WOULD BE $20 per day if he stayed all 30 days?

In other words, if the total number of "person-days" per period is less than the total days per period, divide by the total number of days? That's how I would do it.

Wow, that's a really interesting question. I don't think I've run into that scenario yet (fortunately). We usually have a busy enough clientele, there's always a waiting list and rarely ever a long enough period in-between residents to have had an entire billing cycle where an apartment was completely vacant...at least, not in the 3 years I've worked here.  I guess I assumed our utility companies would only be billing us for the previous month's usage, and so if no one was in the unit using electricity or water, would we even get a bill?  If that happened, we would have to just eat the cost since there's no one to bill it to at that point.

On the other hand, in a situation like you described, where only one person was in there for a small number of days...my guess is the bill would be really small too and so we would just bill it to him entirely, since he was obviously the only one using electricity in that unit that month.

Yeah, I know for a fact the utility company will continue to bill you. I was gone for 3 weeks one time, but the utilities keep flowin'. Down here in SW Florida, you need to keep your AC going and keep your house at least cooled to 80 or else mold will start growing. And up north when I lived in Buffalo you'd have to keep your house heated to at least 60 in the winter or else your pipes would freeze. So even if the place is vacant SOMEONE has to pay the bills.

I'll include an option in the video. You can tweak it however you want.