Form with multiple sources does not display incomplete data

[Joel Maxuel]

I have an Access 2000 database (although using the 2003 app) that has several
tables (one table per remote office) linked by the date field (using basic
referential integrity, no cascading updates nor deletes). I created a form
that pulls the data from all these tables and then individual forms for each
office. Entering (entire) data for that day works in the master form well.
The issue I have come across is that when entering only some of the sites,
the information saves in the tables fine, but reloading the form that day
does not exist, and remains hidden until all tables for that day have been at
least started. Obviously, I would want to be able to see missing data from a
specific office on the master form (i.e. represented as blanks and still have
the rest of the data for that day visible), not having to open each table
individually to see which is lacking. Has anyone else come up with this
problem, and if so, was there a solution?

 

[John W. Vinson]

On Fri, 13 Apr 2007 11:36:00 -0700, Joel Maxuel <Joel

I have an Access 2000 database (although using the 2003 app) that has several
tables (one table per remote office) linked by the date field (using basic
referential integrity, no cascading updates nor deletes).

I'm afraid this is an incorrect design. Much better would probably be to have
ONE table, with a field for the office! What will you do when a new office
opens? Add a new table, revise all your queries, revise all your forms, revise
all your reports? Ouch!

I created a form
that pulls the data from all these tables and then individual forms for each
office.

You only need *one* form with the normalized design; you can easily filter it
to display just one office.

Entering (entire) data for that day works in the master form well.
The issue I have come across is that when entering only some of the sites,
the information saves in the tables fine, but reloading the form that day
does not exist, and remains hidden until all tables for that day have been at
least started. Obviously, I would want to be able to see missing data from a
specific office on the master form (i.e. represented as blanks and still have
the rest of the data for that day visible), not having to open each table
individually to see which is lacking. Has anyone else come up with this
problem, and if so, was there a solution?

Normalize your data. Or... use LEFT JOINS to join the master table to the
office tables. An Inner Join will only display records present in all tables;
a left join will display the data in the master table and NULL for all fields
in the child table, if there is no corresponding record.


John W. Vinson [MVP]