Too much data?

  • Thread starter Thread starter Alex H
  • Start date Start date
A

Alex H

Hi, I am trying to create a report which provides analysis from our MIS
system.

The report covers 20 sections, each swction takes 4Cm of vertical space.
A total of 20 different queries are involved.

Each section is identical (apart from the data obviously) as I had started
to actually lay out the individual sections, and use label names to provide
the data from within code. However I have run out of space on the report
design form, so am rethinking my actionplan!

Any suggestions most gratefully received.

I had wondered if I could have just a single section defined within the
detail section and using unbound controls, and then somehow loop round
between the queries, but not sure if this is possible?

Thanks
Alex
 
I think you are making this harder than it needs to be. Why not base the
report on a single query? Then each of the 20 "sections" would be
represented by one instance of the detail section.
 
I wish I could - in order to get the information, ( which include time
elsapsed,averages, sums et al) it is necessary to have the 20 queries.

A
 
Alex

In an Access database, tables store data and reports (and forms) display it.
So everything starts with the tables and your data.

You've described "how" you are trying to solve an issue.

You've not described the "what" - what are you trying to accomplish?

You've explained that you are using 20 queries to return the values you
need. This is most unusual!

Start with the data and fill us in a bit more... An example of the
underlying data would help - we aren't there.
 
Ok sorry for not giving enough detail

i have an Access database which is used to track every aspect of a students
progress through the course we run.

I wanted to produce a report, that gives me a single source of Yearly
Performance Statistical Information for the current year and past four
years. This information includes the number of students starting the course
, number of modules they have completed, Average modules/student, average
marks per module/ average number of days taken etc etc - 17 sections in all.
Each section contains different information from different areas of the
database, and it is impossible to combine all of the data groupings and
averaging into a single query - it takes 20.

I set up a report using the detail area and the code below is an example for
a single section.

As I said, my problem was that each section takes 6 lines of space, and with
space top and bottom of each section = 4cm x 20 = 80 cms = too long for
single detail report.

I was trying to think of other ways in which this could be achieved, other
than as I have done at present by embedding a couple of sub-reports. I
suspect there is a better way of generating the report, and was wondering if
I could use unbound controls, and then look rounf in the detail section.

Any advice help suggestions much appreciated

Alex
==
Dim rs As Recordset
Dim e As Long
Dim x As Long
Dim y As Long
Dim Ycount As Long

Const MaxBarWidth = 567 * 7
Dim MaxValue As Double

'1. The number of students starting the course
Set rs = CurrentDb.OpenRecordset("qryYPA NumStarters", dbOpenSnapshot)
If Not rs.EOF Then
MaxValue = 0
Do Until rs.EOF
If rs![Value] > MaxValue Then MaxValue = rs![Value]
rs.MoveNext
Loop
x = 102
y = 0
e = 0
Ycount = -1
rs.MoveFirst
Do Until rs.EOF
If x = 102 Then
Me("Label" & x).Caption = "Year to date " & rs![Year]
Else
Me("Label" & x).Caption = "Academic year " & rs![Year]
End If
Me("AvLabel1").Caption = "Average previous years"
Me("Label" & x + 5).Caption = rs![Value]
If x = 102 Then
e = rs![Value]
End If
If x > 102 Then
y = y + rs![Value]
End If
Me("Box" & x + 10).Width = CLng((rs![Value] / MaxValue) *
MaxBarWidth)
Me("Box" & x + 10).Visible = True
rs.MoveNext
x = x + 1
Ycount = Ycount + 1
Loop
Me("BoxE1").Width = CLng(((e / (DateDiff("d", "1 aug " &
Str(Year(Date) - 1), Date)) * 365) / MaxValue) * MaxBarWidth)
Me("BoxE1").Visible = True
Me("BoxE1").Caption = Round((e / (DateDiff("d", "1 aug " &
Str(Year(Date) - 1), Date)) * 365), 0)
Me("AvLabel2").Caption = Round(y / Ycount, 0)
Me("AvBox3").Width = CLng(((y / Ycount) / MaxValue) * MaxBarWidth)
Me("AvBox3").Visible = True
End If
 
Alex

Sorry to be dense, but I'm still having trouble visualizing your data/data
structure. From what you've described so far, I don't see the need for 20
different sections.

Good luck

Jeff Boyce
<Access MVP>

Alex H said:
Ok sorry for not giving enough detail

i have an Access database which is used to track every aspect of a students
progress through the course we run.

I wanted to produce a report, that gives me a single source of Yearly
Performance Statistical Information for the current year and past four
years. This information includes the number of students starting the course
, number of modules they have completed, Average modules/student, average
marks per module/ average number of days taken etc etc - 17 sections in all.
Each section contains different information from different areas of the
database, and it is impossible to combine all of the data groupings and
averaging into a single query - it takes 20.

I set up a report using the detail area and the code below is an example for
a single section.

As I said, my problem was that each section takes 6 lines of space, and with
space top and bottom of each section = 4cm x 20 = 80 cms = too long for
single detail report.

I was trying to think of other ways in which this could be achieved, other
than as I have done at present by embedding a couple of sub-reports. I
suspect there is a better way of generating the report, and was wondering if
I could use unbound controls, and then look rounf in the detail section.

Any advice help suggestions much appreciated

Alex
==
Dim rs As Recordset
Dim e As Long
Dim x As Long
Dim y As Long
Dim Ycount As Long

Const MaxBarWidth = 567 * 7
Dim MaxValue As Double

'1. The number of students starting the course
Set rs = CurrentDb.OpenRecordset("qryYPA NumStarters", dbOpenSnapshot)
If Not rs.EOF Then
MaxValue = 0
Do Until rs.EOF
If rs![Value] > MaxValue Then MaxValue = rs![Value]
rs.MoveNext
Loop
x = 102
y = 0
e = 0
Ycount = -1
rs.MoveFirst
Do Until rs.EOF
If x = 102 Then
Me("Label" & x).Caption = "Year to date " & rs![Year]
Else
Me("Label" & x).Caption = "Academic year " & rs![Year]
End If
Me("AvLabel1").Caption = "Average previous years"
Me("Label" & x + 5).Caption = rs![Value]
If x = 102 Then
e = rs![Value]
End If
If x > 102 Then
y = y + rs![Value]
End If
Me("Box" & x + 10).Width = CLng((rs![Value] / MaxValue) *
MaxBarWidth)
Me("Box" & x + 10).Visible = True
rs.MoveNext
x = x + 1
Ycount = Ycount + 1
Loop
Me("BoxE1").Width = CLng(((e / (DateDiff("d", "1 aug " &
Str(Year(Date) - 1), Date)) * 365) / MaxValue) * MaxBarWidth)
Me("BoxE1").Visible = True
Me("BoxE1").Caption = Round((e / (DateDiff("d", "1 aug " &
Str(Year(Date) - 1), Date)) * 365), 0)
Me("AvLabel2").Caption = Round(y / Ycount, 0)
Me("AvBox3").Width = CLng(((y / Ycount) / MaxValue) * MaxBarWidth)
Me("AvBox3").Visible = True
End If





Jeff Boyce said:
Alex

In an Access database, tables store data and reports (and forms) display
it.
So everything starts with the tables and your data.

You've described "how" you are trying to solve an issue.

You've not described the "what" - what are you trying to accomplish?

You've explained that you are using 20 queries to return the values you
need. This is most unusual!

Start with the data and fill us in a bit more... An example of the
underlying data would help - we aren't there.

--
Good luck

Jeff Boyce
<Access MVP>
Click to expand...
Click to expand...
 
Jeff

Since the data source was different, I was assuming that I needed a section
for each query. I have got it working at moment by using multiple reports,
buts that not very elegant.

My initial idea was to have a single group head and detail section and then
keep looping through using code to change the data source - is that
possible?

Alex



Jeff Boyce said:
Alex

Sorry to be dense, but I'm still having trouble visualizing your data/data
structure. From what you've described so far, I don't see the need for 20
different sections.

Good luck

Jeff Boyce
<Access MVP>

Alex H said:
Ok sorry for not giving enough detail

i have an Access database which is used to track every aspect of a students
progress through the course we run.

I wanted to produce a report, that gives me a single source of Yearly
Performance Statistical Information for the current year and past four
years. This information includes the number of students starting the course
, number of modules they have completed, Average modules/student, average
marks per module/ average number of days taken etc etc - 17 sections in all.
Each section contains different information from different areas of the
database, and it is impossible to combine all of the data groupings and
averaging into a single query - it takes 20.

I set up a report using the detail area and the code below is an example for
a single section.

As I said, my problem was that each section takes 6 lines of space, and with
space top and bottom of each section = 4cm x 20 = 80 cms = too long for
single detail report.

I was trying to think of other ways in which this could be achieved,
other
than as I have done at present by embedding a couple of sub-reports. I
suspect there is a better way of generating the report, and was wondering if
I could use unbound controls, and then look rounf in the detail section.

Any advice help suggestions much appreciated

Alex
==
Dim rs As Recordset
Dim e As Long
Dim x As Long
Dim y As Long
Dim Ycount As Long

Const MaxBarWidth = 567 * 7
Dim MaxValue As Double

'1. The number of students starting the course
Set rs = CurrentDb.OpenRecordset("qryYPA NumStarters", dbOpenSnapshot)
If Not rs.EOF Then
MaxValue = 0
Do Until rs.EOF
If rs![Value] > MaxValue Then MaxValue = rs![Value]
rs.MoveNext
Loop
x = 102
y = 0
e = 0
Ycount = -1
rs.MoveFirst
Do Until rs.EOF
If x = 102 Then
Me("Label" & x).Caption = "Year to date " & rs![Year]
Else
Me("Label" & x).Caption = "Academic year " & rs![Year]
End If
Me("AvLabel1").Caption = "Average previous years"
Me("Label" & x + 5).Caption = rs![Value]
If x = 102 Then
e = rs![Value]
End If
If x > 102 Then
y = y + rs![Value]
End If
Me("Box" & x + 10).Width = CLng((rs![Value] / MaxValue) *
MaxBarWidth)
Me("Box" & x + 10).Visible = True
rs.MoveNext
x = x + 1
Ycount = Ycount + 1
Loop
Me("BoxE1").Width = CLng(((e / (DateDiff("d", "1 aug " &
Str(Year(Date) - 1), Date)) * 365) / MaxValue) * MaxBarWidth)
Me("BoxE1").Visible = True
Me("BoxE1").Caption = Round((e / (DateDiff("d", "1 aug " &
Str(Year(Date) - 1), Date)) * 365), 0)
Me("AvLabel2").Caption = Round(y / Ycount, 0)
Me("AvBox3").Width = CLng(((y / Ycount) / MaxValue) * MaxBarWidth)
Me("AvBox3").Visible = True
End If





message
Alex

In an Access database, tables store data and reports (and forms)
display
it.
So everything starts with the tables and your data.

You've described "how" you are trying to solve an issue.

You've not described the "what" - what are you trying to accomplish?

You've explained that you are using 20 queries to return the values you
need. This is most unusual!

Start with the data and fill us in a bit more... An example of the
underlying data would help - we aren't there.

--
Good luck

Jeff Boyce
<Access MVP>

Hi, I am trying to create a report which provides analysis from our MIS
system.

The report covers 20 sections, each swction takes 4Cm of vertical space.
A total of 20 different queries are involved.

Each section is identical (apart from the data obviously) as I had
started
to actually lay out the individual sections, and use label names to
provide
the data from within code. However I have run out of space on the report
design form, so am rethinking my actionplan!

Any suggestions most gratefully received.

I had wondered if I could have just a single section defined within
the
detail section and using unbound controls, and then somehow loop round
between the queries, but not sure if this is possible?

Thanks
Alex
Click to expand...
Click to expand...
Click to expand...
 
Alex

Possible, ?maybe. Necessary? -- perhaps not.

My request for more information was to help me consider if there might be a
better way than using multiple queries, multiple reports.

But hey! If you have it working...

Good luck

Jeff Boyce
<Access MVP>

Alex H said:
Jeff

Since the data source was different, I was assuming that I needed a section
for each query. I have got it working at moment by using multiple reports,
buts that not very elegant.

My initial idea was to have a single group head and detail section and then
keep looping through using code to change the data source - is that
possible?

Alex



Jeff Boyce said:
Alex

Sorry to be dense, but I'm still having trouble visualizing your data/data
structure. From what you've described so far, I don't see the need for 20
different sections.

Good luck

Jeff Boyce
<Access MVP>

Alex H said:
Ok sorry for not giving enough detail

i have an Access database which is used to track every aspect of a students
progress through the course we run.

I wanted to produce a report, that gives me a single source of Yearly
Performance Statistical Information for the current year and past four
years. This information includes the number of students starting the course
, number of modules they have completed, Average modules/student, average
marks per module/ average number of days taken etc etc - 17 sections in all.
Each section contains different information from different areas of the
database, and it is impossible to combine all of the data groupings and
averaging into a single query - it takes 20.

I set up a report using the detail area and the code below is an
Click to expand...
example
for
a single section.

As I said, my problem was that each section takes 6 lines of space, and with
space top and bottom of each section = 4cm x 20 = 80 cms = too long for
single detail report.

I was trying to think of other ways in which this could be achieved,
other
than as I have done at present by embedding a couple of sub-reports. I
suspect there is a better way of generating the report, and was
Click to expand...
wondering
if
I could use unbound controls, and then look rounf in the detail section.

Any advice help suggestions much appreciated

Alex
==
Dim rs As Recordset
Dim e As Long
Dim x As Long
Dim y As Long
Dim Ycount As Long

Const MaxBarWidth = 567 * 7
Dim MaxValue As Double

'1. The number of students starting the course
Set rs = CurrentDb.OpenRecordset("qryYPA NumStarters", dbOpenSnapshot)
If Not rs.EOF Then
MaxValue = 0
Do Until rs.EOF
If rs![Value] > MaxValue Then MaxValue = rs![Value]
rs.MoveNext
Loop
x = 102
y = 0
e = 0
Ycount = -1
rs.MoveFirst
Do Until rs.EOF
If x = 102 Then
Me("Label" & x).Caption = "Year to date " & rs![Year]
Else
Me("Label" & x).Caption = "Academic year " & rs![Year]
End If
Me("AvLabel1").Caption = "Average previous years"
Me("Label" & x + 5).Caption = rs![Value]
If x = 102 Then
e = rs![Value]
End If
If x > 102 Then
y = y + rs![Value]
End If
Me("Box" & x + 10).Width = CLng((rs![Value] / MaxValue) *
MaxBarWidth)
Me("Box" & x + 10).Visible = True
rs.MoveNext
x = x + 1
Ycount = Ycount + 1
Loop
Me("BoxE1").Width = CLng(((e / (DateDiff("d", "1 aug " &
Str(Year(Date) - 1), Date)) * 365) / MaxValue) * MaxBarWidth)
Me("BoxE1").Visible = True
Me("BoxE1").Caption = Round((e / (DateDiff("d", "1 aug " &
Str(Year(Date) - 1), Date)) * 365), 0)
Me("AvLabel2").Caption = Round(y / Ycount, 0)
Me("AvBox3").Width = CLng(((y / Ycount) / MaxValue) * MaxBarWidth)
Me("AvBox3").Visible = True
End If





message
Alex

In an Access database, tables store data and reports (and forms)
display
it.
So everything starts with the tables and your data.

You've described "how" you are trying to solve an issue.

You've not described the "what" - what are you trying to accomplish?

You've explained that you are using 20 queries to return the values you
need. This is most unusual!

Start with the data and fill us in a bit more... An example of the
underlying data would help - we aren't there.

--
Good luck

Jeff Boyce
<Access MVP>

Hi, I am trying to create a report which provides analysis from our MIS
system.

The report covers 20 sections, each swction takes 4Cm of vertical space.
A total of 20 different queries are involved.

Each section is identical (apart from the data obviously) as I had
started
to actually lay out the individual sections, and use label names to
provide
the data from within code. However I have run out of space on the report
design form, so am rethinking my actionplan!

Any suggestions most gratefully received.

I had wondered if I could have just a single section defined within
the
detail section and using unbound controls, and then somehow loop round
between the queries, but not sure if this is possible?

Thanks
Alex
Click to expand...
Click to expand...
Click to expand...
 
Hi Jeff

Whilst its working, I would love to get a more elegant solution that can be
expanded as required. Would you be prepared to look at mt report code is I
zipped it up and emails it to you?

Alex

Jeff Boyce said:
Alex

Possible, ?maybe. Necessary? -- perhaps not.

My request for more information was to help me consider if there might be
a
better way than using multiple queries, multiple reports.

But hey! If you have it working...

Good luck

Jeff Boyce
<Access MVP>

Alex H said:
Jeff

Since the data source was different, I was assuming that I needed a section
for each query. I have got it working at moment by using multiple reports,
buts that not very elegant.

My initial idea was to have a single group head and detail section and then
keep looping through using code to change the data source - is that
possible?

Alex



message
Alex

Sorry to be dense, but I'm still having trouble visualizing your data/data
structure. From what you've described so far, I don't see the need for 20
different sections.

Good luck

Jeff Boyce
<Access MVP>

Ok sorry for not giving enough detail

i have an Access database which is used to track every aspect of a
students
progress through the course we run.

I wanted to produce a report, that gives me a single source of Yearly
Performance Statistical Information for the current year and past four
years. This information includes the number of students starting the
course
, number of modules they have completed, Average modules/student, average
marks per module/ average number of days taken etc etc - 17 sections
in
all.
Each section contains different information from different areas of
the
database, and it is impossible to combine all of the data groupings
and
averaging into a single query - it takes 20.

I set up a report using the detail area and the code below is an example
for
a single section.

As I said, my problem was that each section takes 6 lines of space,
and
with
space top and bottom of each section = 4cm x 20 = 80 cms = too long
for
single detail report.

I was trying to think of other ways in which this could be achieved,
other
than as I have done at present by embedding a couple of sub-reports.
I
suspect there is a better way of generating the report, and was wondering
if
I could use unbound controls, and then look rounf in the detail section.

Any advice help suggestions much appreciated

Alex
==
Dim rs As Recordset
Dim e As Long
Dim x As Long
Dim y As Long
Dim Ycount As Long

Const MaxBarWidth = 567 * 7
Dim MaxValue As Double

'1. The number of students starting the course
Set rs = CurrentDb.OpenRecordset("qryYPA NumStarters", dbOpenSnapshot)
If Not rs.EOF Then
MaxValue = 0
Do Until rs.EOF
If rs![Value] > MaxValue Then MaxValue = rs![Value]
rs.MoveNext
Loop
x = 102
y = 0
e = 0
Ycount = -1
rs.MoveFirst
Do Until rs.EOF
If x = 102 Then
Me("Label" & x).Caption = "Year to date " & rs![Year]
Else
Me("Label" & x).Caption = "Academic year " & rs![Year]
End If
Me("AvLabel1").Caption = "Average previous years"
Me("Label" & x + 5).Caption = rs![Value]
If x = 102 Then
e = rs![Value]
End If
If x > 102 Then
y = y + rs![Value]
End If
Me("Box" & x + 10).Width = CLng((rs![Value] / MaxValue) *
MaxBarWidth)
Me("Box" & x + 10).Visible = True
rs.MoveNext
x = x + 1
Ycount = Ycount + 1
Loop
Me("BoxE1").Width = CLng(((e / (DateDiff("d", "1 aug " &
Str(Year(Date) - 1), Date)) * 365) / MaxValue) * MaxBarWidth)
Me("BoxE1").Visible = True
Me("BoxE1").Caption = Round((e / (DateDiff("d", "1 aug " &
Str(Year(Date) - 1), Date)) * 365), 0)
Me("AvLabel2").Caption = Round(y / Ycount, 0)
Me("AvBox3").Width = CLng(((y / Ycount) / MaxValue) *
MaxBarWidth)
Me("AvBox3").Visible = True
End If





message
Alex

In an Access database, tables store data and reports (and forms)
display
it.
So everything starts with the tables and your data.

You've described "how" you are trying to solve an issue.

You've not described the "what" - what are you trying to accomplish?

You've explained that you are using 20 queries to return the values you
need. This is most unusual!

Start with the data and fill us in a bit more... An example of the
underlying data would help - we aren't there.

--
Good luck

Jeff Boyce
<Access MVP>

Hi, I am trying to create a report which provides analysis from
our
MIS
system.

The report covers 20 sections, each swction takes 4Cm of vertical
space.
A total of 20 different queries are involved.

Each section is identical (apart from the data obviously) as I had
started
to actually lay out the individual sections, and use label names to
provide
the data from within code. However I have run out of space on the
report
design form, so am rethinking my actionplan!

Any suggestions most gratefully received.

I had wondered if I could have just a single section defined within
the
detail section and using unbound controls, and then somehow loop round
between the queries, but not sure if this is possible?

Thanks
Alex
Click to expand...
Click to expand...
Click to expand...
 
The newsgroups exist to give all readers the opportunity to post, respond,
say "hey, me too!", etc. I'd prefer not to take it off line.

Can you provide a description of your tables and data, please!?! What your
report does is based on your current data structure.

Jeff Boyce
<Access MVP>

Alex H said:
Hi Jeff

Whilst its working, I would love to get a more elegant solution that can be
expanded as required. Would you be prepared to look at mt report code is I
zipped it up and emails it to you?

Alex

Jeff Boyce said:
Alex

Possible, ?maybe. Necessary? -- perhaps not.

My request for more information was to help me consider if there might be
a
better way than using multiple queries, multiple reports.

But hey! If you have it working...

Good luck

Jeff Boyce
<Access MVP>

Alex H said:
Jeff

Since the data source was different, I was assuming that I needed a section
for each query. I have got it working at moment by using multiple reports,
buts that not very elegant.

My initial idea was to have a single group head and detail section and then
keep looping through using code to change the data source - is that
possible?

Alex



message
Alex

Sorry to be dense, but I'm still having trouble visualizing your data/data
structure. From what you've described so far, I don't see the need
Click to expand...
for
20
different sections.

Good luck

Jeff Boyce
<Access MVP>

Ok sorry for not giving enough detail

i have an Access database which is used to track every aspect of a
students
progress through the course we run.

I wanted to produce a report, that gives me a single source of Yearly
Performance Statistical Information for the current year and past four
years. This information includes the number of students starting the
course
, number of modules they have completed, Average modules/student, average
marks per module/ average number of days taken etc etc - 17 sections
in
all.
Each section contains different information from different areas of
the
database, and it is impossible to combine all of the data groupings
and
averaging into a single query - it takes 20.

I set up a report using the detail area and the code below is an example
for
a single section.

As I said, my problem was that each section takes 6 lines of space,
and
with
space top and bottom of each section = 4cm x 20 = 80 cms = too long
for
single detail report.

I was trying to think of other ways in which this could be achieved,
other
than as I have done at present by embedding a couple of sub-reports.
I
suspect there is a better way of generating the report, and was wondering
if
I could use unbound controls, and then look rounf in the detail section.

Any advice help suggestions much appreciated

Alex
==
Dim rs As Recordset
Dim e As Long
Dim x As Long
Dim y As Long
Dim Ycount As Long

Const MaxBarWidth = 567 * 7
Dim MaxValue As Double

'1. The number of students starting the course
Set rs = CurrentDb.OpenRecordset("qryYPA NumStarters", dbOpenSnapshot)
If Not rs.EOF Then
MaxValue = 0
Do Until rs.EOF
If rs![Value] > MaxValue Then MaxValue = rs![Value]
rs.MoveNext
Loop
x = 102
y = 0
e = 0
Ycount = -1
rs.MoveFirst
Do Until rs.EOF
If x = 102 Then
Me("Label" & x).Caption = "Year to date " & rs![Year]
Else
Me("Label" & x).Caption = "Academic year " & rs![Year]
End If
Me("AvLabel1").Caption = "Average previous years"
Me("Label" & x + 5).Caption = rs![Value]
If x = 102 Then
e = rs![Value]
End If
If x > 102 Then
y = y + rs![Value]
End If
Me("Box" & x + 10).Width = CLng((rs![Value] / MaxValue) *
MaxBarWidth)
Me("Box" & x + 10).Visible = True
rs.MoveNext
x = x + 1
Ycount = Ycount + 1
Loop
Me("BoxE1").Width = CLng(((e / (DateDiff("d", "1 aug " &
Str(Year(Date) - 1), Date)) * 365) / MaxValue) * MaxBarWidth)
Me("BoxE1").Visible = True
Me("BoxE1").Caption = Round((e / (DateDiff("d", "1 aug " &
Str(Year(Date) - 1), Date)) * 365), 0)
Me("AvLabel2").Caption = Round(y / Ycount, 0)
Me("AvBox3").Width = CLng(((y / Ycount) / MaxValue) *
MaxBarWidth)
Me("AvBox3").Visible = True
End If





message
Alex

In an Access database, tables store data and reports (and forms)
display
it.
So everything starts with the tables and your data.

You've described "how" you are trying to solve an issue.

You've not described the "what" - what are you trying to accomplish?

You've explained that you are using 20 queries to return the
Click to expand...
values
you
need. This is most unusual!

Start with the data and fill us in a bit more... An example of the
underlying data would help - we aren't there.

--
Good luck

Jeff Boyce
<Access MVP>

Hi, I am trying to create a report which provides analysis from
our
MIS
system.

The report covers 20 sections, each swction takes 4Cm of vertical
space.
A total of 20 different queries are involved.

Each section is identical (apart from the data obviously) as I had
started
to actually lay out the individual sections, and use label names to
provide
the data from within code. However I have run out of space on the
report
design form, so am rethinking my actionplan!

Any suggestions most gratefully received.

I had wondered if I could have just a single section defined within
the
detail section and using unbound controls, and then somehow loop round
between the queries, but not sure if this is possible?

Thanks
Alex
Click to expand...
Click to expand...
Click to expand...
 
Ok fully understand, I'll try and explain - apologies for it being a little
long.



We run a distance learning education course and I want to analyse our
performance over the past five academic year by 18 different criteria.



There are basically two tables, one containing student data, and the other
contains details of each module they have undertaken. The course is split
into two Phases, 1 and 2, and in addition credits are awarded for each
module at different levels - Level 0 and Level 1



For each criterion, I need to see the criteria value for the past four
years, the average criteria for those four years, and the current criteria
value to date in the current academic year. For luck I have added a
straight line forecast for the full current year, based on achievement to
date.



The criteria vary:

The number of students enrolled.

The number of modules started

The number of modules completed

The average number of modules started per student

The average number of modules complete per student

The number of level 0 credit awarded

The number of Level 1 credit awarded

The average number of level 0 credits per student

And so on. There are 18 in total.



I have defined a function called academic year, which basically allows me to
group each set of data based on the start and finish dates of each academic
year.



I have created a total of 18 queries which obtain the data for me. I have
created variable names in the report, which are the same as far possible for
each query



I would rather not have 18 separate reports, since the detail section of
each report would only be six lines of data. (the previous four years, the
average plus the current year. The data displayed is simply the year, the
value, and a bar representing the value, ie three control per line, 18
controls in total per criteria



I had hoped that I would be able to create a report that allowed me to
define within the detail area 18 unbound controls, and then using code loop
through the various queries to display the data for each criterion.



At present what I have done is to define the controls for each criteria, and
I get five per page, and have create multiple reports, with subreports.



The following is an example of the code I am using for each criteria:

'1. The number of students starting the course

Set rs = CurrentDb.OpenRecordset("qryYPA NumStarters", dbOpenSnapshot)

If Not rs.EOF Then

MaxValue = 0

Do Until rs.EOF

If rs![Value] > MaxValue Then MaxValue = rs![Value]

rs.MoveNext

Loop

x = 102

y = 0

ycount = -1

rs.MoveFirst

Do Until rs.EOF

If x = 102 Then

Me("Label" & x).Caption = "Year to date " & rs![Year]

Else

Me("Label" & x).Caption = "Academic year " & rs![Year]

End If

Me("AvLabel1").Caption = "Average previous years"

Me("Label" & x + 5).Caption = rs![Value]

If x = 102 Then e = rs![Value]

If x > 102 Then y = y + rs![Value]

Me("Box" & x + 10).Width = CLng((rs![Value] / MaxValue) *
MaxBarWidth)

Me("Box" & x + 10).Visible = True

rs.MoveNext

x = x + 1

ycount = ycount + 1

Loop

Me("BoxE1").Width = CLng(((e / (DateDiff("d", "1 aug " &
Str(Year(Date) - 1), Date)) * 365) / MaxValue) * MaxBarWidth)

Me("BoxE1").Visible = True

Me("BoxE1").Caption = Round((e / (DateDiff("d", "1 aug " &
Str(Year(Date) - 1), Date)) * 365), 0)

Me("AvLabel2").Caption = Round(y / 4, 0)

Me("AvBox3").Width = CLng(((y / 4) / MaxValue) * MaxBarWidth)

Me("AvBox3").Visible = True

End If





I am sure that what I have done is not the best way to approach the problem
and was looking to see if anyone could come up with a better solution.



Apologies once again for the length



Alex







Jeff Boyce said:
The newsgroups exist to give all readers the opportunity to post, respond,
say "hey, me too!", etc. I'd prefer not to take it off line.

Can you provide a description of your tables and data, please!?! What
your
report does is based on your current data structure.

Jeff Boyce
<Access MVP>

Alex H said:
Hi Jeff

Whilst its working, I would love to get a more elegant solution that can be
expanded as required. Would you be prepared to look at mt report code is I
zipped it up and emails it to you?

Alex

message
Alex

Possible, ?maybe. Necessary? -- perhaps not.

My request for more information was to help me consider if there might be
a
better way than using multiple queries, multiple reports.

But hey! If you have it working...

Good luck

Jeff Boyce
<Access MVP>

Jeff

Since the data source was different, I was assuming that I needed a
section
for each query. I have got it working at moment by using multiple
reports,
buts that not very elegant.

My initial idea was to have a single group head and detail section and
then
keep looping through using code to change the data source - is that
possible?

Alex



message
Alex

Sorry to be dense, but I'm still having trouble visualizing your
data/data
structure. From what you've described so far, I don't see the need for
20
different sections.

Good luck

Jeff Boyce
<Access MVP>

Ok sorry for not giving enough detail

i have an Access database which is used to track every aspect of a
students
progress through the course we run.

I wanted to produce a report, that gives me a single source of Yearly
Performance Statistical Information for the current year and past four
years. This information includes the number of students starting the
course
, number of modules they have completed, Average modules/student,
average
marks per module/ average number of days taken etc etc - 17
sections
in
all.
Each section contains different information from different areas of
the
database, and it is impossible to combine all of the data groupings
and
averaging into a single query - it takes 20.

I set up a report using the detail area and the code below is an
example
for
a single section.

As I said, my problem was that each section takes 6 lines of space,
and
with
space top and bottom of each section = 4cm x 20 = 80 cms = too long
for
single detail report.

I was trying to think of other ways in which this could be
achieved,
other
than as I have done at present by embedding a couple of
sub-reports.
I
suspect there is a better way of generating the report, and was
wondering
if
I could use unbound controls, and then look rounf in the detail
section.

Any advice help suggestions much appreciated

Alex
==
Dim rs As Recordset
Dim e As Long
Dim x As Long
Dim y As Long
Dim Ycount As Long

Const MaxBarWidth = 567 * 7
Dim MaxValue As Double

'1. The number of students starting the course
Set rs = CurrentDb.OpenRecordset("qryYPA NumStarters",
dbOpenSnapshot)
If Not rs.EOF Then
MaxValue = 0
Do Until rs.EOF
If rs![Value] > MaxValue Then MaxValue = rs![Value]
rs.MoveNext
Loop
x = 102
y = 0
e = 0
Ycount = -1
rs.MoveFirst
Do Until rs.EOF
If x = 102 Then
Me("Label" & x).Caption = "Year to date " & rs![Year]
Else
Me("Label" & x).Caption = "Academic year " & rs![Year]
End If
Me("AvLabel1").Caption = "Average previous years"
Me("Label" & x + 5).Caption = rs![Value]
If x = 102 Then
e = rs![Value]
End If
If x > 102 Then
y = y + rs![Value]
End If
Me("Box" & x + 10).Width = CLng((rs![Value] / MaxValue) *
MaxBarWidth)
Me("Box" & x + 10).Visible = True
rs.MoveNext
x = x + 1
Ycount = Ycount + 1
Loop
Me("BoxE1").Width = CLng(((e / (DateDiff("d", "1 aug " &
Str(Year(Date) - 1), Date)) * 365) / MaxValue) * MaxBarWidth)
Me("BoxE1").Visible = True
Me("BoxE1").Caption = Round((e / (DateDiff("d", "1 aug " &
Str(Year(Date) - 1), Date)) * 365), 0)
Me("AvLabel2").Caption = Round(y / Ycount, 0)
Me("AvBox3").Width = CLng(((y / Ycount) / MaxValue) *
MaxBarWidth)
Me("AvBox3").Visible = True
End If





message
Alex

In an Access database, tables store data and reports (and forms)
display
it.
So everything starts with the tables and your data.

You've described "how" you are trying to solve an issue.

You've not described the "what" - what are you trying to accomplish?

You've explained that you are using 20 queries to return the values
you
need. This is most unusual!

Start with the data and fill us in a bit more... An example of the
underlying data would help - we aren't there.

--
Good luck

Jeff Boyce
<Access MVP>

Hi, I am trying to create a report which provides analysis from
our
MIS
system.

The report covers 20 sections, each swction takes 4Cm of
vertical
space.
A total of 20 different queries are involved.

Each section is identical (apart from the data obviously) as I had
started
to actually lay out the individual sections, and use label names to
provide
the data from within code. However I have run out of space on the
report
design form, so am rethinking my actionplan!

Any suggestions most gratefully received.

I had wondered if I could have just a single section defined within
the
detail section and using unbound controls, and then somehow loop
round
between the queries, but not sure if this is possible?

Thanks
Alex
Click to expand...
Click to expand...
Click to expand...
 
See comments in-line...

Steve J said:
Ok fully understand, I'll try and explain - apologies for it being a little
long.



We run a distance learning education course and I want to analyse our
performance over the past five academic year by 18 different criteria.



There are basically two tables, one containing student data, and the other
contains details of each module they have undertaken. The course is split
into two Phases, 1 and 2, and in addition credits are awarded for each
module at different levels - Level 0 and Level 1
Click to expand...

It seems to me that you have students, modules, and
modules-taken-by-students -- that would be three tables, not two.
For each criterion, I need to see the criteria value for the past four
years, the average criteria for those four years, and the current criteria
value to date in the current academic year. For luck I have added a
straight line forecast for the full current year, based on achievement to
date.



The criteria vary:

The number of students enrolled.

The number of modules started

The number of modules completed

The average number of modules started per student

The average number of modules complete per student

The number of level 0 credit awarded

The number of Level 1 credit awarded

The average number of level 0 credits per student

And so on. There are 18 in total.



I have defined a function called academic year, which basically allows me to
group each set of data based on the start and finish dates of each academic
year.



I have created a total of 18 queries which obtain the data for me. I have
created variable names in the report, which are the same as far possible for
each query



I would rather not have 18 separate reports, since the detail section of
each report would only be six lines of data. (the previous four years, the
average plus the current year. The data displayed is simply the year, the
value, and a bar representing the value, ie three control per line, 18
controls in total per criteria
Click to expand...

If you have 18 separate queries to get your data (?criteria), you need 18
reports (general rule of thumb). However, you could create one more (empty)
report and embed the ones you already have as subreports. (hmmm, this
thread's gone on so long I forget, have you already tried that?)
I had hoped that I would be able to create a report that allowed me to
define within the detail area 18 unbound controls, and then using code loop
through the various queries to display the data for each criterion.



At present what I have done is to define the controls for each criteria, and
I get five per page, and have create multiple reports, with subreports.



The following is an example of the code I am using for each criteria:

'1. The number of students starting the course

Set rs = CurrentDb.OpenRecordset("qryYPA NumStarters", dbOpenSnapshot)

If Not rs.EOF Then

MaxValue = 0

Do Until rs.EOF

If rs![Value] > MaxValue Then MaxValue = rs![Value]

rs.MoveNext

Loop

x = 102

y = 0

ycount = -1

rs.MoveFirst

Do Until rs.EOF

If x = 102 Then

Me("Label" & x).Caption = "Year to date " & rs![Year]

Else

Me("Label" & x).Caption = "Academic year " & rs![Year]

End If

Me("AvLabel1").Caption = "Average previous years"

Me("Label" & x + 5).Caption = rs![Value]

If x = 102 Then e = rs![Value]

If x > 102 Then y = y + rs![Value]

Me("Box" & x + 10).Width = CLng((rs![Value] / MaxValue) *
MaxBarWidth)

Me("Box" & x + 10).Visible = True

rs.MoveNext

x = x + 1

ycount = ycount + 1

Loop

Me("BoxE1").Width = CLng(((e / (DateDiff("d", "1 aug " &
Str(Year(Date) - 1), Date)) * 365) / MaxValue) * MaxBarWidth)

Me("BoxE1").Visible = True

Me("BoxE1").Caption = Round((e / (DateDiff("d", "1 aug " &
Str(Year(Date) - 1), Date)) * 365), 0)

Me("AvLabel2").Caption = Round(y / 4, 0)

Me("AvBox3").Width = CLng(((y / 4) / MaxValue) * MaxBarWidth)

Me("AvBox3").Visible = True

End If





I am sure that what I have done is not the best way to approach the problem
and was looking to see if anyone could come up with a better solution.
Click to expand...

I suppose the bottom line is "if it works now...<g>!"
 
Hi Jeff

Thanks for all your help and comments

As you say, if it aint broke !! Think I'll put this to one side for the
time being

A


Jeff Boyce said:
See comments in-line...

Steve J said:
Ok fully understand, I'll try and explain - apologies for it being a little
long.



We run a distance learning education course and I want to analyse our
performance over the past five academic year by 18 different criteria.



There are basically two tables, one containing student data, and the
other
contains details of each module they have undertaken. The course is
split
into two Phases, 1 and 2, and in addition credits are awarded for each
module at different levels - Level 0 and Level 1
Click to expand...

It seems to me that you have students, modules, and
modules-taken-by-students -- that would be three tables, not two.
For each criterion, I need to see the criteria value for the past four
years, the average criteria for those four years, and the current
criteria
value to date in the current academic year. For luck I have added a
straight line forecast for the full current year, based on achievement to
date.



The criteria vary:

The number of students enrolled.

The number of modules started

The number of modules completed

The average number of modules started per student

The average number of modules complete per student

The number of level 0 credit awarded

The number of Level 1 credit awarded

The average number of level 0 credits per student

And so on. There are 18 in total.



I have defined a function called academic year, which basically allows me to
group each set of data based on the start and finish dates of each academic
year.



I have created a total of 18 queries which obtain the data for me. I
have
created variable names in the report, which are the same as far possible for
each query



I would rather not have 18 separate reports, since the detail section of
each report would only be six lines of data. (the previous four years,
the
average plus the current year. The data displayed is simply the year,
the
value, and a bar representing the value, ie three control per line, 18
controls in total per criteria
Click to expand...

If you have 18 separate queries to get your data (?criteria), you need 18
reports (general rule of thumb). However, you could create one more
(empty)
report and embed the ones you already have as subreports. (hmmm, this
thread's gone on so long I forget, have you already tried that?)
I had hoped that I would be able to create a report that allowed me to
define within the detail area 18 unbound controls, and then using code loop
through the various queries to display the data for each criterion.



At present what I have done is to define the controls for each criteria, and
I get five per page, and have create multiple reports, with subreports.



The following is an example of the code I am using for each criteria:

'1. The number of students starting the course

Set rs = CurrentDb.OpenRecordset("qryYPA NumStarters", dbOpenSnapshot)

If Not rs.EOF Then

MaxValue = 0

Do Until rs.EOF

If rs![Value] > MaxValue Then MaxValue = rs![Value]

rs.MoveNext

Loop

x = 102

y = 0

ycount = -1

rs.MoveFirst

Do Until rs.EOF

If x = 102 Then

Me("Label" & x).Caption = "Year to date " & rs![Year]

Else

Me("Label" & x).Caption = "Academic year " & rs![Year]

End If

Me("AvLabel1").Caption = "Average previous years"

Me("Label" & x + 5).Caption = rs![Value]

If x = 102 Then e = rs![Value]

If x > 102 Then y = y + rs![Value]

Me("Box" & x + 10).Width = CLng((rs![Value] / MaxValue) *
MaxBarWidth)

Me("Box" & x + 10).Visible = True

rs.MoveNext

x = x + 1

ycount = ycount + 1

Loop

Me("BoxE1").Width = CLng(((e / (DateDiff("d", "1 aug " &
Str(Year(Date) - 1), Date)) * 365) / MaxValue) * MaxBarWidth)

Me("BoxE1").Visible = True

Me("BoxE1").Caption = Round((e / (DateDiff("d", "1 aug " &
Str(Year(Date) - 1), Date)) * 365), 0)

Me("AvLabel2").Caption = Round(y / 4, 0)

Me("AvBox3").Width = CLng(((y / 4) / MaxValue) * MaxBarWidth)

Me("AvBox3").Visible = True

End If





I am sure that what I have done is not the best way to approach the problem
and was looking to see if anyone could come up with a better solution.
Click to expand...

I suppose the bottom line is "if it works now...<g>!"

--
Good luck

Jeff Boyce
<Access MVP>
Click to expand...
 
Back
Top