tables

[Sanjo]

I have a two table Access 2010 database. The 1st table is preloaded with 80 apartments, space A and space B. This means that someone can rent apartment 50, space A & B or simply apartment 50, space A or apartment 50, space B. So I have a table that looks like:
Apartment Space A Space B
1 /_/ /_/ 2 /_/ /_/ these are yes/no boxes boxes
\/ \/ \/
50 /_/ /_/

I have a second table containing customer info, including Apartment #, Space A and Space B.

This 2nd table is a source for my data entry screen in which I have to identify which apartment/space(s) a customer rents.

My question is: when I enter into the data entry screen that a customer has rented apartment 2, space A and B, how can Access mark my 1st table, that has all apartment numbers and space numbers preloaded, that apartment 2 and spaces A and B are occupied.

I subsequently need this 1st table to be updated or marked everytime I rent to a customer so that I can run a query to see what apartments/spaces are available

 

[RainLover]

You don't.

If you want to know if an Appartment is Vacant run a Query that looks at the second Table for unoccupied Appartments.

The second Table would have a Date Vacated Field, so you would look at that to see if the appartment was Vacant.

Table one should not have Space A and Space B as Fields. They should be combined as one.

 

[Sanjo]

table 2 only has the tables that have been rented out. only table 1 has all 50 apartments preloaded. Table 2 would have to be able communicate with table 1 that apartment x, space a or b has been rented. Then when a query is run against table1 it would look for the fields that are vacant and those will be the apartments not yet rented, ie available to be rented.

 

[RainLover]

Are you happy with that?

 

[MarkK]

Try not to think of a table as needing to communicate with another table. Rather, a table is a place where you simply record facts, and a fact is an object that has dimensions in space and time. Record the dimensions of facts as they occur, or will occur.

So maybe you need a table of occupancy events that have a value +1 to occupy, and -1 to vacate.

tOccupancyEvent
OccupancyEventID (PK)
RoomID (FK)
CustomerID (FK)
Value (+1 or -1)
Date (might occur in the future or the past)

Then if you sum the values to a certain date, you can know if a RoomID is occupied...

SELECT SUM(Value) As IsOccupied
FROM tOccupancyEvent
WHERE Date <= #DateYouWantToTest#
AND RoomID = <RoomYouWantToTest>

If IsOccupied = 1, the room is occupied. If you GROUP BY RoomID you can get a listing of occupancy on all rooms for a particular date. And so on.

 

[Pat Hartman]

Sanjo,
The problem is that your schema is incorrect plus the relationship you have defined between customers and appartments is backwards.
1. Each unique rentable space should have its own row. Call them Apt10A and Apt10B or whatever but make them separate rows.
2. Depending on your business rules you may need a junction table if multiple people can rent the same apartment. If an apartment can be rented by only one person, then the customerID must be in the apartment rental. If an apartment can be rented by multiple people (roommates), then you need a junction table so you can have a row for each combination of customer and apartment.