extracting totals and/or hiding subreports

Ah, I think I understand now why i can't group the queries together as per
you message below. for ease of explanation, i didn't go into full details
(surprising, I know given the length of the post!) but the relationships
between the criteria are more complex than is obvious!

for example, the SQL for the 'S&L normal' field adjusts for the another
field so that if the value in 'palliative' is 'yes' it DOESN'T count the
'yes' in S&L normal. i've had to programme this back to front so that the
query only counts the 'yes' (coded as 1) if the answer for 'palliative' is
either 1 (No), 0 (incomplete) or 'Is Null' (in case it's blank for any
reason).

The SQL for this query is therefo

SELECT Screened.Site, Count(Screened.slunnaffected) AS CountOfslunnaffected
FROM Screened
WHERE (((Screened.slunnaffected)=1) AND ((Screened.Deceased_Pall_Notes)=2))
OR (((Screened.slunnaffected)=1) AND ((Screened.Deceased_Pall_Notes) Is
Null)) OR (((Screened.slunnaffected)=1) AND
((Screened.Deceased_Pall_Notes)=0))
GROUP BY Screened.Site
ORDER BY Screened.Site;

so i'm not sure if i could add in that line that appears to be for making it
show '0' instead of blank - IIf([Speech&Language], 1, 0)

similarly, because each individual criteria has 'conditional' counting (some
are more complex than the above as they look at the answers to one criteria
relative to 2 others) - i don't think they could be grouped? although there
is a hierarchy in what numbers to count first i.e. always count the true
number of 'yes' in 'palliative' THEN count the number of 'yes' in S&L normal
(after subtracting any entried where there's already a 'yes' in
deceased/palliative) etc.

i know it may be a bit confusing! my main focus now is to try and get each
query to return the site numbers 1-8 in the left hand column and then display
the total for a given criteria; even if that total is 0. after i've done that
then perhaps i could think about merging them?

Thanks again for all your help!


"Marshall Barton" wrote:

Don't worry about my time. I only do this when I have the
time to spare.

As for what I posted, that **IS** the query. It can be
confusing to try to post all the stuff in the query design
grid (which is just a convenient user interface for creating
relatively simple SQL statements). On the other hand,
posting a query's SQL view is concise(?) and unambiguous.
If I knew the name of your table and fields, I could have
posted the exact SQL statement so all you would need to do
is paste it into a new query's SQL view. (You can get a
feel for SQL by switching your queries to SQL view and
trying to figure out how it correlates to what you had in
the grid.)

But, I'll try to express it in something more closely
related to the query design grid.

Each of these are fields in the new query we're trying to
put together:

Site
Group By

CountSandL: IIf([Speech&Language], 1, 0)
Sum

TotalSandL: Speech&Language
Count

CountThis: IIf([this], 1, 0)
Sum

Totalthis: this
Count

CountThat: IIf([that], 1, 0)
Sum

Totalthat: that
Count

and so on for all the fields you want to count.
--
Marsh
MVP [MS Access]


Emelina Bumsquash wrote:
I'm very sorry but don't understand how the expressions you've written
translate into programming the queries. Where would i enter those sorts of
calculations in the query itself?

i feel like i've taken up a lot of your time so if this is too much trouble,
honestly don't worry about it! i know i can find long ways around the
problems but i enjoy working in access so like to learn new things. however,
i think this stuff may just be above my level!

"Marshall Barton" wrote:


Let's take a timeout on the report and concentrate on
creating a single query that does more of the work.

I don't yet know all the types of counts that you need to
calculate, so this is probably a little too simple. OTOH, I
think this kind of query might be a good start and then
we'll see what issues are not covered.

SELECT T.Site,
Sum(IIf([Speech&Language], 1, 0) As CountSandL,
Count([Speech&Language]) As TotalSnamdL,
Sum(IIf([this], 1, 0) As CountThis,
Count([this]) As Totalthis,
Sum(IIf([that], 1, 0) As CountThat,
Count([that]) As Totalthat,
Sum(IIf([Other], 1, 0) As CountOther
Count([Other]) As TotalOther,
FROM yourtable As T
GROUP BY Site

Emelina Bumsquash wrote:

Ah, I think I understand now why i can't group the queries together as per
you message below. for ease of explanation, i didn't go into full details
(surprising, I know given the length of the post!) but the relationships
between the criteria are more complex than is obvious!

for example, the SQL for the 'S&L normal' field adjusts for the another
field so that if the value in 'palliative' is 'yes' it DOESN'T count the
'yes' in S&L normal. i've had to programme this back to front so that the
query only counts the 'yes' (coded as 1) if the answer for 'palliative' is
either 1 (No), 0 (incomplete) or 'Is Null' (in case it's blank for any
reason).

The SQL for this query is therefo

SELECT Screened.Site, Count(Screened.slunnaffected) AS CountOfslunnaffected
FROM Screened
WHERE (((Screened.slunnaffected)=1) AND ((Screened.Deceased_Pall_Notes)=2))
OR (((Screened.slunnaffected)=1) AND ((Screened.Deceased_Pall_Notes) Is
Null)) OR (((Screened.slunnaffected)=1) AND
((Screened.Deceased_Pall_Notes)=0))
GROUP BY Screened.Site
ORDER BY Screened.Site;

so i'm not sure if i could add in that line that appears to be for making it
show '0' instead of blank - IIf([Speech&Language], 1, 0)

similarly, because each individual criteria has 'conditional' counting (some
are more complex than the above as they look at the answers to one criteria
relative to 2 others) - i don't think they could be grouped? although there
is a hierarchy in what numbers to count first i.e. always count the true
number of 'yes' in 'palliative' THEN count the number of 'yes' in S&L normal
(after subtracting any entried where there's already a 'yes' in
deceased/palliative) etc.

i know it may be a bit confusing! my main focus now is to try and get each
query to return the site numbers 1-8 in the left hand column and then display
the total for a given criteria; even if that total is 0. after i've done that
then perhaps i could think about merging them?


Well, technically, that really doesn't change what I
suggested earlier. It does make it more complex though.

The issue you are struggling with is that when the criteria
doesn't return any records, there are no records to count so
the query returns nothing (not even a zero). This is the
reason that I keep coming back to a query that counts
records without using the WHERE clause to determine what to
count.

We can do this by using the IIf condition instead of the
WHERE clause. For your example above, the query would be
like:

SELECT Site,
Sum(IIf(slunnaffected=1
AND (Deceased_Pall_Notes=2
OR Deceased_Pall_Notes Is Null
OR Deceased_Pall_Notes=0) , 1, 0)
) AS CountOfslunnaffected
FROM Screened
GROUP BY Site
ORDER BY Site

This query should return one record with the count (even 0s)
for each site. I hope this is clear enough that you can
work out how to add more calculated fields for your other
counts.

I removed all the extra ( ) that I thought were unnecessary
and rearranged the logic (factor out the common term) to
make it easier to understand the condition. Also note that
because there is only one table involved, there is no need
to specify the table name in front of every field.

I strongly suggest that you save the query from SQL View so
it retains this easier to read format. If you switch to
Design View and back to SQL view, Access will add all those
extra ( ) and table names back and make it hard to read
again. (This is a such a sore point that some people always
work in SQL view or even go so far as to write/edit the
query in Notepad and copy/paste the query from notepad into
the SQL view whenever they edit the SQL statement.)

Without trying to muddy the waters, your table is really
more of a spreadsheet than a database and trying to apply
database techniques to a spreadsheet can be a very messy
affair. I think your only other alternative to my suggested
approach above may be to restructure your table into
multiple tables that are normalized so you only need to
count the records in one or two tables. But that would
mean pretty much starting your entire database over from
scratch.

--
Marsh
MVP [MS Access]

I understand what you're saying about the spreadsheet thing - this database
is hugely over complex in my opinion and I hate having to work on it!!

However, you are an absolute legend with the help you've given me!!! those
statements work perfectly so i'm absolutely chuffed to bits! I know it's
probably a much longer way around than could work if the database were
reorganised now but there's no way we can as it's around a research project
that's already begun!

thank you so much for taking the time to tell me this stuff! Emma

"Marshall Barton" wrote:

Emelina Bumsquash wrote:

Ah, I think I understand now why i can't group the queries together as per
you message below. for ease of explanation, i didn't go into full details
(surprising, I know given the length of the post!) but the relationships
between the criteria are more complex than is obvious!

for example, the SQL for the 'S&L normal' field adjusts for the another
field so that if the value in 'palliative' is 'yes' it DOESN'T count the
'yes' in S&L normal. i've had to programme this back to front so that the
query only counts the 'yes' (coded as 1) if the answer for 'palliative' is
either 1 (No), 0 (incomplete) or 'Is Null' (in case it's blank for any
reason).

The SQL for this query is therefo

SELECT Screened.Site, Count(Screened.slunnaffected) AS CountOfslunnaffected
FROM Screened
WHERE (((Screened.slunnaffected)=1) AND ((Screened.Deceased_Pall_Notes)=2))
OR (((Screened.slunnaffected)=1) AND ((Screened.Deceased_Pall_Notes) Is
Null)) OR (((Screened.slunnaffected)=1) AND
((Screened.Deceased_Pall_Notes)=0))
GROUP BY Screened.Site
ORDER BY Screened.Site;

so i'm not sure if i could add in that line that appears to be for making it
show '0' instead of blank - IIf([Speech&Language], 1, 0)

similarly, because each individual criteria has 'conditional' counting (some
are more complex than the above as they look at the answers to one criteria
relative to 2 others) - i don't think they could be grouped? although there
is a hierarchy in what numbers to count first i.e. always count the true
number of 'yes' in 'palliative' THEN count the number of 'yes' in S&L normal
(after subtracting any entried where there's already a 'yes' in
deceased/palliative) etc.

i know it may be a bit confusing! my main focus now is to try and get each
query to return the site numbers 1-8 in the left hand column and then display
the total for a given criteria; even if that total is 0. after i've done that
then perhaps i could think about merging them?


Well, technically, that really doesn't change what I
suggested earlier. It does make it more complex though.

The issue you are struggling with is that when the criteria
doesn't return any records, there are no records to count so
the query returns nothing (not even a zero). This is the
reason that I keep coming back to a query that counts
records without using the WHERE clause to determine what to
count.

We can do this by using the IIf condition instead of the
WHERE clause. For your example above, the query would be
like:

SELECT Site,
Sum(IIf(slunnaffected=1
AND (Deceased_Pall_Notes=2
OR Deceased_Pall_Notes Is Null
OR Deceased_Pall_Notes=0) , 1, 0)
) AS CountOfslunnaffected
FROM Screened
GROUP BY Site
ORDER BY Site

This query should return one record with the count (even 0s)
for each site. I hope this is clear enough that you can
work out how to add more calculated fields for your other
counts.

I removed all the extra ( ) that I thought were unnecessary
and rearranged the logic (factor out the common term) to
make it easier to understand the condition. Also note that
because there is only one table involved, there is no need
to specify the table name in front of every field.

I strongly suggest that you save the query from SQL View so
it retains this easier to read format. If you switch to
Design View and back to SQL view, Access will add all those
extra ( ) and table names back and make it hard to read
again. (This is a such a sore point that some people always
work in SQL view or even go so far as to write/edit the
query in Notepad and copy/paste the query from notepad into
the SQL view whenever they edit the SQL statement.)

Without trying to muddy the waters, your table is really
more of a spreadsheet than a database and trying to apply
database techniques to a spreadsheet can be a very messy
affair. I think your only other alternative to my suggested
approach above may be to restructure your table into
multiple tables that are normalized so you only need to
count the records in one or two tables. But that would
mean pretty much starting your entire database over from
scratch.

--
Marsh
MVP [MS Access]

Hi Marshall,
I just realised there was one more thing I'd really like some advice on if
possible?
So, now the queries are working and always return the following: the left
hand column has numbers 1-8; the right hand column has the count of the
particular criteria (even if it is a 0)

What I need to do is, again, pretty strange and may not be what Access was
originally intended for but it would help a lot if I could do it.

I need to get together a lot of this data in another sort of report that
shows recruitment rates by site. So far I've been importing the queries as
subreports onto a main report and then extracting the numbers manually into
an Excel file that performs various calculations on the data.

To do these calculations though, the 'by site' numbers need to be separated
out. Is there any way I can import the queries as a subreport (like I
currently am) and then use a text box on the main report to JUST show the
count for, say, site one?

Cheers, Emma



"Marshall Barton" wrote:

Well, technically, that really doesn't change what I
suggested earlier. It does make it more complex though.

The issue you are struggling with is that when the criteria
doesn't return any records, there are no records to count so
the query returns nothing (not even a zero). This is the
reason that I keep coming back to a query that counts
records without using the WHERE clause to determine what to
count.

We can do this by using the IIf condition instead of the
WHERE clause. For your example above, the query would be
like:

SELECT Site,
Sum(IIf(slunnaffected=1
AND (Deceased_Pall_Notes=2
OR Deceased_Pall_Notes Is Null
OR Deceased_Pall_Notes=0) , 1, 0)
) AS CountOfslunnaffected
FROM Screened
GROUP BY Site
ORDER BY Site

This query should return one record with the count (even 0s)
for each site. I hope this is clear enough that you can
work out how to add more calculated fields for your other
counts.

I removed all the extra ( ) that I thought were unnecessary
and rearranged the logic (factor out the common term) to
make it easier to understand the condition. Also note that
because there is only one table involved, there is no need
to specify the table name in front of every field.

I strongly suggest that you save the query from SQL View so
it retains this easier to read format. If you switch to
Design View and back to SQL view, Access will add all those
extra ( ) and table names back and make it hard to read
again. (This is a such a sore point that some people always
work in SQL view or even go so far as to write/edit the
query in Notepad and copy/paste the query from notepad into
the SQL view whenever they edit the SQL statement.)

Without trying to muddy the waters, your table is really
more of a spreadsheet than a database and trying to apply
database techniques to a spreadsheet can be a very messy
affair. I think your only other alternative to my suggested
approach above may be to restructure your table into
multiple tables that are normalized so you only need to
count the records in one or two tables. But that would
mean pretty much starting your entire database over from
scratch.

--
Marsh
MVP [MS Access]

Emelina Bumsquash wrote:
So, now the queries are working and always return the following: the left
hand column has numbers 1-8; the right hand column has the count of the
particular criteria (even if it is a 0)

What I need to do is, again, pretty strange and may not be what Access was
originally intended for but it would help a lot if I could do it.

I need to get together a lot of this data in another sort of report that
shows recruitment rates by site. So far I've been importing the queries as
subreports onto a main report and then extracting the numbers manually into
an Excel file that performs various calculations on the data.

To do these calculations though, the 'by site' numbers need to be separated
out. Is there any way I can import the queries as a subreport (like I
currently am) and then use a text box on the main report to JUST show the
count for, say, site one?


I don't think I followed that very well. I still have the
feeling that you are relying on subreports too much. It's
far better if you can create a query that gets the needed
data. Actually, I don't see why you are creating a report
of any kind if all you are doing with it is exporting the
values to an Excel workbook. Can't you just export a query?

If all you want is the calculated values for one site, you
can add a criteria to the monster query we've been working
on, or, maybe less confusing, create a new query based on
the calculating query and add the criteria to this one:
SELECT *
FROM monsterquery
WHERE Site = [Enter Site Number]

If you really need to use a report for other purposes, then
maybe you can use the report based on the monster query as a
subreport. The Link Master/Child properties can be used to
isolate the subreport to the site on the main report.

--
Marsh
MVP [MS Access]

You are definitely right, I am relying on subreports too much but I can't
seem to make the monster query work! It's just too complex. For example, most
of the criteria that I'm counting the 'yes/1's' for are simple straight
forward criteria just based on one field's entries. However, there are other
criteria that are on the form as drop down boxes with 4 different options and
I am currently using separate queries to count the number of option 1's,
option 2's and so on. When I try to combine them in the monster query it
doesn't seem to work as I'm asking it to look at and count different values
in the same field.

Also, some of the crtieria i'm counting rely on two different tables:i want
to group the variables by the 'Site' field (as in all my other queries) but
the counting is Currently based on other 'WHERE' criteria for a field in a
different table as such:

SELECT Screened.Site, Count(Recruitment.Consent) AS CountOfConsent
FROM Recruitment INNER JOIN Screened ON Recruitment.StudyNumber =
Screened.StudyNumber
WHERE (((Recruitment.Researcher_1)=1 Or (Recruitment.Researcher_1)=2 Or
(Recruitment.Researcher_1)=3 Or (Recruitment.Researcher_1)=4 Or
(Recruitment.Researcher_1)=5 Or (Recruitment.Researcher_1)=6 Or
(Recruitment.Researcher_1)=7 Or (Recruitment.Researcher_1)=8 Or
(Recruitment.Researcher_1)=9))
GROUP BY Screened.Site, Recruitment.Consent
HAVING (((Recruitment.Consent)=2));

I can't even make this work using your helpful SQL statement as an
individual query, let alone combing it with all the others as a monster
query!! Could you help?

The reason I'm using subreports reports a lot / exporting figures to other
programmes, is because the person who's requested this has asked for the
figures to be presented in a very specific format i.e. a flowchart, so i
found the easiest way to get the formatting right (i.e. figures in the right
place on the page) was to actually use text boxes to display the figures i
want which can then be moved and manipulated specifically on the page exactly
where i want it.

don't know if that makes it make any more sense but any help you can offer
on the query above which combines 2 tables, or on how to make this monster
query would be greatly appreciated.

as always, thanks for your time!

"Marshall Barton" wrote:

Emelina Bumsquash wrote:
So, now the queries are working and always return the following: the left
hand column has numbers 1-8; the right hand column has the count of the
particular criteria (even if it is a 0)

What I need to do is, again, pretty strange and may not be what Access was
originally intended for but it would help a lot if I could do it.

I need to get together a lot of this data in another sort of report that
shows recruitment rates by site. So far I've been importing the queries as
subreports onto a main report and then extracting the numbers manually into
an Excel file that performs various calculations on the data.

To do these calculations though, the 'by site' numbers need to be separated
out. Is there any way I can import the queries as a subreport (like I
currently am) and then use a text box on the main report to JUST show the
count for, say, site one?


I don't think I followed that very well. I still have the
feeling that you are relying on subreports too much. It's
far better if you can create a query that gets the needed
data. Actually, I don't see why you are creating a report
of any kind if all you are doing with it is exporting the
values to an Excel workbook. Can't you just export a query?

If all you want is the calculated values for one site, you
can add a criteria to the monster query we've been working
on, or, maybe less confusing, create a new query based on
the calculating query and add the criteria to this one:
SELECT *
FROM monsterquery
WHERE Site = [Enter Site Number]

If you really need to use a report for other purposes, then
maybe you can use the report based on the monster query as a
subreport. The Link Master/Child properties can be used to
isolate the subreport to the site on the main report.

--
Marsh
MVP [MS Access]

Emelina Bumsquash wrote:

You are definitely right, I am relying on subreports too much but I can't
seem to make the monster query work! It's just too complex. For example, most
of the criteria that I'm counting the 'yes/1's' for are simple straight
forward criteria just based on one field's entries. However, there are other
criteria that are on the form as drop down boxes with 4 different options and
I am currently using separate queries to count the number of option 1's,
option 2's and so on. When I try to combine them in the monster query it
doesn't seem to work as I'm asking it to look at and count different values
in the same field.

Also, some of the crtieria i'm counting rely on two different tables:i want
to group the variables by the 'Site' field (as in all my other queries) but
the counting is Currently based on other 'WHERE' criteria for a field in a
different table as such:

SELECT Screened.Site, Count(Recruitment.Consent) AS CountOfConsent
FROM Recruitment INNER JOIN Screened ON Recruitment.StudyNumber =
Screened.StudyNumber
WHERE (((Recruitment.Researcher_1)=1 Or (Recruitment.Researcher_1)=2 Or
(Recruitment.Researcher_1)=3 Or (Recruitment.Researcher_1)=4 Or
(Recruitment.Researcher_1)=5 Or (Recruitment.Researcher_1)=6 Or
(Recruitment.Researcher_1)=7 Or (Recruitment.Researcher_1)=8 Or
(Recruitment.Researcher_1)=9))
GROUP BY Screened.Site, Recruitment.Consent
HAVING (((Recruitment.Consent)=2));

I can't even make this work using your helpful SQL statement as an
individual query, let alone combing it with all the others as a monster
query!! Could you help?

The reason I'm using subreports reports a lot / exporting figures to other
programmes, is because the person who's requested this has asked for the
figures to be presented in a very specific format i.e. a flowchart, so i
found the easiest way to get the formatting right (i.e. figures in the right
place on the page) was to actually use text boxes to display the figures i
want which can then be moved and manipulated specifically on the page exactly
where i want it.

don't know if that makes it make any more sense but any help you can offer
on the query above which combines 2 tables, or on how to make this monster
query would be greatly appreciated.


Assuming there can be more than one Recruitment entry for a
study number, you will need a different GROUP BY clause.
This menas that you need a second query to count the
Recruitment counts.

I'm kind of guessing at what you want to count here.
How does the Researcher fit into it?
Why do you only want Recearchers 1 through 9?

Anyway, let's start with:

SELECT Site, Consent
Sum(IIf(Recruitment.Researcher_1=9
) AS CountOfConsent
FROM Recruitment INNER JOIN Screened
ON Recruitment.StudyNumber = Screened.StudyNumber
GROUP BY Site, Consent

Once we get this straightened out, we'll try to figure out
how to combine it with the monster.

--
Marsh
MVP [MS Access]

Hi Marshall,
sorry for the delay, i've been off work for a few days. i've tried you're
calculations for getting the '0' to display in my last weird query and it's
worked a treat- thank you.

the 'researcher' is just a field which - if there's an entry in it (from
1-9: the number of researchers) means the person has been visited at least
once for an information visit before recruitment.

this database is just far too complex and i've vocalised this to my manager
but to no effect. i really don't think a monster query will work given the
subtlety between the various queries i.e. for one, i need to count the number
of '4s' in a particular field and for another, i need to count the number of
'3s' in that same field.

i've more or less managed to get the report to look exactly as it was
requested - but i've used lots of 'invisible' subreports related to multiple
queries. the monster query would be much more efficient but i'm now being
asked to try and get other stuff out of the database which, again is far too
complex, so i'm going to try and move on!

your help has been brilliant - thank you so much!!


"Marshall Barton" wrote:


Assuming there can be more than one Recruitment entry for a
study number, you will need a different GROUP BY clause.
This menas that you need a second query to count the
Recruitment counts.

I'm kind of guessing at what you want to count here.
How does the Researcher fit into it?
Why do you only want Recearchers 1 through 9?

Anyway, let's start with:

SELECT Site, Consent
Sum(IIf(Recruitment.Researcher_1=9
) AS CountOfConsent
FROM Recruitment INNER JOIN Screened
ON Recruitment.StudyNumber = Screened.StudyNumber
GROUP BY Site, Consent

Once we get this straightened out, we'll try to figure out
how to combine it with the monster.

--
Marsh
MVP [MS Access]

Keep the idea or using a very few queries and one report
instead of lots of subreports with all their associated
queries in mind as you gain more experience.

And don't forget that the real underlying problem is that
you are trying to use database techniques on data that was
organized as a spreadsheet.

You have a very difficult situation and I wish you luck in
getting management to understand this.
--
Marsh
MVP [MS Access]


Emelina Bumsquash wrote:
sorry for the delay, i've been off work for a few days. i've tried you're
calculations for getting the '0' to display in my last weird query and it's
worked a treat- thank you.

the 'researcher' is just a field which - if there's an entry in it (from
1-9: the number of researchers) means the person has been visited at least
once for an information visit before recruitment.

this database is just far too complex and i've vocalised this to my manager
but to no effect. i really don't think a monster query will work given the
subtlety between the various queries i.e. for one, i need to count the number
of '4s' in a particular field and for another, i need to count the number of
'3s' in that same field.

i've more or less managed to get the report to look exactly as it was
requested - but i've used lots of 'invisible' subreports related to multiple
queries. the monster query would be much more efficient but i'm now being
asked to try and get other stuff out of the database which, again is far too
complex, so i'm going to try and move on!

your help has been brilliant - thank you so much!!


"Marshall Barton" wrote:
Assuming there can be more than one Recruitment entry for a
study number, you will need a different GROUP BY clause.
This menas that you need a second query to count the
Recruitment counts.

I'm kind of guessing at what you want to count here.
How does the Researcher fit into it?
Why do you only want Recearchers 1 through 9?

Anyway, let's start with:

SELECT Site, Consent
Sum(IIf(Recruitment.Researcher_1=9
) AS CountOfConsent
FROM Recruitment INNER JOIN Screened
ON Recruitment.StudyNumber = Screened.StudyNumber
GROUP BY Site, Consent

Once we get this straightened out, we'll try to figure out
how to combine it with the monster.