Form to show all records from one table, even if no match in anoth

[Jeff Borden]

I am working on a project where I want a sub-form to show all records from
one table, even if there are no matching records in another table, allowing
the user to add data to match those new records.

The key fields I'm working with are these:

TABLE 1: PROPERTY
PROPERTY.NAME
PROPERTY.PROPERTYID
....

TABLE 2: PERIOD (this table has approx. 90 consecutive days listed)
PERIOD.DATE
PERIOD.ID
....

TABLE 3: INVENTORY (this table stores the available inventory)
INVENTORY.PROPERTYID <- link to Properties table
INVENTORY.DATE
INVENTORY.BEDS
INVENTORY.RATE
....

I want the main form to show the Property, and the subform to be something
like this:

DATE - BEDS - RATE

I can get that much working OK, but the form will only show dates that
already have matching records in the INVENTORY table, but I want it to show
ALL dates from the PERIOD table, even if there is no record in INVENTORY for
that PROPERTY<->DATE combination... HELP!

 

[Scott Lichtenberg]

Jeff,

A crosstab query might help, but I'm not sure it will do everything you
want. You would be able to show all 90 days, regardless of whether the
property had rooms. Unfortunately, you would only be able to show either
beds or rate. You can't have 2 crosstab values.

Your best bet might be creating a temporary table, inserting records for
all of your dates, then updating it with your room information. You would
then use this a the recordsource of your subform. Each time the store
changed, you would run an update query (or SQL statement) to set rooms and
rates to zero, then update the table with the information for your new
property. Along these lines, you could add two fields to your date table
(rooms and rate) and use that instead of a temp table.

Hope this helps.

 

[Jeff Borden]

Thanks Scott,

So far I've settled on a work-around of creating a record for each
date/property combination in the Inventory table to force the sub-form to
show a list of all dates. It works, but it leaves me with two issues I need
to address some other way...

First problem: This creates extra records in the INVENTORY table (about
25,000 of them!)

Second problem: Whenever a new property is added, I need to have the
database automatically add new rows to the INVENTORY table for each
DATE<->PROPERTY combination (about 90 new rows).

Could a UNION query possibly help here?

Jeff

 

[Beetle]

I would say you have a problem with the structure of your tables but
it's hard to say for sure without knowing more. If you can describe a
little more about the purpose of your application and how the
different entities are related to each other, someone may be able to
offer some advice.