sorting

  • Thread starter Thread starter hin
  • Start date Start date
H

hin

I have two tables, Area (Area:Number, Description:Text) and Location
(ID: Autonumber, Area:Number,Loc:Number,Desc:Text)

Area table with the following records
--------------------------------------
Area Description
1 San Jose
2 Oakland

Location table with the following records
ID Area Loc Desc
1 1 1 63th Ave
2 1 11 Main St
3 1 2 Pine St
4 2 4 Oak Ave
5 2 2 3 rd St

If a user want the parameter for the query to:
Area # 1 - 2
Location # (Not ID) 11 - 4

which give me the result in sorted order:

ID Area Loc Desc
2 1 11 Main St.
5 2 2 3 rd St.
4 2 4 Oak Ave


How can I do it?

Thanks!
H
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Probably like this:

SELECT ID, Area, Loc, Desc
FROM Location
WHERE Area IN (1, 2) AND Loc IN (4, 11)
ORDER BY Area

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQP8br4echKqOuFEgEQLKDACguwr39TCDaoWMO0agfbGgpQBN9CcAoP4R
F3m3Eg0eUB2zrGLU2hasY4tv
=v3Tv
-----END PGP SIGNATURE-----
 
Hi hin,

I believe you are dissatisfied with
way Access will sort your text fields
like text...8-)

In query design grid add 2 columns
where you change text to number
and sort by them

first column:

Field: Sort1: Val([Area])
Table: Location
Sort: Ascending
Show: <unchecked>
Criteria:
Or:

second column:

Field: Sort2: Val([Loc])
Table: Location
Sort: Ascending
Show: <unchecked>
Criteria:
Or:

your SQL would end with:

ORDER BY
Val([Location].[Area]),
Val([Location].[Loc]);

Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
please disregard above msg..sorry
Hi hin,

I believe you are dissatisfied with
way Access will sort your text fields
like text...8-)

In query design grid add 2 columns
where you change text to number
and sort by them

first column:

Field: Sort1: Val([Area])
Table: Location
Sort: Ascending
Show: <unchecked>
Criteria:
Or:

second column:

Field: Sort2: Val([Loc])
Table: Location
Sort: Ascending
Show: <unchecked>
Criteria:
Or:

your SQL would end with:

ORDER BY
Val([Location].[Area]),
Val([Location].[Loc]);

Please respond back if I have misunderstood.

Good luck,

Gary Walter


I have two tables, Area (Area:Number, Description:Text) and Location
(ID: Autonumber, Area:Number,Loc:Number,Desc:Text)

Area table with the following records
--------------------------------------
Area Description
1 San Jose
2 Oakland

Location table with the following records
ID Area Loc Desc
1 1 1 63th Ave
2 1 11 Main St
3 1 2 Pine St
4 2 4 Oak Ave
5 2 2 3 rd St

If a user want the parameter for the query to:
Area # 1 - 2
Location # (Not ID) 11 - 4

which give me the result in sorted order:

ID Area Loc Desc
2 1 11 Main St.
5 2 2 3 rd St.
4 2 4 Oak Ave


How can I do it?

Thanks!
H
Click to expand...
Click to expand...
 
Back
Top