DISTINCT funtion

G

Get Started

Hi,

I have an database with more than hundred rows and columns Column 1, Column
2 and Column3.

I would like to setup new query with DISTINCT function just for Column1 with
related data for Column2 and 3.

Just like folowing:
MY BASE:
Row 1 Column 1 Column 2 Column 3
Row 2 Column 1 (the same like row above) Column 2 Column 3
Row 3 Column 1 Column 2 Column 3
Row 4 Column 1 (the same like row above) Column 2 Column 3

Data from Column1 are reocuring, and I'd like to create query(s) with final
result:

Row 1 Column 1 Column 2 Column 3
Row 3 Column 1 Column 2 Column 3
(because Row1 Column1 is the same like Row2 Column1)

Please help advise.
Tnx!
 
M

Marshall Barton

Get said:
I have an database with more than hundred rows and columns Column 1, Column
2 and Column3.

I would like to setup new query with DISTINCT function just for Column1 with
related data for Column2 and 3.

Just like folowing:
MY BASE:
Row 1 Column 1 Column 2 Column 3
Row 2 Column 1 (the same like row above) Column 2 Column 3
Row 3 Column 1 Column 2 Column 3
Row 4 Column 1 (the same like row above) Column 2 Column 3

Data from Column1 are reocuring, and I'd like to create query(s) with final
result:

Row 1 Column 1 Column 2 Column 3
Row 3 Column 1 Column 2 Column 3
(because Row1 Column1 is the same like Row2 Column1)
Click to expand...


So, you don't care what values you get in columns 2 and 3.
If so:

SELECT column1, First(column2) As c2 First(column3) As c3
FROM table
GROUP BY column1
 
G

Get Started

Marshall Barton said:
So, you don't care what values you get in columns 2 and 3.
If so:

SELECT column1, First(column2) As c2 First(column3) As c3
FROM table
GROUP BY column1
Click to expand...


Well, my SQL query looks like:

SELECT [database].company, [database].address, [database].phone
FROM [database];

"Company" is reocuring.

I know distinct function and I could help myself with:
SELECT DISTINCT [database].company,
FROM [database];

....but I need "distincted" fields with included address and phone. I don't
know to setup GROUP BY function.

Thnx.
 
G

Guest

G

Guest

Hi GettingStarted,

Sorry, but for some reason several posts were not shown in my browser when I
posted my first reply, after a refresh, including Marshall's reply and your
reply back to Marshall...

Try the following:

SELECT Database.Company, Database.Address, Database.Phone
FROM [Database]
GROUP BY Database.Company, Database.Address, Database.Phone
ORDER BY Database.Company;

By the way, Database is a reserved word. You should avoid naming anything
with a reserved word. Here is a handy list for your future reference:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
M

Marshall Barton

Get said:
"Marshall Barton"wrote
So, you don't care what values you get in columns 2 and 3.
If so:

SELECT column1, First(column2) As c2 First(column3) As c3
FROM table
GROUP BY column1
Click to expand...


Well, my SQL query looks like:

SELECT [database].company, [database].address, [database].phone
FROM [database];

"Company" is reocuring.

I know distinct function and I could help myself with:
SELECT DISTINCT [database].company,
FROM [database];

...but I need "distincted" fields with included address and phone. I don't
know to setup GROUP BY function.
Click to expand...


If all companies only have one address or if some companies
can have several different addresses and you want to see all
the addresses, then just use DISTINCT:

SELECT DISTINCT [database].company,
[database].address,
[database].phone
FROM [database]

[In the Query Design window set the query's Unique Values
property]

If you only want to see a company once, then the question
becomes: What to do with the different addresses?

If you don't care which address the query returns then use a
query like I posted earlier:

SELECT [database].company,
First([database].address) As Addr,
First([database].phone) As Ph
FROM [database]
GROUP BY [database].company

[In the Query Design window, a Group By query is known as a
Totals query (View menu)]
 
J

John W. Vinson

SELECT [database].company, [database].address, [database].phone
FROM [database];

"Company" is reocuring.

I know distinct function and I could help myself with:
SELECT DISTINCT [database].company,
FROM [database];

...but I need "distincted" fields with included address and phone. I don't
know to setup GROUP BY function.
Click to expand...

Ok... if you have two records

Acme Corp.; 123 Main St., Anytown MD; 222-555-5555
Acme Corp.; 515 Elm Ave, Bangor ME; 444-555-5555


What do you want to see?

You say you want to a) see each company only once and b) included
address and phone. These appear to be contradictory requirements.
Clearly we're not understanding your question!

John W. Vinson [MVP]
 
G

Get Started

Tom Wickerath said:
I'm sorry, but I just don't understand your example....and I'm likely not
the
only one who is having this difficulty. Can you show an example of your
data
as it exists, and the query result that you would like to obtain. For
example:

Existing data
Field1 Field2 Field3
Tom Dick Harry
Cindy Linda JimBob


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
Click to expand...



Hi Tom (and John and Marshall),

Well, here is my real example:
Access - Table:

Field1 Field2 Field3 Field4

Tom Address1 Phone1 Fax1
Tom "empty" "empty" "empty"
Tom "empty" "empty" "empty"
Jane Address1 Phone1 Fax1
Jane "empty" "empty" "empty"
Jane "empty" "empty" "empty"
Jane "empty" "empty" "empty"
Max Address1 Phone1 Fax1
Max "empty" "empty" "empty"
Max "empty" "empty" "empty"
Max "empty" "empty" "empty"
Max "empty" "empty" "empty"
Max "empty" "empty" "empty"
Max "empty" "empty" "empty"


I can create one query like following:

SELECT DISTINCT mybase.Field1
FROM mybase;

and result will be:

Tom
Jane
Max

I would like to separate/joing other columns and get an result:
Tom Address1 Phone1 Fax1
Jane Address1 Phone1 Fax1
Max Address1 Phone1 Fax1

Yes, I know, maybe it is not possible, and I'm not an access expert :-(
I would expect SQl or Normaln design solutin, it doesn't metter.

Thanks a lot for your help!

Regards!
 
G

Guest

If you literally have the string "empty" in Field2-Field4, then the following
query should do the job. This would require the word empty across the record
(ie., if one field was indicated empty, all would need to be indicated as
empty):

SELECT mybase.Field1, mybase.Field2 ,
mybase.Field3 , mybase.Field4
FROM mybase
WHERE (((mybase.Field2 )<>"empty") AND
((mybase.Field3 )<>"empty") AND
((mybase.Field4)<>"empty"));

If, by empty, you mean a zero length string (ZLS), then change the criteria
to either:

WHERE (((mybase.Field2 )<>"") AND
((mybase.Field3 )<>"") AND
((mybase.Field4)<>""));

Finally, if empty is intended to represent a null, us the following:

SELECT mybase.Field1, mybase.Field2 ,
mybase.Field3 , mybase.Field4
FROM mybase
WHERE (((mybase.Field2 ) Is Not Null) AND
((mybase.Field3 ) Is Not Null) AND
((mybase.Field4) Is Not Null));



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
G

Get Started

If, by empty, you mean a zero length string (ZLS), then change the
criteria
to either:

WHERE (((mybase.Field2 )<>"") AND
((mybase.Field3 )<>"") AND
((mybase.Field4)<>""));
Click to expand...

That's my point! Very very god job, thank you very much!!!


And I have the second (the last, I promise question).. If you know answer
please replay...

Well, with your instructions I have got following query:
Tom Address1 Phone1 Fax1
Jane Address1 Phone1 Fax1
Max Address1 Phone1 Fax1

Now, I have another base (query) (or I can import it using "Get external
data" and "import in a existing table"). Datafields are:
Tom "empty" "empty" "empty"
Tom "empty" "empty" "empty"
Jane "empty" "empty" "empty"
Jane "empty" "empty" "empty"
John "empty" "empty" "empty"
John "empty" "empty" "empty"
John "empty" "empty" "empty"
Matt "empty" "empty" "empty"
Matt "empty" "empty" "empty"
Matt "empty" "empty" "empty"
Nick "empty" "empty" "empty"
Nick "empty" "empty" "empty"
Nick "empty" "empty" "empty"
Nick "empty" "empty" "empty"
Nick "empty" "empty" "empty"

You could find that there are existing names (Tom, Jane) and a few new names
(John, Matt, Nick).

Is it possible to join it on new query and get final query like following:

Tom Address1 Phone1 Fax1
Jane Address1 Phone1 Fax1
Max Address1 Phone1 Fax1
John "empty" "empty" "empty"
Matt "empty" "empty" "empty"
Nick "empty" "empty" "empty"

Actually, I would like to setup DISTINCT function just on Field1 and add an
existing data on other fields: Address and phone for handled data, and
"empty" for unsorted.


Thanks again for your previously help!!
 
G

Guest

Hi Get Started,
That's my point! Very very god job, thank you very much!!!
Click to expand...

God job? <smile>. You're welcome.

Regarding zero length strings (ZLS), there is a good reason to avoid them
altogether. See this page by Access MVP Allen Browne:

Problem properties
http://allenbrowne.com/bug-09.html

On your follow-up question, you can likely achieve what you need by using a
Union query, with appropriate selection criteria. A union query can be used
to join two or more similar recordsets together. Something like this
(untested):

SELECT mybase.Field1, mybase.Field2 ,
mybase.Field3 , mybase.Field4
FROM mybase
WHERE (((mybase.Field2 )<>"") AND
((mybase.Field3 )<>"") AND
((mybase.Field4)<>""))
UNION
SELECT myQuery.Field1, myQuery.Field2 ,
myQuery.Field3 , myQuery.Field4
FROM myQuery
WHERE (((myQuery.Field2 )="") AND
((myQuery.Field3 )="") AND
((myQuery.Field4)=""))
ORDER BY Field1;

Union queries cannot be represented in the more familiar Query Design view;
you are limited to using the SQL view. However, you can build the individual
queies using design view, and then copy & paste the appropriate portions of
the SQL statement together (ORDER BY, GROUP BY and HAVING clauses can only go
at the end). Check out this tutorial, which was written several years ago by
a co-worker of mine:

Union Queries
http://home.comcast.net/~tutorme2/samples/unionqueries.zip


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
G

Get Started

SELECT mybase.Field1, mybase.Field2 ,


/cut/

No, I've tested your suggestion but it isn't my way...


Well, I'll explain with further example. New query should be like following:

a)
SELECT mybase.Field1, mybase.Field2, mybase.Field3, mybase.Field4
FROM baza
WHERE (((mybase.Field2 )<>""))
ORDER BY Field1

b)
UNION
SELECT DISTINCT mybase.Field1
FROM mybase



From a) I'll get:
Tom Field2 Field3 Field4
Jane Field2 Field3 Field4
Max Field2 Field3 Field4
(Field2-4 are data)

and from b) I'll get
Tom
Jane
Max
Marc
Frank
Joseph
Matt
(Fields2-4 for all other rows are empty)


with a) UNION b) I'd like to get:
Tom Field2 Field3 Field4
Jane Field2 Field3 Field4
Max Field2 Field3 Field4
Marc "empty" "empty" "empty"
Frank "empty" "empty" "empty"
Joseph "empty" "empty" "empty"
Matt "empty" "empty" "empty"


I'm so boring.... but my problem is: I have a lot of sorted data (one query,
example a), you helped me with this one) and a lot of un-sorted data with
the same Field1 and no-data on other fields. So basically, I'd like to join
sorted data and put DISTINCT function for Field1.
 
G

George Nicholson

You have to have the same number of fields in each part of a UNION query,
even if the fields are empty placeholders.

change
UNION
SELECT DISTINCT mybase.Field1
Click to expand...

to
UNION SELECT DISTINCT mybase.Field1, Null as Field2, Null as Field3, Null as
Field4

FYI, only the Field names used in the first SELECT statement are used, so
you could really name the placeholders anything you like and it would make
no difference, as long as the field counts match.

HTH,
 
G

Get Started

George Nicholson said:
You have to have the same number of fields in each part of a UNION query,
even if the fields are empty placeholders.

change

to
UNION SELECT DISTINCT mybase.Field1, Null as Field2, Null as Field3, Null
as Field4

FYI, only the Field names used in the first SELECT statement are used, so
you could really name the placeholders anything you like and it would make
no difference, as long as the field counts match.
Click to expand...


So, finaly query should be like:

SELECT mybase.Field1, mybase.Field2, mybase.Field3, mybase.Field4
FROM mybase
WHERE (((mybase.Field2 )<>""))
ORDER BY Field1
UNION SELECT DISTINCT mybase.Field1, mybase.Field2, mybase.Field3,
mybase.Field4
FROM mybase


It doesn't work... Could you please describe "mybase.Field1, Null as Field2,
Null as Field3, Null as Field4" ?

Thnx.
 
G

Guest

Only one ORDER BY clause is allowed in a union query, and it has to go at the
end of the SQL statement. How about this? I think it is close to what you
want, although the ordering may not be correct.


SELECT DISTINCT mybase.Field1, mybase.Field2 ,
mybase.Field3 , mybase.Field4
FROM mybase
WHERE (((mybase.Field2 ) Is Not Null)
AND ((mybase.Field3 ) Is Not Null)
AND ((mybase.Field4) Is Not Null))
UNION
SELECT DISTINCT mybase.Field1, "Empty" AS 2,
"Empty" AS 3, "Empty" AS 4
FROM mybase
WHERE (((mybase.Field2 ) Is Null Or (mybase.Field2 )="")
AND ((mybase.Field3 ) Is Null Or (mybase.Field3 )="")
AND ((mybase.Field4) Is Null Or (mybase.Field4)=""))
ORDER BY 1;


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
G

Get Started

Tom Wickerath said:
Only one ORDER BY clause is allowed in a union query, and it has to go at
the
end of the SQL statement. How about this? I think it is close to what you
want, although the ordering may not be correct.


SELECT DISTINCT mybase.Field1, mybase.Field2 ,
mybase.Field3 , mybase.Field4
FROM mybase
WHERE (((mybase.Field2 ) Is Not Null)
AND ((mybase.Field3 ) Is Not Null)
AND ((mybase.Field4) Is Not Null))
UNION
SELECT DISTINCT mybase.Field1, "Empty" AS 2,
"Empty" AS 3, "Empty" AS 4
FROM mybase
WHERE (((mybase.Field2 ) Is Null Or (mybase.Field2 )="")
AND ((mybase.Field3 ) Is Null Or (mybase.Field3 )="")
AND ((mybase.Field4) Is Null Or (mybase.Field4)=""))
ORDER BY 1;
Click to expand...


ORDER BY is not so important, I'll setup it in final step.

Sorry, but I don't understand
UNION SELECT DISTINCT mybase.Field1, "Empty" AS 2, "Empty" AS 3, "Empty" AS
4


What do you mean "Empty" AS 2 ?

Table name is "mybase" and columns are "Field1-4".

Thnx.
 
G

Get Started

Get Started said:
ORDER BY is not so important, I'll setup it in final step.

Sorry, but I don't understand
UNION SELECT DISTINCT mybase.Field1, "Empty" AS 2, "Empty" AS 3, "Empty"
AS 4


What do you mean "Empty" AS 2 ?

Table name is "mybase" and columns are "Field1-4".

Thnx.
Click to expand...

And maybe another way: It is not important to DISTINCT the same base. I can
creaty query

SELECT mybase.Field1, mybase.Field2, mybase.Field3, mybase.Field4
FROM mybase
WHERE (((mybase.Field2 )<>""))



UNION SELECT DISTINCT newbase.Field1
FROM newbase



But that's my problem. How to DISTINCT just Field1 and UNION it with query
above...
 
J

John Spencer

Perhaps you want the following.

SELECT mybase.Field1, mybase.Field2, mybase.Field3, mybase.Field4
FROM mybase
WHERE (((mybase.Field2 )<>""))

UNION ALL
SELECT DISTINCT newbase.Field1, Null, Null, Null
FROM newbase

That will give you all records that match your criteria in MyBase and
only Distinct values from NewBase.

The nulls are needed since you MUST have the same number of fields in
all the queries involved in a UNION query. The fields should all be the
same type, but you can use Null as a placeholder.

UNION (without all) does a DISTINCT over the entire results. UNION ALL
just returns all the results from the queries without eliminating the
duplicates.
 
G

Get Started

John Spencer said:
Perhaps you want the following.

SELECT mybase.Field1, mybase.Field2, mybase.Field3, mybase.Field4
FROM mybase
WHERE (((mybase.Field2 )<>""))

UNION ALL
SELECT DISTINCT newbase.Field1, Null, Null, Null
FROM newbase

That will give you all records that match your criteria in MyBase and only
Distinct values from NewBase.

The nulls are needed since you MUST have the same number of fields in all
the queries involved in a UNION query. The fields should all be the same
type, but you can use Null as a placeholder.

UNION (without all) does a DISTINCT over the entire results. UNION ALL
just returns all the results from the queries without eliminating the
duplicates.
Click to expand...


Hello guys,

thanks a lot, that was my question.

One note: why is not possible to add data in UNION query? As I can see UNION
query is designed for final result...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel 2013 Merge Data 2
Vlookup to Access Dlookup 0
VBAscript for data matching excels 0
Excel VBA 1
Excel Concatenate with line breaks but ignore blank cells 3
Sequential numbering when X column repeats records 1
Access 2007 Graphs 6
using macro insert data from a table to a new table 2

Top