query with input for criteria?

[zSplash]

I'm trying to create a form that summarizes database data. The form should
show a count of records having these criteria:
1. DateEvent1 >#01/01/05# And <#12/31/05#
2. Event1Who="Peter"

I think I want to "get" the range of DateEvent1 and the value of Event1Who
with on-open-event input-boxes, but I can't access the VBA editor of the
query, to find the open-event of the query. How do I access the VBA editor?
Or, is there a better design?

I have 12 separate events (DateEvent2/Event2Who, usw). Can I create a
single query to use?

TIA

 

[Al Campagna]

z,
Queries are just SQL statements. There are no events or coding... it just delivers a
recordset according to a criteria.
Using your first example, you could create a parameter query to ask the user for those
values when the query runs.
In the DateEvent1 column use this criteria...
Between [Enter Start Date] AND [Enter End Date]
In the Event1Who...
= [Enter Name]
These are examples of "parameters". Because Access doews not know what these values
are... it will ask you what they should be.
When the query runs the user will be prompted to enter a [Enter Start Date]... then an
[Enter End Date]... then an [Enter Name].
The query will use those inputted values as the criteria.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[zSplash]

Thanks, Al. Excellent explanation.

Now, suppose I want to run a report for several "Who" for the 12 events (all
the same date criteria). How do I put the parameter information into a
query that I can add to a report? That is, I want to show Peter, Amy, and
Al's counts for the 12 events. I don't want to have to enter "Peter" 12
times, then "Amy" every 12 events, then "Al" every 12 events, but want to be
able to just pull up the report with the counts for the 12 events for Peter,
Amy, and Al. How do I add that to the query, or do I need 12x3 queries?

TIA

z,
Queries are just SQL statements. There are no events or coding... it just delivers a
recordset according to a criteria.
Using your first example, you could create a parameter query to ask the user for those
values when the query runs.
In the DateEvent1 column use this criteria...
Between [Enter Start Date] AND [Enter End Date]
In the Event1Who...
= [Enter Name]
These are examples of "parameters". Because Access doews not know what these values
are... it will ask you what they should be.
When the query runs the user will be prompted to enter a [Enter Start Date]... then an
[Enter End Date]... then an [Enter Name].
The query will use those inputted values as the criteria.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I'm trying to create a form that summarizes database data. The form should show a count
of records having these criteria:
1. DateEvent1 >#01/01/05# And <#12/31/05#
2. Event1Who="Peter"

I think I want to "get" the range of DateEvent1 and the value of Event1Who with
on-open-event input-boxes, but I can't access the VBA editor of the query, to find the
open-event of the query. How do I access the VBA editor? Or, is there a better design?

I have 12 separate events (DateEvent2/Event2Who, usw). Can I create a single query to
use?

TIA

 

[Al Campagna]

zSplash,
For multiple criteria, it would be best to create a dialog form to feed the report
query the varying values.
A trick I use is to create a field in your Names table called SelectMe (T/F checkbox)
On the dialog form I place a subform based on the names table, and the SelectMe
checkbox.
Users can select/deselect names as required.
The report query would contain the Names table with a SelectMe criteria of = True.
That would deliver all the names you have checked on the dialog form.

I'm not sure, but it sounds like you may need (or want to) do the same for your Events
table, and include that on the dialog form too. You make that call according to how the
Events table is setup... but that's the basic principle.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Thanks, Al. Excellent explanation.

Now, suppose I want to run a report for several "Who" for the 12 events (all
the same date criteria). How do I put the parameter information into a
query that I can add to a report? That is, I want to show Peter, Amy, and
Al's counts for the 12 events. I don't want to have to enter "Peter" 12
times, then "Amy" every 12 events, then "Al" every 12 events, but want to be
able to just pull up the report with the counts for the 12 events for Peter,
Amy, and Al. How do I add that to the query, or do I need 12x3 queries?

TIA

z,
Queries are just SQL statements. There are no events or coding... it just delivers a
recordset according to a criteria.
Using your first example, you could create a parameter query to ask the user for those
values when the query runs.
In the DateEvent1 column use this criteria...
Between [Enter Start Date] AND [Enter End Date]
In the Event1Who...
= [Enter Name]
These are examples of "parameters". Because Access doews not know what these values
are... it will ask you what they should be.
When the query runs the user will be prompted to enter a [Enter Start Date]... then an
[Enter End Date]... then an [Enter Name].
The query will use those inputted values as the criteria.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I'm trying to create a form that summarizes database data. The form should show a count
of records having these criteria:
1. DateEvent1 >#01/01/05# And <#12/31/05#
2. Event1Who="Peter"

I think I want to "get" the range of DateEvent1 and the value of Event1Who with
on-open-event input-boxes, but I can't access the VBA editor of the query, to find the
open-event of the query. How do I access the VBA editor? Or, is there a better design?

I have 12 separate events (DateEvent2/Event2Who, usw). Can I create a single query to
use?

TIA

 

[zSplash]

Thanks, Al. I'll try to "make it work" -- somehow! I appreciate your help.

zSplash,
For multiple criteria, it would be best to create a dialog form to feed
the report query the varying values.
A trick I use is to create a field in your Names table called SelectMe
(T/F checkbox)
On the dialog form I place a subform based on the names table, and the
SelectMe checkbox.
Users can select/deselect names as required.
The report query would contain the Names table with a SelectMe criteria
of = True. That would deliver all the names you have checked on the dialog
form.

I'm not sure, but it sounds like you may need (or want to) do the same
for your Events table, and include that on the dialog form too. You make
that call according to how the Events table is setup... but that's the
basic principle.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Thanks, Al. Excellent explanation.

Now, suppose I want to run a report for several "Who" for the 12 events
(all
the same date criteria). How do I put the parameter information into a
query that I can add to a report? That is, I want to show Peter, Amy,
and
Al's counts for the 12 events. I don't want to have to enter "Peter" 12
times, then "Amy" every 12 events, then "Al" every 12 events, but want to
be
able to just pull up the report with the counts for the 12 events for
Peter,
Amy, and Al. How do I add that to the query, or do I need 12x3 queries?

TIA

z,
Queries are just SQL statements. There are no events or coding... it just delivers a
recordset according to a criteria.
Using your first example, you could create a parameter query to ask
the user for those
values when the query runs.
In the DateEvent1 column use this criteria...
Between [Enter Start Date] AND [Enter End Date]
In the Event1Who...
= [Enter Name]
These are examples of "parameters". Because Access doews not know
what these values
are... it will ask you what they should be.
When the query runs the user will be prompted to enter a [Enter Start Date]... then an
[Enter End Date]... then an [Enter Name].
The query will use those inputted values as the criteria.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."


I'm trying to create a form that summarizes database data. The form should show a count
of records having these criteria:
1. DateEvent1 >#01/01/05# And <#12/31/05#
2. Event1Who="Peter"

I think I want to "get" the range of DateEvent1 and the value of Event1Who with
on-open-event input-boxes, but I can't access the VBA editor of the query, to find the
open-event of the query. How do I access the VBA editor? Or, is there
a better design?

I have 12 separate events (DateEvent2/Event2Who, usw). Can I create a single query to
use?

TIA