Q: Query property 'Unique records'

[Beat Scheidiger]

I do not quite understand this property. Everything is seems clear to
me, when I read the corresponding help text.

But in practice I have a question: Why are there 3 identical records
in the following query (with property 'Unique records' = Yes)?

SELECT DISTINCTROW tblCustomer.[Customer ID], tblCustomer.[Company
Name], tblCustomer.[Locatiion Code], tblOrders.[Customer ID],
tblOrders.[Order ID], tblOrders.Artikel FROM tblCustomer INNER JOIN
tblOrders ON tblCustomer.[Customer ID] = tblOrders.[Customer ID];

Can you help me? Thanks.

Beat Scheidiger

[Peter Russell]

Beat Scheidiger previously wrote:

I do not quite understand this property. Everything is seems clear to
me, when I read the corresponding help text.

But in practice I have a question: Why are there 3 identical records
in the following query (with property 'Unique records' = Yes)?

SELECT DISTINCTROW tblCustomer.[Customer ID], tblCustomer.[Company
Name], tblCustomer.[Locatiion Code], tblOrders.[Customer ID],
tblOrders.[Order ID], tblOrders.Artikel FROM tblCustomer INNER JOIN
tblOrders ON tblCustomer.[Customer ID] = tblOrders.[Customer ID];

Can you help me? Thanks.

Beat Scheidiger

Online Help is truly perplexing for Distinctrow,I think.

I prefer this definition:

'DISTINCTROW requests that Access return only rows in which the
concatenation of the primary keys from all tables supplying output columns
is unique.'

So it's looking at the values produced by concatenating primary keys and
is nothing to do with any other data. And the second part of the
definition is crucial. It's looking at such concatenations for all tables
contributing to the output.

So in your case the query behaves as if you had included the primary key
in your output whether you specified it or not.

This leads to the conclusion that Distinctrow only has any effect if one
or more tables in your query do not contribute fields to the output.

If you want to explain what you are trying to show in your query I dare
say someone here will help.

Regards

Peter Russell

[Rick Brandt]

"Beat Scheidiger" <be*************@swisscom.com> wrote in message
news:88**************************@posting.google.c om...

I do not quite understand this property. Everything is seems clear to
me, when I read the corresponding help text.

But in practice I have a question: Why are there 3 identical records
in the following query (with property 'Unique records' = Yes)?

SELECT DISTINCTROW tblCustomer.[Customer ID], tblCustomer.[Company
Name], tblCustomer.[Locatiion Code], tblOrders.[Customer ID],
tblOrders.[Order ID], tblOrders.Artikel FROM tblCustomer INNER JOIN
tblOrders ON tblCustomer.[Customer ID] = tblOrders.[Customer ID];

Unique Records means that the same "Record" will not be repeated in the
output. It doesn't mean you won't see rows with the same values. For that
you use Unique Values. In SQL the difference is that one uses DISTINCT
while the other uses DISTINCTROW.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Wayne Morgan]

DISTINCTROW will remove any records that are duplicates in ALL fields in the
underlying table(s), not just the fields being output by the query. DISTINCT
will remove duplicates in the fields being output by the query. In the query
design grid, if you right click the background where the tables are and
choose Properties, these 2 items correspond with "Unique Records" and
"Unique Values" respectively.

--
Wayne Morgan
"Beat Scheidiger" <be*************@swisscom.com> wrote in message
news:88**************************@posting.google.c om...

I do not quite understand this property. Everything is seems clear to
me, when I read the corresponding help text.

But in practice I have a question: Why are there 3 identical records
in the following query (with property 'Unique records' = Yes)?

SELECT DISTINCTROW tblCustomer.[Customer ID], tblCustomer.[Company
Name], tblCustomer.[Locatiion Code], tblOrders.[Customer ID],
tblOrders.[Order ID], tblOrders.Artikel FROM tblCustomer INNER JOIN
tblOrders ON tblCustomer.[Customer ID] = tblOrders.[Customer ID];

Can you help me? Thanks.

Beat Scheidiger