concur with namliam
You will frequently see discussions on the subject of normalization. I think this is a case where if you did what you proposed, you would clearly split a table that describes your occupants into two tables that would double your efforts to build all possible reports. Not to mention the difficulties in maintaining.
I'll try to put it into perspective, but you'll have some reading to do. I'll name the reading part first. If you are not familiar with normalization, look it up in Access Help. Also you can go to Wikipedia.org for Database Normalization. Then pick your favorite search engine for a search for Database Normalization. In the latter, only bother with those articles originating from the .EDU domain from a college or university you knew something about, however indirectly you knew it. When you've read enough different articles, you will get the feeling that you are re-tracing familiar ground and you are not learning anything new. When you read three articles in a row and have that feeling, you can stop and return to your problem.
OK, why would you NOT split the table? Well, this depends on your business model to a very large degree. Search this forum for the topic of "Reservations" and "Bookings" for a LOT of discussion on the topic. And trust me, it is a common topic.
I'm one guy with one opinion. If I were attacking this, here's what I would do (in a very broad-brush overview). I'll be using PK for prime key and FK for foreign key.
tbSites, PK SiteID, name & address of a complex, other information
tbUnits, PK UnitID, info about unit, FK SiteID
tbOccupants, PK OccID, info about occupants
tbUnitBook, see discussion for PK, containing UnitID, starting and ending booking date, possible status flag or flags showing why unit is booked, FK for OccID but see discussion.
The tbUnitBook is what is called a junction table. Whereas the other tables have to be complete, this one is "sparse." I.e. if a unit is bookable, it is because it has no booking at a particular time. No booking means no entry in the tbUnitBook table for that date. Which is the text book definition of a sparse database.
The PK depends on whether anything will reference this junction table. If each booking is an independent entity on which nothing else depends, you don't need a PK. Otherwise, I would build a synthetic PK via autonumber. (Search the forum for discussions on the merits of meaningful and meaningless keys. Hoo, BOY will you find discussions on THAT topic.)
The optional FK for occupants depends on the code or flags that tell you WHY the unit is not bookable. Flags or codes that might be relevant include:
Booked long-term
Booked trial
Down for maintenance
Down for legalities (heavens forbid, but sometimes lawsuits can force you to keep something not booked even though it provides no income in that state.)
Down for construction
In each case, you have a start/stop date for the booking. Of the possible reasons, only two involve occupants. But all of the reasons stop you from booking.
This simplifies a lot of things for you. Here's how you would use the booking table.
To see what is occupied on a particular date, pick the date. Then scan the booking table for all bookings with the picked date between the booking start and stop dates. See keyword operator BETWEEN to see how you do that. This is going to be so important a query that you will want to make it a stored query. Also look up PARAMETER queries because the "picked date" is a PERFECT example of a parameter for a parameter query.
To see what units are open, take your units table OUTER-joined to the above parameter query, supply a date, and then find those units with no booking codes in the resultant JOIN recordset. (Outer join is necessary to assure you scan all units.) OUTER join is something you can look up on Access Help too.
To see who is occupying your units on date X, take that parameter query and join it to the occupants table, but this time INNER join only. You will get a query that names the unit, occupant, and you can get the code that shows whether it is a long-term or short-term booking.
Just a couple of ideas for you. This might not match your business model, after all. If it doesn't, you still might see a way to apply some of the principles.
Something you will NOT want to do for various reasons: Do not try to build a table of dates and somehow try to mark the calendar with each booking. That's what a date field is for, and that's why I brought up the BETWEEN operator.
Another idea: You might wish to analyze your business model. Search this forum in the database design heading for articles with keywords such as "business model" or "entity analysis" to see how to decide what tables you need and what elements are merely attributes of the main entity being tracked.
Good luck. Any major database that a business will use for asset tracking needs to be carefully matched up to its intent. So one last caution: When you are designing your database, remember that form follows function. Or purpose dictates design. Work from the problem to the solution, not the other way around. (If you do this in the wrong order, you have a case of the tool defining the business or "the tail wagging the dog.")
