Need better tracking with my tables (1 Viewer)

[raydan]

I have a DB thats working fine, but (there always is a but) I now need to do something a little bit different. I need to be able to track my originators and who they have brought into the company. So I will have an originator and many down-line originators, and these people that come in down-line will have people coming in down-line from them and so forth and so on. I have a table that has all originator info and originatorID, but will I need another table or can I just add to the exisisting one. Here is the table structure:

‎Originators:
-------------
OriginatorID -autoNumber
FirstName - Text
LastName- Text
OrigSSN- Text
Address- Text
City- Text
State- Text
PostalCode- Text
HomePhone- Text
WorkPhone- Text
CellPhone- Text
FaxPhone- Text
EmailName- Text
BirthDate- Date/time
Level- Text


If more info is needed please let me Know.. Thanks in advance.

Scott

 

[Pat Hartman]

You need to add a ParentOfOriginatorID to the table. You will populate this field with the OriginatorID of the person who brought this one in. To enforce RI, add the table to the relationship window and draw a join line between the OriginatorID of one instance of the table to the ParentOfOriginatorID in the other table instance.

 

[raydan]

Ok, this makes sense to me but i'm having trouble implementing the idea. I added the line UpLineOrig to that table. I save it then, open relationship table and I join the OriginatorID to UpLineOrig. Is this what im suposed to do because the results aren't correct. I get a sub-table that copies exactly what the Originators table to Originators_1. I guess im not sure how to join to entries in the same table.

 

[raydan]

Wow, there is this beautiful tab called lookup. I used that and guess what it worked, thanks for the help. But before i call this done, will what i just did work because I didn't have to join anything in the relationship window. I'm concerened that this won't work. Now it looks like this:

‎Originators:
-------------
OriginatorID -autoNumber
FirstName - Text
LastName- Text
OrigSSN- Text
Address- Text
City- Text
State- Text
PostalCode- Text
HomePhone- Text
WorkPhone- Text
CellPhone- Text
FaxPhone- Text
EmailName- Text
BirthDate- Date/time
Level- Text
UpLineOrigID- Number(with lookup set as;
display control-List Box
row source type- table/query
row source- Originators)

???? Now there is just this table and no others to join, is that correct?

Scott

 

[Pat Hartman]

I know the lookup tab in the table properties looks like it solved your problem but it really causes more problems when you actually try to work with the table using VBA or SQL.

Try again to follow my directions. It is really important to implement RI. Also, Access automatically sets the default for numeric fields to 0. This will cause a problem in fields used as a foreign key so remove the 0 so the default will be null.

Once the relationship is properly defined, create a query that joins the two tables and use that whenever you want to see the parent name as well as its ID.

I get a sub-table that copies exactly what the Originators table to Originators_1. I guess im not sure how to join to entries in the same table.

Access adds the _1 so it can distinguish the two instances of the table. The _1 table is not a copy of the original it is only a view of it. Draw the join line as I told you.