Hi all,



I have an Access database with one table an many fields (more than 12). It 

contains stocks with their names prices through the last years statistics 

etc. As you understand, each stock's name is written many times in 

multiple records. All i want to do is make a query that gets only the 

unique values of the stock names (together with the first row they find 

with it - any record) and return me a list of all available stocks in the 

stock market. As i said i want to get with this list a row - any row which 

contains other important data about the stock (its code name - IPO date 

etc). I will export it in another database as a new table. Any advice on 

the correct syntaxt for the query???

If you use the query designer, it's easy (and if you want a SQL

statement, after you get it right in the query designer, change to SQL

view and copy it). 



Open a new query; put the field with the name of the stock and any

information that you need (as long as it is the same for all records

with that stock name), set the sort to ascending for the stock name,

then click on the group by button. You can check it with datasheet view

to be sure it is what you want, then use a make table query to write it

to a new file. This assumes that the information in the other fields you

choose is the same in all records for the stock (are there some design

problems here?). If not, instead of leaving Group By in the field,

choose First Of.



Hope this helps.

> Hi all,

> 

> I have an Access database with one table an many fields (more than 12). 

It contains stocks with their names prices through the last years 

statistics  etc. As you understand, each stock's name is written many 

times in > multiple records. All i want to do is make a query that gets 

only the > unique values of the stock names (together with the first row 

they find with it - any record) and return me a list of all available 

stocks in the > stock market. As i said i want to get with this list a 

row - any row which > contains other important data about the stock (its 

code name - IPO date > etc). I will export it in another database as a new 

table. Any advice on > the correct syntaxt for the query???

> 



Here is the instructions to make a query to get the unique data you want.

Create the query, then try changing it to a make-table query. Then you can

export the new table. Good Luck! Carol



> ACC: How to Use a Query to Filter Unique Data

When you want to filter a table to eliminate duplicate data, you need to 

use a query that uses one of the aggregate (totals) functions, such as 

First(), Last(), Min(), or Max(), in the fields that do not contain the 

duplicate data. The fields that contain the duplicate data should contain 

the GroupBy() function. 



NOTE: A demo of the technique used in this article can be seen in the 

sample file, Qrysmp97.exe. For info about how to obtain this sample file, 

see Microsoft Knowledge Base: Q182568 ACC97: MS Access 97 Sample Queries 

Available in Download Center 

MORE INFORMATION

For example, suppose you import an inventory table from an application and 

discover that the data has duplicates in it. Your goal is to get the data 

back down to a baseline, such as one record per product, so that you can 

then re-inventory and have a correct and complete set of data. You can use 

a query to filter the data.   Your table might look like this: 

   ProdID   Description   Cost   MarkUp    Quantity

   ------------------------------------------------

   1        A Product     $1.50  0.5      10

   2        B Product     $2.50  0.7      100

   3        C Product     $1.59  0.9      25

   2        D Product     $4.59  0.8      30

   5        E Product     $1.99  0.7      40

   6        F Product     $2.69  0.4      60

   9        G Product     $4.95  0.8      20

   8        H Product     $6.79  0.9      32

   9        I Product     $6.89  0.7      0

   1        J Product     $2.99  0.5      11 

If you want to filter the table down to the point where it has a unique 

ProdID code and take the first entry from each of the other fields, you 

can create a query to do this, as follows: 

1. Create a new query based on the original table. 

2. Add all the fields from the field list to the QBE grid. 

3. On the View menu, click Totals. 

4. Set the Total row of the query grid to First() for every field except 

ProdID. Set ProdID to Group By(). 

5. On the View menu, click Datasheet View (or Datasheet in MS Access 7.0 

or earlier. The data that you see should be a list of unique ProdID data 

with the first value that is encountered for that product in each of the 

other fields. If you use this procedure on the above data, your result is 

the following: 

   ProdID   Description   Cost   MarkUp   Quantity

   -----------------------------------------------

   1        A Product     $1.50  0.5      10

   2        B Product     $2.50  0.7      100

   3        C Product     $1.59  0.9      25

   5        E Product     $1.99  0.7      40

   6        F Product     $2.69  0.4      60

   8        H Product     $6.79  0.9      32

   9        G Product     $4.95  0.8      20 

To obtain different results, you can use Max(), Min(), or Last() instead 

of First(). 

To generate a unique table from this query, you can change the query into 

a make-table query in Design view of the query. 



ACC97: Microsoft Access 97 Sample Queries Available in Download Center

The Qrysmp97.exe file contains a sample database with over 20 query 

examples. These examples demonstrate how to create many types of queries, 

including select queries, crosstab queries, totals queries, and SQL pass-

through queries. Topics include using a subquery as criteria, referring to 

a field in the previous or next record, creating a union query, ranking 

records, creating a SQL pass-through query in code, grouping column 

headings in a crosstab query, and many more. 



The file is available for download from the MS Download Center. Click to 

download: Qrysmp97.exe  which contains the following files: rysmp97.mdb   

The queries sample database-Readme.txt -This info in text format. 

Hi,



Well, the answer for both the above advices is that I received the folwing 

message from Access when trying to execute the query:

"Cannot group on fields selected with '*'. I followed instructions exactly 

in both cases and the result was the same. Any suggestions??



Thanks in advance



Nick





> > Hi all,

> > 

> > I have an Access database with one table an many fields (more than 

12). 

> It contains stocks with their names prices through the last years 

> statistics  etc. As you understand, each stock's name is written many 

> times in > multiple records. All i want to do is make a query that gets 

> only the > unique values of the stock names (together with the first row 

> they find with it - any record) and return me a list of all available 

> stocks in the > stock market. As i said i want to get with this list a 

> row - any row which > contains other important data about the stock (its 

> code name - IPO date > etc). I will export it in another database as a 

new 

> table. Any advice on > the correct syntaxt for the query???

> > 

> 

> Here is the instructions to make a query to get the unique data you want.

> Create the query, then try changing it to a make-table query. Then you 

can

> export the new table. Good Luck! Carol

> 

> > ACC: How to Use a Query to Filter Unique Data

> When you want to filter a table to eliminate duplicate data, you need to 

> use a query that uses one of the aggregate (totals) functions, such as 

> First(), Last(), Min(), or Max(), in the fields that do not contain the 

> duplicate data. The fields that contain the duplicate data should 

contain 

> the GroupBy() function. 

> 

> NOTE: A demo of the technique used in this article can be seen in the 

> sample file, Qrysmp97.exe. For info about how to obtain this sample 

file, 

> see Microsoft Knowledge Base: Q182568 ACC97: MS Access 97 Sample Queries 

> Available in Download Center 

> MORE INFORMATION

> For example, suppose you import an inventory table from an application 

and 

> discover that the data has duplicates in it. Your goal is to get the 

data 

> back down to a baseline, such as one record per product, so that you can 

> then re-inventory and have a correct and complete set of data. You can 

use 

> a query to filter the data.   Your table might look like this: 

>    ProdID   Description   Cost   MarkUp    Quantity

>    ------------------------------------------------

>    1        A Product     $1.50  0.5      10

>    2        B Product     $2.50  0.7      100

>    3        C Product     $1.59  0.9      25

>    2        D Product     $4.59  0.8      30

>    5        E Product     $1.99  0.7      40

>    6        F Product     $2.69  0.4      60

>    9        G Product     $4.95  0.8      20

>    8        H Product     $6.79  0.9      32

>    9        I Product     $6.89  0.7      0

>    1        J Product     $2.99  0.5      11 

> If you want to filter the table down to the point where it has a unique 

> ProdID code and take the first entry from each of the other fields, you 

> can create a query to do this, as follows: 

> 1. Create a new query based on the original table. 

> 2. Add all the fields from the field list to the QBE grid. 

> 3. On the View menu, click Totals. 

> 4. Set the Total row of the query grid to First() for every field except 

> ProdID. Set ProdID to Group By(). 

> 5. On the View menu, click Datasheet View (or Datasheet in MS Access 7.0 

> or earlier. The data that you see should be a list of unique ProdID data 

> with the first value that is encountered for that product in each of the 

> other fields. If you use this procedure on the above data, your result 

is 

> the following: 

>    ProdID   Description   Cost   MarkUp   Quantity

>    -----------------------------------------------

>    1        A Product     $1.50  0.5      10

>    2        B Product     $2.50  0.7      100

>    3        C Product     $1.59  0.9      25

>    5        E Product     $1.99  0.7      40

>    6        F Product     $2.69  0.4      60

>    8        H Product     $6.79  0.9      32

>    9        G Product     $4.95  0.8      20 

> To obtain different results, you can use Max(), Min(), or Last() instead 

> of First(). 

> To generate a unique table from this query, you can change the query 

into 

> a make-table query in Design view of the query. 

> 

> ACC97: Microsoft Access 97 Sample Queries Available in Download Center

> The Qrysmp97.exe file contains a sample database with over 20 query 

> examples. These examples demonstrate how to create many types of 

queries, 

> including select queries, crosstab queries, totals queries, and SQL pass-

> through queries. Topics include using a subquery as criteria, referring 

to 

> a field in the previous or next record, creating a union query, ranking 

> records, creating a SQL pass-through query in code, grouping column 

> headings in a crosstab query, and many more. 

> 

> The file is available for download from the MS Download Center. Click to 

> download: Qrysmp97.exe  which contains the following files: 

rysmp97.mdb   

> The queries sample database-Readme.txt -This info in text format. 

> 

> 

>