table recursion

[DPHarr]

*winces* don't hit me - I found this newsgroup after posting to the other.
*hides*
sorry for doublepost


From: "DPHarr" <[email protected]>
Subject: recursive lookup
Date: Wednesday, January 19, 2005 7:09 PM

Access 2000, on WinXP
I'll use the shortened form, and ask if it's possible.

I have a table, "Clients"
(pk)eID%, First$, Last$, Atty%, Applications(?)

Atty field is a long, which links to Atty Table, and references a specific
atty.

Now, Atty table, looks like:
First$, Last$, (pk)Barcard%, Atty2%, Atty3%

What I'm hoping to accomplish here, is that Atty2 can look up another
attorney's info. For example.

(client table)
004,Joy,Ellum,000001,?

(atty table)
Steve, Jobs,000001,000002,000003,
Mark, Hanson, 000002,,,
Jack, Jackson, 000003,,,

If you were familiar with OOPs or vba, or vb, and I set a var to Joy's
entry, it'd look something similar...

Client(004).first = "Joy"
Client(004).Atty = 000001
Client(004).Atty.first = "Steve"
Client(004).Atty.Atty2 = 000002
Client(004).Atty.Atty2.First = "Mark"
Client(004).Atty.Atty3 = 000003
Client(004).Atty.Atty3.Last = "Jackson"

So that an Attorney can have a 'secondary' and 'tertiary' attorney for that
specific clients case. Is it possible to make something like this happen?
Where Atty2 field of AttyTable can reference and search it's own table of
info (is this it's own database?)

I realize, that if i pointed Atty(00003).atty2 = 000001, it would cause a
pretty good loop, but only as far down as I wanted to go.

That's only one question. I'm new to Access, and it's currently competing
against MySQL as far as which database to use for a project. I opted to try
Access first because I had it with the office CD.
When I tried making the table lastnight, and making Atty2 a lookup field
based off the #, it wouldn't let me.

Question 2: Is it possible to have a dynamic array on record, of Longs?

TIA,
DPharr

 

[Jeff Boyce]

I am unclear why a table that holds an Attorney would hold a second/third.
Tables in well-normalized Access databases hold facts about an entity, and
"other attorneys" are not facts about an attorney. From your description, I
suspect that you've left out a necessary "resolver" table -- these handle
many-to-many situations.

If you have clients (and by the way, having a single field for
"Applications" could result in more than one fact in a single field,
similarly a "no-no" in good database design), and if you have attorneys, you
need some way to connect them. I'm guessing here, but are recording
information about "cases"? If so, you've left that out, too!

A more-normalized design, if I've described your situation correctly, would
be:

tblClient
ClientID
... (other client-as-person/org info -- NO case info here)

tblCase
CaseID
...(case-specific info, but NOT participants)

tblAttorney
AttorneyID
...(attorney-specific info -- and YOU are the one who implied that
attorneys aren't like clients <g>)

trelCaseParticipant (one row for each valid combination of case, client,
attorney)
CaseParticipantID
CaseID
ClientID
AttorneyID

You could further normalize this design if you considered clients and
attorneys as variations on persons/orgs, and used a single table to record
them. You'd modify the CaseParticipant table to hold a single person/org
ID, and add a "Role" (client, first atty, ...)

Or have I totally misrepresented your situation?

 

[Khai]

WEll, Jeff, after reading your info, it's great stuff. Only, here's how
TexasWorker's compensation works.

A client can have an attorney represent them (Atty1, or Primary Atty). That
attorney can have up to two others as helpers (atty2, atty3).

When the commission recieves billing from the Atty1, the Atty1 also has to
file for any activities Atty2 and atty3 want to be paid on. Not always will
there be activities from em, but - I was hoping to make the a table so that
each Atty (which could serve as an atty1), would hold the Bar# of Atty2/3.
This way, I could prevent redundant data that Atty1 and atty3 both have
Atty2 as a second atty. Any Clearer?

As for the applications field, I figured I could make it a memo field, and
just store the application #'s (about 20 chars each) in a delimited string,
then, I would have a macro run on _Current event, that splits that string,
and loads em into a listbox. And accessing the applications would be as
easy as subforming the application form to the client form, and using the
trace#'s as Unique ID's for each application.

Really, I didn't think it'd be this complex to create a simple database of
applications. hehe. But - it is something that keeps my mind busy. Also,
how come Listbox in forms doesn't support the .Clear method, but rather,
have to cycle through and RemoveItem for each thing there? hehe.

</rambling off>

-DPharr.

Let me know if I'm still being unclear.

 

[Jeff Boyce]

See in-line comments...

WEll, Jeff, after reading your info, it's great stuff. Only, here's how
TexasWorker's compensation works.

A client can have an attorney represent them (Atty1, or Primary Atty). That
attorney can have up to two others as helpers (atty2, atty3).

You are saying that a client can have 0, 1, 2, or 3 attorneys, one of which
is designated "Primary". This is a 1:m relationship. That Atty2 (or 3) is
a "helper" to Atty1 is an additional relationship. By the way, once Atty2
has been designated "helper" for Atty1, does that mean s/he will NEVER leave
Atty1? (or is this relationship ONLY in relationship to the client/case?)

When the commission recieves billing from the Atty1, the Atty1 also has to
file for any activities Atty2 and atty3 want to be paid on. Not always will
there be activities from em, but - I was hoping to make the a table so that
each Atty (which could serve as an atty1), would hold the Bar# of Atty2/3.
This way, I could prevent redundant data that Atty1 and atty3 both have
Atty2 as a second atty. Any Clearer?

I may be missing an important point, but it seems to me that recording the
following would prevent redundant data and allow a "summing" of all atty
time on a client/case:

trelCaseParticipant (one row for each valid combination of case,
client, attorney)
CaseParticipantID
CaseID
ClientID
AttorneyID
TotalBilled

(and a more normalized design would have the individual "hours" in something
like:
trelCaseHours
CaseID
AttyID
Hours

You don't need to record facts about the case (already in the tblCase!), or
about the client (already accessible via the trelCaseParticipant!), or the
attorney (see tblAtty!).

As for the applications field, I figured I could make it a memo field, and
just store the application #'s (about 20 chars each) in a delimited string,
then, I would have a macro run on _Current event, that splits that string,
and loads em into a listbox. And accessing the applications would be as
easy as subforming the application form to the client form, and using the
trace#'s as Unique ID's for each application.

If I understand correctly, you have the possibility of more than one
"application". If so, you need a 1:m relationship to make the best use of
Access' functionality. You could "stuff" more than one application in a
delimited string, but then you get to add routines to stuff 'em together and
tear 'em apart. This is more work and limits which features you can use in
Access.

An alternate approach would be to create an Application table, with it's own
unique ID column. And this has the advantage of being able to simply add a
NEW application to the table to make it available in any of your forms' list
or combo boxes that refer to the table.

Really, I didn't think it'd be this complex to create a simple database of
applications. hehe. But - it is something that keeps my mind busy. Also,
how come Listbox in forms doesn't support the .Clear method, but rather,
have to cycle through and RemoveItem for each thing there? hehe.

If you "manually" stuff the listbox full, you have to remove the items. As
mentioned above, a simpler approach is to base the listbox on a query, based
on a table. It fills automatically, and to "clear it" if you ever need to,
you can set the ControlSource = "".

Good luck!

Jeff Boyce

 

[Khai]

[trimmed up]

You are saying that a client can have 0, 1, 2, or 3 attorneys, one of which
is designated "Primary". This is a 1:m relationship. That Atty2 (or 3) is
a "helper" to Atty1 is an additional relationship. By the way, once Atty2
has been designated "helper" for Atty1, does that mean s/he will NEVER leave
Atty1? (or is this relationship ONLY in relationship to the client/case?)

Steve, Mark, and John are all attorneys. Steve signs up someone(Bob), and
is the Primary Atty. Mark works for Steve on the case, so Mark is Atty2.
(example 1)

Mark signs up a case (Dan), and decides to have Steve and John help him. So
Mark = atty1, Steve = 2, John = 3.

Dan's record would list Mark as Atty1. So, Access goes to Mark's record,
and sees the barcard in 'atty2' field, and says, 'this barcode matches to
Steve'.

Well, now as I think about it more... would it be better to just take the
extra space, and have the table as

tblClient
First$,
Last$,
...
Atty1Bar%
Atty2Bar%
Atty3Bar%

and just not have any "linked" atty's to other attys? The reason I went the
first way, is Steve has been working for Mark for the last couple years, and
every application has Steve listed as atty2 to Mark, and vice versa. But -
say Mark splits off, and then I make a change to the tblAtty record for
Steve/Mark, it'll affect each client too, because it won't see em as linked
anymore.. right? grmmph. Then I won't be able to truly recreate the
application as it was down in the future...

Well, alright - that settles that.

If you "manually" stuff the listbox full, you have to remove the items. As
mentioned above, a simpler approach is to base the listbox on a query, based
on a table. It fills automatically, and to "clear it" if you ever need to,
you can set the ControlSource = "".

so - ( My.Knowledge(SQL) = False), in plain english is "For each application
who's ClientID matches this ClientID, load the ApplicationTraceNumber(PK)
into the listbox." ?

I would have to write code (My.Knowledge(VB) = True) to pull up either a
subform, or open a new form (I like subform, but here bad things about it
recently), based on "Find this ApplicationTraceNumber in the
ApplicationDatabaseTable, and load it's info into the new form to show it."

That sound about right? And what's a really really good source on getting
the hang of the SQL usage and Access Form Records/Fields?

Thank you so much for dealing with me, I'm just one of those types that
really likes to jump in and learn as much as possible.

 

[Jeff Boyce]

Khai

I noticed your more recent post. LeAnne seems to have covered this topic
quite well.

Best of luck

Jeff Boyce
<Access MVP>

[trimmed up]

You are saying that a client can have 0, 1, 2, or 3 attorneys, one of which
is designated "Primary". This is a 1:m relationship. That Atty2 (or 3) is
a "helper" to Atty1 is an additional relationship. By the way, once Atty2
has been designated "helper" for Atty1, does that mean s/he will NEVER leave
Atty1? (or is this relationship ONLY in relationship to the client/case?)

Steve, Mark, and John are all attorneys. Steve signs up someone(Bob), and
is the Primary Atty. Mark works for Steve on the case, so Mark is Atty2.
(example 1)

Mark signs up a case (Dan), and decides to have Steve and John help him. So
Mark = atty1, Steve = 2, John = 3.

Dan's record would list Mark as Atty1. So, Access goes to Mark's record,
and sees the barcard in 'atty2' field, and says, 'this barcode matches to
Steve'.

Well, now as I think about it more... would it be better to just take the
extra space, and have the table as

tblClient
First$,
Last$,
...
Atty1Bar%
Atty2Bar%
Atty3Bar%

and just not have any "linked" atty's to other attys? The reason I went the
first way, is Steve has been working for Mark for the last couple years, and
every application has Steve listed as atty2 to Mark, and vice versa. But -
say Mark splits off, and then I make a change to the tblAtty record for
Steve/Mark, it'll affect each client too, because it won't see em as linked
anymore.. right? grmmph. Then I won't be able to truly recreate the
application as it was down in the future...

Well, alright - that settles that.

If you "manually" stuff the listbox full, you have to remove the items. As
mentioned above, a simpler approach is to base the listbox on a query, based
on a table. It fills automatically, and to "clear it" if you ever need to,
you can set the ControlSource = "".

so - ( My.Knowledge(SQL) = False), in plain english is "For each application
who's ClientID matches this ClientID, load the ApplicationTraceNumber(PK)
into the listbox." ?

I would have to write code (My.Knowledge(VB) = True) to pull up either a
subform, or open a new form (I like subform, but here bad things about it
recently), based on "Find this ApplicationTraceNumber in the
ApplicationDatabaseTable, and load it's info into the new form to show it."

That sound about right? And what's a really really good source on getting
the hang of the SQL usage and Access Form Records/Fields?

Thank you so much for dealing with me, I'm just one of those types that
really likes to jump in and learn as much as possible.