Query based on date and location

[Guest]

Hello. I'll outline what I'm working on right now before I get into my
question.

Table - Traffic Counts

Each data entry has a location number, count, and start date

-----

Table - Counter Locations

A table of every counter location. I use this in a form to make sure that
the date enterer can only input correct counter locations.

-----

Each counter location has many counts. I want to be able to do the following:

1. A query which lists the 1-4 most current counts for every counter

2. A query which lists the 1-4 most current counts for a particular counter

3. A query which lists the 1-3 most current counts for 2 or more counters

Thank you in advance for your help.

 

[MGFoster]

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

1:
SELECT TOP 4 [start date], [count]
FROM [traffic counts]
ORDER BY [start date] DESC

2:
PARAMETERS [Enter the Location Number] LONG;
SELECT TOP 4 [start date], [count]
FROM [traffic counts]
WHERE [location number] = [Enter the Location Number]
ORDER BY [start date] DESC

3:
PARAMETERS [Enter the comma-separated Location Numbers] LONG;
SELECT TOP 3 [start date], [count]
FROM [traffic counts]
WHERE InStr("," & [Enter the comma-separated Location Numbers] &
"," , "," & [location number] & ",")>0
ORDER BY [start date] DESC

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

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

iQA/AwUBRDVH9YechKqOuFEgEQLyWACfVK7S/L6Ha0psdgCgx3Gt4TF2n2QAoOG9
bkwfeIV0DkKnifXhi6QnV+rn
=hAes
-----END PGP SIGNATURE-----

 

[Guest]

Ok, where do I enter that?

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

1:
SELECT TOP 4 [start date], [count]
FROM [traffic counts]
ORDER BY [start date] DESC

2:
PARAMETERS [Enter the Location Number] LONG;
SELECT TOP 4 [start date], [count]
FROM [traffic counts]
WHERE [location number] = [Enter the Location Number]
ORDER BY [start date] DESC

3:
PARAMETERS [Enter the comma-separated Location Numbers] LONG;
SELECT TOP 3 [start date], [count]
FROM [traffic counts]
WHERE InStr("," & [Enter the comma-separated Location Numbers] &
"," , "," & [location number] & ",")>0
ORDER BY [start date] DESC

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

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

iQA/AwUBRDVH9YechKqOuFEgEQLyWACfVK7S/L6Ha0psdgCgx3Gt4TF2n2QAoOG9
bkwfeIV0DkKnifXhi6QnV+rn
=hAes
-----END PGP SIGNATURE-----

Hello. I'll outline what I'm working on right now before I get into my
question.

Table - Traffic Counts

Each data entry has a location number, count, and start date

-----

Table - Counter Locations

A table of every counter location. I use this in a form to make sure that
the date enterer can only input correct counter locations.

-----

Each counter location has many counts. I want to be able to do the following:

1. A query which lists the 1-4 most current counts for every counter

2. A query which lists the 1-4 most current counts for a particular counter

3. A query which lists the 1-3 most current counts for 2 or more counters

Thank you in advance for your help.

 

[MGFoster]

In the SQL view of a Query object.

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

Ok, where do I enter that?

:

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

1:
SELECT TOP 4 [start date], [count]
FROM [traffic counts]
ORDER BY [start date] DESC

2:
PARAMETERS [Enter the Location Number] LONG;
SELECT TOP 4 [start date], [count]
FROM [traffic counts]
WHERE [location number] = [Enter the Location Number]
ORDER BY [start date] DESC

3:
PARAMETERS [Enter the comma-separated Location Numbers] LONG;
SELECT TOP 3 [start date], [count]
FROM [traffic counts]
WHERE InStr("," & [Enter the comma-separated Location Numbers] &
"," , "," & [location number] & ",")>0
ORDER BY [start date] DESC

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

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

iQA/AwUBRDVH9YechKqOuFEgEQLyWACfVK7S/L6Ha0psdgCgx3Gt4TF2n2QAoOG9
bkwfeIV0DkKnifXhi6QnV+rn
=hAes
-----END PGP SIGNATURE-----

Hello. I'll outline what I'm working on right now before I get into my
question.

Table - Traffic Counts

Each data entry has a location number, count, and start date

-----

Table - Counter Locations

A table of every counter location. I use this in a form to make sure that
the date enterer can only input correct counter locations.

-----

Each counter location has many counts. I want to be able to do the following:

1. A query which lists the 1-4 most current counts for every counter

2. A query which lists the 1-4 most current counts for a particular counter

3. A query which lists the 1-3 most current counts for 2 or more counters

Thank you in advance for your help.