date range query

[glover.brian]

Hi

My job currently requires me to enter data from 300+ forms a month.

The system which we used in Excel was slow , and theprevious guy had
three workbooks for this job .

I have created a table in Access with four fields

Date Location Number of Adult Visitors Number of
Chikd Visitors


I have created queries to tell me how many occurances of Location and
total and average number of visitors there are in the table and this
works fine for reporting purposes.

We keep data on a month by month basis

It has been decided that we will keep all data in a single database
from now on.

What I need to do is rewrite the queries so that I can query a date
range rather then the entire table.

How do I do this?

brian

 

[MH]

SELECT *
FROM MyTable
WHERE MyDateField BETWEEN #01/01/2007# AND #31/01/2007#

You seem to be using a column name of "Date" which is a reserved word, I
would change it to something more meaningful, e.g. "DateOfVisit" to prevent
problems later on.

Also, if you are coming from an Excel background, it may be worth pointing
out that your "Location" field may well be a text field but you have a
finite number of locations. If this is the case, you may want to create a
seperate table called "tblLocations" to store a PRIMARY KEY and the location
name. Then you can create a relationship between this table and the one you
have. This will enforce REFERENTIAL INTEGRITY between the two tables so you
don't get things like "Location1" and "Location 1" (notice the space).

MH

 

[flumpuk]

SELECT *
FROM MyTable
WHERE MyDateField BETWEEN #01/01/2007# AND #31/01/2007#

You seem to be using a column name of "Date" which is a reserved word, I
would change it to something more meaningful, e.g. "DateOfVisit" to prevent
problems later on.

Also, if you are coming from an Excel background, it may be worth pointing
out that your "Location" field may well be a text field but you have a
finite number of locations. If this is the case, you may want to create a
seperate table called "tblLocations" to store a PRIMARY KEY and the location
name. Then you can create a relationship between this table and the one you
have. This will enforce REFERENTIAL INTEGRITY between the two tables so you
don't get things like "Location1" and "Location 1" (notice the space).

MH
















- Show quoted text -

I have changed to Dateofvisit and also set up a table of locations

i am still confused. my queries work for the whole table. I just cant
get the criteria statement correct. Am i right in assuming i put the
criteria in the same query as my current query .

 

[Rick Brandt]

On May 13, 4:04 pm, "MH" <[email protected]> wrote:

I have changed to Dateofvisit and also set up a table of locations

i am still confused. my queries work for the whole table. I just cant
get the criteria statement correct. Am i right in assuming i put the
criteria in the same query as my current query .

Yep. Post the SQL of your query.

 

[flumpuk]

Yep. Post the SQL of your query.

I have sorted it thanks everyone.

My next question is this.

When I run the query , i get two popups asking for start and end date.
what i would like is a form that I can type in these dates and click a
button and away we go. however , I have creted the form , But when I
click the button , the report still asks for a start and end date.

 

[MH]

SELECT *
FROM MyTable
WHERE MyDateField BETWEEN [MyFormName]![StartDate] AND
[MyFormName]![EndDate]

MH

 

[flumpuk]

SELECT *
FROM MyTable
WHERE MyDateField BETWEEN [MyFormName]![StartDate] AND
[MyFormName]![EndDate]

MH

I have sorted it thanks everyone.

My next question is this.

When I run the query , i get two popups asking for start and end date.
what i would like is a form that I can type in these dates and click a
button and away we go. however , I have creted the form , But when I
click the button , the report still asks for a start and end date.- Hide quoted text -

- Show quoted text -

Excellant. One lst question. How di iget the report to print the date
range at the top?

 

[MH]

Add a text-box to the page header (or report header) and set it's control
source property to:

"Date Range " & [MyFormName]![StartDate] & " to " & [MyFormName]![EndDate]

If you want to format the dates in some way, use the format function:

Format([MyFormName]![StartDate], "dd/mm/yyyy")

MH

SELECT *
FROM MyTable
WHERE MyDateField BETWEEN [MyFormName]![StartDate] AND
[MyFormName]![EndDate]

MH

flumpuk wrote:
I have changed to Dateofvisit and also set up a table of locations

i am still confused. my queries work for the whole table. I just
cant
get the criteria statement correct. Am i right in assuming i put the
criteria in the same query as my current query .

Yep. Post the SQL of your query.

I have sorted it thanks everyone.

My next question is this.

When I run the query , i get two popups asking for start and end date.
what i would like is a form that I can type in these dates and click a
button and away we go. however , I have creted the form , But when I
click the button , the report still asks for a start and end date.-
Hide quoted text -

- Show quoted text -

Excellant. One lst question. How di iget the report to print the date
range at the top?