HELP! Newbie! (1 Viewer)

[rrsarge]

All,

I am trying to track business relationships for customers. We connect our customers via VPN. Example: I have a Customer table with 100 customer names. I would like a Relationships table that shows Customer1 has a relationship with Customer 2, Customer 10, and Customer 30. So by looking at Customer 30 I can see he has a relationship with Customer 1 (as defined before). Sounds easy, but not for me so far!

Can you look at my attached sample? It only does one side of the equation, setting up the initial relationship, but not the next step.

Thank you for the assistance.

 

[TessB]

I couldn't open your database, but I am having a hard time understanding what is so difficult. Personally, I would just build a Customer cross-reference table thus

Customer (primary Key)
Related Customer (Primary Key)

And the results would be

Customer1, Customer2
Customer1, Customer10
Customer1, Customer30

Therefore, when you query using this table, your criteria would read

Select Customer, Related Customer
From tblCustomerXReference
Where Customer is Like cboCustomer OR Related Customer is Like cboCustomer

Am I missing something? I can't tell since I couldn't open your database. (sorry)

 

[llkhoutx]

You're way off.

ClientID should be numeric, that's why it's an ID.

The relationship you want is called a junction table consisting of two numeric culumns, with CustID in the first column, RelatedCustID in the second column, eg.

1 2
1 3
1 4
2 5
2 6
3 9
4 8

I've also attached a zipped Access 2002 RevisedRelationship.mdb with the revised tables and a form displaying the relationships via a combo box where a Customer is selected and the related Customers are displayed in a list box.

Note
1. the combobox AfterUpdate event
2. The ListBox rowsource, column count and column widths

Good luck.

 

[rrsarge]

I was able to open your file and run it from within the .zip folder (read only) and it seemed to work. However, when I unzip it and run it, it does not work. No results are shown in the listbox (like in read only mode). Is this because I am using Access 2000 and you are using 2002?

By the way, thank you for your help and advice. Just so I understand what is being done here:

The clients table has a number primary key (NOT an autonumber?)
and the client name. The JCNtbRelatedCustomer table tracks the relationships. There are no 'Relationships' (one-to-many, etc.) between these tables, all this is being done via the SQL statement in the form combobox and listbox. Am I on track?

Questions:

Cust 1 has a relationship with Cust 2, 6, and 8. Yet when I look at Cust 2, 6, or 8 they don't show a relationship with Cust 1.

How to actually ENTER the relationships? I must be overlooking the obvious.

Thanks again!

 

[llkhoutx]

You got it. The index can be an autonumber, you originally had it as text.

I've attached to database in Access 97, zipped of course, which you can convert.

 

[rrsarge]

Great, that worked. Thanks.

Could you help with just two more questions, just so I'm clear.

Cust 1 has a relationship with Cust 2, 6, and 8. Yet when I look at Cust 2, 6, or 8 they don't show a relationship with Cust 1.

And, how do I actually ENTER the relationships by name?

Thanks again!

 

[llkhoutx]

You have to put those relationshipships in the junction table as data. It may seem redundant but its not.

Practically when your relationships are built, you build 1,2 and 2,1 at the same time. The 1st fields in the JCN table is the 1 (left column) side of a 1 to many (right column) side relationship.

One way to build those relationships is with DAO code behind a combo box on the AfterUpdate Event:

You have two combo boxes on your form, one designating the Customer, and a second designating the related customer. On AfterUpdate of the related customer, add two records to the JCN table,

cboCustomerID, cboRelated CustomerID
cboRelated CustomerID, cboCustomerID


private sub cboRelatedCustomer_AfterUpdate
dim db as dao.database
dim rs as daorecordset
set db=currentdb
set rs=db.openrecordset("JCNtable",dbopenrecordset)
rs.addnew
rs.fields(0)=Forms!YourFormName!cboCustomer
rs.fields(1)=Forms!YourFormName!cboRelatedCustomer
rs.update
rs.addnew
rs.fields(1)=Forms!YourFormName!cboCustomer
rs.fields(0)=Forms!YourFormName!cboRelatedCustomer
rs.update
rs.close
db.close
set rs=nothing
set db=nothing
end sub

Remember each combobox has two columns, the first being the ID and is hidden so that the customer name is displayed. The ID is there, it's just hidden. Then when you reference the combobox, you get the ID, not the name.

 

[rrsarge]

I'm afraid I may have been going down the wrong path in my original approach. Knowing just enough to be dangerous, I started looking at sample databases that came with a book I bought, "Que: Using Access 2000".

What I was shooting for is shown in a orders, employees, customer database (probably like Northwind). In the tables, next to each record there is a '+' sign that expands open a order to show who the customer was, and another '+' sign you can then expand to show who the employee was for that customer. That is exactly what I'm shooting for, except I don't have three tables like that. I have basically a customer table and a relationship table. I would like to expand customer 1 and see he has relationships with customers 6, 7 and 8. Then be able to expand, say, customer 8 and see he has a relationship with customer 1 by virtue of the previously established relationship.

Am I making this harder than it has to be? Or is your previous advice the way I should be going?

Once again, thanks for your help.

 

[llkhoutx]

In response to my customers' questions about whether or not someting can be done, I tell them, the answer is always yes. It can be done, at a cost.

I think your making something hard out of something easy and practical. What's wrong with the little form which I posted which displays the relationships?

I think that you're thinking in terms of tables. Think in terms of forms too.

Access and object oriented programming requires a whole new mind set. Think "Events."

 

[rrsarge]

That's all I needed to hear. I wanted to make sure I was explaining what I needed clearly so you could recommend the best approach. It sounds like that's what exactly you did.

I'd like to dissect your previous instructions to make sure I understand them. (explain it to me like I'm a two-year old)

"You have to put those relationshipships in the junction table as data. It may seem redundant but its not. " - Does this mean I enter the data 1,2 in the form and the DAO code enters the data 2,1 in the JCN table?

The form you posted has one combobox and one list box. Do I now need to make a form with two comboboxes and a listbox, using the code you posted on the second combobox?

 

[llkhoutx]

First of all the DAO is code that you generate.

Yes, one way to create the relationships is with two combo boxes, the first specifying the a customerID (hidden) and customer; the second from which you pick another customer (with custmerID hidden). Then on the second combo box AfterUpdae event, use DAO code to add the relationship to the juntion table and requery the list box hereafter described to display whats just been added. You should additionally add a list box with the junction table as the rowsource with criteria of the first combobox customerid so you can see what customers are related to the customer specified in the first combobox. On the AfterUpdate event of the first combo box, requery the list box so you related customer are displayed initially.

To do this, simply use the form I supplied, add the second combobox and its afterupdate event.

If you can't do it now . . .