Steve,
You could create a query with a self-join, but how depends on your
table-structure. Just first names as a reference-key have a big risk
of duplicate values. The most sensible solution is to enter customer
and partner as separate records with a ID-reference (partnetr-ID) when
applicable. When you want customers and partners together you can
create a query using the same table twice and making a connection
between the partner-id of the first to the id of the 2nd. The relation
should be changed to always cointain the client if no parner exists.
Then you can select the names and ID's of both partners in one row
together. If the partner-id is registered at both records you could
filter out doubles (in reverse order) by a clause like 'where
partner.partner_id is null or (partner.partner_id = clustomer.id and
clustomr.id < partner.id)'
In that case you check that a circular reference is there and in that
case present only the first entered customer.
Marc
st********@wn.com.au (Steve P) wrote in message news:<36*************************@posting.google.c om>...
I am creating a report where I have a Unique identifier for each
customer. ie 1, 2, 3 etc. The Customer has a name of Joe, Mary, Fred
etc. The customers details all come from the one table and some of
the customers have partners which are also customers in their own
right and have the unique identifiers. For examaple: Fred is
customer 1 who has a partner, Mary, who is customer 23. On my report
I have the customers details in Customer Number Order and I also have
their partners name in the report for each record.
What I am trying to achieve is to put the Partner's customer number
along side the Parnters Name for the Primary customer. For example:
Cust No. Name Partners Name Partner's Cust No.
1 Fred Mary 23
I guess it is like the VLOOKUP on excel but I am not able to achieve
it in Access.
Any help with this would be greatly appreciated.
Regards
Steve
