recursive lookup

[DPHarr]

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

 

[Dirk Goldgar]

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.

You can certainly set up a table that is related to itself in this
fashion. Don't let the fact that the lookup wizard won't create a
lookup fields for this throw you. Lookup fields are more trouble than
they're worth, anyway. You can create and enforce this relationship in
the Relationships window. You'll have to add the table to the
Relationships window twice so that you can draw the relationship lines
between two representations of the same table, but that's not a problem.

Having created the relationship, you can use a combo box on a form to
show you the same lookup information a lookup field would give you in a
table.

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

I don't really understand what you have in mind with this question.
Perhaps if you explained more, I could answer it. Normally, though, the
concept of an array is represented by a set of records in a table, maybe
a table on the "many" side of a "one-to-many" relationship.

 

[Nick Coe \(UK\)]

In DPHarr typed:

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(?)
SNIPPED

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

Further to Dirk's post.

When you add the same table to the Relationship view more
than once you are, essentially, creating an Alias for that
table. It's Aliasing that really allows recursive lookups.

Take a look at Allen Browne's site - the example on Self
Joins...

http://members.iinet.net.au/~allenbrowne/ser-06.html

--
Nick Coe (UK)
AccHelp v1.01 Access Application Help File Builder
http://www.alphacos.co.uk/
Download Free Copy
----

 

[Albert D. Kallal]

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.

You have to remember that ms-acces is not at all like MySql, and in fact is
a differnt problem.

You would state that a airpLane and a glass of water are compet products.

ms-access is a develpument tool that lets you build appciones. MySql is
simply a database, and does not apple you to build applciones.

You an't build forms in MySql
You can't byuild reprots in Mysql
You can't write code in MySql.

So, ms-acces is not a database, but a tool that lets you build applciations.
ms-access as a tool can connect to a database like MySql, or sql server.

You can use MySql as the database for ms-access, but you do need to rezilize
that MySql is not free for commerical use.

There is a free data engine included with ms-access, and also you can
download a free server based engine from Mcirosfot here:

http://lab.msdn.microsoft.com/express/sql/default.aspx

http://www.microsoft.com/sql/express/

I just wanted you to be aware that ms-access and a database engine like
MySql are complete different products, and don't really have the same
purpose at all, and comparing the two, or considering one or the other does
not make sense at all.

The question you need to answer here is how do you plan to build the
application part, and you can't do that with MySql..but you can with
ms-access.

How do you plan to display, and let users work with this data?