By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,266 Members | 1,290 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,266 IT Pros & Developers. It's quick & easy.

Combine 2 or more fields in Similar rows for a report

P: 1
I have a MS 2003 Dbase with some of the fields following:
House_no, Address1, Address2, Lname,Fname, etc..etc..etc...

Sample data is:

10, Avery Street, , Smith, Tom
10, Avery Street, , Smith, Amy
12, Avery Street, , Jones, Bob
12, Avery Street, , Jones, Mary

I would like to generate a Label report that would look like this:

Tom & Amy Smith
10 Avery Street

Bob & Mary Jones
12 Avery Street

I tried a query where I concatenated House_No, address1, and Lname to create a unique Identifier But I am now lost. Any help you can toss my way would be greatly aprpeciated.
May 30 '10 #1
Share this Question
Share on Google+
1 Reply


Delerna
Expert 100+
P: 1,134
From the fields you have listed

You need to left join the table to itself using
House_No,Address1 and Lname and FName<>FName as the join fields

That will return 2 records for each couple
Bob & Mary Jones
and
Mary & Bob Jones

You will need a field (that you haven't listed) to eradicate the duplicate
For example if you have a field [sex]=Male/Female .... not Yes/No

then you can add a
where sex=male.
although thats probably not a good example because it will filter out all the single females
But it gives you the idea

something like this
Expand|Select|Wrap|Line Numbers
  1. SELECT a. Fname & " & " & b.FName & " " & Lname as Addressee,
  2.          a.House_no & " " & Address1 as Address
  3. FROM theTable a
  4. LEFT JOIN theTable b
  5.      ON a.House_no=b.House_no 
  6.     AND a.Address1=b.Address1
  7.     AND a.Lname=b.Lname
  8.     AND a.Fname<>b.Fname
  9. WHERE a.Sex="Male"
  10.  
use NZ() and is null to detect and handle the single people
Look it up in the access help
May 31 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.