Generate selective query results

[RoBo]

Who can give me advice?

In a table I am storing the information of apartments and their total
surface area in square meters within a building.

When I select an apartment within a building in a form, I would like to
print a report of maximum 10 equivalent apartments of approximately the same
size (surface area ± 2 m2). Of these apartments I would like to see the
apartment number and the size of the surface area.

I tried the Top 10 option but that does not give me the correct result.

Key of the table is the building number (CPX_NUMBER). The fieldname of the
apartment is VHE_NUMBER and the fieldname of the size of the surface area is
VHE_SURFACE

How do I solve this?

Thanks for helping me out, Ron (from the Dutch communities).

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It helps, if you show the SQL of the query you are running.

Here's a stab in the dark:

PARAMETERS Forms!frmApartment!vhe_surface Double,
Forms!frmApartment!cpx_number Integer;
SELECT TOP 10 vhe_number, vhe_surface
FROM Apartments
WHERE vhe_surface BETWEEN Forms!frmApartment!vhe_surface-2 And
Forms!frmApartment!vhe_surface+2
AND cpx_number = Forms!frmApartment!cpx_number
ORDER BY vhe_surface DESC

I'm assuming that you meant +/- 2 sq. meters.

Change the names of tables & forms & form controls as needed.

Access TOP includes ties (i.e., if there are 2 rows with the same
surface area both will be shown).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBR7677IechKqOuFEgEQLDLwCgsiqNOWGAnuZ+mgeB7NSZhTrtRPEAoIdD
JDV+mimtVwOHoqSKBKltyrM8
=nDnu
-----END PGP SIGNATURE-----

 

[Allen Browne]

Say the apartment you are comparing to has a surface area of 200m2. You
could get the 10 closest matches by area like this:

SELECT TOP 10 CPX_NUMBER, VHE_NUMBER, VHE_SURFACE
FROM Table1
ORDER BY Abs(VHE_SURFACE - 200), CPX_NUMBER;