Subform and queries

[Paolo]

Friends,
My database has 5 different tables (tbl1, tbl2, tlb3, tbl4, and tbl5)
with the same fields: ID, SSN, LNAME, FNAME, RECEIVED and CLOSED. The
ID is the PK and its autonumber.

Obiously different data is stored in each table according to the type
of work my office handles. The database is used for a small insurance
company.

I have created a form on which added subforms each of which has it's
record source to a different query based on each of the above tables.I
give criteria to these queries using a form:
forms![myform]![mytxtbox]. It works fine. The form opens and displays
all the subforms with its data (SSN, FNAME, LNAME, RECEIVED and
CLEARED).

Since I would like not to repeat data like SSN, FNAME or LNAME more
than once, I was thinking of a way to display this information only
once. The problem is I could use one subform as the master one to
display the SSN, FNAME and LNAME, adn the other ones to display all
the other information, but it could be that that specific table (tbl1)
does not have that data but this has only been stored in tbl2 and
tbl3.

I believe the only way I can do this is using a query based on all the
5 tables an setting the criteria to the SSN again. But still cannot
get it working.

Can any one help me with the solution?

[DFS]

Paolo,

The first part of the solution is to redesign your database. Combine the 5
work tables into one, and create a Customer table:
WorkTable
---------------------
WorkID *
WorkType ++
CustomerID ++
Received
Closed (or Cleared)
(other fields from tbl1, tbl2, etc)
Customer
---------------------
CustomerID *
SSN +
LastName
FirstName
(other fields)
Asterisk is primary key, + and ++ indicates unique index across fields.


"Paolo" <jp***@tin.it > wrote in message
news:9f******** *************** ***@posting.goo gle.com...

Friends,
My database has 5 different tables (tbl1, tbl2, tlb3, tbl4, and tbl5)
with the same fields: ID, SSN, LNAME, FNAME, RECEIVED and CLOSED. The
ID is the PK and its autonumber.

Obiously different data is stored in each table according to the type
of work my office handles. The database is used for a small insurance
company.

I have created a form on which added subforms each of which has it's
record source to a different query based on each of the above tables.I
give criteria to these queries using a form:
forms![myform]![mytxtbox]. It works fine. The form opens and displays
all the subforms with its data (SSN, FNAME, LNAME, RECEIVED and
CLEARED).

Since I would like not to repeat data like SSN, FNAME or LNAME more
than once, I was thinking of a way to display this information only
once. The problem is I could use one subform as the master one to
display the SSN, FNAME and LNAME, adn the other ones to display all
the other information, but it could be that that specific table (tbl1)
does not have that data but this has only been stored in tbl2 and
tbl3.

I believe the only way I can do this is using a query based on all the
5 tables an setting the criteria to the SSN again. But still cannot
get it working.

Can any one help me with the solution?

[Paolo]

Thanks,
I realize that this is the best solution, but at this stage I cannot
do this since the database is used in over 40 Units and by over 400
users. If I redesign it now I will need to import data from the old
tables to the new ones and this should be done via code as I would be
sending the update file via e-mail. See, my program is splitted and I
use the _mdb file to sent out updates. The program is used for
statistical purposes and cannot afford to lose data or corrupt
integrity. I will need to import that data from the tables, exactly as
they are showing now.

THanks.

"DFS" <no****@nospam. com> wrote in message news:<10******* ******@corp.sup ernews.com>...

Paolo,

The first part of the solution is to redesign your database. Combine the 5
work tables into one, and create a Customer table:
WorkTable
---------------------
WorkID *
WorkType ++
CustomerID ++
Received
Closed (or Cleared)
(other fields from tbl1, tbl2, etc)
Customer
---------------------
CustomerID *
SSN +
LastName
FirstName
(other fields)
Asterisk is primary key, + and ++ indicates unique index across fields.


"Paolo" <jp***@tin.it > wrote in message
news:9f******** *************** ***@posting.goo gle.com...

Friends,
My database has 5 different tables (tbl1, tbl2, tlb3, tbl4, and tbl5)
with the same fields: ID, SSN, LNAME, FNAME, RECEIVED and CLOSED. The
ID is the PK and its autonumber.

Obiously different data is stored in each table according to the type
of work my office handles. The database is used for a small insurance
company.

I have created a form on which added subforms each of which has it's
record source to a different query based on each of the above tables.I
give criteria to these queries using a form:
forms![myform]![mytxtbox]. It works fine. The form opens and displays
all the subforms with its data (SSN, FNAME, LNAME, RECEIVED and
CLEARED).

Since I would like not to repeat data like SSN, FNAME or LNAME more
than once, I was thinking of a way to display this information only
once. The problem is I could use one subform as the master one to
display the SSN, FNAME and LNAME, adn the other ones to display all
the other information, but it could be that that specific table (tbl1)
does not have that data but this has only been stored in tbl2 and
tbl3.

I believe the only way I can do this is using a query based on all the
5 tables an setting the criteria to the SSN again. But still cannot
get it working.

Can any one help me with the solution?

[Matthew Sullivan]

Paolo:

Sounds like you need a Union query as the data source for your parent
form, so that all the SSNs are captured:

(SELECT SSN, LNAME, FNAME FROM tbl1)
UNION (SELECT SSN, LNAME, FNAME FROM tbl2)
UNION (SELECT SSN, LNAME, FNAME FROM tbl3)
UNION (SELECT SSN, LNAME, FNAME FROM tbl4)
UNION (SELECT SSN, LNAME, FNAME FROM tbl5);

You can't do that query in the GUI interface; you have to go into SQL
view and paste it in.
Unsolicited comment: As another respondent mentioned, your database
schema is truly horrible.

For instance, unless you have guaranteed that each unique SSN has
only 1 unique LNAME and FNAME tied to it, then when you run the Union
query above you can have a given SSN show up more than one time. That
in turn can cause data to be duplicated, triplicated, or worse. Which
can *really* mess up things.

One way to mitigate the duplicated SSN problem is to have the Union
query above pull *only* the SSN. Then display the name info via some
other means (perhaps a separate subform).

You said the database was used for statistical purposes; imagine how
badly your stats can be messed up by duplicated data. I urge you to
think a little bit more about redesigning your database, rather than
dismissing the suggestion outright.

Good luck!

-Matt


On 28 Feb 2004 03:10:57 -0800, jp***@tin.it (Paolo) wrote:

I believe the only way I can do this is using a query based on all the
5 tables an setting the criteria to the SSN again. But still cannot
get it working.