extracting totals and/or hiding subreports

[Guest]

Hi All,
i've always got such helpful replies from this so hope someone can help me
now!

I have a numerous simple reports that use =sum([amount]) to get a total of
the variable i'm interested in.

on one main report i only want the =sum([amount]) fields from each of the
other reports i have already designed. this is to make it into an easy,
one-page flowchart for reference.

I don't understand why i can't just lift the '=sum([total])' textbox fields
from other reports onto a main report to get the summary information i want.
at the moment, i am creating subreports within this main flowchart so that i
am able to create a text box that references the 'total' fields i'm after.
but this makes the main flowchart difficult to manage and takes up more than
one page with a lot of duplicate information.

is there a way to get a text box to reference fields that have been created
in another report without actually having to input that report as a
subreport?! either that or is there a way to completely hide a subreport so
that i can input it onto the main report and use the totals text box as above
but not have it muck up the formatting!

please help, it's doing my head in! PS apologies for the very long
description but i'm not very good at access so don't know how much
information someone else might need!

Cheers, Emma

 

[Marshall Barton]

i've always got such helpful replies from this so hope someone can help me
now!

I have a numerous simple reports that use =sum([amount]) to get a total of
the variable i'm interested in.

on one main report i only want the =sum([amount]) fields from each of the
other reports i have already designed. this is to make it into an easy,
one-page flowchart for reference.

I don't understand why i can't just lift the '=sum([total])' textbox fields
from other reports onto a main report to get the summary information i want.
at the moment, i am creating subreports within this main flowchart so that i
am able to create a text box that references the 'total' fields i'm after.
but this makes the main flowchart difficult to manage and takes up more than
one page with a lot of duplicate information.

is there a way to get a text box to reference fields that have been created
in another report without actually having to input that report as a
subreport?! either that or is there a way to completely hide a subreport so
that i can input it onto the main report and use the totals text box as above
but not have it muck up the formatting!

Rather than try to use an entire subreport to calculate a
single value, you should try to create a query that
calculates all the totals. Then your report should be very
simple.

If worse comes to worst, you can make your subreports
invisible (with CanShrink set to Yes). Then the main report
text boxes can use expressions like:
=subreportcontrol.Report.totaltextbox
to display the total from the subreport.

 

[Guest]

Hi Marshall - sorry i think i accidentally put 'No' to rate this reponse but
it was very helpful! although:

i don't think i can combine all the queries they are all separated by the
same variable so that in some cases the answer for site 1 might = 7 but in
other queries site 1 might = 10. (basically, i'm looking at various different
checkbox answers to different questions on a form but one of the constants in
the site at which that form originated so i always want to know how many
question a's were yes at site one and then to know how many question b's were
yes at site 1 etc.

do you think it would be possible to combine queries such as this? if not,
i'll try that 'can shrink' option

many thanks, Emma

:

 

[Marshall Barton]

It's difficult to tell if you can combine your queries
without more information about the queries. I think that if
your tables are properly normalized, combining them would
not be too difficult. OTOH, if your tables are just
spreadsheets, it could get messy. I might be able to
provide a more specific answer if you posted a few
representative queries (Copy/Paste the queries SQL view).

Have you tried to create a Totals type query that
sums/counts a bunch fields?

 

[Guest]

I think I've actually managed to get around the solution now by creating
slightly different queries however, they have a different fault which may be
easier to solve.

in these queries I am using the count function in the totals row, obviously
to count up the number of relevant checkboxes.

however when there are zero checkboxes for a given query it returns a blank
page but i'd like it to return a '0'... otherwise i just get '#error'
returned when i try to import this figure as a text box on the report.

can you get a txt box / query to report 0 instead of a blank? thanks again,
Emma

It's difficult to tell if you can combine your queries
without more information about the queries. I think that if
your tables are properly normalized, combining them would
not be too difficult. OTOH, if your tables are just
spreadsheets, it could get messy. I might be able to
provide a more specific answer if you posted a few
representative queries (Copy/Paste the queries SQL view).

Have you tried to create a Totals type query that
sums/counts a bunch fields?
--
Marsh
MVP [MS Access]

i don't think i can combine all the queries they are all separated by the
same variable so that in some cases the answer for site 1 might = 7 but in
other queries site 1 might = 10. (basically, i'm looking at various different
checkbox answers to different questions on a form but one of the constants in
the site at which that form originated so i always want to know how many
question a's were yes at site one and then to know how many question b's were
yes at site 1 etc.

do you think it would be possible to combine queries such as this? if not,
i'll try that 'can shrink' option

 

[Marshall Barton]

I think I've actually managed to get around the solution now by creating
slightly different queries however, they have a different fault which may be
easier to solve.

in these queries I am using the count function in the totals row, obviously
to count up the number of relevant checkboxes.

however when there are zero checkboxes for a given query it returns a blank
page but i'd like it to return a '0'... otherwise i just get '#error'
returned when i try to import this figure as a text box on the report.

can you get a txt box / query to report 0 instead of a blank?

It depends on how you are using the queries.

I don't understand why you are still using more than one
query. If you can explain the "new" arrangement, maybe I
(or someone) could figure it out.

 

[Guest]

I'll try and be as clear as possible!

I have a database form that has various yes/no criteria answered for various
participants. Each of the participants is recruited from 1 of 8 sites; so
everyone will fit into a 'site' class (1-8). My queries are very simple and
aim to count up the total number of 'yes' for the various criteria i.e. "show
me how many participants had normal speech and language"

a typical query is programmed as follows:

Group By - Site
Where - Speech&Language = Yes
Count - Speech&Language

The problem that I found is that I couldn't group all these various queries
as each 'Count' function returned different numbers across the 'Site'
grouping variable: there are some criteria for which site 3 has no 'Yes' but
other criteria where site 3 has 24 ‘yes’ etc.

Previously, I was creating the report using the queries described above and
then using a =sum text box in the subreports to get the totals which I was
reporting in the main report flowchart. However, what I’ve done now is copy
and duplicate the queries but removed the Group By – Site function so that
the query now just returns one number i.e the total across all sites. So now,
for each criteria, I have 2 queries: 1 that shows overall totals and 1 that
shows totals grouped by site.

I thought this would resolve the issue of allowing me to combine queries as
there should no be no discrepancy since I’ve removed the common ‘site’
variable from the equation. It was weird when I tried to do this – it started
letting me combine queries and showing all totals and then it would
temporarily fail.

So, rather than combine all the queries I’ve just kept them individually,
and used multiple subreports in my main report which I’ve then made
‘invisible’ so they don’t interfere with the page setup (I’m very
inexperienced and didn’t know how simple it was to do this until I started
playing around with it so that solved my setup problems)

I also got around the showing zero problem mentioned in my previous post
using the following paraphrased function where the subreport = Sub1 and the
Total textbox = txtTotal:

=IIf([Sub1].[Report].[HasData], [Sub1].[Report].[txtTotal], 0)

So, essentially, I’ve got around a lot of the issues that this post was
about. I’m not sure if it was the most straight-forward approach but it works
fine so I’m fairly happy. The only thing I would like some further advice on
are the queries described above which still use the ‘Site’ as a grouping
variable.

Ideally, I would like the query to return ‘0’ if there are no yes’s for a
given site. This would mean that the query would also show the site numbers
1-8 and the totals for each group, regardless of whether it was 0. Currently,
a query will return something like:

Site Count
1 43
2 37
4 2
6 15
8 17

So, it just misses out those sites where the count is ‘0’. Since I will be
using these queries in other reports (where site grouping is important) It
would be a lot better if the queries were uniform in that they all showed
sites 1-8 and the totals for each (even if that total count is 0)

I think these queries are very basic and simple so perhaps it’s just the
nature of them but do you know of any way I could use a formula similar to
the ‘IIf’ one shown above in a query? So that if there is ‘no data’ show ‘0’.
It would even be fine if I could use some formula on the report whereby I
could perhaps program the ‘Site’ variable to always show 1-8 and then the
‘count totals’ variable to show ‘0’ if there is no other data.

Is this possible? Apologies for the length of this but, given my
inexperience, I wanted to try and make it as clear as possible! Cheers, Emma

 

[Marshall Barton]

I'll try and be as clear as possible!

I have a database form that has various yes/no criteria answered for various
participants. Each of the participants is recruited from 1 of 8 sites; so
everyone will fit into a 'site' class (1-8). My queries are very simple and
aim to count up the total number of 'yes' for the various criteria i.e. "show
me how many participants had normal speech and language"

a typical query is programmed as follows:

Group By - Site
Where - Speech&Language = Yes
Count - Speech&Language

The problem that I found is that I couldn't group all these various queries
as each 'Count' function returned different numbers across the 'Site'
grouping variable: there are some criteria for which site 3 has no 'Yes' but
other criteria where site 3 has 24 ‘yes’ etc.

Previously, I was creating the report using the queries described above and
then using a =sum text box in the subreports to get the totals which I was
reporting in the main report flowchart. However, what I’ve done now is copy
and duplicate the queries but removed the Group By – Site function so that
the query now just returns one number i.e the total across all sites. So now,
for each criteria, I have 2 queries: 1 that shows overall totals and 1 that
shows totals grouped by site.

I thought this would resolve the issue of allowing me to combine queries as
there should no be no discrepancy since I’ve removed the common ‘site’
variable from the equation. It was weird when I tried to do this – it started
letting me combine queries and showing all totals and then it would
temporarily fail.

So, rather than combine all the queries I’ve just kept them individually,
and used multiple subreports in my main report which I’ve then made
‘invisible’ so they don’t interfere with the page setup (I’m very
inexperienced and didn’t know how simple it was to do this until I started
playing around with it so that solved my setup problems)

I also got around the showing zero problem mentioned in my previous post
using the following paraphrased function where the subreport = Sub1 and the
Total textbox = txtTotal:

=IIf([Sub1].[Report].[HasData], [Sub1].[Report].[txtTotal], 0)

So, essentially, I’ve got around a lot of the issues that this post was
about. I’m not sure if it was the most straight-forward approach but it works
fine so I’m fairly happy. The only thing I would like some further advice on
are the queries described above which still use the ‘Site’ as a grouping
variable.

Ideally, I would like the query to return ‘0’ if there are no yes’s for a
given site. This would mean that the query would also show the site numbers
1-8 and the totals for each group, regardless of whether it was 0. Currently,
a query will return something like:

Site Count
1 43
2 37
4 2
6 15
8 17

So, it just misses out those sites where the count is ‘0’. Since I will be
using these queries in other reports (where site grouping is important) It
would be a lot better if the queries were uniform in that they all showed
sites 1-8 and the totals for each (even if that total count is 0)

I think these queries are very basic and simple so perhaps it’s just the
nature of them but do you know of any way I could use a formula similar to
the ‘IIf’ one shown above in a query? So that if there is ‘no data’ show ‘0’.
It would even be fine if I could use some formula on the report whereby I
could perhaps program the ‘Site’ variable to always show 1-8 and then the
‘count totals’ variable to show ‘0’ if there is no other data.

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

 

[Guest]

Hi Marshall,
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!
Cheers, E

:

 

[Marshall Barton]

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]

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!

:

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

 

[Guest]

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 therefore:

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!

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]

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!

:

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

 

[Marshall Barton]

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 therefore:

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.

 

[Guest]

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

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 therefore:

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.

 

[Guest]

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]

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.

 

[Guest]

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!

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.

 

[Marshall Barton]

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.

 

[Guest]

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]

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.