SQL Command help for Unique records.

[mscurto]

What is the syntax for an sql command to get the following.

I want to pull in a handful of fields from a table but one of the
fields needs to be unique.

For example, if I have a customer table and I want to get each unique
customer number but I also want to get the customer's name, address,
email address, etc. What is the syntax?

I am trying to use DISTINCTROW but to no avail. If I use DISTINCT,
how do I pull in the other fields?

TIA.

[storrboy]

On Feb 16, 12:50 pm, [e-mail] wrote:

What is the syntax for an sql command to get the following.

I want to pull in a handful of fields from a table but one of the
fields needs to be unique.

For example, if I have a customer table and I want to get each unique
customer number but I also want to get the customer's name, address,
email address, etc. What is the syntax?

I am trying to use DISTINCTROW but to no avail. If I use DISTINCT,
how do I pull in the other fields?

TIA.

This is a little confusing, perhaps due to the tables not being
normalized better.
Why would a customer be in a 'customer table' more than once? Futher,
why would there be different information attached to the same cutomer
number? A customers table really should contain information about a
customer only once.

[mscurto]

On Feb 16, 12:29 pm, "storrboy" <[e-mail]:

On Feb 16, 12:50 pm, [e-mail] wrote:

What is the syntax for an sql command to get the following.

I want to pull in a handful of fields from a table but one of the
fields needs to be unique.

For example, if I have a customer table and I want to get each unique
customer number but I also want to get the customer's name, address,
email address, etc. What is the syntax?

I am trying to use DISTINCTROW but to no avail. If I use DISTINCT,
how do I pull in the other fields?

TIA.

This is a little confusing, perhaps due to the tables not being
normalized better.
Why would a customer be in a 'customer table' more than once? Futher,
why would there be different information attached to the same cutomer
number? A customers table really should contain information about a
customer only once.

Trust me, the db I have to work with here was not created by me and
the relationships are set up very poorly.

[storrboy]

On Feb 19, 9:20 am, [e-mail] wrote:

On Feb 16, 12:29 pm, "storrboy" <[e-mail]:

On Feb 16, 12:50 pm, [e-mail] wrote:

What is the syntax for an sql command to get the following.

I want to pull in a handful of fields from a table but one of the
fields needs to be unique.

For example, if I have a customer table and I want to get each unique
customer number but I also want to get the customer's name, address,
email address, etc. What is the syntax?

I am trying to use DISTINCTROW but to no avail. If I use DISTINCT,
how do I pull in the other fields?

TIA.

This is a little confusing, perhaps due to the tables not being
normalized better.
Why would a customer be in a 'customer table' more than once? Futher,
why would there be different information attached to the same cutomer
number? A customers table really should contain information about a
customer only once.

Trust me, the db I have to work with here was not created by me and
the relationships are set up very poorly.

I don't have a table like your to try things on, but if there is
different information attached to the same customer number then I
think the best you can do without normalizing is to use groupings.
This should merge all like records but duplicate the ones where one or
more fields are different.

[purpleflash]

Simple workaround

Query 1 SELECT DISTINCT (CUSTOMER_NUMBER)

then join that query to the main table and I think you will be able to
get what you want!
storrboy wrote:

On Feb 19, 9:20 am, [e-mail] wrote:

On Feb 16, 12:29 pm, "storrboy" <[e-mail]:

On Feb 16, 12:50 pm, [e-mail] wrote:

What is the syntax for an sql command to get the following.

I want to pull in a handful of fields from a table but one of the
fields needs to be unique.

For example, if I have a customer table and I want to get each unique
customer number but I also want to get the customer's name, address,
email address, etc. What is the syntax?

I am trying to use DISTINCTROW but to no avail. If I use DISTINCT,
how do I pull in the other fields?

TIA.

This is a little confusing, perhaps due to the tables not being
normalized better.
Why would a customer be in a 'customer table' more than once? Futher,
why would there be different information attached to the same cutomer
number? A customers table really should contain information about a
customer only once.

Trust me, the db I have to work with here was not created by me and
the relationships are set up very poorly.

I don't have a table like your to try things on, but if there is
different information attached to the same customer number then I
think the best you can do without normalizing is to use groupings.
This should merge all like records but duplicate the ones where one or
more fields are different.

[mscurto]

On Feb 21, 3:43 am, "purpleflash" <[e-mail]:

Simple workaround

Query 1 SELECT DISTINCT (CUSTOMER_NUMBER)

then join that query to the main table and I think you will be able to
get what you want!

storrboy wrote:

On Feb 19, 9:20 am, [e-mail] wrote:

On Feb 16, 12:29 pm, "storrboy" <[e-mail]:

On Feb 16, 12:50 pm, [e-mail] wrote:

What is the syntax for an sql command to get the following.

I want to pull in a handful of fields from a table but one of the
fields needs to be unique.

For example, if I have a customer table and I want to get each unique
customer number but I also want to get the customer's name, address,
email address, etc. What is the syntax?

I am trying to use DISTINCTROW but to no avail. If I use DISTINCT,
how do I pull in the other fields?

TIA.

This is a little confusing, perhaps due to the tables not being
normalized better.
Why would a customer be in a 'customer table' more than once? Futher,
why would there be different information attached to the same cutomer
number? A customers table really should contain information about a
customer only once.

Trust me, the db I have to work with here was not created by me and
the relationships are set up very poorly.

I don't have a table like your to try things on, but if there is
different information attached to the same customer number then I
think the best you can do without normalizing is to use groupings.
This should merge all like records but duplicate the ones where one or
more fields are different.- Hide quoted text -

- Show quoted text -

Does this command pull in all the fields of the table though?

[mscurto]

On Feb 21, 12:13 pm, [e-mail] wrote:

On Feb 21, 3:43 am, "purpleflash" <[e-mail]:

Simple workaround

Query 1 SELECT DISTINCT (CUSTOMER_NUMBER)

then join that query to the main table and I think you will be able to
get what you want!

storrboy wrote:

On Feb 19, 9:20 am, [e-mail] wrote:
On Feb 16, 12:29 pm, "storrboy" <[e-mail]:

On Feb 16, 12:50 pm, [e-mail] wrote:

What is the syntax for an sql command to get the following.

I want to pull in a handful of fields from a table but one of the
fields needs to be unique.

For example, if I have a customer table and I want to get each unique
customer number but I also want to get the customer's name, address,
email address, etc. What is the syntax?

I am trying to use DISTINCTROW but to no avail. If I use DISTINCT,
how do I pull in the other fields?

TIA.

This is a little confusing, perhaps due to the tables not being
normalized better.
Why would a customer be in a 'customer table' more than once? Futher,
why would there be different information attached to the same cutomer
number? A customers table really should contain information about a
customer only once.

Trust me, the db I have to work with here was not created by me and
the relationships are set up very poorly.

I don't have a table like your to try things on, but if there is
different information attached to the same customer number then I
think the best you can do without normalizing is to use groupings.
This should merge all like records but duplicate the ones where one or
more fields are different.- Hide quoted text -

- Show quoted text -

Does this command pull in all the fields of the table though?- Hide quoted text -

- Show quoted text -

To elaborate further, I am using the following command:

SELECT DISTINCTROW [EA_K]
FROM Customers;

I want to display all the fields in this query when the query runs and
use this to make a comparison against another table. When I run this,
it only displays the EA_K field. How do I get it to display the other
12 fields in the Customers table?

[christianlott1]

On Feb 21, 11:20 am, [e-mail] wrote:

To elaborate further, I am using the following command:

SELECT DISTINCTROW [EA_K]
FROM Customers;

I want to display all the fields in this query when the query runs and
use this to make a comparison against another table. When I run this,
it only displays the EA_K field. How do I get it to display the other
12 fields in the Customers table?

Just drag and drop the astrix field into the design grid, that will
show all fields in that table.

[Gord]

On Feb 21, 12:20 pm, [e-mail] wrote:

On Feb 21, 12:13 pm, [e-mail] wrote:

On Feb 21, 3:43 am, "purpleflash" <[e-mail]:

Simple workaround

Query 1 SELECT DISTINCT (CUSTOMER_NUMBER)

then join that query to the main table and I think you will be able to
get what you want!

storrboy wrote:
On Feb 19, 9:20 am, [e-mail] wrote:
On Feb 16, 12:29 pm, "storrboy" <[e-mail]:

On Feb 16, 12:50 pm, [e-mail] wrote:

What is the syntax for an sql command to get the following.

I want to pull in a handful of fields from a table but one of the
fields needs to be unique.

For example, if I have a customer table and I want to get each unique
customer number but I also want to get the customer's name, address,
email address, etc. What is the syntax?

I am trying to use DISTINCTROW but to no avail. If I use DISTINCT,
how do I pull in the other fields?

TIA.

This is a little confusing, perhaps due to the tables not being
normalized better.
Why would a customer be in a 'customer table' more than once? Futher,
why would there be different information attached to the same cutomer
number? A customers table really should contain information about a
customer only once.

Trust me, the db I have to work with here was not created by me and
the relationships are set up very poorly.

I don't have a table like your to try things on, but if there is
different information attached to the same customer number then I
think the best you can do without normalizing is to use groupings.
This should merge all like records but duplicate the ones where one or
more fields are different.- Hide quoted text -

- Show quoted text -

Does this command pull in all the fields of the table though?- Hide quoted text -

- Show quoted text -

To elaborate further, I am using the following command:

SELECT DISTINCTROW [EA_K]
FROM Customers;

I want to display all the fields in this query when the query runs and
use this to make a comparison against another table. When I run this,
it only displays the EA_K field. How do I get it to display the other
12 fields in the Customers table?

>I am trying to use DISTINCTROW but to no avail. If I use DISTINCT, how
do I pull in the other fields?

The DISTINCT and DISTINCTROW clauses do not affect the actual columns
that are returned by a query. That is determined by the column names
listed before the FROM clause, e.g.

SELECT [Table1].[Column1], [Table1].[Column2] FROM [Table1]

or

SELECT [Table1].* FROM [Table1]

However, DISTINCT and DISTINCTROW affect the *rows* that are returned,
and it is important to note the difference. DISTINCT removes duplicate
rows by comparing just the selected columns, whereas DISTINCTROW
removes duplicate rows by comparing *all* columns in the source table
(or set of joined tables) regardless of which columns are to be
returned. So if you had a table of Cities and Provinces called
[CityProv], like this

[City] [Province]
Calgary AB
Edmonton AB
Vancouver BC
Victoria BC
Toronto ON
Montreal QC

You would see the following:

SELECT DISTINCT [CityProv].[Province] FROM [CityProv];

Province
--------
AB
BC
ON
QC

SELECT DISTINCTROW [CityProv].[Province] FROM [CityProv];

Province
--------
AB
AB
BC
BC
ON
QC

[mscurto]

On Feb 21, 4:43 pm, "Gord" <[e-mail]:

On Feb 21, 12:20 pm, [e-mail] wrote:

On Feb 21, 12:13 pm, [e-mail] wrote:

On Feb 21, 3:43 am, "purpleflash" <[e-mail]:

Simple workaround

Query 1 SELECT DISTINCT (CUSTOMER_NUMBER)

then join that query to the main table and I think you will be able to
get what you want!

storrboy wrote:
On Feb 19, 9:20 am, [e-mail] wrote:
On Feb 16, 12:29 pm, "storrboy" <[e-mail]:

On Feb 16, 12:50 pm, [e-mail] wrote:

What is the syntax for an sql command to get the following.

I want to pull in a handful of fields from a table but one of the
fields needs to be unique.

For example, if I have a customer table and I want to get each unique
customer number but I also want to get the customer's name, address,
email address, etc. What is the syntax?

I am trying to use DISTINCTROW but to no avail. If I use DISTINCT,
how do I pull in the other fields?

TIA.

This is a little confusing, perhaps due to the tables not being
normalized better.
Why would a customer be in a 'customer table' more than once? Futher,
why would there be different information attached to the same cutomer
number? A customers table really should contain information about a
customer only once.

Trust me, the db I have to work with here was not created by me and
the relationships are set up very poorly.

I don't have a table like your to try things on, but if there is
different information attached to the same customer number then I
think the best you can do without normalizing is to use groupings.
This should merge all like records but duplicate the ones where one or
more fields are different.- Hide quoted text -

- Show quoted text -

Does this command pull in all the fields of the table though?- Hide quoted text -

- Show quoted text -

To elaborate further, I am using the following command:

SELECT DISTINCTROW [EA_K]
FROM Customers;

I want to display all the fields in this query when the query runs and
use this to make a comparison against another table. When I run this,
it only displays the EA_K field. How do I get it to display the other
12 fields in the Customers table?
I am trying to use DISTINCTROW but to no avail. If I use DISTINCT, how
do I pull in the other fields?

The DISTINCT and DISTINCTROW clauses do not affect the actual columns
that are returned by a query. That is determined by the column names
listed before the FROM clause, e.g.

SELECT [Table1].[Column1], [Table1].[Column2] FROM [Table1]

or

SELECT [Table1].* FROM [Table1]

However, DISTINCT and DISTINCTROW affect the *rows* that are returned,
and it is important to note the difference. DISTINCT removes duplicate
rows by comparing just the selected columns, whereas DISTINCTROW
removes duplicate rows by comparing *all* columns in the source table
(or set of joined tables) regardless of which columns are to be
returned. So if you had a table of Cities and Provinces called
[CityProv], like this

[City] [Province]
Calgary AB
Edmonton AB
Vancouver BC
Victoria BC
Toronto ON
Montreal QC

You would see the following:

SELECT DISTINCT [CityProv].[Province] FROM [CityProv];

Province
--------
AB
BC
ON
QC

SELECT DISTINCTROW [CityProv].[Province] FROM [CityProv];

Province
--------
AB
AB
BC
BC
ON
QC- Hide quoted text -

- Show quoted text -

yes, that is what I am getting something similar.

How could I write the SQL command to get the following?

[City] [Province] (Original Table)
Calgary AB
Edmonton AB
Vancouver BC
Victoria BC
Toronto ON
Montreal QC

[City] [Province] (Results desired)
Calgary AB
Vancouver BC
Toronto ON
Montreal QC

I want to eliminate the duplicate Provinces (AB,BC) yet display the
City and the Province.

[Gord]

I want to eliminate the duplicate Provinces (AB,BC) yet display the

City and the Province.

Try something like this:

SELECT First(CityProv.City) AS City, CityProv.Province
FROM CityProv
GROUP BY CityProv.Province;

On Feb 22, 3:56 pm, [e-mail] wrote:

On Feb 21, 4:43 pm, "Gord" <[e-mail]:

On Feb 21, 12:20 pm, [e-mail] wrote:

On Feb 21, 12:13 pm, [e-mail] wrote:

On Feb 21, 3:43 am, "purpleflash" <[e-mail]:

Simple workaround

Query 1 SELECT DISTINCT (CUSTOMER_NUMBER)

then join that query to the main table and I think you will be able to
get what you want!

storrboy wrote:
On Feb 19, 9:20 am, [e-mail] wrote:
On Feb 16, 12:29 pm, "storrboy" <[e-mail]:

On Feb 16, 12:50 pm, [e-mail] wrote:

What is the syntax for an sql command to get the following.

I want to pull in a handful of fields from a table but one of the
fields needs to be unique.

For example, if I have a customer table and I want to get each unique
customer number but I also want to get the customer's name, address,
email address, etc. What is the syntax?

I am trying to use DISTINCTROW but to no avail. If I use DISTINCT,
how do I pull in the other fields?

TIA.

This is a little confusing, perhaps due to the tables not being
normalized better.
Why would a customer be in a 'customer table' more than once? Futher,
why would there be different information attached to the same cutomer
number? A customers table really should contain information about a
customer only once.

Trust me, the db I have to work with here was not created by me and
the relationships are set up very poorly.

I don't have a table like your to try things on, but if there is
different information attached to the same customer number then I
think the best you can do without normalizing is to use groupings.
This should merge all like records but duplicate the ones where one or
more fields are different.- Hide quoted text -

- Show quoted text -

Does this command pull in all the fields of the table though?- Hide quoted text -

- Show quoted text -

To elaborate further, I am using the following command:

SELECT DISTINCTROW [EA_K]
FROM Customers;

I want to display all the fields in this query when the query runs and
use this to make a comparison against another table. When I run this,
it only displays the EA_K field. How do I get it to display the other
12 fields in the Customers table?
>I am trying to use DISTINCTROW but to no avail. If I use DISTINCT, how
>do I pull in the other fields?

The DISTINCT and DISTINCTROW clauses do not affect the actual columns
that are returned by a query. That is determined by the column names
listed before the FROM clause, e.g.

SELECT [Table1].[Column1], [Table1].[Column2] FROM [Table1]

or

SELECT [Table1].* FROM [Table1]

However, DISTINCT and DISTINCTROW affect the *rows* that are returned,
and it is important to note the difference. DISTINCT removes duplicate
rows by comparing just the selected columns, whereas DISTINCTROW
removes duplicate rows by comparing *all* columns in the source table
(or set of joined tables) regardless of which columns are to be
returned. So if you had a table of Cities and Provinces called
[CityProv], like this

[City] [Province]
Calgary AB
Edmonton AB
Vancouver BC
Victoria BC
Toronto ON
Montreal QC

You would see the following:

SELECT DISTINCT [CityProv].[Province] FROM [CityProv];

Province
--------
AB
BC
ON
QC

SELECT DISTINCTROW [CityProv].[Province] FROM [CityProv];

Province
--------
AB
AB
BC
BC
ON
QC- Hide quoted text -

- Show quoted text -

yes, that is what I am getting something similar.

How could I write the SQL command to get the following?

[City] [Province] (Original Table)
Calgary AB
Edmonton AB
Vancouver BC
Victoria BC
Toronto ON
Montreal QC

[City] [Province] (Results desired)
Calgary AB
Vancouver BC
Toronto ON
Montreal QC

I want to eliminate the duplicate Provinces (AB,BC) yet display the
City and the Province.