Jump to content

Recommended Posts

Goobz

Good day everyone,

 

So I have decided I have gone way over my head with this project. I have learned a lot from reading these forums. So here it goes:

 

I am trying to pull a report from approx. 114 different tables. Yup I said 114. I am building a database for my local Girl Guide group. The Report is going to show the girl's name, Imis#, and each badge name and weather they have completed it or not. The main heading will be the girl's name. The badge/award name will be sub-headings. Under the sub-heading I want to know if it is completed but not awarded. The Imis# is just going to be on the report somewhere. Also, I only want to see the records that have a completed date but not an awarded date. I don't want the badge name to show up if the badge dates don't follow the information provided.

 

I am sure I am going to have to run a script to find the proper field. But I can't get the layout of the report to even work out correctly. Any help would be greatly appreciated.

Share this post


Link to post
Share on other sites
eos
approx. 114 different tables. Yup I said 114.

 

I'm assuming you created one table per girl scout (I can't imagine how else you'd reach that count; and why "approx."?).

 

That is NOT the way to do it, and if you don't fix that immediately, reporting will be the least of your problems in the long run.

 

I suggest you first tell us what these tables are about; then we can tell you how to consolidate them. Then we can talk about reporting.

Share this post


Link to post
Share on other sites
Goobz

No I didn't use one table for each girl, that would be stupid since we have 200 girls active at this point. Here are some of the tables: GirlInformation, GuideYouInGuiding, GuideUnderstandPromise, GuideCanadianGuiding, GuidePeace, GuideHighOnLife. The table GirlInformation is the main relationship, it holds the girls' id and all the badges are the other tables.

 

Hopefully that is enough info. If not let me know.

Share this post


Link to post
Share on other sites
eos
No I didn't use one table for each girl, that would be stupid since we have 200 girls active at this point.

 

I wouldn't call it “stupid”; rather “uninformed”, if the person(s) who created that database just didn't know better. And that's on general principle, not because of a specific number of entities … (or are you saying it is stupid because ~114

 

Anyway: if each of these tables holds the information about one particular girl/badgeType combination (that means you have around 113 badgeTypes?), you have a structure like this:

 

Girls --

. . . .--

. . . .--

. . . .--

 

What you should have instead is:

 

Girls ---- BadgeTypes

 

where each record is the combination of a girl, a badgeType, status and all the other attributes of a girl/badge combination (dateCompleted/dateAwarded/season/whatever); remember the remark about consolidation?

 

Base your report layout on GirlsBadges; you can search that table by girl, date, year, season, badgeType, status (actually any field from any of these three tables!), and sort/group however you wish.

 

This also means that when add a new badgeType, you don't need to add a new table, but just a new BadgeType record.

Share this post


Link to post
Share on other sites
Goobz

Ok I will start again.

 

Hi my name is Wendy I am a Girl Guide leader in Canada. I am developing a piece of software for my district which at any given time can have over 100 girls registered. I have many tables I am dealing with to track the girls badges they have earned. Here is an example of what a badge table looks like:

 

Badge.png

 

Each badge has their own table because at any given time the badge can be changed. From how many requirements to taking it out of the kids program all together. The OptionTotal is added from the Option#, when it retches a certain number the CompletedDate will be auto entered. It is easier to manage with multiple tables then having one table that has over 1000 fields. Each badge table is in a relationship with the GirlInformation table, which contains the fields:

 

Birthday

CurrentBranch

CurrentUnit

EmergContactAddress

EmergContactName

EmergCntactPhone

GirlInformationAddress

GirlInformationBrownieYears

GirlInformationGuideYears

GirlInformationId (primary key)

GirlInformationImage

GirlInformationLeaderYears

GirlInformationName

GirlInformationOtherPhone

GirlInformationPathfinderYears

GirlInformationPhone

GirlInformationRangerYears

GirlInformationSparkYears

GirlInformationTotalYears

ImisNo

Notes

Status (either active or not active)

 

The purpose of this database is to track the girl's progress from the time they enter Guiding to the time they finish. They can also apply for international camps that this information will all be relevant to.

 

I would like to build a report that pulls the girls names and which badge has a CompletedDate but not an AwardedDate and only those badges.

 

Thanks in advance for your assistance.

Share this post


Link to post
Share on other sites
Josh Ormond

eos' suggestion still applies. You can specify in the badges table if a badge is active/inactive, # of requirement for completion. If you need to adjust the data in the join table ( GirlsBadgesType in eos' example ), that is just a matter of manipulating the data.

 

You don't need a 1000 fields. You may want another table to track the requirements progress. Where you add a record that relates to the Person and the badge, and in the process of updating the Requirement event, you update the badge completion data.

Share this post


Link to post
Share on other sites
Goobz

So basically just have another table with the GirldId and the badge CompletedDate and AwardedDate. Then pull the information from that.

Share this post


Link to post
Share on other sites
eos

Thanks for chiming in, Josh …

 

Wendy –

 

first, I wonder why you think that having one table for each of >100 girls is “stupid”, but having one table for each of ~113 badges is a brilliant idea … :D

 

Then, let me elaborate on Josh's last point. It probably helps if you imagine doing this the old-fashioned way on paper; I'll try to add examples to that effect.

 

If in this structure

 

Girls ---- Badges

 

the options / completion requirements per badge are different in kind and/or number (as they probably are), you can

 

1. add a BadgeRequirements table, and relate that to Badges; enter one record per requirement, and enter a description, a chronological sort order / whatever else you need.

(Like writing the individual pages of a specific section in a Badges manual – where a list of the Badges records is the TOC.)

 

2. add a GirlsBadgesProgress table; when you create GirlsBadges record, you copy over the appropriate records from BadgeRequirements into this table, including their description – so if the number/activities for a given badge ever change, you still know what they were for that Girl/Badge at that time.

(Like making a carbon copy of the Badge manual pages in question, and handing them to one girl, ready to be filled in; if later a new issue of the manual is created, the girl will still have the old page(s).)

 

If you need to adjust a given GirlBadges requirement profile, you could change the number of GirlsBadgesProgress records by deleting some of them, adding additional ones (e.g. blank ones with an ad-hoc description), or change the default description.

(Delete/white-out sections on the paper; write additional specs on a post-it and stick it to the copy)

 

Add a completionDate to the GirlsBadgesProgress table; when all GirlsBadgesProgress records for a GirlsBadges record have this date, you can set a completionDate in GirlsBadges.

(The girl comes to you with her copied manual page on which all requirements are marked as finished and “signed”; you then “sign” the GirlBadges report by entering a completion date.)

 

This means that a total of 5 tables gives you total flexibility to describe *any* type of badge, and their associated requirement profiles. Set up the framework once, then all you need to do is add badges / add/change requirements / modify descriptions – all the stuff you have to do anyway to keep up with your association's regulations.

 

This will look like

 

Girls ---- BadgeRequirements >-- Badges

 

Girls (id, name, etc.)

Badges (id, name, etc.)

GirlsBadges (id, id_girl, id_badge, dateCompleted, etc.)

GirlsBadgesProgress (id, id_girlsbadges, id_badgerequirement, dateCompleted, description, etc.)

BadgeRequirements (id, id_badge, description, sortNo, etc.)

 

I already described the benefits of such a system for reporting. Another is that with the right kind of user interface, anyone would be able to maintain this system, because now your “business data” is stored in records, not encoded within the schema (your table and field definitions).

 

Last but not least, as a byproduct you now have convenient, search- and sortable lists of all badge names / requirements / descriptions / whatnot – in just 2 related tables!

 

Changing your database (once, and be done with it) is *much* easier (and a better use of your time) than having to deal with ~100 tables; be aware that whenever a new badge is introduced, not only do you need to create a new table, but you also have to (find and) modify every single one of your scripts that uses badges … and these scripts would be very lengthy and complicated as it is!

 

I hope the prospect of having to make fundamental changes doesn't discourage you; but I think you've been given a pretty good idea of a proven approach.

Share this post


Link to post
Share on other sites
eos
So basically just have another table with the GirldId and the badge CompletedDate and AwardedDate. Then pull the information from that.

 

That is the *very* brief version of what I wrote in my other post; but (also) *very* basically: "yes" – except that there is nothing to pull, only to find/sort/display, provided you have a correctly configured layout.

Share this post


Link to post
Share on other sites



  • WE NEED YOUR DONATION NOW

    WE have helped 100,000's of people for many years, now we need your help!!!! Your Donation is Needed to Keep Us Online! Covid 19 and isolation!!!! Without Your Help We Are Gone After 23  Years!



    3% of donation goal reached.
    Donate Sidebar by DevFuse
  • Images

×
×
  • Create New...

Important Information

Terms of Use