Tags: access, database, difficulty, distinct, example, funtion, microsoft, mysql, oracle, sql, theonly

DISTINCT funtion

On Database » Microsoft Access

17,556 words with 8 Comments; publish: Sun, 01 Jun 2008 09:50:00 GMT; (250107.42, « »)

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

__________________________________________

"Get Started" wrote:

> 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!

All Comments

Leave a comment...

  • 8 Comments
    • 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

      __________________________________________

      "Get Started" wrote:

      > 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!

      #1; Sun, 01 Jun 2008 09:52:00 GMT
    • Hi Get Started,

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

      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

      __________________________________________

      "Get Started" wrote:

      > 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!!

      #2; Sun, 01 Jun 2008 09:53:00 GMT
    • 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

      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,

      "Get Started" <starteget.ms-access.todaysummary.com._REMOVE_THIS_gmail.com> wrote in message

      news:eqg13f$jvj$1.ms-access.todaysummary.com.ss408.t-com.hr...

      >

      > /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.

      >

      #3; Sun, 01 Jun 2008 09:54:00 GMT
    • 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

      __________________________________________

      "Get Started" wrote:

      >

      > 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.

      #4; Sun, 01 Jun 2008 09:55:00 GMT
    • 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.

      John Spencer

      Access MVP 2001-2005, 2007

      Get Started wrote:

      > "Get Started" <starteget.ms-access.todaysummary.com._REMOVE_THIS_gmail.com> wrote in message

      > news:OzaKoWQTHHA.920.ms-access.todaysummary.com.TK2MSFTNGP05.phx.gbl...

      > 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...

      >

      >

      #5; Sun, 01 Jun 2008 09:56:00 GMT
    • Instead of just replying with:

      > Sorry, but I don't understand

      Have you actually tried to run the query I suggested? If so, did it return

      the results you wanted or not? Also, have you tried John Spencer's

      suggestion?

      Tom Wickerath

      Microsoft Access MVP

      http://www.access.qbuilt.com/html/expert_contributors.html

      http://www.access.qbuilt.com/html/search.html

      __________________________________________

      "Get Started" wrote:

      > 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.

      #6; Sun, 01 Jun 2008 09:57:00 GMT
    • A Union query is considered a read-only recordset.

      Suppose you were Mr. JET DB Engine, and your user wanted to add a new record

      to a union query that you dutifully produced. The next thing you'd be asking

      is which table do you want me to add this record to? Considering that a

      union query can be based on 1 to n tables, where n is some fairly large

      number (I'm not even going to guess), you'd need some way to identify exactly

      which table, of those n tables selected, that you wanted the record added to.

      That doesn't seem all that practicle, does it?

      Tom Wickerath

      Microsoft Access MVP

      http://www.access.qbuilt.com/html/expert_contributors.html

      http://www.access.qbuilt.com/html/search.html

      __________________________________________

      "Get Started" wrote:

      > 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...

      #7; Sun, 01 Jun 2008 09:58:00 GMT
    • Plus if you don't use UNION ALL the records have been "combined" so that

      one record could represent multiple records in the same table. Again,

      which one of the many records should be updated.

      IF you explain why you think you need to update a record, perhaps

      someone can come up with a solution for you. It would involve VBA code

      and changing the union query to specify the table name PLUS either all

      fields would need to be named the same in all the tables or there would

      have to be another way to identify the fields.

      In other words, it isn't very practical to use a UNION query for

      anything other than displaying data in a report or for displaying data

      on a form.

      John Spencer

      Access MVP 2001-2005, 2007

      Tom Wickerath wrote:[vbcol=seagreen]

      > A Union query is considered a read-only recordset.

      > Suppose you were Mr. JET DB Engine, and your user wanted to add a new record

      > to a union query that you dutifully produced. The next thing you'd be asking

      > is which table do you want me to add this record to? Considering that a

      > union query can be based on 1 to n tables, where n is some fairly large

      > number (I'm not even going to guess), you'd need some way to identify exactly

      > which table, of those n tables selected, that you wanted the record added to.

      > That doesn't seem all that practicle, does it?

      >

      > Tom Wickerath

      > Microsoft Access MVP

      > http://www.access.qbuilt.com/html/expert_contributors.html

      > http://www.access.qbuilt.com/html/search.html

      > __________________________________________

      > "Get Started" wrote:

      #8; Sun, 01 Jun 2008 09:59:00 GMT