Re: Too much data?
- From: "Jeff Boyce" <JeffBoyce_IF@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 22 Apr 2005 07:02:55 -0700
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" <somone@xxxxxxxxxxxxx> wrote in message
news:ehlrQ5mRFHA.3560@xxxxxxxxxxxxxxxxxxxxxxx
> 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" <JeffBoyce_IF@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:uSvpydmRFHA.3988@xxxxxxxxxxxxxxxxxxxxxxx
> > 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" <somone@xxxxxxxxxxxxx> wrote in message
> > news:OprNIGmRFHA.1236@xxxxxxxxxxxxxxxxxxxxxxx
> >> 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" <JeffBoyce_IF@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
> >> message
> >> news:urmu0jaRFHA.2384@xxxxxxxxxxxxxxxxxxxxxxx
> >> > 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" <somone@xxxxxxxxxxxxx> wrote in message
> >> > news:e8NlY$BRFHA.904@xxxxxxxxxxxxxxxxxxxxxxx
> >> >> 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" <JeffBoyce_IF@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
> >> >> message
> >> >> news:%23MmWSRBRFHA.3880@xxxxxxxxxxxxxxxxxxxxxxx
> >> >> > 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>
> >> >> >
> >> >> > "Alex H" <APH@xxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> >> > news:O7UyXz3QFHA.1476@xxxxxxxxxxxxxxxxxxxxxxx
> >> >> >> 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
> >> >> >>
> >> >> >>
> >> >> >
> >> >>
> >> >>
> >> >
> >>
> >>
> >
>
>
.
- Follow-Ups:
- Re: Too much data?
- From: Steve J
- Re: Too much data?
- References:
- Too much data?
- From: Alex H
- Re: Too much data?
- From: Jeff Boyce
- Re: Too much data?
- From: Alex H
- Re: Too much data?
- From: Jeff Boyce
- Re: Too much data?
- From: Alex H
- Re: Too much data?
- From: Jeff Boyce
- Re: Too much data?
- From: Alex H
- Too much data?
- Prev by Date: Re: custom report name?
- Next by Date: Re: Combo box criteria for report
- Previous by thread: Re: Too much data?
- Next by thread: Re: Too much data?
- Index(es):
Relevant Pages
|
