Self-join append... "thing"

[Dinictus]

Hi,

I have a dbase with a form to look and search and stuff.
Now It hold clients and retailers.

What I want is to join certain retailers to a client. like:

Client "blabla"
Can get products from:
retailer1: name address Button: CONNECT A RETAILER HERE
Retailer2: name address Button: CONNECT A RETAILER HERE

But as you might see, the retailers and clients are in the same table.
I tried builing a self-join append query but it does not work. If I start it

The whole purpose is to prevent users to have to copy the address info from the retailers to the clients.

Anyone who can set me up? I appreciate it.

 

[FoFa]

having them in the same table, should work, but not the best approach.
Typically you would build a linking table to handle the relationships and use the self join via this relationship table.
Not even sure why you would have a self joined append query?!?

 

[Dinictus]

Not sure how I can approach this another way so any advice is welcome.
What do you mean by linking table?

I need the retailers in the same table cos for them the entire data base is the same as for the clients, the only difference being that you can join them to clients. (technically it would also be possible to link a client to a client. Its just copying address info from 1 record to another record, within the same table.

 

[Pat Hartman]

There is nothing wrong with having them in the same table. I assume that you have a relation table that implements the many-to-many relationship. Then your query would join:
Client ---> Relationtable <--- Client_1
Is that what you are doing? When you add the same table to the query twice, Access appends a suffix so you can tell them apart. You do the same thing in the Relationship window.