Counting Number of Accounts

  • Thread starter Thread starter Patrick Graham
  • Start date Start date
P

Patrick Graham

I have a table structure like so:

tblIPS.IpsID -- > tblClient.ClientID -->
tblClientAccount.AccountNumber -->
tblAccount.AccountNumber

Now every client is connected to only one IPS (Investment
Policy Statement). An IPS can have 1 to however many
clients i.e. Husband and Wife.

Each Client can have 1 account, multiple accounts, no
accounts and (now heres the kicker) an account can be
shared between 2 clients.

There is one advisor associated with an IPS and I need to
count the number of accounts that exist for that IPS. But
the shared accounts are a problem. How can I deal with
the shared accounts without ignoring the other accounts
that client may share individually?

I'm looking for a quick method, I have a way that uses 3
queries but it is slooowwww.
 
Dear Patrick:

Are you saying the same Account may be in the Account table more than
once, having different ClientAccounts? This suggests that your
Account table is substituting for the link table of a many-to-many
relationship between the ClientAccount table and a (potentially
missing) RealAccount table.

If you build the database in the way suggested above, then the
Account/ClientAccount link table will be governed by the RealAccount
table such that it will always be possible to identify the Account in
the instance of each of its ClientAccounts. Without this, how can you
constraing the Account table to allow matching by the Account value
itself?

OK, maybe this isn't clear, and maybe its not your real problem, but
I've been grappling with your post, and this is the best understanding
which I can obtain. If I'm nowhere close, please post some sample
data for the tables that illustrates the problem and I (and others)
can try again.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
tblClient contains ClientID and client specific
information.

tblClientAccount contains only ClientIDs and
AccountNumbers

tblAccounts contains AccountNumber and account specific
info.


Co ClientIDs 101 and 102 are linked to the
tblClientAccount

101 6A0001A
101 6A0002T
101 6AAAA0A
102 6AAA0BB
102 6A0001A

So in this case 101 is associated with 3 accounts 2 owned
individually and one shared (6A0001A) with clientid 102.

Like wise 102 is associated with 2 accounts one of which
is shared with 101 (6A0001A)

Now if 101 and 102 both have the same advisorID of 15.

then suming that accountnumbers under advisorID 15 should
= 4, not 5 because one of those accounts is shared.

What I've done so far was run a series of queries that
looks for duplication in the tblClientAccount and
eliminates them. But my method is a BRUTALLY slow process.

Is there an easy/quick way to eliminate the duplication?
 
tblClient contains ClientID and client specific
information.

tblClientAccount contains only ClientIDs and
AccountNumbers

tblAccounts contains AccountNumber and account specific
info.


Co ClientIDs 101 and 102 are linked to the
tblClientAccount

101 6A0001A
101 6A0002T
101 6AAAA0A
102 6AAA0BB
102 6A0001A

So in this case 101 is associated with 3 accounts 2 owned
individually and one shared (6A0001A) with clientid 102.

Like wise 102 is associated with 2 accounts one of which
is shared with 101 (6A0001A)

Now if 101 and 102 both have the same advisorID of 15.

then suming that accountnumbers under advisorID 15 should
= 4, not 5 because one of those accounts is shared.
Click to expand...

Create a query that shows only advisorID and accountnumbers. Add a
DISTINCT predicate. Then do the count on that.
What I've done so far was run a series of queries that
looks for duplication in the tblClientAccount and
eliminates them. But my method is a BRUTALLY slow process.
Click to expand...

SELECT the columns and make them distinct. Append these to a new
table, and have that new table take the place of the old one. Or, you
can just put the results in the clipboard, drop your relationships,
delete all the old rows, and paste in the new.
Is there an easy/quick way to eliminate the duplication?
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top