Relationship Problem

[majaro]

Once a month I get a file and append it to the main table. This also loads records without certain information in certain fields.

So, I created tables with the correect codes and info for each field. I need to run a query that will use the main table and then linking to the tables I created to fill in the empty fields.

When I link the main table with just one of the tables i get the desired results. However, when I link yet another table to the existing two tables I get way more records than I wanted and also a lot of repeating records in my query.

The DB is to large to post even after running Compact and Repair.

Was hoping for some help if I could email it to someone.

Thanks in advance for your help.

[CreganTur]

May be a dumb suggestion, but have you tried using an Inner Join with the 3rd table you mention (the one that, when added, causes all of the undesireable results)?

[austenr]

Even a stripped down version of the DB is not possible to pot, even compactedand repaird. Basically I want the big table linked to the tables:

tblAD
tblClinServ
tblMgrPrac
tblPracMgmt

The smaller tables are ones I created to fill in the blank fields in the records.

I did do an inner join on all of the tables. but got really screwy results. It almost looks like a cartesian join.

[asingh]

I would suggest this...

Take your main main..table...and link it separately to each of your smaller tables..and see which link is causing your records to expand. It has to be that one your tables after the join is causing multiple results. You have a typical one-is-to-many relationship problem. Left outer, or inner join will not solve this. Your Main table has many common fields in one our your seconday tables. Link them one by one...and fire each query separately.

regards,

asingh

[Tommy]

You could zip the file and post it.(Maybe)
As long as the main table has a index field that would point to the smaller tables you should be able to do this. I have a sample on that OBP help me get started on, but it is incomplete. I used it to try to figure out how to do it, so there is no telling what kind of shape it is in. Trial an error thing.