473,287 Members | 1,655 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Combine 2 or more fields in Similar rows for a report

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
1 1258
Delerna
1,134 Expert 1GB
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

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

Similar topics

2
by: Sky | last post by:
Hello: Another question about trying to wring functionality from a DataGrid... Have a DB table of "Contacts" -- 14 or more fields per record Show in datagrid -- but only 5 columns (First,Last,...
7
by: Ken Dopierala Jr. | last post by:
Hi, I'm filling a datagrid from a dataset but only the first 255 rows in the dataset show up in the grid. Is there a 255 row limit on the datagrid? I've searched the properties but can't find...
5
by: johnny | last post by:
hi all, I wonder if I could do the following task in PHP before searching for javascript which I prefer not to have in my code: I have a form to create new tables in a database and, since I...
2
by: Ironman | last post by:
Dear Access Developers: I have an interesting problem that I hope someone could suggest how to solve: I have the following Report: Col-1 Col-2 -------- ------- 483.3 100
0
by: Aveblein | last post by:
Hello everyone! I am using MS Access 2003 to create an interface using forms to SQL Server, my problem is that when I try to insert from a table to another table more than 10000 rows, SQL Server only...
1
by: sunilkumar.r80 | last post by:
Hi I have a problem in reading a .xls file using java. I am using jakarta POI ApI for that it work fine for a 9000 rows contians 25 colums. But i need to read more than 25000 rows (in a single...
1
iBasho
by: iBasho | last post by:
I am working on a report template. The database associated with the report has many text fields (sentences/non-numerical). The text fields are not populated early in the year, but they are...
1
by: Rouge | last post by:
Is there a way I can view more than 15 rows in a Pivot Table?
7
by: billa856 | last post by:
Hi, My Project is in MS Access 2002. Now I want to know that can I add more field to table.Its maximum capacity is 255 fields.But I want to add 10 more fields to that table.Is this thing possible?...
10
by: mulamootil | last post by:
Is there a way to hide fields in a report that do not have any data. I have few fields that have images and I would like to hide them as well whenever there is none. I am using Access 07.
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.