Preserving sort order in a query

Without going into too much detail, I have a table consisting of FirstName,
SurName and Address. I want to produce labels with names and addresses. If 2
people live at the same address say Mr Taffy Robinson & Mrs Gwen Robinson
both living at 1 Great North Road using a bit if code I produce

Mr & Mrs T Robinson
1 Great North Road

Equally their next door neighbours Dr John Smith and Miss Mary Jones come
out as
Dr J Smith & Miss M Jones
3 Great North Road

The labels need to be generated in alphabetical order ordered by the surname
( if more than 1 surname at 1 address with the man's name first. - Yes - I
am a sexist). If I include the first name and the surname in the query I get
4 records. If I include only the surname, I get 3 records. If I omit the
surname, I get the 2 records as required, but I get Smith before Robinson.
Presumably because Dr comes before Mr. Any ideas how to get 2 records with
Robinson appearing before Smith

Thanks

Phil

Re: Preserving sort order in a query

On Jan 16, 2:22=A0pm, "Phil Stanton" wrote:
> Without going into too much detail, I have a table consisting of FirstName=
,
> SurName and Address. I want to produce labels with names and addresses. If=
2
> people live at the same address say Mr Taffy Robinson & Mrs Gwen Robinson
> both living at 1 Great North Road using a bit if code I produce
>
> Mr & Mrs T Robinson
> 1 Great North Road
>
> Equally their next door neighbours Dr John Smith and Miss Mary Jones come
> out as
> Dr J Smith & Miss M Jones
> 3 Great North Road
>
> The labels need to be generated in alphabetical order ordered by the surna=
me
> ( if more than 1 surname at 1 address with =A0the man's name first. - Yes =
- I
> am a sexist). If I include the first name and the surname in the query I g=
et
> 4 records. If I include only the surname, I get 3 records. If I omit the
> surname, I get the 2 records as required, but I get Smith before Robinson.=

> Presumably because Dr comes before Mr. Any ideas how to get 2 records with=

> Robinson appearing before Smith
>
> Thanks
>
> Phil

You need a individual field for each data element. Specifically the
Title for each person needs to be in its own field. Also consider a
field for generational qualifier (aka suffix).

Re: Preserving sort order in a query

Thanks for coming back

All the original fields are separate ( SurName, FirstName, Title and about 5
fields to make up the address) I use code to build up the combined names and
a massive IIf clause to build the address

MemAddLF: IIf([MemAdd1]>"",[MemAdd1]) & IIf([MemAdd2]>"",Chr$(13) & Chr$(10)
& [MemAdd2]) & IIf([MemAdd3]>"",Chr$(13) & Chr$(10) & [MemAdd3]) &
IIf([Town]>"",Chr$(13) & Chr$(10) & [Town]) & IIf([County]>"",Chr$(13) &
Chr$(10) & [County]) & IIf([postcode]>"",Chr$(13) & Chr$(10) &
Format([PostCode],"@@@@ @@@")) & IIf([Country]>"",Chr$(13) & Chr$(10) &
[Country])
Here is the SQL

SELECT DISTINCT [Member].[MemSurName], [Member].[MemHead],
[Member].[MemRetiredID], Names([Member]![MemAddID]) AS [Names],
[Member].[PaymentMethodID], [Member].[MemFirstName], [Member].[MemTitle],
[Member].[MemEMail], [lstAddress].[MemAddLF]
FROM [Year] INNER JOIN (Member INNER JOIN lstAddress ON
[Member].[MemAddID]=[lstAddress].[MemAddID]) ON
[Year].[YearID]=[Member].[YearID]
WHERE ((([Member].[MemHead])=True) And
(([Member].[MemRetiredID])=IIf([Year]![MemActivity]=0,[Membe r].[MemRetiredID],[Year]![MemActivity])))
ORDER BY [Member].[MemSurName];

The Names(Member!MemAddID) calls a function to combine all members who live
in at that address

The problem is to sort alphabetically, I need the SurName in the query. If I
use it I get too many records. If I include it as a Order By field but don't
have SurName in the main part of the SQL I can't use the DISTINT Keyword to
limit the number of records.

Catch 22

Phil

wrote in message
news:64ca7a41-343d-4f19-960f-9fc10869303c@d70g2000hsb.google groups.com...
On Jan 16, 2:22 pm, "Phil Stanton" wrote:
> Without going into too much detail, I have a table consisting of
> FirstName,
> SurName and Address. I want to produce labels with names and addresses. If
> 2
> people live at the same address say Mr Taffy Robinson & Mrs Gwen Robinson
> both living at 1 Great North Road using a bit if code I produce
>
> Mr & Mrs T Robinson
> 1 Great North Road
>
> Equally their next door neighbours Dr John Smith and Miss Mary Jones come
> out as
> Dr J Smith & Miss M Jones
> 3 Great North Road
>
> The labels need to be generated in alphabetical order ordered by the
> surname
> ( if more than 1 surname at 1 address with the man's name first. - Yes - I
> am a sexist). If I include the first name and the surname in the query I
> get
> 4 records. If I include only the surname, I get 3 records. If I omit the
> surname, I get the 2 records as required, but I get Smith before Robinson.
> Presumably because Dr comes before Mr. Any ideas how to get 2 records with
> Robinson appearing before Smith
>
> Thanks
>
> Phil

You need a individual field for each data element. Specifically the
Title for each person needs to be in its own field. Also consider a
field for generational qualifier (aka suffix).

Re: Preserving sort order in a query

You email is somewhat vague, but let's see.

Sounds like you have a table of addresses, and a table of names and that
you're linking names to an address.
You need to concatenate names for each address and hold the desired surname
(sorted) separately before you could query.

Concatenation is not fun. In basic terms (although not that basic, still
easier than working it out completely, hence your big pay to work this out)

- Your query will have 2 Name tables, one with males list, the other with
females, both linked to the addresses.
- per Address, First male_name & First female_name, First male_surname

I hope this helps to point you in the right direction


Dominic

"Phil Stanton" wrote in message
news:13osmcadjqurcff@corp.supernews.com...
> Without going into too much detail, I have a table consisting of
> FirstName, SurName and Address. I want to produce labels with names and
> addresses. If 2 people live at the same address say Mr Taffy Robinson &
> Mrs Gwen Robinson both living at 1 Great North Road using a bit if code I
> produce
>
> Mr & Mrs T Robinson
> 1 Great North Road
>
> Equally their next door neighbours Dr John Smith and Miss Mary Jones come
> out as
> Dr J Smith & Miss M Jones
> 3 Great North Road
>
> The labels need to be generated in alphabetical order ordered by the
> surname ( if more than 1 surname at 1 address with the man's name
> first. - Yes - I am a sexist). If I include the first name and the surname
> in the query I get 4 records. If I include only the surname, I get 3
> records. If I omit the surname, I get the 2 records as required, but I get
> Smith before Robinson. Presumably because Dr comes before Mr. Any ideas
> how to get 2 records with Robinson appearing before Smith
>
> Thanks
>
> Phil
>

Re: Preserving sort order in a query

Thanks for reply, Dominic

I was being stupid. I already have a field in the Members table for whether
that person was the Adressee. Just forgot to flag it. Your reply put me on
the right track of selecting the Alpha Male in the home

Thanks again

Phil

"Dominic Vella" wrote in message
news:478e85cf$0$22049$afc38c87@news.optusnet.com.au...
> You email is somewhat vague, but let's see.
>
> Sounds like you have a table of addresses, and a table of names and that
> you're linking names to an address.
> You need to concatenate names for each address and hold the desired
> surname (sorted) separately before you could query.
>
> Concatenation is not fun. In basic terms (although not that basic, still
> easier than working it out completely, hence your big pay to work this
> out)
>
> - Your query will have 2 Name tables, one with males list, the other with
> females, both linked to the addresses.
> - per Address, First male_name & First female_name, First male_surname
>
> I hope this helps to point you in the right direction
>
>
> Dominic
>
> "Phil Stanton" wrote in message
> news:13osmcadjqurcff@corp.supernews.com...
>> Without going into too much detail, I have a table consisting of
>> FirstName, SurName and Address. I want to produce labels with names and
>> addresses. If 2 people live at the same address say Mr Taffy Robinson &
>> Mrs Gwen Robinson both living at 1 Great North Road using a bit if code I
>> produce
>>
>> Mr & Mrs T Robinson
>> 1 Great North Road
>>
>> Equally their next door neighbours Dr John Smith and Miss Mary Jones come
>> out as
>> Dr J Smith & Miss M Jones
>> 3 Great North Road
>>
>> The labels need to be generated in alphabetical order ordered by the
>> surname ( if more than 1 surname at 1 address with the man's name
>> first. - Yes - I am a sexist). If I include the first name and the
>> surname in the query I get 4 records. If I include only the surname, I
>> get 3 records. If I omit the surname, I get the 2 records as required,
>> but I get Smith before Robinson. Presumably because Dr comes before Mr.
>> Any ideas how to get 2 records with Robinson appearing before Smith
>>
>> Thanks
>>
>> Phil
>>
>
>