Hiding Records with identical Address Details

[jcbhydro]

Good Morning,

I have written a simple query for address label printing purposes, but I do not want to print labels to identical postal addresses.

This sounds such a simple requirement but my efforts to write a simple 'criteria' in the Address1 Field have been unsuccessful.

Any suggestions for the appropriate code would be gratefully received.

regards,
jcbhydro

 

[pr2-eugin]

Try GROUP BY Query or DISTINCT.. Either of the two should eliminate the problem of duplicate values in the result.

 

[MSAccessRookie]

Good Morning,

I have written a simple query for address label printing purposes, but I do not want to print labels to identical postal addresses.

This sounds such a simple requirement but my efforts to write a simple 'criteria' in the Address1 Field have been unsuccessful.

Any suggestions for the appropriate code would be gratefully received.

regards,
jcbhydro

Joining a Table to itself can provide a way to accomplish this. Please note that since you did not provide specific details, the example will need to be modified to your specifications. I hope this provides a good start.

[COLOR=black][FONT=Times New Roman][SIZE=3]Select AddressID, theAddress From tblAddress a[/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3]    On cStr(a.AddressID) + a.theAddress =[/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3]                 (Select Min(cStr(b.AddressID) + b.theAddress)[/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3]                  From tblAddress b[/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3]                  Where b.AddressID = a.AddressID)[/SIZE][/FONT][/COLOR]

 

[jcbhydro]

Paul,

I have tried writing criteria in the Design View of my Query using the 'Group By' or the 'Distinct' expressions but without success.

I have tried;
SELECT DISTINCT
[Address1] (The relevant Field)
FROM [Mail List] (The relevant Table)

alternatively, I have tried;
SELECT [Address1]
FROM [Mail List]
WHERE (What else is needed here?)
GROUP BY [Address1]

The Query already includes a selection by 'False' on another field, but that shouldn't affect this routine!

Where am I going wrong.

jcbhydro

 

[pr2-eugin]

Using GroupBy would need to involve only the field that you need distinct values for.. What I mean by that is.. Say for example you have the following data..

logID    dupAgent_ID    dupDialedDate
1         88        05/12/2012
2         88        05/12/2012
3         88        05/12/2012
4         88        05/12/2012
5         88        05/12/2012
6         88        05/12/2012
7         91        05/12/2012
8         91        05/12/2012
9         88        05/12/2012
10        91        05/12/2012
11        88        05/12/2012
12        88        05/12/2012
13        89        05/12/2012
14        83        05/12/2012
15        89        05/12/2012

If I use a GROUP BY Query,

SELECT dialLoggerTbl.dupAgent_ID, dialLoggerTbl.dupDialedDate
FROM dialLoggerTbl
GROUP BY dialLoggerTbl.dupAgent_ID, dialLoggerTbl.dupDialedDate;

I would get the following result..

dupAgent_ID    dupDialedDate
83        05/12/2012
88        05/12/2012
89        05/12/2012
91        05/12/2012

But see what happens if I do the same with an added (a field that cannot be grouped by) field..

SELECT dialLoggerTbl.logID, dialLoggerTbl.dupAgent_ID, dialLoggerTbl.dupDialedDate
FROM dialLoggerTbl
GROUP BY dialLoggerTbl.logID, dialLoggerTbl.dupAgent_ID, dialLoggerTbl.dupDialedDate

logID    dupAgent_ID    dupDialedDate
1         88        05/12/2012
2         88        05/12/2012
3         88        05/12/2012
4         88        05/12/2012
5         88        05/12/2012
6         88        05/12/2012
7         91        05/12/2012
8         91        05/12/2012
9         88        05/12/2012
10        91        05/12/2012
11        88        05/12/2012
12        88        05/12/2012
13        89        05/12/2012
14        83        05/12/2012
15        89        05/12/2012

As you can see when the new field which cannot be grouped, the result is not what we want to see..

I think MSAccessRookie has given you a good solution, try that out.

 

[jcbhydro]

I regret that in my novice Access status I don't understand the solution proposed by MSAccessRookie.
I have a members table named 'Mail List', holding details of Society members including address details. The 1st address field is named 'Address1'. I have a label printing query which selects members requiring hand delivered communications and this is achieved with a 'false' criteria setting.
My requirement is to add some SQL code to this existing query to ensure that 2nd or subsequent occurrences of the Address1 field are excluded from the label printing procedure.

If the earlier proposed solution can deal with this requirement, I would be delighted.

Regards,

jcbhydro

 

[jcbhydro]

Dear MSAccessRookie,

I would love to try your suggested coding, but I'm much more of a 'rookie' than you and can't see how to translate it to my needs.

My membership table is named [Mail List] and contains records of members contact details. The first field of the postal address is named [Address1]. This identifies a unique residence.
I have several label printing queries used to print out address labels to various categories of member, but as described, I wish to print out only one record for multiple occurrences of any one address.

I would welcome your further assistance in translating your code to match my details.

Regards,

jcbhydro

 

[MSAccessRookie]

You did not specify the name of the Primary Key for the Table [Mail List], so I have chosen to use the Field Name [AddressID]. You will need to change [AddressID] to the correct name for your Primary Key.

[SIZE=3][FONT=Times New Roman]Select [AddressID], [Address1] From [Mail List] AS a[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   On cStr(a.[AddressID]) + a.[Address1] =[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                (Select Min(cStr(b.[AddressID]) + b.[Address1])[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                 From [Mail List] AS b[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                 Where b.[AddressID] = a.[AddressID])[/FONT][/SIZE]

 

[jcbhydro]

Many thanks for your further efforts.
The PK is [Member ID] and I have incorporated this in your code as follows;

Select [Member ID], [Address1] From [Mail List] AS a
On cStr(a.[Member ID]) + a.[Address 1] =
(Select Min(cStr(b.[Member ID]) + b.[Address 1])
From [Mail List] AS b
Where b.[Member ID] = a.[Member ID])

When I run the SQL i get a syntax error in FROM Clause with the word On highlighted. Any ideas?
Should there be someting after the = sign on line 2?
I wondered if there should be a semi colon at the end of the statement, but it didn't make any difference.

jcbhydro

 

[MSAccessRookie]

Many thanks for your further efforts.
The PK is [Member ID] and I have incorporated this in your code as follows;

Select [Member ID], [Address1] From [Mail List] AS a
On cStr(a.[Member ID]) + a.[Address 1] =
(Select Min(cStr(b.[Member ID]) + b.[Address 1])
From [Mail List] AS b
Where b.[Member ID] = a.[Member ID])

When I run the SQL i get a syntax error in FROM Clause with the word On highlighted. Any ideas?
Should there be someting after the = sign on line 2?
I wondered if there should be a semi colon at the end of the statement, but it didn't make any difference.

jcbhydro

Are you doing this in SQL or VBA? Your comment about the error being highlighted leads me to think that it is VBA. SQL should allow the proposed syntax, but VBA will not.

The proper syntax for SQL would be either to have one long String (which would be very hard to read), or to make a series of short Strings connected together with "& _" characters (see below).

-- Rookie

strSQL = "Select [Member ID], [Address1] From [Mail List] AS a " & _
        "On cStr(a.[Member ID]) + a.[Address 1] = " & _
        "(Select Min(cStr(b.[Member ID]) + b.[Address 1]) " & _
        "From [Mail List] AS b " & _
        "Where b.[Member ID] = a.[Member ID]);"

 

[jcbhydro]

Brian,
I am definitely inserting the code in the SQL pane of my query.
This time I did a copy & paste directly from within the quote marks of your amended code and received the same syntax error in FROM Clause with "&_" highlighted.
What can be the problem?

jcbhydro

 

[RainLover]

Good Morning,

I have written a simple query for address label printing purposes, but I do not want to print labels to identical postal addresses.

This sounds such a simple requirement but my efforts to write a simple 'criteria' in the Address1 Field have been unsuccessful.

Any suggestions for the appropriate code would be gratefully received.

regards,
jcbhydro

I would have a table that stores nothing but addresses. Then from your other tables you should select the address from the address table. This address table should have no duplicates.

This method is correct based upon Normalization rules.

You should also look at your naming conventions. eg NoSpaces and table names should begin with tbl so an example would be tblAddresses.

 

[MSAccessRookie]

Brian,
I am definitely inserting the code in the SQL pane of my query.
This time I did a copy & paste directly from within the quote marks of your amended code and received the same syntax error in FROM Clause with "&_" highlighted.
What can be the problem?

jcbhydro

If the HIGHLIGHTED code is as you have it, it needs a space between the "&" and the "_"

 

[jcbhydro]

No, the spaces are there as pasted from your coding.

Select [Member ID], [Address1] From [Mail List] AS a " & _
"On cStr(a.[Member ID]) + a.[Address 1] = " & _
"(Select Min(cStr(b.[Member ID]) + b.[Address 1]) " & _
"From [Mail List] AS b " & _
"Where b.[Member ID] = a.[Member ID]);

Jcbhydro

 

[MSAccessRookie]

No, the spaces are there as pasted from your coding.

strSQL = "Select [Member ID], [Address1] From [Mail List] AS a " & _
"On cStr(a.[Member ID]) + a.[Address 1] = " & _
"(Select Min(cStr(b.[Member ID]) + b.[Address 1]) " & _
"From [Mail List] AS b " & _
"Where b.[Member ID] = a.[Member ID]);"

Jcbhydro

The String worked for me as I posted it, but I had the logic incorrect, and it did not limit to one per address. A revised string that works in a test DB is displayed below

The beginning and ending of the string are also missing (See the RED above), and I am not sure if that is the way that you have it coded, or if it is a failure to copy the whole line.

-- Rookie

strSQL = "Select a.[Member ID], a.[Address1] " & _
From [Mail List] as a " & _
"Where (cStr(a.[Member ID]) + a.[Address1]) = " & _
(Select cStr(Min(b.[Member ID])) + b.[Address1] " & _
From [Mail List] as b Where b.[Address1] = a.[Address1] " & _
Group By Address1);

 

[jcbhydro]

It still doesn't run, I'm afraid.
I did a copy and paste of your most recent coding with no success.
I noted that the trailing speech marks were missing from most of the " &_" expressions,so I added those, but I still had a syntax error 'missing operator in query expression 'a.(Address 1) " &_", with " &-" highlighted.

I didn't understand your comments about parts missing from beginning and end.

jcbhydro

 

[MSAccessRookie]

Show us the code the way you have it now and highlight in RED the part where the error shows up. I will look at it tomorrow morning, but if I am unable to get here soon enough, I am sure someone else will be able to help you to figure it out.

-- Rookie

 

[Cronk]

Rookie,
The OP is pasting your string into a SQL query design window, not into VBA code ie &'s and line continuations not needed and will errors.

jcbhydro,
I can't see anything basically wrong with your earlier SQL
SELECT DISTINCT [Address1] FROM [Mail List]

Typos?

 

[jcbhydro]

I feel rather embarrassed about the trouble I am putting you to, but I am exremely grateful for your efforts.
Here is your code as I copies and pasted it into my Query SQL pane. I still get the 'syntax error' woth the first " &_" highlighted.

Select a.[Member ID], a.[Address 1] " & _"
From [Mail List] as a " & _"
Where (cStr(a.[Member ID]) + a.[Address 1]) = " & _"
(Select cStr(Min(b.[Member ID])) + b.[Address 1] " & _"
From [Mail List] as b Where b.[Address 1] = a.[Address 1] " & _"
Group By Address 1);

I have added a space in [Address 1] as that is how the field is named.

regards,

jcbhydro

 

[MSAccessRookie]

I feel rather embarrassed about the trouble I am putting you to, but I am exremely grateful for your efforts.
Here is your code as I copies and pasted it into my Query SQL pane. I still get the 'syntax error' woth the first " &_" highlighted.

Select a.[Member ID], a.[Address 1] " & _"
From [Mail List] as a " & _"
Where (cStr(a.[Member ID]) + a.[Address 1]) = " & _"
(Select cStr(Min(b.[Member ID])) + b.[Address 1] " & _"
From [Mail List] as b Where b.[Address 1] = a.[Address 1] " & _"
Group By Address 1);

I have added a space in [Address 1] as that is how the field is named.

regards,

jcbhydro

Forgive the confusion, but I was thinking you were using VBA Format. The SQL Pane Format would be something more like the following:

Select a.[Member ID], a.[Address 1] 
From [Mail List] as a 
Where (cStr(a.[Member ID]) + a.[Address 1]) = 
(Select cStr(Min(b.[Member ID])) + b.[Address 1] 
From [Mail List] as b 
Where b.[Address 1] = a.[Address 1] 
Group By Address 1);