bad data structure

[Guest]

I am reviewing a database that has significant problems. I have to join 3
tables - holdings, lentity and pplsoftbalances. I am trying to generate a
report or query results that will return 391,564 entries (the # of entries in
the holding file) along with an acct number. which is in the pplsoftbalances
file.
The lentity file is a conecting file since holdings and pplsoftbalances have
no common fields. Lentity conects to holdings via a field named portfolio; it
connects to pplsoftbalances via a field called businessunit.
I have tried to create an intermediary table by makeTbl query joining the
lentity and peoplesoftbalances tbl but I get errors when I try to select only
fields within lentity = to pplsoftbalances. no join returns 9,179 records but
I only should get about 2,131 records.
I am pulling my hair out because I know this is something simple & I am not
seeing it. Can any please help me?
Thanks,
Arbert

 

[Amy Blankenship]

I'm not clear...if you already have the table Lentity connecting holdings
and pplsoftbalances, what new functionality are you trying to get out of
your maketable query?

-Amy

 

[Guest]

Amy;
I need to view data that has a GL acct number attached to the holdings but
there is no relationship or index defined in any of the tables. I mapped out
the tbl structure to determine common fields and found that only tblHoldings,
tblLentity and tblPplsoftBalances had the data I needed BUT... tblLentity was
just a means to an end. It has no useful or required data in it.

I am trying to get a file that has the following layout:

tblHolding:AMOUNT | tblPplSoftBalances:ACCTNUMBER | tblHoldingORT

I have about 300,000 records in tblHolding and only several 100 in Pplsoft
table. I unfortunately get a cartesin product when I try and create the file
using the three tbl including the join.
Hope this makes sense!
Thanks for your help,
Arby
--
Arbert

Only dead fish go with the flow!

I'm not clear...if you already have the table Lentity connecting holdings
and pplsoftbalances, what new functionality are you trying to get out of
your maketable query?

-Amy

 

[Amy Blankenship]

I would say your first step would be to get a *query* that returns what you
want before you go trying to make a table. Usually junction or map tables
don't have any useful information--their sole purpose is to relate other
tables. So I'm not sure whether the relationship is *there* and just not
formally defined, or whether the information is simply missing. If the
information is simply missing, there's not a whole lot you can do to fix
that. If the relationship is there and not defined, you just need to use it
correctly, in which case making another table won't be useful.

Without knowing more about the table structures involved, I'm afraid I can't
help you more.

HTH;

Amy

Amy;
I need to view data that has a GL acct number attached to the holdings but
there is no relationship or index defined in any of the tables. I mapped
out
the tbl structure to determine common fields and found that only
tblHoldings,
tblLentity and tblPplsoftBalances had the data I needed BUT... tblLentity
was
just a means to an end. It has no useful or required data in it.

I am trying to get a file that has the following layout:

tblHolding:AMOUNT | tblPplSoftBalances:ACCTNUMBER | tblHoldingORT

I have about 300,000 records in tblHolding and only several 100 in Pplsoft
table. I unfortunately get a cartesin product when I try and create the
file
using the three tbl including the join.
Hope this makes sense!
Thanks for your help,
Arby