autofill fields with multiple fields

[Jeff @ CI]

Access 2000 database

Form is used to enter in new clients and their information.

One of the criteria is for the new client to be associated with the person
who recruited him/her. This creates a tier system that is only required to
go 4 deep

new client(1) - tier 0
client(2) who recruited client(1) - tier 1
client(3) who recruited client (2) - tier 2
client(4) who recruited client (3) - tier 3

what I seek assistance on is etting up an autofill capability so that when
the user enters the tier 1 ClientID number. tier 2 and tier 3 are
automatically filled.

all fields are in the same table

thanks in advance

Jeff

 

[Allen Browne]

In your client table, store only the immediate recruiter. Do not include
fields for tiers 2 and 3. If you do, you are introducing a maintenance
nightmare, because the entries in this table may not match the entries in
those recruiters own records. You can use a query or an expression to get
the details of who recruited the recruiter.

Therefore, your Client table will have fields like this:
ClientID AutoNumber primary key
Surname Text
Firstname Text
RecruiterID Number the ClientID of the recruiter

Then open the Relationships window (Database ribbon in A2007, or Tools menu
in earlier versions), and add the Client table twice. Access will alias the
2nd one as Client_1. Drag Client_1.RecruiterID and drop onto
Client_ClientID. Access pops up a dialog. Check the box for Referential
Integrity (only.) Okay the dialog. This will prevent entering a RecruiterID
that doesn't match any of the clients in your database.

Now create a query, with 2 copies of the client table. Again, Access will
call the 2nd one Client_1. If Access doesn't automatically join the 2
tables, drag Client_1.RecruiterID and drop onto Client_ClientID. You can now
output the recruiter's names (from Client_1) as well as the client's names.

As it stands, this query won't show anyone who has no recruiter. To fix
that, double-click the line joining the 2 tables in query design. Access
pops up a dialog with 3 options. Choose the one that says:
All record from Client, and any matches from Client_1.
This is known as an outer join.

Once you get that working, add a 3rd copy of the Client table to the query.
Drag Client_2.ClientID, and drop onto Client_1.RecruiterID. Change it to an
outer join. You can now output the names of the level 2 tier recruiter.

Repeat that again, adding a 4th copy of the Client table as an outer join,
so you can see the names of the level 3 tier recruiter as well.

You now have something that is reliable and incredibly easy to maintain. The
self-join (joining the table to itself) is the standard solution for
iterating previous generations.

 

[Jeff @ CI]

Allen, I configured the query like you directed, however, I am struggling to
implement the solution.

Problem One - I am trying to implement the list of which client recruited
who until we get to the featured client. On the form, I display contact info
and other info on said client. I would then need to display (from a query)
the ClientID and Name (joined into a string) the person who recruited the
current Client, and then the person who recruited the client who recruited
the current client, and finally, the top level person.

Related, I have to also create the report to show the downline.

I am thinking that the best way to do this is to create a seperate table
with four fields. [ClientID], [TierLevel1], [TierLevel2], [TierLevel3].
All are long integer fields and there is no primary key. Tier 1 is the id of
the client who recruited current client. Tier 2 is the next level up and so
forth. What I am unclear on is how do I populate this table - if it is
practicle.

The method I am looking at is when I enter a new client into the database, I
enter only the person who recruited the new client. When I save the data, it
would launch the process to add the new client to the above table and add in
the available tiered clients - establish parentage / genealogy if you will.

This table would then be the basis for a down line report and any other
objects that I will need to provide as management continues to evolve my
little beast.

Thanks in advance for your help and thanks again for the many previous times
you have helped.

Jeff

 

[Allen Browne]

Jeff, you're making this much harder than it needs to be.

In less time than it takes to explain it, I've built the table as explained
in the last reply and the query:
http://allenbrowne.com/temp/DownlineQueryDesign.PNG

When you run the query, it looks like this:
http://allenbrowne.com/temp/DownlineQueryResult.PNG

All you do is enter the ClientID of the recruiter into the RecruiterID
column. Access automatically populates the Recruiter1, Recruiter2 and
Recruiter3 columns of the *query* for you.

Use the *query* as the RecordSource for any form or report where you need to
show the upline recruiters.

You can easily design a similar query going the opposite way (downline
recruits), but of course the initial client will be repeated many times if
they have multiple recruits.

If it helps, you can paste this into SQL view in a new query:

SELECT Client.*,
[Client_1].[Surname] & ", "+[Client_1].[FirstName] AS Recruiter1,
[Client_2].[Surname] & ", "+[Client_2].[FirstName] AS Recruiter2,
[Client_3].[Surname] & ", "+[Client_3].[FirstName] AS Recruiter3
FROM ((Client LEFT JOIN Client AS Client_1
ON Client.RecruiterID = Client_1.ClientID)
LEFT JOIN Client AS Client_2
ON Client_1.RecruiterID = Client_2.ClientID)
LEFT JOIN Client AS Client_3
ON Client_2.RecruiterID = Client_3.ClientID;

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen, I configured the query like you directed, however, I am struggling
to
implement the solution.

Problem One - I am trying to implement the list of which client recruited
who until we get to the featured client. On the form, I display contact
info
and other info on said client. I would then need to display (from a
query)
the ClientID and Name (joined into a string) the person who recruited the
current Client, and then the person who recruited the client who recruited
the current client, and finally, the top level person.

Related, I have to also create the report to show the downline.

I am thinking that the best way to do this is to create a seperate table
with four fields. [ClientID], [TierLevel1], [TierLevel2], [TierLevel3].
All are long integer fields and there is no primary key. Tier 1 is the id
of
the client who recruited current client. Tier 2 is the next level up and
so
forth. What I am unclear on is how do I populate this table - if it is
practicle.

The method I am looking at is when I enter a new client into the database,
I
enter only the person who recruited the new client. When I save the data,
it
would launch the process to add the new client to the above table and add
in
the available tiered clients - establish parentage / genealogy if you
will.

This table would then be the basis for a down line report and any other
objects that I will need to provide as management continues to evolve my
little beast.

Thanks in advance for your help and thanks again for the many previous
times
you have helped.

Jeff