Select Query

[Nicolae Fieraru]

Hi All,

I have a Table1 with ID, Name, Address, Purchase.
I want to build a query which shows all the records from Table1 (only fields
ID, Name, Address), but there shouldn't be listed records which have the
same Name and Address (if a customer made two different purchases, he should
be listed only once).
Any help appreciated

Regards,
Nicolae

[Nicolae Fieraru]

I think I managed to build this query by myself, using the sample query
which John Winterbottom had the kindness to show me a few days ago.

SELECT a.*
FROM Table1 AS a
WHERE a.ID =
(
SELECT min(b.ID)
FROM Table1 AS b
WHERE b.Address = a.Address AND b.Name = a.Name
);

Regards,
Nicolae
"Nicolae Fieraru" <no****@please.cxm> wrote in message
news:40********@duster.adelaide.on.net...

Hi All,

I have a Table1 with ID, Name, Address, Purchase.
I want to build a query which shows all the records from Table1 (only fields ID, Name, Address), but there shouldn't be listed records which have the
same Name and Address (if a customer made two different purchases, he should be listed only once).
Any help appreciated

Regards,
Nicolae

[John Winterbottom]

"Nicolae Fieraru" <no****@please.cxm> wrote in message
news:40********@duster.adelaide.on.net...

Hi All,

I have a Table1 with ID, Name, Address, Purchase.
I want to build a query which shows all the records from Table1 (only fields ID, Name, Address), but there shouldn't be listed records which have the
same Name and Address (if a customer made two different purchases, he should be listed only once).
Any help appreciated

without the ID column you can do:

select [Name], Address
from Table1
group by [Name], Address

if you have more than one purchase for a customer, does it matter which ID
is returned? And if it does, what criteria will you use.- i.e. the first,
the last etc..

[Nicolae Fieraru]

Hi John,

The real table actually has more fields than the one in my example. I need
to show in my query some of the fields. These fields contain information
about the customers mailing addresses. I don't need any other details from
the rest of the fields and I don't care too much which of the records will
be shown in the query (although I think the earlier ID is preffered).
It seems I oversimplified my problem and this causes some problems.
Here is an example closer to my real problem:

Table 1 with ID, FirstName, Surname, Address1, Address2, Suburb, State,
Mailout, Purchase

I need all the fields from all records from Table1 with unique Surname AND
Address1 (there are customers who made multiple purchases and they are
recorded in the table multiple times. But for example Address1 or State
might not be filled in into some records, while in others it is)

The query I built

SELECT a.*
FROM Table1 AS a
WHERE a.ID =
(
SELECT min(b.ID)
FROM Table1 AS b
WHERE b.Address1 = a.Address1 AND b.Surname = a.Surname
);

works fine for my sample table with 10 records, but stops responding for the
real table, with 228000 records...
(I run a sample database under Access97 on my computer, where I test my
queries and the real database on Access2002 on a remote computer, using
Remote Desktop Connection)

Regards,
Nicolae
"John Winterbottom" <as******@hotmail.com> wrote in message
news:2j************@uni-berlin.de...

"Nicolae Fieraru" <no****@please.cxm> wrote in message
news:40********@duster.adelaide.on.net...

Hi All,

I have a Table1 with ID, Name, Address, Purchase.
I want to build a query which shows all the records from Table1 (only

fields

ID, Name, Address), but there shouldn't be listed records which have the
same Name and Address (if a customer made two different purchases, he

should

be listed only once).
Any help appreciated

without the ID column you can do:

select [Name], Address
from Table1
group by [Name], Address

if you have more than one purchase for a customer, does it matter which ID
is returned? And if it does, what criteria will you use.- i.e. the first,
the last etc..

[Salad]

Nicolae Fieraru wrote:

Hi John,

The real table actually has more fields than the one in my example. I need
to show in my query some of the fields. These fields contain information
about the customers mailing addresses. I don't need any other details from
the rest of the fields and I don't care too much which of the records will
be shown in the query (although I think the earlier ID is preffered).
It seems I oversimplified my problem and this causes some problems.
Here is an example closer to my real problem:

Table 1 with ID, FirstName, Surname, Address1, Address2, Suburb, State,
Mailout, Purchase

I need all the fields from all records from Table1 with unique Surname AND
Address1 (there are customers who made multiple purchases and they are
recorded in the table multiple times. But for example Address1 or State
might not be filled in into some records, while in others it is)

The query I built

SELECT a.*
FROM Table1 AS a
WHERE a.ID =
(
SELECT min(b.ID)
FROM Table1 AS b
WHERE b.Address1 = a.Address1 AND b.Surname = a.Surname
);

works fine for my sample table with 10 records, but stops responding for the
real table, with 228000 records...
(I run a sample database under Access97 on my computer, where I test my
queries and the real database on Access2002 on a remote computer, using
Remote Desktop Connection)

Regards,
Nicolae
"John Winterbottom" <as******@hotmail.com> wrote in message
news:2j************@uni-berlin.de...

"Nicolae Fieraru" <no****@please.cxm> wrote in message
news:40********@duster.adelaide.on.net...

Hi All,

I have a Table1 with ID, Name, Address, Purchase.
I want to build a query which shows all the records from Table1 (only

fields

ID, Name, Address), but there shouldn't be listed records which have the
same Name and Address (if a customer made two different purchases, he

should

be listed only once).
Any help appreciated

without the ID column you can do:

select [Name], Address
from Table1
group by [Name], Address

if you have more than one purchase for a customer, does it matter which ID
is returned? And if it does, what criteria will you use.- i.e. the first,
the last etc..

As I mentioned in an earlier response to you regarding slow
queries...build a second query. Make it a totals query. THen link your
current query to the totals query and remove your subquery.