468,136 Members | 1,483 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,136 developers. It's quick & easy.

Reports - Lookup Values

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
Nov 12 '05 #1
1 3459
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

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by CoreyWhite | last post: by
4 posts views Thread by Vik | last post: by
reply views Thread by TB | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.