query question

[c tom]

id name location
1 tom new york
2 jeny sicago
3 tom new york
4 luca sidney
5 luca sidney

i want to make below table using query.

id name location
1 tom new york
2 jeny sicago
3 luca sidney

please show query statement.

*** Sent via Developersdex http://www.developersdex.com ***

[Smartin]

Re: query question

c tom wrote:[color=blue]
> id name location
> 1 tom new york
> 2 jeny sicago
> 3 tom new york
> 4 luca sidney
> 5 luca sidney
>
> i want to make below table using query.
>
> id name location
> 1 tom new york
> 2 jeny sicago
> 3 luca sidney
>
> please show query statement.
>
> *** Sent via Developersdex http://www.developersdex.com ***[/color]

Check out DISTINCT and DISTINCTROW keywords?

--
Smartin

[Larry Linson]

Re: query question

I am not sure just what you expect for "id". Access records do not have a
Record Number as do some databases. Often we use an AutoNumber as a unique
identified (aka Surrogate Key).

I will describe this as you should do it in Access, in the Query Builder:

1. Open the Query Builder and select your Table as a data source,
2. pull down Name and Location into the Grid,
3. then right click in the upper part, and choose Properties, then select
Unique Values --
4. run the query to assure that you are getting the output you want.
5. You may find that the records aren't in the order you show... go back to
query design and enter Ascending in the Sort Line under the Location
Field --
6. run again to check the output.
7. Now back to Query Design
8. On the menu, under Query, select Make Table
9. Run
10. Supply your new table name in the dialog box
11. Allow it to create the table
12. Open the new table in design view
13. Put the cursor on the first line (the Name field) and press the Insert
key
14. Enter the field name ID and select AutoNumber.
15. Click the Table View icon
16. Allow it to Save

That may seem a lot of steps, but each is trivially simple, so it isn't a
lot of work.

One tip: In the future, describe what you want to do, in some detail, as
well as giving an example, so whoever answers doesn't have to guess (for
example, I had to guess from your example's order that you wanted the
records in location order... when in fact, tables are by definition,
unordered, and you need to sort in the query that extracts the data as you
use it; I also had to guess that the ID was simply intended to be a unique
ID for the record.

Another: Don't use "Name" as a Field Name, because it is an Access reserved
word and can cause you confusion later on. Access knows exactly how those
reserved words should be used in all circumstances, but that may not be what
we expect. And, Access proper use may result in an error message that we
don't expect.

Just for the record, though, the SQL statement for the MakeTable Query, in
my example is:

SELECT DISTINCT tblPeopleLocati on.TheName, tblPeopleLocati on.Location INTO
tblUniquePeople Location
FROM tblPeopleLocati on
ORDER BY tblPeopleLocati on.Location;

Larry Linson
Microsoft Access MVP

"c tom" <tomi3440@yahoo .com> wrote in message
news:eUv8f.51$g p6.4231@news.us west.net...[color=blue]
> id name location
> 1 tom new york
> 2 jeny sicago
> 3 tom new york
> 4 luca sidney
> 5 luca sidney
>
> i want to make below table using query.
>
> id name location
> 1 tom new york
> 2 jeny sicago
> 3 luca sidney
>
> please show query statement.
>
> *** Sent via Developersdex http://www.developersdex.com ***[/color]

[Chris2]

Re: query question


"c tom" <tomi3440@yahoo .com> wrote in message
news:eUv8f.51$g p6.4231@news.us west.net...[color=blue]
> id name location
> 1 tom new york
> 2 jeny sicago
> 3 tom new york
> 4 luca sidney
> 5 luca sidney
>
> i want to make below table using query.
>
> id name location
> 1 tom new york
> 2 jeny sicago
> 3 luca sidney
>
> please show query statement.
>
> *** Sent via Developersdex http://www.developersdex.com ***[/color]

c tom,

Table:

CREATE TABLE Unknown_1029200 5_1
(id INTEGER
,name TEXT(10)
,location TEXT(10)
,CONSTRAINT pk_Unknown_1029 2005_1 PRIMARY KEY (id)
)


Sample Data:

As above.


Query:

SELECT (SELECT COUNT(U02.id)
FROM (SELECT MIN(U01.id) as id
,U01.name
,U01.location
FROM Unknown_1029200 5_1 as U01
GROUP BY U01.name
,U01.location
ORDER BY MIN(U01.id)) AS U02
WHERE U02.id <= U1.id) as id
,U1.name
,U1.location
INTO Unknown_1029200 5_2
FROM (SELECT MIN(U01.id) as id
,U01.name
,U01.location
FROM Unknown_1029200 5_1 as U01
GROUP BY U01.name
,U01.location
ORDER BY MIN(U01.id)) AS U1

Output:

id name location
1 tom new york
2 jeny sicago
3 luca sidney


Sincerely,

Chris O.