Question Am i doing the Optimal Solution for a summary page? (1 Viewer)

[nofeara]

Hello folks!,

uhm i am building an enrollment system in my school and i have created a report which gives the summary of enrollment. Specifically it will provide the summary of male,female,foreign,local,new,regular, as well as the total number of enrollment per level per department in our school. The report i am making contains 204 unbound text boxes[uniquely provides information needed] that will be populated by a vba on load. And because of this i created 188 queries for this to function, so this report will be populated on load using the codes below:

[I don't have 10 post yet: but i hope u understand the link of the code:]

hypertexttransferprotocols://www[DOT]dropbox[DOT]com/s/g4crdar45flkxlp/CodesForReport.txt?dl=0

Report output:

hypertexttransferprotocols://www[DOT]dropbox[DOT]com/s/l95c9ff0uycguym/rptEnrollmentESummary.pdf?dl=0

Now because of this, it will take me approximately 20 secs to load and finish the report. It uses approximately 40mb now in memory. I also notice a database opening slowdown (i cant determine the exact quantity, just a slowdown from before). And base from the report we have just 21 enrollees, what more if it will rise?

[When i was doing the query and code for the English Department Summary part, i t was just 3 secs.(i compared the recordset recordcount to dcount(dcount is was way slower, 11 secs, so no way that i will use d count)]

Now that the overallall loading is 20 secs, am i still doing the optimal solution with my code and queries?

Is there better solution?

Btw, i'm using MS Access 2010, the database is not yet split, plan to split yes,

sorry for the noob post, i wish some replies. tnx in advance.

[sorry for forcing to post links without minimum requirements, dont worry i will meet them soon.]

 

[stopher]

Looking at your report I would expect this information to be easily derived in a very small number of queries. Your approach seems very wrong. But without knowing your data structure I can't comment much more.

 

[nofeara]

Hello Sir, thank you very much for the reply.

Uhm, what is that Data Structure you are talking about? sorry, i am actually a noob trying to enter into your world, i build this database copying the idea from time and billing template of microsoft in invoicing.

btw here is the copy of the database:
hypertexttransferprotocols://www[DOT]dropbox[DOT]com/s/2gn6ltz119uvmgn/forAssessment_2015-05-03_%281%29.accdb?dl=0

(for now i wanna how to make that very wrong, right, focus on efficient way to create that report., but if it will take to recreate some parts of that db again, or all its ok. Anyway i am doing the database for free in the school. That 20secs to generate that report is way 10000000% faster and cooler than their manual habit of making reports.)

[Uhm so, just some snapshot: If a student makes a transaction and he pays an enrollment fee, he will be in the list for enrollment, then i will enroll them put their enrollment date, their level enrolled in english and/or chinese, etc.]

 

[jdraw]

You can post an attachment with fewer than 10 posts if you zip your file(s).

I agree with stopher in that your approach seems wrong/strange, but we don't know the
specifications, nor details of your environment.

Your description of work to date

 [COLOR="Purple"][I]contains 204 unbound text boxes[uniquely provides information needed] that will be populated
 by a vba on load. And because of this i created 188 queries for this[/I][/COLOR]

raises a lot of red flags.

 

[nofeara]

You can post an attachment with fewer than posts if you zip your file(s).

I agree with stopher in that your approach seems wrong/strange, but we don't know the
specifications, nor details of your environment.

Your description of work to date

 [COLOR="Purple"][I]contains 204 unbound text boxes[uniquely provides information needed] that will be populated
 by a vba on load. And because of this i created 188 queries for this[/I][/COLOR]

raises a lot of red flags.

so uhm can you tell me that specifications/details that you needed to know?

 

[jdraw]

Did someone give you a list of things that this data base is to support? What info did you use to develop the database?
It isn't so much the specs/details I need, it is the "plan" you needed to build the database.
You are asking readers if your solution is optimal, but we don't know the problem description.

If your solution/database supports the business in the eyes of those who requested the database, then you have a solution. Your approach to solution is rare and probably not optimal -- but if it works satisfactorily, go with it.

If you want a tutorial on database design, here is one that you can work through in 30-45 minutes and you will learn.

Here is a link to a database specifications template -- you can pick and choose what is relevant to your situation.

Here is a link to a Checklist for developing a database specification.

Good luck.

 

[nofeara]

Did someone give you a list of things that this data base is to support?

Uhm, actually for now, this is just an initiative of mine making a database of our school so that we can track things in our enrollment instead of making manual logs, so that we know what is the exact amount that came in and for what, how many enrolled to this level, who are the new/regular. etc..

What info did you use to develop the database?

We'll i based from my experience handling enrollment(2 school years), that i am very tired of making a provisional receipt, my hands is wet my penmanship is not good. That i need to know how much did the parent paid for enrollment fees alone, books, school materials, etc. That i need to know how much is the balance of a particular student,what he paid, when he paid. I need to keep track of school uniform orders. That i need to provide a summary to the principal regarding the enrollment, compact but as detailed as possible.

It isn't so much the specs/details I need, it is the "plan" you needed to build the database.

And actually i admit that the plan is crazy, i'm making it crazy. That is :the database must be very flexible that if i change things or they want to know things it can help me provide it. So basically i am the creator of the database based on what i think is needed.

You are asking readers if your solution is optimal, but we don't know the problem description.

uhm ok, i'll just trim it down. Uhm as you can see i have those codes, there quite long and i want to know if there is better to build those like:

Dim rscountmaleENursery As DAO.Recordset
Set rscountmaleENursery = CurrentDb.OpenRecordset("qryEnrolledENurseryMale")
If rscountmaleENursery.RecordCount <> 0 Then
    rscountmaleENursery.MoveLast
End If
countmaleENursery.Value = rscountmaleENursery.RecordCount
rscountmaleENursery.Close

can i open that same recordset by just opening "qryEnrolleeDetails" who has that same information but we just need to filter out the level to "Nursery" and Gender to "Male"?

I also created that "qryEnrolledENurseryMale" because i want when i click that number in the report i will know who are male enrolled in nursery in the english department.

Now is there a way that i will just refer into "qryEnrolleeDetails" and i can provide or populate those 204 unbound boxes?

Thank You very much.

Btw. uhm i worked in a different computer(faster) and i was able to open the report in 3 secs.

 

[jdraw]

Did you look at the links? The tutorial will help you with tables and design.
Th others may help you organize your approach and level of detail.

Good luck with your project.

 

[JLCantara]

Hi Nofeara!

You will find, as an attached document, a solution to your problem: the job is done using ten queries and a standard report (no vba needed!!!).
You will also noticed a rather severe modification of your tables because you had too many useless ones. Remember that a unique property should always be store with the record it characterize. Thus the Students table is almost complete (I have omitted the financial stuff...). Of course, the record size is something to keep in mind: so one has to find proper balance between theory and practical considerations...

Good work, JLCantara.

 

[nofeara]

Thank You very much for the help. I am looking into your solutions and suggestions but uhm i got something in mind, and it uses only 1 query, without using vba. If it works, i'll post it here. But right now, i got a problem with this: DLookup with 3 multiple numeric criteria, xD.