Help creating a report

[pokdbz]

I have a talbe with all of this information in it:
Study title
Male/Female
Hispanic- yes no
Race

I cannot get a report to come out the way that I need too. If I could get
some help that would be great. Here is how it needs to be setup

Study 1
M F
Hispanic
Af Am 5 3
White 0 1
Alask 1 3

Non Hispanic
Af Am 3 2
White 6 3
Alask 4 3

Study Title 2

Hispanic
Af Am 3 2
White 7 6
Alask 0 0
NonHispanic
Af Am 7 1
White 1 0
Alask 2 1

If you could help me set this up it would be a big help. Do I have to set
up some queries to help me do this?
Thanks

 

[Frank Stone]

hi,
usually a query is the data source for a report. Start
with the query. get the info you need then do the report
and setting the query at the reports data source in the
reports property sheet.
To do the report, just click the report tab then click new.
you will get a blank report.
Report header- use label control- will appear on each page
Page Header - use label control- id's the query fields
Detail - use text box - set control sourse in the
properties box to query field
Page footer(if desired)- use text box for date, page ect
lay out - be creative- you already have that.
Regards
Frank

 

[Duane Hookom]

I think the easiest path woul be to create subreports based on totals or
crosstab queries. Your query for the first Study would be:

SELECT IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp, Race,
Abs(Sum([Male/Female]="M")) As M,
Abs(Sum([Male/Female]="F")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY IIf(Hispanic,"Hispanic","Non Hispanic"), Race

 

[pokdbz]

So I would make a report that brings up the study title and then with the
code below put that in a subreport? Then will this provide me with the
counts of each male and female of the race

I think the easiest path woul be to create subreports based on totals or
crosstab queries. Your query for the first Study would be:

SELECT IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp, Race,
Abs(Sum([Male/Female]="M")) As M,
Abs(Sum([Male/Female]="F")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY IIf(Hispanic,"Hispanic","Non Hispanic"), Race

--
Duane Hookom
MS Access MVP
--

I have a talbe with all of this information in it:
Study title
Male/Female
Hispanic- yes no
Race

I cannot get a report to come out the way that I need too. If I could get
some help that would be great. Here is how it needs to be setup

Study 1
M F
Hispanic
Af Am 5 3
White 0 1
Alask 1 3

Non Hispanic
Af Am 3 2
White 6 3
Alask 4 3

Study Title 2

Hispanic
Af Am 3 2
White 7 6
Alask 0 0
NonHispanic
Af Am 7 1
White 1 0
Alask 2 1

If you could help me set this up it would be a big help. Do I have to set
up some queries to help me do this?
Thanks

 

[Duane Hookom]

Sorry, I didn't read your original post very well. You should be able to
create a query like this:
SELECT [Study Title],
IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp,
Race, Abs(Sum([Male/Female]="M")) As M,
Abs(Sum([Male/Female]="F")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY [Study Title], IIf(Hispanic,"Hispanic","Non Hispanic"), Race;

Build your report based on this query and set the sorting and grouping in
the report design to sort by:
Study Title
IsHispanic
Race

--
Duane Hookom
MS Access MVP
--

So I would make a report that brings up the study title and then with the
code below put that in a subreport? Then will this provide me with the
counts of each male and female of the race

I think the easiest path woul be to create subreports based on totals or
crosstab queries. Your query for the first Study would be:

SELECT IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp, Race,
Abs(Sum([Male/Female]="M")) As M,
Abs(Sum([Male/Female]="F")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY IIf(Hispanic,"Hispanic","Non Hispanic"), Race

--
Duane Hookom
MS Access MVP
--

I have a talbe with all of this information in it:
Study title
Male/Female
Hispanic- yes no
Race

I cannot get a report to come out the way that I need too. If I could
get
some help that would be great. Here is how it needs to be setup

Study 1
M F
Hispanic
Af Am 5 3
White 0 1
Alask 1 3

Non Hispanic
Af Am 3 2
White 6 3
Alask 4 3

Study Title 2

Hispanic
Af Am 3 2
White 7 6
Alask 0 0
NonHispanic
Af Am 7 1
White 1 0
Alask 2 1

If you could help me set this up it would be a big help. Do I have to
set
up some queries to help me do this?
Thanks

 

[pokdbz]

Could you explain the as IsHisp to me I am not understanding what this is for.

Sorry, I didn't read your original post very well. You should be able to
create a query like this:
SELECT [Study Title],
IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp,
Race, Abs(Sum([Male/Female]="M")) As M,
Abs(Sum([Male/Female]="F")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY [Study Title], IIf(Hispanic,"Hispanic","Non Hispanic"), Race;

Build your report based on this query and set the sorting and grouping in
the report design to sort by:
Study Title
IsHispanic
Race

--
Duane Hookom
MS Access MVP
--

So I would make a report that brings up the study title and then with the
code below put that in a subreport? Then will this provide me with the
counts of each male and female of the race

I think the easiest path woul be to create subreports based on totals or
crosstab queries. Your query for the first Study would be:

SELECT IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp, Race,
Abs(Sum([Male/Female]="M")) As M,
Abs(Sum([Male/Female]="F")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY IIf(Hispanic,"Hispanic","Non Hispanic"), Race

--
Duane Hookom
MS Access MVP
--

I have a talbe with all of this information in it:
Study title
Male/Female
Hispanic- yes no
Race

I cannot get a report to come out the way that I need too. If I could
get
some help that would be great. Here is how it needs to be setup

Study 1
M F
Hispanic
Af Am 5 3
White 0 1
Alask 1 3

Non Hispanic
Af Am 3 2
White 6 3
Alask 4 3

Study Title 2

Hispanic
Af Am 3 2
White 7 6
Alask 0 0
NonHispanic
Af Am 7 1
White 1 0
Alask 2 1

If you could help me set this up it would be a big help. Do I have to
set
up some queries to help me do this?
Thanks

 

[Duane Hookom]

The expression states if the field Hispanic is yes/true than return the word
Hispanic. If it is no/false, return Non Hispanic.

--
Duane Hookom
MS Access MVP

Could you explain the as IsHisp to me I am not understanding what this is for.

Sorry, I didn't read your original post very well. You should be able to
create a query like this:
SELECT [Study Title],
IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp,
Race, Abs(Sum([Male/Female]="M")) As M,
Abs(Sum([Male/Female]="F")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY [Study Title], IIf(Hispanic,"Hispanic","Non Hispanic"), Race;

Build your report based on this query and set the sorting and grouping in
the report design to sort by:
Study Title
IsHispanic
Race

--
Duane Hookom
MS Access MVP
--

So I would make a report that brings up the study title and then with the
code below put that in a subreport? Then will this provide me with the
counts of each male and female of the race

:

I think the easiest path woul be to create subreports based on totals or
crosstab queries. Your query for the first Study would be:

SELECT IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp, Race,
Abs(Sum([Male/Female]="M")) As M,
Abs(Sum([Male/Female]="F")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY IIf(Hispanic,"Hispanic","Non Hispanic"), Race

--
Duane Hookom
MS Access MVP
--

I have a talbe with all of this information in it:
Study title
Male/Female
Hispanic- yes no
Race

I cannot get a report to come out the way that I need too. If I could
get
some help that would be great. Here is how it needs to be setup

Study 1
M F
Hispanic
Af Am 5 3
White 0 1
Alask 1 3

Non Hispanic
Af Am 3 2
White 6 3
Alask 4 3

Study Title 2

Hispanic
Af Am 3 2
White 7 6
Alask 0 0
NonHispanic
Af Am 7 1
White 1 0
Alask 2 1

If you could help me set this up it would be a big help. Do I have to
set
up some queries to help me do this?
Thanks

 

[pokdbz]

Ok. In the query it is only pulling out the Hispanic people and skipping the
non Hispanice people. When the report is viewed it only shows the Hispanic
people and skips the non Hispanic, do you know how to fix this?

Also it isn't counting the Races for Hispanic or Non Hispanic.

The expression states if the field Hispanic is yes/true than return the word
Hispanic. If it is no/false, return Non Hispanic.

--
Duane Hookom
MS Access MVP

Could you explain the as IsHisp to me I am not understanding what this is for.

Sorry, I didn't read your original post very well. You should be able to
create a query like this:
SELECT [Study Title],
IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp,
Race, Abs(Sum([Male/Female]="M")) As M,
Abs(Sum([Male/Female]="F")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY [Study Title], IIf(Hispanic,"Hispanic","Non Hispanic"), Race;

Build your report based on this query and set the sorting and grouping in
the report design to sort by:
Study Title
IsHispanic
Race

--
Duane Hookom
MS Access MVP
--

So I would make a report that brings up the study title and then with the
code below put that in a subreport? Then will this provide me with the
counts of each male and female of the race

:

I think the easiest path woul be to create subreports based on totals or
crosstab queries. Your query for the first Study would be:

SELECT IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp, Race,
Abs(Sum([Male/Female]="M")) As M,
Abs(Sum([Male/Female]="F")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY IIf(Hispanic,"Hispanic","Non Hispanic"), Race

--
Duane Hookom
MS Access MVP
--

I have a talbe with all of this information in it:
Study title
Male/Female
Hispanic- yes no
Race

I cannot get a report to come out the way that I need too. If I could
get
some help that would be great. Here is how it needs to be setup

Study 1
M F
Hispanic
Af Am 5 3
White 0 1
Alask 1 3

Non Hispanic
Af Am 3 2
White 6 3
Alask 4 3

Study Title 2

Hispanic
Af Am 3 2
White 7 6
Alask 0 0
NonHispanic
Af Am 7 1
White 1 0
Alask 2 1

If you could help me set this up it would be a big help. Do I have to
set
up some queries to help me do this?
Thanks

 

[Duane Hookom]

There is nothing in my SQL that filters out any people. What are you using
for a record source for the report?

--
Duane Hookom
MS Access MVP
--

Ok. In the query it is only pulling out the Hispanic people and skipping
the
non Hispanice people. When the report is viewed it only shows the
Hispanic
people and skips the non Hispanic, do you know how to fix this?

Also it isn't counting the Races for Hispanic or Non Hispanic.

The expression states if the field Hispanic is yes/true than return the
word
Hispanic. If it is no/false, return Non Hispanic.

--
Duane Hookom
MS Access MVP

Could you explain the as IsHisp to me I am not understanding what this
is for.



:

Sorry, I didn't read your original post very well. You should be able
to
create a query like this:
SELECT [Study Title],
IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp,
Race, Abs(Sum([Male/Female]="M")) As M,
Abs(Sum([Male/Female]="F")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY [Study Title], IIf(Hispanic,"Hispanic","Non Hispanic"),
Race;

Build your report based on this query and set the sorting and
grouping in
the report design to sort by:
Study Title
IsHispanic
Race

--
Duane Hookom
MS Access MVP
--

So I would make a report that brings up the study title and then
with the
code below put that in a subreport? Then will this provide me with the
counts of each male and female of the race

:

I think the easiest path woul be to create subreports based on
totals or
crosstab queries. Your query for the first Study would be:

SELECT IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp, Race,
Abs(Sum([Male/Female]="M")) As M,
Abs(Sum([Male/Female]="F")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY IIf(Hispanic,"Hispanic","Non Hispanic"), Race

--
Duane Hookom
MS Access MVP
--

I have a talbe with all of this information in it:
Study title
Male/Female
Hispanic- yes no
Race

I cannot get a report to come out the way that I need too. If I could
get
some help that would be great. Here is how it needs to be setup

Study 1
M F
Hispanic
Af Am 5 3
White 0 1
Alask 1 3

Non Hispanic
Af Am 3 2
White 6 3
Alask 4 3

Study Title 2

Hispanic
Af Am 3 2
White 7 6
Alask 0 0
NonHispanic
Af Am 7 1
White 1 0
Alask 2 1

If you could help me set this up it would be a big help. Do I
have to
set
up some queries to help me do this?
Thanks

 

[pokdbz]

Where do I check what I am using as my record source for the report. When I
open up the form it says:

Enter Parameter
Male/Female
then blank space for input

There is nothing in my SQL that filters out any people. What are you using
for a record source for the report?

--
Duane Hookom
MS Access MVP
--

Ok. In the query it is only pulling out the Hispanic people and skipping
the
non Hispanice people. When the report is viewed it only shows the
Hispanic
people and skips the non Hispanic, do you know how to fix this?

Also it isn't counting the Races for Hispanic or Non Hispanic.

The expression states if the field Hispanic is yes/true than return the
word
Hispanic. If it is no/false, return Non Hispanic.

--
Duane Hookom
MS Access MVP


Could you explain the as IsHisp to me I am not understanding what this
is
for.



:

Sorry, I didn't read your original post very well. You should be able
to
create a query like this:
SELECT [Study Title],
IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp,
Race, Abs(Sum([Male/Female]="M")) As M,
Abs(Sum([Male/Female]="F")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY [Study Title], IIf(Hispanic,"Hispanic","Non Hispanic"),
Race;

Build your report based on this query and set the sorting and
grouping
in
the report design to sort by:
Study Title
IsHispanic
Race

--
Duane Hookom
MS Access MVP
--

So I would make a report that brings up the study title and then
with
the
code below put that in a subreport? Then will this provide me with
the
counts of each male and female of the race

:

I think the easiest path woul be to create subreports based on
totals
or
crosstab queries. Your query for the first Study would be:

SELECT IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp, Race,
Abs(Sum([Male/Female]="M")) As M,
Abs(Sum([Male/Female]="F")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY IIf(Hispanic,"Hispanic","Non Hispanic"), Race

--
Duane Hookom
MS Access MVP
--

I have a talbe with all of this information in it:
Study title
Male/Female
Hispanic- yes no
Race

I cannot get a report to come out the way that I need too. If I
could
get
some help that would be great. Here is how it needs to be setup

Study 1
M F
Hispanic
Af Am 5 3
White 0 1
Alask 1 3

Non Hispanic
Af Am 3 2
White 6 3
Alask 4 3

Study Title 2

Hispanic
Af Am 3 2
White 7 6
Alask 0 0
NonHispanic
Af Am 7 1
White 1 0
Alask 2 1

If you could help me set this up it would be a big help. Do I
have
to
set
up some queries to help me do this?
Thanks

 

[Duane Hookom]

You provided some "field" names which included "Male/Female". I simply
followed your lead on thinking this was a field in your table.

The report's record source is one of its data properties.

Also, you stated "open up the form". Do you mean report?

--
Duane Hookom
MS Access MVP
--

Where do I check what I am using as my record source for the report. When
I
open up the form it says:

Enter Parameter
Male/Female
then blank space for input

There is nothing in my SQL that filters out any people. What are you
using
for a record source for the report?

--
Duane Hookom
MS Access MVP
--

Ok. In the query it is only pulling out the Hispanic people and
skipping
the
non Hispanice people. When the report is viewed it only shows the
Hispanic
people and skips the non Hispanic, do you know how to fix this?

Also it isn't counting the Races for Hispanic or Non Hispanic.

:

The expression states if the field Hispanic is yes/true than return
the
word
Hispanic. If it is no/false, return Non Hispanic.

--
Duane Hookom
MS Access MVP


Could you explain the as IsHisp to me I am not understanding what
this
is
for.



:

Sorry, I didn't read your original post very well. You should be
able
to
create a query like this:
SELECT [Study Title],
IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp,
Race, Abs(Sum([Male/Female]="M")) As M,
Abs(Sum([Male/Female]="F")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY [Study Title], IIf(Hispanic,"Hispanic","Non Hispanic"),
Race;

Build your report based on this query and set the sorting and
grouping
in
the report design to sort by:
Study Title
IsHispanic
Race

--
Duane Hookom
MS Access MVP
--

So I would make a report that brings up the study title and then
with
the
code below put that in a subreport? Then will this provide me
with
the
counts of each male and female of the race

:

I think the easiest path woul be to create subreports based on
totals
or
crosstab queries. Your query for the first Study would be:

SELECT IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp, Race,
Abs(Sum([Male/Female]="M")) As M,
Abs(Sum([Male/Female]="F")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY IIf(Hispanic,"Hispanic","Non Hispanic"), Race

--
Duane Hookom
MS Access MVP
--

I have a talbe with all of this information in it:
Study title
Male/Female
Hispanic- yes no
Race

I cannot get a report to come out the way that I need too.
If I
could
get
some help that would be great. Here is how it needs to be
setup

Study 1
M F
Hispanic
Af Am 5 3
White 0 1
Alask 1 3

Non Hispanic
Af Am 3 2
White 6 3
Alask 4 3

Study Title 2

Hispanic
Af Am 3 2
White 7 6
Alask 0 0
NonHispanic
Af Am 7 1
White 1 0
Alask 2 1

If you could help me set this up it would be a big help. Do
I
have
to
set
up some queries to help me do this?
Thanks

 

[pokdbz]

Sorry about the confusion. The field name is "Sex" and there are two options
Male or Female.

As for the record source it is the title of the query that you build which I
called MainQuery.

Yes I do mean report, sorry.

You provided some "field" names which included "Male/Female". I simply
followed your lead on thinking this was a field in your table.

The report's record source is one of its data properties.

Also, you stated "open up the form". Do you mean report?

--
Duane Hookom
MS Access MVP
--

Where do I check what I am using as my record source for the report. When
I
open up the form it says:

Enter Parameter
Male/Female
then blank space for input

There is nothing in my SQL that filters out any people. What are you
using
for a record source for the report?

--
Duane Hookom
MS Access MVP
--

Ok. In the query it is only pulling out the Hispanic people and
skipping
the
non Hispanice people. When the report is viewed it only shows the
Hispanic
people and skips the non Hispanic, do you know how to fix this?

Also it isn't counting the Races for Hispanic or Non Hispanic.

:

The expression states if the field Hispanic is yes/true than return
the
word
Hispanic. If it is no/false, return Non Hispanic.

--
Duane Hookom
MS Access MVP


Could you explain the as IsHisp to me I am not understanding what
this
is
for.



:

Sorry, I didn't read your original post very well. You should be
able
to
create a query like this:
SELECT [Study Title],
IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp,
Race, Abs(Sum([Male/Female]="M")) As M,
Abs(Sum([Male/Female]="F")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY [Study Title], IIf(Hispanic,"Hispanic","Non Hispanic"),
Race;

Build your report based on this query and set the sorting and
grouping
in
the report design to sort by:
Study Title
IsHispanic
Race

--
Duane Hookom
MS Access MVP
--

So I would make a report that brings up the study title and then
with
the
code below put that in a subreport? Then will this provide me
with
the
counts of each male and female of the race

:

I think the easiest path woul be to create subreports based on
totals
or
crosstab queries. Your query for the first Study would be:

SELECT IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp, Race,
Abs(Sum([Male/Female]="M")) As M,
Abs(Sum([Male/Female]="F")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY IIf(Hispanic,"Hispanic","Non Hispanic"), Race

--
Duane Hookom
MS Access MVP
--

I have a talbe with all of this information in it:
Study title
Male/Female
Hispanic- yes no
Race

I cannot get a report to come out the way that I need too.
If I
could
get
some help that would be great. Here is how it needs to be
setup

Study 1
M F
Hispanic
Af Am 5 3
White 0 1
Alask 1 3

Non Hispanic
Af Am 3 2
White 6 3
Alask 4 3

Study Title 2

Hispanic
Af Am 3 2
White 7 6
Alask 0 0
NonHispanic
Af Am 7 1
White 1 0
Alask 2 1

If you could help me set this up it would be a big help. Do
I
have
to
set
up some queries to help me do this?
Thanks

 

[Duane Hookom]

Then you query/sql might be:
SELECT [Study Title],
IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp,
Race, Abs(Sum([Sex]="Male")) As M,
Abs(Sum([Sex]="Female")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY [Study Title], IIf(Hispanic,"Hispanic","Non Hispanic"), Race;


--
Duane Hookom
MS Access MVP
--

Sorry about the confusion. The field name is "Sex" and there are two
options
Male or Female.

As for the record source it is the title of the query that you build which
I
called MainQuery.

Yes I do mean report, sorry.

You provided some "field" names which included "Male/Female". I simply
followed your lead on thinking this was a field in your table.

The report's record source is one of its data properties.

Also, you stated "open up the form". Do you mean report?

--
Duane Hookom
MS Access MVP
--

Where do I check what I am using as my record source for the report.
When
I
open up the form it says:

Enter Parameter
Male/Female
then blank space for input

:

There is nothing in my SQL that filters out any people. What are you
using
for a record source for the report?

--
Duane Hookom
MS Access MVP
--

Ok. In the query it is only pulling out the Hispanic people and
skipping
the
non Hispanice people. When the report is viewed it only shows the
Hispanic
people and skips the non Hispanic, do you know how to fix this?

Also it isn't counting the Races for Hispanic or Non Hispanic.

:

The expression states if the field Hispanic is yes/true than return
the
word
Hispanic. If it is no/false, return Non Hispanic.

--
Duane Hookom
MS Access MVP


Could you explain the as IsHisp to me I am not understanding what
this
is
for.



:

Sorry, I didn't read your original post very well. You should
be
able
to
create a query like this:
SELECT [Study Title],
IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp,
Race, Abs(Sum([Male/Female]="M")) As M,
Abs(Sum([Male/Female]="F")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY [Study Title], IIf(Hispanic,"Hispanic","Non
Hispanic"),
Race;

Build your report based on this query and set the sorting and
grouping
in
the report design to sort by:
Study Title
IsHispanic
Race

--
Duane Hookom
MS Access MVP
--

So I would make a report that brings up the study title and
then
with
the
code below put that in a subreport? Then will this provide
me
with
the
counts of each male and female of the race

:

I think the easiest path woul be to create subreports based
on
totals
or
crosstab queries. Your query for the first Study would be:

SELECT IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp,
Race,
Abs(Sum([Male/Female]="M")) As M,
Abs(Sum([Male/Female]="F")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY IIf(Hispanic,"Hispanic","Non Hispanic"), Race

--
Duane Hookom
MS Access MVP
--

I have a talbe with all of this information in it:
Study title
Male/Female
Hispanic- yes no
Race

I cannot get a report to come out the way that I need too.
If I
could
get
some help that would be great. Here is how it needs to be
setup

Study 1
M F
Hispanic
Af Am 5 3
White 0 1
Alask 1 3

Non Hispanic
Af Am 3 2
White 6 3
Alask 4 3

Study Title 2

Hispanic
Af Am 3 2
White 7 6
Alask 0 0
NonHispanic
Af Am 7 1
White 1 0
Alask 2 1

If you could help me set this up it would be a big help.
Do
I
have
to
set
up some queries to help me do this?
Thanks

 

[pokdbz]

Almost, the only problems I still am having is with the Hispanic part. It is
only pulling out the Hispanic people and skipping the Non Hispanic. Maybe
because I didn't explain it very well.
The field is "Hispanic" which is a combo box with "Yes" and "No" for
choices. Each person is either Hispanic or Non Hispanic. So I am not really
sure why it is not showing up in the query. I don't know if this helps.
Thanks for all of your help.

Then you query/sql might be:
SELECT [Study Title],
IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp,
Race, Abs(Sum([Sex]="Male")) As M,
Abs(Sum([Sex]="Female")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY [Study Title], IIf(Hispanic,"Hispanic","Non Hispanic"), Race;


--
Duane Hookom
MS Access MVP
--

Sorry about the confusion. The field name is "Sex" and there are two
options
Male or Female.

As for the record source it is the title of the query that you build which
I
called MainQuery.

Yes I do mean report, sorry.

You provided some "field" names which included "Male/Female". I simply
followed your lead on thinking this was a field in your table.

The report's record source is one of its data properties.

Also, you stated "open up the form". Do you mean report?

--
Duane Hookom
MS Access MVP
--

Where do I check what I am using as my record source for the report.
When
I
open up the form it says:

Enter Parameter
Male/Female
then blank space for input

:

There is nothing in my SQL that filters out any people. What are you
using
for a record source for the report?

--
Duane Hookom
MS Access MVP
--

Ok. In the query it is only pulling out the Hispanic people and
skipping
the
non Hispanice people. When the report is viewed it only shows the
Hispanic
people and skips the non Hispanic, do you know how to fix this?

Also it isn't counting the Races for Hispanic or Non Hispanic.

:

The expression states if the field Hispanic is yes/true than return
the
word
Hispanic. If it is no/false, return Non Hispanic.

--
Duane Hookom
MS Access MVP


Could you explain the as IsHisp to me I am not understanding what
this
is
for.



:

Sorry, I didn't read your original post very well. You should
be
able
to
create a query like this:
SELECT [Study Title],
IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp,
Race, Abs(Sum([Male/Female]="M")) As M,
Abs(Sum([Male/Female]="F")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY [Study Title], IIf(Hispanic,"Hispanic","Non
Hispanic"),
Race;

Build your report based on this query and set the sorting and
grouping
in
the report design to sort by:
Study Title
IsHispanic
Race

--
Duane Hookom
MS Access MVP
--

So I would make a report that brings up the study title and
then
with
the
code below put that in a subreport? Then will this provide
me
with
the
counts of each male and female of the race

:

I think the easiest path woul be to create subreports based
on
totals
or
crosstab queries. Your query for the first Study would be:

SELECT IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp,
Race,
Abs(Sum([Male/Female]="M")) As M,
Abs(Sum([Male/Female]="F")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY IIf(Hispanic,"Hispanic","Non Hispanic"), Race

--
Duane Hookom
MS Access MVP
--

I have a talbe with all of this information in it:
Study title
Male/Female
Hispanic- yes no
Race

I cannot get a report to come out the way that I need too.
If I
could
get
some help that would be great. Here is how it needs to be
setup

Study 1
M F
Hispanic
Af Am 5 3
White 0 1
Alask 1 3

Non Hispanic
Af Am 3 2
White 6 3
Alask 4 3

Study Title 2

Hispanic
Af Am 3 2
White 7 6
Alask 0 0
NonHispanic
Af Am 7 1
White 1 0
Alask 2 1

If you could help me set this up it would be a big help.
Do
I
have
to
set
up some queries to help me do this?
Thanks

 

[Duane Hookom]

Again this is caused by my not understanding your data type for the field
Hispanic. I assumed from your first email that the field type was yes/no.
Apparently it is text and you are storing the values "yes" or "no".
Then you query/sql might be:
SELECT [Study Title],
IIf(Hispanic="Yes","Hispanic","Non Hispanic") as IsHisp,
Race, Abs(Sum([Sex]="Male")) As M,
Abs(Sum([Sex]="Female")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY [Study Title], IIf(Hispanic,"Hispanic","Non Hispanic"), Race;

--
Duane Hookom
MS Access MVP
--

Almost, the only problems I still am having is with the Hispanic part. It
is
only pulling out the Hispanic people and skipping the Non Hispanic. Maybe
because I didn't explain it very well.
The field is "Hispanic" which is a combo box with "Yes" and "No" for
choices. Each person is either Hispanic or Non Hispanic. So I am not
really
sure why it is not showing up in the query. I don't know if this helps.
Thanks for all of your help.

Then you query/sql might be:
SELECT [Study Title],
IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp,
Race, Abs(Sum([Sex]="Male")) As M,
Abs(Sum([Sex]="Female")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY [Study Title], IIf(Hispanic,"Hispanic","Non Hispanic"), Race;


--
Duane Hookom
MS Access MVP
--

Sorry about the confusion. The field name is "Sex" and there are two
options
Male or Female.

As for the record source it is the title of the query that you build
which
I
called MainQuery.

Yes I do mean report, sorry.


:

You provided some "field" names which included "Male/Female". I simply
followed your lead on thinking this was a field in your table.

The report's record source is one of its data properties.

Also, you stated "open up the form". Do you mean report?

--
Duane Hookom
MS Access MVP
--

Where do I check what I am using as my record source for the report.
When
I
open up the form it says:

Enter Parameter
Male/Female
then blank space for input

:

There is nothing in my SQL that filters out any people. What are
you
using
for a record source for the report?

--
Duane Hookom
MS Access MVP
--

Ok. In the query it is only pulling out the Hispanic people and
skipping
the
non Hispanice people. When the report is viewed it only shows
the
Hispanic
people and skips the non Hispanic, do you know how to fix this?

Also it isn't counting the Races for Hispanic or Non Hispanic.

:

The expression states if the field Hispanic is yes/true than
return
the
word
Hispanic. If it is no/false, return Non Hispanic.

--
Duane Hookom
MS Access MVP


Could you explain the as IsHisp to me I am not understanding
what
this
is
for.



:

Sorry, I didn't read your original post very well. You
should
be
able
to
create a query like this:
SELECT [Study Title],
IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp,
Race, Abs(Sum([Male/Female]="M")) As M,
Abs(Sum([Male/Female]="F")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY [Study Title], IIf(Hispanic,"Hispanic","Non
Hispanic"),
Race;

Build your report based on this query and set the sorting
and
grouping
in
the report design to sort by:
Study Title
IsHispanic
Race

--
Duane Hookom
MS Access MVP
--

So I would make a report that brings up the study title
and
then
with
the
code below put that in a subreport? Then will this
provide
me
with
the
counts of each male and female of the race

:

I think the easiest path woul be to create subreports
based
on
totals
or
crosstab queries. Your query for the first Study would
be:

SELECT IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp,
Race,
Abs(Sum([Male/Female]="M")) As M,
Abs(Sum([Male/Female]="F")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY IIf(Hispanic,"Hispanic","Non Hispanic"), Race

--
Duane Hookom
MS Access MVP
--

message
I have a talbe with all of this information in it:
Study title
Male/Female
Hispanic- yes no
Race

I cannot get a report to come out the way that I need
too.
If I
could
get
some help that would be great. Here is how it needs to
be
setup

Study 1
M F
Hispanic
Af Am 5 3
White 0 1
Alask 1 3

Non Hispanic
Af Am 3 2
White 6 3
Alask 4 3

Study Title 2

Hispanic
Af Am 3 2
White 7 6
Alask 0 0
NonHispanic
Af Am 7 1
White 1 0
Alask 2 1

If you could help me set this up it would be a big
help.
Do
I
have
to
set
up some queries to help me do this?
Thanks

 

[pokdbz]

That didn't seem to work it is saying:
You tried to execute a query that does not include the specific expression
IIf(Hispanic="Yes", "Hispanic","Non Hispanic") as part of an aggregate.

Also I for the hispanic part will it also show the non hispanic (No) with
the new statement provided or only the "Yes" which are Hispanic

Again this is caused by my not understanding your data type for the field
Hispanic. I assumed from your first email that the field type was yes/no.
Apparently it is text and you are storing the values "yes" or "no".
Then you query/sql might be:
SELECT [Study Title],
IIf(Hispanic="Yes","Hispanic","Non Hispanic") as IsHisp,
Race, Abs(Sum([Sex]="Male")) As M,
Abs(Sum([Sex]="Female")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY [Study Title], IIf(Hispanic,"Hispanic","Non Hispanic"), Race;

--
Duane Hookom
MS Access MVP
--

Almost, the only problems I still am having is with the Hispanic part. It
is
only pulling out the Hispanic people and skipping the Non Hispanic. Maybe
because I didn't explain it very well.
The field is "Hispanic" which is a combo box with "Yes" and "No" for
choices. Each person is either Hispanic or Non Hispanic. So I am not
really
sure why it is not showing up in the query. I don't know if this helps.
Thanks for all of your help.

Then you query/sql might be:
SELECT [Study Title],
IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp,
Race, Abs(Sum([Sex]="Male")) As M,
Abs(Sum([Sex]="Female")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY [Study Title], IIf(Hispanic,"Hispanic","Non Hispanic"), Race;


--
Duane Hookom
MS Access MVP
--

Sorry about the confusion. The field name is "Sex" and there are two
options
Male or Female.

As for the record source it is the title of the query that you build
which
I
called MainQuery.

Yes I do mean report, sorry.


:

You provided some "field" names which included "Male/Female". I simply
followed your lead on thinking this was a field in your table.

The report's record source is one of its data properties.

Also, you stated "open up the form". Do you mean report?

--
Duane Hookom
MS Access MVP
--

Where do I check what I am using as my record source for the report.
When
I
open up the form it says:

Enter Parameter
Male/Female
then blank space for input

:

There is nothing in my SQL that filters out any people. What are
you
using
for a record source for the report?

--
Duane Hookom
MS Access MVP
--

Ok. In the query it is only pulling out the Hispanic people and
skipping
the
non Hispanice people. When the report is viewed it only shows
the
Hispanic
people and skips the non Hispanic, do you know how to fix this?

Also it isn't counting the Races for Hispanic or Non Hispanic.

:

The expression states if the field Hispanic is yes/true than
return
the
word
Hispanic. If it is no/false, return Non Hispanic.

--
Duane Hookom
MS Access MVP


Could you explain the as IsHisp to me I am not understanding
what
this
is
for.



:

Sorry, I didn't read your original post very well. You
should
be
able
to
create a query like this:
SELECT [Study Title],
IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp,
Race, Abs(Sum([Male/Female]="M")) As M,
Abs(Sum([Male/Female]="F")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY [Study Title], IIf(Hispanic,"Hispanic","Non
Hispanic"),
Race;

Build your report based on this query and set the sorting
and
grouping
in
the report design to sort by:
Study Title
IsHispanic
Race

--
Duane Hookom
MS Access MVP
--

So I would make a report that brings up the study title
and
then
with
the
code below put that in a subreport? Then will this
provide
me
with
the
counts of each male and female of the race

:

I think the easiest path woul be to create subreports
based
on
totals
or
crosstab queries. Your query for the first Study would
be:

SELECT IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp,
Race,
Abs(Sum([Male/Female]="M")) As M,
Abs(Sum([Male/Female]="F")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY IIf(Hispanic,"Hispanic","Non Hispanic"), Race

--
Duane Hookom
MS Access MVP
--

message
I have a talbe with all of this information in it:
Study title
Male/Female
Hispanic- yes no
Race

I cannot get a report to come out the way that I need
too.
If I
could
get
some help that would be great. Here is how it needs to
be
setup

Study 1
M F
Hispanic
Af Am 5 3
White 0 1
Alask 1 3

Non Hispanic
Af Am 3 2
White 6 3
Alask 4 3

Study Title 2

Hispanic
Af Am 3 2
White 7 6
Alask 0 0
NonHispanic
Af Am 7 1
White 1 0
Alask 2 1

If you could help me set this up it would be a big
help.
Do
I
have
to
set
up some queries to help me do this?
Thanks

 

[Duane Hookom]

My bad. As I have been trying to hit the moving target, I haven't been
aiming all my arrows correctly. Try:
SELECT [Study Title],
IIf(Hispanic="Yes","Hispanic","Non Hispanic") as IsHisp,
Race, Abs(Sum([Sex]="Male")) As M,
Abs(Sum([Sex]="Female")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY [Study Title], IIf(Hispanic="Yes","Hispanic","Non Hispanic")
,Race;


--
Duane Hookom
MS Access MVP
--

That didn't seem to work it is saying:
You tried to execute a query that does not include the specific expression
IIf(Hispanic="Yes", "Hispanic","Non Hispanic") as part of an aggregate.

Also I for the hispanic part will it also show the non hispanic (No) with
the new statement provided or only the "Yes" which are Hispanic

Again this is caused by my not understanding your data type for the field
Hispanic. I assumed from your first email that the field type was yes/no.
Apparently it is text and you are storing the values "yes" or "no".
Then you query/sql might be:
SELECT [Study Title],
IIf(Hispanic="Yes","Hispanic","Non Hispanic") as IsHisp,
Race, Abs(Sum([Sex]="Male")) As M,
Abs(Sum([Sex]="Female")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY [Study Title], IIf(Hispanic,"Hispanic","Non Hispanic"), Race;

--
Duane Hookom
MS Access MVP
--

Almost, the only problems I still am having is with the Hispanic part.
It
is
only pulling out the Hispanic people and skipping the Non Hispanic.
Maybe
because I didn't explain it very well.
The field is "Hispanic" which is a combo box with "Yes" and "No" for
choices. Each person is either Hispanic or Non Hispanic. So I am not
really
sure why it is not showing up in the query. I don't know if this
helps.
Thanks for all of your help.

:

Then you query/sql might be:
SELECT [Study Title],
IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp,
Race, Abs(Sum([Sex]="Male")) As M,
Abs(Sum([Sex]="Female")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY [Study Title], IIf(Hispanic,"Hispanic","Non Hispanic"),
Race;


--
Duane Hookom
MS Access MVP
--

Sorry about the confusion. The field name is "Sex" and there are
two
options
Male or Female.

As for the record source it is the title of the query that you build
which
I
called MainQuery.

Yes I do mean report, sorry.


:

You provided some "field" names which included "Male/Female". I
simply
followed your lead on thinking this was a field in your table.

The report's record source is one of its data properties.

Also, you stated "open up the form". Do you mean report?

--
Duane Hookom
MS Access MVP
--

Where do I check what I am using as my record source for the
report.
When
I
open up the form it says:

Enter Parameter
Male/Female
then blank space for input

:

There is nothing in my SQL that filters out any people. What are
you
using
for a record source for the report?

--
Duane Hookom
MS Access MVP
--

Ok. In the query it is only pulling out the Hispanic people
and
skipping
the
non Hispanice people. When the report is viewed it only shows
the
Hispanic
people and skips the non Hispanic, do you know how to fix
this?

Also it isn't counting the Races for Hispanic or Non Hispanic.

:

The expression states if the field Hispanic is yes/true than
return
the
word
Hispanic. If it is no/false, return Non Hispanic.

--
Duane Hookom
MS Access MVP


Could you explain the as IsHisp to me I am not
understanding
what
this
is
for.



:

Sorry, I didn't read your original post very well. You
should
be
able
to
create a query like this:
SELECT [Study Title],
IIf(Hispanic,"Hispanic","Non Hispanic") as IsHisp,
Race, Abs(Sum([Male/Female]="M")) As M,
Abs(Sum([Male/Female]="F")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY [Study Title], IIf(Hispanic,"Hispanic","Non
Hispanic"),
Race;

Build your report based on this query and set the sorting
and
grouping
in
the report design to sort by:
Study Title
IsHispanic
Race

--
Duane Hookom
MS Access MVP
--

message
So I would make a report that brings up the study title
and
then
with
the
code below put that in a subreport? Then will this
provide
me
with
the
counts of each male and female of the race

:

I think the easiest path woul be to create subreports
based
on
totals
or
crosstab queries. Your query for the first Study would
be:

SELECT IIf(Hispanic,"Hispanic","Non Hispanic") as
IsHisp,
Race,
Abs(Sum([Male/Female]="M")) As M,
Abs(Sum([Male/Female]="F")) As F
FROM tblWithAllOfThisInformationInIt
GROUP BY IIf(Hispanic,"Hispanic","Non Hispanic"), Race

--
Duane Hookom
MS Access MVP
--

message
I have a talbe with all of this information in it:
Study title
Male/Female
Hispanic- yes no
Race

I cannot get a report to come out the way that I
need
too.
If I
could
get
some help that would be great. Here is how it needs
to
be
setup

Study 1
M F
Hispanic
Af Am 5 3
White 0 1
Alask 1 3

Non Hispanic
Af Am 3 2
White 6 3
Alask 4 3

Study Title 2

Hispanic
Af Am 3 2
White 7 6
Alask 0 0
NonHispanic
Af Am 7 1
White 1 0
Alask 2 1

If you could help me set this up it would be a big
help.
Do
I
have
to
set
up some queries to help me do this?
Thanks