Summary Queries.

  • Thread starter Thread starter Tammy
  • Start date Start date
T

Tammy

I am trying to summarize some information in a tracking
database. What I would like to to summarize those events
that occur only within a user defined year. Problem I am
having is that if one event occurs twice on the same date,
it is only counted as one instead of 2. What do I need to
do to eliminate this problem so I can correctly graph my
information?
 
You can probably get what you want with the following 2
queries.

1) Create a new query. Switch from design view to sql
view. Paste this in:

SELECT Table.event, Table.dater, Count(Table.date) AS
CountOfdate
FROM Table
WHERE (((Table.date) Between [Enter Start] And [Enter
End]))
GROUP BY Table.event, Table.date;

Change "Table" to your table name. Change "event"
and "date" to your field names. I'm not sure how
comfortable you are with access, so if I'm being too
basic, I apologize. The query will accept parameters for
your start and end date ranges. Once you have changed the
table and field names, you can switch your query back to
design view.

2) Create a second query. Use your first query as the
data source. Select the event field and the counted
field. Change to a group by query. Change the group by
to count for the count field.

This should give you a count of the events in the time
period that was entered.

Hope it helps.
 
Not sure if I explained myself correctly. This tracks a
variety of types of one event. I am trying to summarize
the count of this event for each type. Problem is if the
type of event happens twice on the same day, I am foo-
barred. I am going to try what you say, using type
instead of event and see if it works. And by the by, I am
relatively new, so you are writing just what I need to
see..

Tammy
-----Original Message-----
You can probably get what you want with the following 2
queries.

1) Create a new query. Switch from design view to sql
view. Paste this in:

SELECT Table.event, Table.dater, Count(Table.date) AS
CountOfdate
FROM Table
WHERE (((Table.date) Between [Enter Start] And [Enter
End]))
GROUP BY Table.event, Table.date;

Change "Table" to your table name. Change "event"
and "date" to your field names. I'm not sure how
comfortable you are with access, so if I'm being too
basic, I apologize. The query will accept parameters for
your start and end date ranges. Once you have changed the
table and field names, you can switch your query back to
design view.

2) Create a second query. Use your first query as the
data source. Select the event field and the counted
field. Change to a group by query. Change the group by
to count for the count field.

This should give you a count of the events in the time
period that was entered.

Hope it helps.
-----Original Message-----
I am trying to summarize some information in a tracking
database. What I would like to to summarize those events
that occur only within a user defined year. Problem I am
having is that if one event occurs twice on the same date,
it is only counted as one instead of 2. What do I need to
do to eliminate this problem so I can correctly graph my
information?
.
Click to expand...
.
Click to expand...
 
Now it is asking for the date range twice??? What I did
was what you said, but for the second step I used the
Query Wizard. I do not know how to convert it to a
summary query "group by" query without it???

Thanks though, other than that it works perfectly!!!!!!!!!

Tammy
-----Original Message-----
You can probably get what you want with the following 2
queries.

1) Create a new query. Switch from design view to sql
view. Paste this in:

SELECT Table.event, Table.dater, Count(Table.date) AS
CountOfdate
FROM Table
WHERE (((Table.date) Between [Enter Start] And [Enter
End]))
GROUP BY Table.event, Table.date;

Change "Table" to your table name. Change "event"
and "date" to your field names. I'm not sure how
comfortable you are with access, so if I'm being too
basic, I apologize. The query will accept parameters for
your start and end date ranges. Once you have changed the
table and field names, you can switch your query back to
design view.

2) Create a second query. Use your first query as the
data source. Select the event field and the counted
field. Change to a group by query. Change the group by
to count for the count field.

This should give you a count of the events in the time
period that was entered.

Hope it helps.
-----Original Message-----
I am trying to summarize some information in a tracking
database. What I would like to to summarize those events
that occur only within a user defined year. Problem I am
having is that if one event occurs twice on the same date,
it is only counted as one instead of 2. What do I need to
do to eliminate this problem so I can correctly graph my
information?
.
Click to expand...
.
Click to expand...
 
When I try to make a report/chart using the query, how do
I get it to display the date range in the title?

Thanks
-----Original Message-----
Now it is asking for the date range twice??? What I did
was what you said, but for the second step I used the
Query Wizard. I do not know how to convert it to a
summary query "group by" query without it???

Thanks though, other than that it works perfectly!!!!!!!!!

Tammy
-----Original Message-----
You can probably get what you want with the following 2
queries.

1) Create a new query. Switch from design view to sql
view. Paste this in:

SELECT Table.event, Table.dater, Count(Table.date) AS
CountOfdate
FROM Table
WHERE (((Table.date) Between [Enter Start] And [Enter
End]))
GROUP BY Table.event, Table.date;

Change "Table" to your table name. Change "event"
and "date" to your field names. I'm not sure how
comfortable you are with access, so if I'm being too
basic, I apologize. The query will accept parameters for
your start and end date ranges. Once you have changed the
table and field names, you can switch your query back to
design view.

2) Create a second query. Use your first query as the
data source. Select the event field and the counted
field. Change to a group by query. Change the group by
to count for the count field.

This should give you a count of the events in the time
period that was entered.

Hope it helps.
-----Original Message-----
I am trying to summarize some information in a tracking
database. What I would like to to summarize those events
that occur only within a user defined year. Problem I am
having is that if one event occurs twice on the same date,
it is only counted as one instead of 2. What do I need to
do to eliminate this problem so I can correctly graph my
information?
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Is it asking for the date range for each query? You only
need to run the 2nd query. It will run the first one
automatically.

-----Original Message-----
Now it is asking for the date range twice??? What I did
was what you said, but for the second step I used the
Query Wizard. I do not know how to convert it to a
summary query "group by" query without it???

Thanks though, other than that it works perfectly!!!!!!!!!

Tammy
-----Original Message-----
You can probably get what you want with the following 2
queries.

1) Create a new query. Switch from design view to sql
view. Paste this in:

SELECT Table.event, Table.dater, Count(Table.date) AS
CountOfdate
FROM Table
WHERE (((Table.date) Between [Enter Start] And [Enter
End]))
GROUP BY Table.event, Table.date;

Change "Table" to your table name. Change "event"
and "date" to your field names. I'm not sure how
comfortable you are with access, so if I'm being too
basic, I apologize. The query will accept parameters for
your start and end date ranges. Once you have changed the
table and field names, you can switch your query back to
design view.

2) Create a second query. Use your first query as the
data source. Select the event field and the counted
field. Change to a group by query. Change the group by
to count for the count field.

This should give you a count of the events in the time
period that was entered.

Hope it helps.
-----Original Message-----
I am trying to summarize some information in a tracking
database. What I would like to to summarize those events
that occur only within a user defined year. Problem I am
having is that if one event occurs twice on the same date,
it is only counted as one instead of 2. What do I need to
do to eliminate this problem so I can correctly graph my
information?
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Okay,
You can go back to the first query and create 2 new
fields from your selected date range. Type something like
this to create your new fields:

Startdt:[Enter Start]

Enddt:[Enter End]

This should create Startdt and Enddt in your query
results.

Then, open your 2nd query in design view, and select the
date fields. They should be available for your report.
-----Original Message-----
When I try to make a report/chart using the query, how do
I get it to display the date range in the title?

Thanks
-----Original Message-----
Now it is asking for the date range twice??? What I did
was what you said, but for the second step I used the
Query Wizard. I do not know how to convert it to a
summary query "group by" query without it???

Thanks though, other than that it works perfectly!!!!!!!!!

Tammy
-----Original Message-----
You can probably get what you want with the following 2
queries.

1) Create a new query. Switch from design view to sql
view. Paste this in:

SELECT Table.event, Table.dater, Count(Table.date) AS
CountOfdate
FROM Table
WHERE (((Table.date) Between [Enter Start] And [Enter
End]))
GROUP BY Table.event, Table.date;

Change "Table" to your table name. Change "event"
and "date" to your field names. I'm not sure how
comfortable you are with access, so if I'm being too
basic, I apologize. The query will accept parameters for
your start and end date ranges. Once you have changed the
table and field names, you can switch your query back to
design view.

2) Create a second query. Use your first query as the
data source. Select the event field and the counted
field. Change to a group by query. Change the group by
to count for the count field.

This should give you a count of the events in the time
period that was entered.

Hope it helps.

-----Original Message-----
I am trying to summarize some information in a tracking
database. What I would like to to summarize those events
that occur only within a user defined year. Problem I am
having is that if one event occurs twice on the same
date,
it is only counted as one instead of 2. What do I need
to
do to eliminate this problem so I can correctly graph my
information?
.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
If type is a subcategory of event, you should have event
as your first field, then add type as your next field.

I don't know your data, but want to ensure that you don't
have the same "types" associated with different "events",
otherwise, you could get incorrect results.
-----Original Message-----
Not sure if I explained myself correctly. This tracks a
variety of types of one event. I am trying to summarize
the count of this event for each type. Problem is if the
type of event happens twice on the same day, I am foo-
barred. I am going to try what you say, using type
instead of event and see if it works. And by the by, I am
relatively new, so you are writing just what I need to
see..

Tammy
-----Original Message-----
You can probably get what you want with the following 2
queries.

1) Create a new query. Switch from design view to sql
view. Paste this in:

SELECT Table.event, Table.dater, Count(Table.date) AS
CountOfdate
FROM Table
WHERE (((Table.date) Between [Enter Start] And [Enter
End]))
GROUP BY Table.event, Table.date;

Change "Table" to your table name. Change "event"
and "date" to your field names. I'm not sure how
comfortable you are with access, so if I'm being too
basic, I apologize. The query will accept parameters for
your start and end date ranges. Once you have changed the
table and field names, you can switch your query back to
design view.

2) Create a second query. Use your first query as the
data source. Select the event field and the counted
field. Change to a group by query. Change the group by
to count for the count field.

This should give you a count of the events in the time
period that was entered.

Hope it helps.
-----Original Message-----
I am trying to summarize some information in a tracking
database. What I would like to to summarize those events
that occur only within a user defined year. Problem I am
having is that if one event occurs twice on the same date,
it is only counted as one instead of 2. What do I need to
do to eliminate this problem so I can correctly graph my
information?
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top