Filter By Calculated Field

K

Katherine

Hi,

My latest challenge is figuring out how to filter based on a calculated
field. Can you help? I included lots of detail...

I'm working at a theatre currently. In table MailingList, I have a
field which keeps track of how many years people were season tickets
holders prior to this year. In table SeasonTickets, I keep track of
how many season tickets each subscriber purchased for the current
season.

I created a series of queries to add these things together. The first
selects a distinct season from the SeasonTicket table:

SELECT DISTINCT Season, MailingListID
FROM SeasonTickets;

Then I created a count query to count the number of Seasons per
MailingListID:

SELECT MailingListID, COUNT(MailingListID) AS SeasonCount
FROM [SelectDistinctSeasons Query]
GROUP BY MailingListID;

Then I added this result to the number of years stored in MailingList
to get a total number of years:

SELECT [UnionSeasonCount Query].MailingListID, Sum([UnionSeasonCount
Query].OldSubscriptionYears) AS [Subscriber Years]
FROM [UnionSeasonCount Query]
GROUP BY [UnionSeasonCount Query].MailingListID
HAVING ((([UnionSeasonCount Query].MailingListID)=[Forms]![Mailing List
Form]![MailingListID]));

I made a subform for this query to include on the main form, and my
fellow employees would love to be able to filter by [Subscriber Years],
but I just don't know how to make it work. Is this possible? How do I
do it?

Thanks for the help!

Katherine
 
M

Marshall Barton

Katherine said:
My latest challenge is figuring out how to filter based on a calculated
field. Can you help? I included lots of detail...

I'm working at a theatre currently. In table MailingList, I have a
field which keeps track of how many years people were season tickets
holders prior to this year. In table SeasonTickets, I keep track of
how many season tickets each subscriber purchased for the current
season.

I created a series of queries to add these things together. The first
selects a distinct season from the SeasonTicket table:

SELECT DISTINCT Season, MailingListID
FROM SeasonTickets;

Then I created a count query to count the number of Seasons per
MailingListID:

SELECT MailingListID, COUNT(MailingListID) AS SeasonCount
FROM [SelectDistinctSeasons Query]
GROUP BY MailingListID;

Then I added this result to the number of years stored in MailingList
to get a total number of years:

SELECT [UnionSeasonCount Query].MailingListID, Sum([UnionSeasonCount
Query].OldSubscriptionYears) AS [Subscriber Years]
FROM [UnionSeasonCount Query]
GROUP BY [UnionSeasonCount Query].MailingListID
HAVING ((([UnionSeasonCount Query].MailingListID)=[Forms]![Mailing List
Form]![MailingListID]));

I made a subform for this query to include on the main form, and my
fellow employees would love to be able to filter by [Subscriber Years],
but I just don't know how to make it work. Is this possible? How do I
do it?
Click to expand...


Your HAVING clause should be a WHERE clause (because its
filtering the data usied in the calculations. Then, to
filter by the total in the Sum calculation, use a HAVING
clause (because it need the result of the calculation).

SELECT MailingListID,
Sum(OldSubscriptionYears) AS [Subscriber Years]
FROM [UnionSeasonCount Query]
WHERE MailingListID)=Forms![Mailing List Form]!MailingListID
GROUP BY MailingListID
HAVING Sum(OldSubscriptionYears) = [Enter Years]
 
K

Katherine

Thanks, Marshall. I haven't tried this code out yet, and though it
seems like it will work, it's not exactly what I'm looking for. This
seems like it will create a field that will enable users to enter a
number and will filter records by that number. That will work, but it
creates an extra field on the form and may confuse my users.

What I'd like is a way to keep the fields I have but create a
calculated field (in this case, [Subscription Years]) that will be
searchable using the "Filter By Form" function that is built into
Access.

Is this possible?

Thanks,
Katherine

Marshall said:
Katherine said:
My latest challenge is figuring out how to filter based on a calculated
field. Can you help? I included lots of detail...

I'm working at a theatre currently. In table MailingList, I have a
field which keeps track of how many years people were season tickets
holders prior to this year. In table SeasonTickets, I keep track of
how many season tickets each subscriber purchased for the current
season.

I created a series of queries to add these things together. The first
selects a distinct season from the SeasonTicket table:

SELECT DISTINCT Season, MailingListID
FROM SeasonTickets;

Then I created a count query to count the number of Seasons per
MailingListID:

SELECT MailingListID, COUNT(MailingListID) AS SeasonCount
FROM [SelectDistinctSeasons Query]
GROUP BY MailingListID;

Then I added this result to the number of years stored in MailingList
to get a total number of years:

SELECT [UnionSeasonCount Query].MailingListID, Sum([UnionSeasonCount
Query].OldSubscriptionYears) AS [Subscriber Years]
FROM [UnionSeasonCount Query]
GROUP BY [UnionSeasonCount Query].MailingListID
HAVING ((([UnionSeasonCount Query].MailingListID)=[Forms]![Mailing List
Form]![MailingListID]));

I made a subform for this query to include on the main form, and my
fellow employees would love to be able to filter by [Subscriber Years],
but I just don't know how to make it work. Is this possible? How do I
do it?
Click to expand...


Your HAVING clause should be a WHERE clause (because its
filtering the data usied in the calculations. Then, to
filter by the total in the Sum calculation, use a HAVING
clause (because it need the result of the calculation).

SELECT MailingListID,
Sum(OldSubscriptionYears) AS [Subscriber Years]
FROM [UnionSeasonCount Query]
WHERE MailingListID)=Forms![Mailing List Form]!MailingListID
GROUP BY MailingListID
HAVING Sum(OldSubscriptionYears) = [Enter Years]
Click to expand...
 
M

Marshall Barton

I don't follow that. There are no new fields, I used the
same fields that you did.

If you mean the prompt string in the Having clause, that was
just an example to demonstrate how to filter on a total.
You can filter the total in many different ways.

If you mean that you do not want to limit the query's
dataset to a specific total, then dump the having clause and
use a subform and manipulate it's Filter property to display
the desired subset of calculated values.

If you mean that you want to use a criteria to select which
years to total. scrap the Having clause and add a criteria
to the Where clause. Maybe something like:

SELECT MailingListID,
Sum(OldSubscriptionYears) AS [Subscriber Years]
FROM [UnionSeasonCount Query]
WHERE MailingListID)=Forms![Mailing List Form]!MailingListID
And OldSubscriptionYears >= [Enter start year]
GROUP BY MailingListID
--
Marsh
MVP [MS Access]

Thanks, Marshall. I haven't tried this code out yet, and though it
seems like it will work, it's not exactly what I'm looking for. This
seems like it will create a field that will enable users to enter a
number and will filter records by that number. That will work, but it
creates an extra field on the form and may confuse my users.

What I'd like is a way to keep the fields I have but create a
calculated field (in this case, [Subscription Years]) that will be
searchable using the "Filter By Form" function that is built into
Access.


Marshall said:
Katherine said:
My latest challenge is figuring out how to filter based on a calculated
field. Can you help? I included lots of detail...

I'm working at a theatre currently. In table MailingList, I have a
field which keeps track of how many years people were season tickets
holders prior to this year. In table SeasonTickets, I keep track of
how many season tickets each subscriber purchased for the current
season.

I created a series of queries to add these things together. The first
selects a distinct season from the SeasonTicket table:

SELECT DISTINCT Season, MailingListID
FROM SeasonTickets;

Then I created a count query to count the number of Seasons per
MailingListID:

SELECT MailingListID, COUNT(MailingListID) AS SeasonCount
FROM [SelectDistinctSeasons Query]
GROUP BY MailingListID;

Then I added this result to the number of years stored in MailingList
to get a total number of years:

SELECT [UnionSeasonCount Query].MailingListID, Sum([UnionSeasonCount
Query].OldSubscriptionYears) AS [Subscriber Years]
FROM [UnionSeasonCount Query]
GROUP BY [UnionSeasonCount Query].MailingListID
HAVING ((([UnionSeasonCount Query].MailingListID)=[Forms]![Mailing List
Form]![MailingListID]));

I made a subform for this query to include on the main form, and my
fellow employees would love to be able to filter by [Subscriber Years],
but I just don't know how to make it work. Is this possible? How do I
do it?
Click to expand...


Your HAVING clause should be a WHERE clause (because its
filtering the data usied in the calculations. Then, to
filter by the total in the Sum calculation, use a HAVING
clause (because it need the result of the calculation).

SELECT MailingListID,
Sum(OldSubscriptionYears) AS [Subscriber Years]
FROM [UnionSeasonCount Query]
WHERE MailingListID)=Forms![Mailing List Form]!MailingListID
GROUP BY MailingListID
HAVING Sum(OldSubscriptionYears) = [Enter Years]
Click to expand...
Click to expand...
 
K

Katherine

Sorry if I was unclear. Hopefully this is better...

Right now the field [Subscriber Years] appears on a mailing list form
all the time. It calclates the years for each person as you scroll
through the database. This mailing list form also automatically loads
when you open our database because (right now anyway) it's the only
thing important to our users stored in this database.

Rather than have to enter a number of years to filter by every time the
mailing list form is opened (which is what happened when I tried out
the query you suggested), I'd like the field [Subscriber Years] to be
usable when using the built in "Filter By Form" feature in Access.
This way, when someone is scrolling through the mailing list entries,
[Subscriber Years] is populated with a calculated result for each
entry. Then when a user wants to filter for everyone who has been a
subscriber for 10 years, they can select "Filter By Form", type 10 into
the [Subscriber Years] field, and restrict the mailing list entries to
people who meet that criteria. When the filter is removed, the field
would go back to showing the result for each entry as you scroll
through.

Does this make more sense?

Thanks,
Katherine

Marshall said:
I don't follow that. There are no new fields, I used the
same fields that you did.

If you mean the prompt string in the Having clause, that was
just an example to demonstrate how to filter on a total.
You can filter the total in many different ways.

If you mean that you do not want to limit the query's
dataset to a specific total, then dump the having clause and
use a subform and manipulate it's Filter property to display
the desired subset of calculated values.

If you mean that you want to use a criteria to select which
years to total. scrap the Having clause and add a criteria
to the Where clause. Maybe something like:

SELECT MailingListID,
Sum(OldSubscriptionYears) AS [Subscriber Years]
FROM [UnionSeasonCount Query]
WHERE MailingListID)=Forms![Mailing List Form]!MailingListID
And OldSubscriptionYears >= [Enter start year]
GROUP BY MailingListID
--
Marsh
MVP [MS Access]

Thanks, Marshall. I haven't tried this code out yet, and though it
seems like it will work, it's not exactly what I'm looking for. This
seems like it will create a field that will enable users to enter a
number and will filter records by that number. That will work, but it
creates an extra field on the form and may confuse my users.

What I'd like is a way to keep the fields I have but create a
calculated field (in this case, [Subscription Years]) that will be
searchable using the "Filter By Form" function that is built into
Access.


Marshall said:
Katherine wrote:
My latest challenge is figuring out how to filter based on a calculated
field. Can you help? I included lots of detail...

I'm working at a theatre currently. In table MailingList, I have a
field which keeps track of how many years people were season tickets
holders prior to this year. In table SeasonTickets, I keep track of
how many season tickets each subscriber purchased for the current
season.

I created a series of queries to add these things together. The first
selects a distinct season from the SeasonTicket table:

SELECT DISTINCT Season, MailingListID
FROM SeasonTickets;

Then I created a count query to count the number of Seasons per
MailingListID:

SELECT MailingListID, COUNT(MailingListID) AS SeasonCount
FROM [SelectDistinctSeasons Query]
GROUP BY MailingListID;

Then I added this result to the number of years stored in MailingList
to get a total number of years:

SELECT [UnionSeasonCount Query].MailingListID, Sum([UnionSeasonCount
Query].OldSubscriptionYears) AS [Subscriber Years]
FROM [UnionSeasonCount Query]
GROUP BY [UnionSeasonCount Query].MailingListID
HAVING ((([UnionSeasonCount Query].MailingListID)=[Forms]![Mailing List
Form]![MailingListID]));

I made a subform for this query to include on the main form, and my
fellow employees would love to be able to filter by [Subscriber Years],
but I just don't know how to make it work. Is this possible? How do I
do it?


Your HAVING clause should be a WHERE clause (because its
filtering the data usied in the calculations. Then, to
filter by the total in the Sum calculation, use a HAVING
clause (because it need the result of the calculation).

SELECT MailingListID,
Sum(OldSubscriptionYears) AS [Subscriber Years]
FROM [UnionSeasonCount Query]
WHERE MailingListID)=Forms![Mailing List Form]!MailingListID
GROUP BY MailingListID
HAVING Sum(OldSubscriptionYears) = [Enter Years]
Click to expand...
Click to expand...
Click to expand...
 
M

Marshall Barton

Katherine said:
Right now the field [Subscriber Years] appears on a mailing list form
all the time. It calclates the years for each person as you scroll
through the database. This mailing list form also automatically loads
when you open our database because (right now anyway) it's the only
thing important to our users stored in this database.

Rather than have to enter a number of years to filter by every time the
mailing list form is opened (which is what happened when I tried out
the query you suggested), I'd like the field [Subscriber Years] to be
usable when using the built in "Filter By Form" feature in Access.
This way, when someone is scrolling through the mailing list entries,
[Subscriber Years] is populated with a calculated result for each
entry. Then when a user wants to filter for everyone who has been a
subscriber for 10 years, they can select "Filter By Form", type 10 into
the [Subscriber Years] field, and restrict the mailing list entries to
people who meet that criteria. When the filter is removed, the field
would go back to showing the result for each entry as you scroll
through.
Click to expand...


I think setting the form's RecordSource to this will do
that?

SELECT T.*,
(SELECT Sum(OldSubscriptionYears)
FROM [UnionSeasonCount Query] AS X
WHERE X.MailingListID = T.MailingListID
) AS [Subscriber Years]
FROM [UnionSeasonCount Query] AS T
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

ANy ideas 3
Urgent help with linking tables via "save" command 2
Urgent help with linking tables via "save" command 5
Query Calculation Doesn't Get Put Into Table 3
Select query criteria 3
finding which date range a date is in? 1
Calculate seasons from a date field 3
Query Data Cannot Be Updated? 4

Top