More help with Paramter Query with Mathamatical Expressions

P

prismlight

Hello,
I was hoping someone could help a newbie.
I have a table named FSAClients with Feilds such as EthnicGroup, StartMonth,
StartYear,EndMonth,EndYear. Im trying to get my query results to calculate
the sums of both the Total and Percent Columns ( sum of Total column will
always vary but the Percent column should always appear as 100%) Also, how
would the query appear if I wanted Each EthnicGroups to appear in the results
even if the total was 0?Here is my query and the results thus far (Thank to
John V. and Bill).
(Thanks in advance)

Query:

PARAMETERS [Begin Date] DateTime, [End Date] DateTime;
SELECT FSAClients.EthnicGroup, Count(*) AS Total, Count(*)/(SELECT Count(*)
FROM FSAClients WHERE DateSerial([StartYear], [StartMonth], 1) <= [End Date]
And (DateSerial([EndYear], [EndMonth]+1, 1) - 1) >= [Begin Date]) AS
PercentByGroup
FROM FSAClients
WHERE (((DateSerial([StartYear],[StartMonth],1))<=[End Date]) AND
((DateSerial([EndYear],[EndMonth]+1,1)-1)>=[Begin Date]))
GROUP BY FSAClients.EthnicGroup;

Query Results:

EthnicGroup Total PercentByGroup
Asian American 1 10.00%
Caucasian 5 50.00%
Latino 3 30.00%
Pacific Island 1 10.00%
 
D

Duane Hookom

To get all ethnic groups to appear, you could join your current query with a
table of all ethnic groups. Your PercentByGroup can more easily be
calculated in a report.
 
P

prismlight

HI Duane and eveyeone,
Is this the only solution? For some reason I thought I could adjust my
current query to show all EthnicGroups in results and calculate directly in
the query?
Thanks,
denise

Duane Hookom said:
To get all ethnic groups to appear, you could join your current query with a
table of all ethnic groups. Your PercentByGroup can more easily be
calculated in a report.

--
Duane Hookom
MS Access MVP


prismlight said:
Hello,
I was hoping someone could help a newbie.
I have a table named FSAClients with Feilds such as EthnicGroup,
StartMonth,
StartYear,EndMonth,EndYear. Im trying to get my query results to calculate
the sums of both the Total and Percent Columns ( sum of Total column will
always vary but the Percent column should always appear as 100%) Also,
how
would the query appear if I wanted Each EthnicGroups to appear in the
results
even if the total was 0?Here is my query and the results thus far (Thank
to
John V. and Bill).
(Thanks in advance)

Query:

PARAMETERS [Begin Date] DateTime, [End Date] DateTime;
SELECT FSAClients.EthnicGroup, Count(*) AS Total, Count(*)/(SELECT
Count(*)
FROM FSAClients WHERE DateSerial([StartYear], [StartMonth], 1) <= [End
Date]
And (DateSerial([EndYear], [EndMonth]+1, 1) - 1) >= [Begin Date]) AS
PercentByGroup
FROM FSAClients
WHERE (((DateSerial([StartYear],[StartMonth],1))<=[End Date]) AND
((DateSerial([EndYear],[EndMonth]+1,1)-1)>=[Begin Date]))
GROUP BY FSAClients.EthnicGroup;

Query Results:

EthnicGroup Total PercentByGroup
Asian American 1 10.00%
Caucasian 5 50.00%
Latino 3 30.00%
Pacific Island 1 10.00%
Click to expand...
Click to expand...
 
D

Duane Hookom

You only get all EthnicGroups to appear if you have a table of all
EthnicGroups included in the query with a join that includes all records in
the all EthnicGroups field.

--
Duane Hookom
MS Access MVP


prismlight said:
HI Duane and eveyeone,
Is this the only solution? For some reason I thought I could adjust my
current query to show all EthnicGroups in results and calculate directly
in
the query?
Thanks,
denise

Duane Hookom said:
To get all ethnic groups to appear, you could join your current query
with a
table of all ethnic groups. Your PercentByGroup can more easily be
calculated in a report.

--
Duane Hookom
MS Access MVP


prismlight said:
Hello,
I was hoping someone could help a newbie.
I have a table named FSAClients with Feilds such as EthnicGroup,
StartMonth,
StartYear,EndMonth,EndYear. Im trying to get my query results to
calculate
the sums of both the Total and Percent Columns ( sum of Total column
will
always vary but the Percent column should always appear as 100%) Also,
how
would the query appear if I wanted Each EthnicGroups to appear in the
results
even if the total was 0?Here is my query and the results thus far
(Thank
to
John V. and Bill).
(Thanks in advance)

Query:

PARAMETERS [Begin Date] DateTime, [End Date] DateTime;
SELECT FSAClients.EthnicGroup, Count(*) AS Total, Count(*)/(SELECT
Count(*)
FROM FSAClients WHERE DateSerial([StartYear], [StartMonth], 1) <= [End
Date]
And (DateSerial([EndYear], [EndMonth]+1, 1) - 1) >= [Begin Date]) AS
PercentByGroup
FROM FSAClients
WHERE (((DateSerial([StartYear],[StartMonth],1))<=[End Date]) AND
((DateSerial([EndYear],[EndMonth]+1,1)-1)>=[Begin Date]))
GROUP BY FSAClients.EthnicGroup;

Query Results:

EthnicGroup Total PercentByGroup
Asian American 1 10.00%
Caucasian 5 50.00%
Latino 3 30.00%
Pacific Island 1 10.00%
Click to expand...
Click to expand...
Click to expand...
 
P

prismlight

Hi,
Thanks for the info.
Can someone show me what my query would like like with Duanne's suggestion.
I thought I read somehwere I could get the query (or report) to produce what
Im looking for.
Here is my query thus far (Thanks to John V and others)
Query:
PARAMETERS [Begin Date] DateTime, [End Date] DateTime;
SELECT FSAClients.EthnicGroup, Count(*) AS Total, Count(*)/(SELECT
Count(*)
FROM FSAClients WHERE DateSerial([StartYear], [StartMonth], 1) <= [End
Date]
And (DateSerial([EndYear], [EndMonth]+1, 1) - 1) >= [Begin Date]) AS
PercentByGroup
FROM FSAClients
WHERE (((DateSerial([StartYear],[StartMonth],1))<=[End Date]) AND
((DateSerial([EndYear],[EndMonth]+1,1)-1)>=[Begin Date]))
GROUP BY FSAClients.EthnicGroup;




Duane Hookom said:
You only get all EthnicGroups to appear if you have a table of all
EthnicGroups included in the query with a join that includes all records in
the all EthnicGroups field.

--
Duane Hookom
MS Access MVP


prismlight said:
HI Duane and eveyeone,
Is this the only solution? For some reason I thought I could adjust my
current query to show all EthnicGroups in results and calculate directly
in
the query?
Thanks,
denise

Duane Hookom said:
To get all ethnic groups to appear, you could join your current query
with a
table of all ethnic groups. Your PercentByGroup can more easily be
calculated in a report.

--
Duane Hookom
MS Access MVP


Hello,
I was hoping someone could help a newbie.
I have a table named FSAClients with Feilds such as EthnicGroup,
StartMonth,
StartYear,EndMonth,EndYear. Im trying to get my query results to
calculate
the sums of both the Total and Percent Columns ( sum of Total column
will
always vary but the Percent column should always appear as 100%) Also,
how
would the query appear if I wanted Each EthnicGroups to appear in the
results
even if the total was 0?Here is my query and the results thus far
(Thank
to
John V. and Bill).
(Thanks in advance)

Query:

PARAMETERS [Begin Date] DateTime, [End Date] DateTime;
SELECT FSAClients.EthnicGroup, Count(*) AS Total, Count(*)/(SELECT
Count(*)
FROM FSAClients WHERE DateSerial([StartYear], [StartMonth], 1) <= [End
Date]
And (DateSerial([EndYear], [EndMonth]+1, 1) - 1) >= [Begin Date]) AS
PercentByGroup
FROM FSAClients
WHERE (((DateSerial([StartYear],[StartMonth],1))<=[End Date]) AND
((DateSerial([EndYear],[EndMonth]+1,1)-1)>=[Begin Date]))
GROUP BY FSAClients.EthnicGroup;

Query Results:

EthnicGroup Total PercentByGroup
Asian American 1 10.00%
Caucasian 5 50.00%
Latino 3 30.00%
Pacific Island 1 10.00%
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

First of all, I would replace the date parameter prompts with references to
values in controls on forms. This is just my preaching so take it or leave
it. The users are yours, not mine.

Do you have a table of each ethnic group?

--
Duane Hookom
MS Access MVP


prismlight said:
Hi,
Thanks for the info.
Can someone show me what my query would like like with Duanne's
suggestion.
I thought I read somehwere I could get the query (or report) to produce
what
Im looking for.
Here is my query thus far (Thanks to John V and others)
Query:
PARAMETERS [Begin Date] DateTime, [End Date] DateTime;
SELECT FSAClients.EthnicGroup, Count(*) AS Total, Count(*)/(SELECT
Count(*)
FROM FSAClients WHERE DateSerial([StartYear], [StartMonth], 1) <= [End
Date]
And (DateSerial([EndYear], [EndMonth]+1, 1) - 1) >= [Begin Date]) AS
PercentByGroup
FROM FSAClients
WHERE (((DateSerial([StartYear],[StartMonth],1))<=[End Date]) AND
((DateSerial([EndYear],[EndMonth]+1,1)-1)>=[Begin Date]))
GROUP BY FSAClients.EthnicGroup;




Duane Hookom said:
You only get all EthnicGroups to appear if you have a table of all
EthnicGroups included in the query with a join that includes all records
in
the all EthnicGroups field.

--
Duane Hookom
MS Access MVP


prismlight said:
HI Duane and eveyeone,
Is this the only solution? For some reason I thought I could adjust my
current query to show all EthnicGroups in results and calculate
directly
in
the query?
Thanks,
denise

:

To get all ethnic groups to appear, you could join your current query
with a
table of all ethnic groups. Your PercentByGroup can more easily be
calculated in a report.

--
Duane Hookom
MS Access MVP


Hello,
I was hoping someone could help a newbie.
I have a table named FSAClients with Feilds such as EthnicGroup,
StartMonth,
StartYear,EndMonth,EndYear. Im trying to get my query results to
calculate
the sums of both the Total and Percent Columns ( sum of Total column
will
always vary but the Percent column should always appear as 100%)
Also,
how
would the query appear if I wanted Each EthnicGroups to appear in
the
results
even if the total was 0?Here is my query and the results thus far
(Thank
to
John V. and Bill).
(Thanks in advance)

Query:

PARAMETERS [Begin Date] DateTime, [End Date] DateTime;
SELECT FSAClients.EthnicGroup, Count(*) AS Total, Count(*)/(SELECT
Count(*)
FROM FSAClients WHERE DateSerial([StartYear], [StartMonth], 1) <=
[End
Date]
And (DateSerial([EndYear], [EndMonth]+1, 1) - 1) >= [Begin Date])
AS
PercentByGroup
FROM FSAClients
WHERE (((DateSerial([StartYear],[StartMonth],1))<=[End Date]) AND
((DateSerial([EndYear],[EndMonth]+1,1)-1)>=[Begin Date]))
GROUP BY FSAClients.EthnicGroup;

Query Results:

EthnicGroup Total PercentByGroup
Asian American 1 10.00%
Caucasian 5 50.00%
Latino 3 30.00%
Pacific Island 1 10.00%
Click to expand...
Click to expand...
Click to expand...
 
P

prismlight

Hi,
Im a veryvery NEWBIE so I dont understand "replace the date parameter
prompts with references to values in controls on forms" (I dont have forms).
I only have ONE TABLE for Client demographics. One of the feilds is
EthnicGroup with a pull down menu. Everything works great in the query
results. I just want the query to calculate the total for all EthnicGroups.
Im taking an Access class next month so maybe Ill have to wait?
Thanks for all your help.

Duane Hookom said:
First of all, I would replace the date parameter prompts with references to
values in controls on forms. This is just my preaching so take it or leave
it. The users are yours, not mine.

Do you have a table of each ethnic group?

--
Duane Hookom
MS Access MVP


prismlight said:
Hi,
Thanks for the info.
Can someone show me what my query would like like with Duanne's
suggestion.
I thought I read somehwere I could get the query (or report) to produce
what
Im looking for.
Here is my query thus far (Thanks to John V and others)
Query:
PARAMETERS [Begin Date] DateTime, [End Date] DateTime;
SELECT FSAClients.EthnicGroup, Count(*) AS Total, Count(*)/(SELECT
Count(*)
FROM FSAClients WHERE DateSerial([StartYear], [StartMonth], 1) <= [End
Date]
And (DateSerial([EndYear], [EndMonth]+1, 1) - 1) >= [Begin Date]) AS
PercentByGroup
FROM FSAClients
WHERE (((DateSerial([StartYear],[StartMonth],1))<=[End Date]) AND
((DateSerial([EndYear],[EndMonth]+1,1)-1)>=[Begin Date]))
GROUP BY FSAClients.EthnicGroup;




Duane Hookom said:
You only get all EthnicGroups to appear if you have a table of all
EthnicGroups included in the query with a join that includes all records
in
the all EthnicGroups field.

--
Duane Hookom
MS Access MVP


HI Duane and eveyeone,
Is this the only solution? For some reason I thought I could adjust my
current query to show all EthnicGroups in results and calculate
directly
in
the query?
Thanks,
denise

:

To get all ethnic groups to appear, you could join your current query
with a
table of all ethnic groups. Your PercentByGroup can more easily be
calculated in a report.

--
Duane Hookom
MS Access MVP


Hello,
I was hoping someone could help a newbie.
I have a table named FSAClients with Feilds such as EthnicGroup,
StartMonth,
StartYear,EndMonth,EndYear. Im trying to get my query results to
calculate
the sums of both the Total and Percent Columns ( sum of Total column
will
always vary but the Percent column should always appear as 100%)
Also,
how
would the query appear if I wanted Each EthnicGroups to appear in
the
results
even if the total was 0?Here is my query and the results thus far
(Thank
to
John V. and Bill).
(Thanks in advance)

Query:

PARAMETERS [Begin Date] DateTime, [End Date] DateTime;
SELECT FSAClients.EthnicGroup, Count(*) AS Total, Count(*)/(SELECT
Count(*)
FROM FSAClients WHERE DateSerial([StartYear], [StartMonth], 1) <=
[End
Date]
And (DateSerial([EndYear], [EndMonth]+1, 1) - 1) >= [Begin Date])
AS
PercentByGroup
FROM FSAClients
WHERE (((DateSerial([StartYear],[StartMonth],1))<=[End Date]) AND
((DateSerial([EndYear],[EndMonth]+1,1)-1)>=[Begin Date]))
GROUP BY FSAClients.EthnicGroup;

Query Results:

EthnicGroup Total PercentByGroup
Asian American 1 10.00%
Caucasian 5 50.00%
Latino 3 30.00%
Pacific Island 1 10.00%
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

You state "pull down menu"... where do the values in this come from? You
shouldn't be using lookup fields
http://www.mvps.org/access/lookupfields.htm.

I would create a table using a make table query:
SELECT EthnicGroup INTO tblEthnicGroups
FROM FSAClients
GROUP BY EthnicGroup;
Then set the primary key of the new table to the EthnicGroup field.

Add this table to a query with your previous query. Join the EthnicGroup
fields. Double-click the join line and select the option that includes all
records from tblEthnicGroups.

Learn about forms. Tables are not meant for data viewing and editing.


--
Duane Hookom
MS Access MVP


prismlight said:
Hi,
Im a veryvery NEWBIE so I dont understand "replace the date parameter
prompts with references to values in controls on forms" (I dont have
forms).
I only have ONE TABLE for Client demographics. One of the feilds is
EthnicGroup with a pull down menu. Everything works great in the query
results. I just want the query to calculate the total for all
EthnicGroups.
Im taking an Access class next month so maybe Ill have to wait?
Thanks for all your help.

Duane Hookom said:
First of all, I would replace the date parameter prompts with references
to
values in controls on forms. This is just my preaching so take it or
leave
it. The users are yours, not mine.

Do you have a table of each ethnic group?

--
Duane Hookom
MS Access MVP


prismlight said:
Hi,
Thanks for the info.
Can someone show me what my query would like like with Duanne's
suggestion.
I thought I read somehwere I could get the query (or report) to produce
what
Im looking for.
Here is my query thus far (Thanks to John V and others)
Query:
PARAMETERS [Begin Date] DateTime, [End Date] DateTime;
SELECT FSAClients.EthnicGroup, Count(*) AS Total, Count(*)/(SELECT
Count(*)
FROM FSAClients WHERE DateSerial([StartYear], [StartMonth], 1) <= [End
Date]
And (DateSerial([EndYear], [EndMonth]+1, 1) - 1) >= [Begin Date]) AS
PercentByGroup
FROM FSAClients
WHERE (((DateSerial([StartYear],[StartMonth],1))<=[End Date]) AND
((DateSerial([EndYear],[EndMonth]+1,1)-1)>=[Begin Date]))
GROUP BY FSAClients.EthnicGroup;




:

You only get all EthnicGroups to appear if you have a table of all
EthnicGroups included in the query with a join that includes all
records
in
the all EthnicGroups field.

--
Duane Hookom
MS Access MVP


HI Duane and eveyeone,
Is this the only solution? For some reason I thought I could adjust
my
current query to show all EthnicGroups in results and calculate
directly
in
the query?
Thanks,
denise

:

To get all ethnic groups to appear, you could join your current
query
with a
table of all ethnic groups. Your PercentByGroup can more easily be
calculated in a report.

--
Duane Hookom
MS Access MVP


message
Hello,
I was hoping someone could help a newbie.
I have a table named FSAClients with Feilds such as EthnicGroup,
StartMonth,
StartYear,EndMonth,EndYear. Im trying to get my query results to
calculate
the sums of both the Total and Percent Columns ( sum of Total
column
will
always vary but the Percent column should always appear as 100%)
Also,
how
would the query appear if I wanted Each EthnicGroups to appear in
the
results
even if the total was 0?Here is my query and the results thus far
(Thank
to
John V. and Bill).
(Thanks in advance)

Query:

PARAMETERS [Begin Date] DateTime, [End Date] DateTime;
SELECT FSAClients.EthnicGroup, Count(*) AS Total,
Count(*)/(SELECT
Count(*)
FROM FSAClients WHERE DateSerial([StartYear], [StartMonth], 1)
<=
[End
Date]
And (DateSerial([EndYear], [EndMonth]+1, 1) - 1) >= [Begin
Date])
AS
PercentByGroup
FROM FSAClients
WHERE (((DateSerial([StartYear],[StartMonth],1))<=[End Date]) AND
((DateSerial([EndYear],[EndMonth]+1,1)-1)>=[Begin Date]))
GROUP BY FSAClients.EthnicGroup;

Query Results:

EthnicGroup Total PercentByGroup
Asian American 1 10.00%
Caucasian 5 50.00%
Latino 3 30.00%
Pacific Island 1 10.00%
Click to expand...
Click to expand...
Click to expand...
 

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

Help with Parameter Query using MathamaticalExpressions 1
Parameter Query with mathamatical expressions 1
Union query not displaying select statement 1
Help with calculation........ 1
Crosstab query with multiple fields 2
View Null Results in Query 1
Parameter query with expressions and calculations 3
Crosstab query by date range 7

Top