John Blood
|
| Posted: 01/02/2005, 11:25 AM |
|
Hi folks,
I've got an application that has 4 related tables and I'm having real
problems constructing my first major query to create records for an editable
grid and to limit the records shown. This is my first query more
complicated than a simple where statement, by the way, but I want to learn
how to do this.
Users Students ProgressReports Periods
========= ========== ============= =============
PK=UserID PK=StudentID PK=ReportID PK=PeriodID
UserName UserID StudentID PeriodStatus
{Active or Closed}
.... DateClosed PeriodID ...
... UserID
...
What Needs to Happen:
Users logon to the system and enter students assigned to them. This builds
the student table. Each quarter the user logs on, and then uses an editable
grid to enter a quarterly progress report (10 drop-down box questions) for
each student assigned to them where the student's record is not closed (I
figure I can use some kind of 'where DataClosed is null' here?) and where
the reporting period is active. The key here is that the editable grid
should show 'blank' outcome records for all students matching the
conditions, so the users can enter reports for their active students for a
given period. They should not be able to enter reports for students they
have not already set up in the system. So, I can turn blank rows and add
records off in the editable grid.
I'm having a devil of a time constructing the query and this has me
overwhelmed and paralyzed at this point. Can anyone offer some advice,
point to an example, or even help me construct the query? If I need to pay
for some consulting time, I'm okay with that.
I've got the whole application built in CCS and it runs great -- except for
this key customer requirement that makes or breaks the whole purpose of the
application.
As is usual for many of us, I need to present something to the client
tomorrow so I'm hoping for a fast response. I'm in over my head.
Can anyone help me with this?
Thank you so much, in advance, and Happy New Year everyone.
John
763-498-7170
|
|
|
 |
JohnB
Posts: 3
|
| Posted: 01/02/2005, 12:58 PM |
|
Hi all,
I'm making this correction via the forums, rather than the newsgroup as I noticed that my ASCII table layout didn't survive the forum system. So, here are the tables shown vertically:
Users
-----------------
PK=UserID
UserName
...
Students
----------------
PK=StudentID
Fkey=UserID
DateClosed
...
ProgressReports
--------------------------
PK=ReportID
FKey=StudentID
FKey=PeriodID
FKey=UserID
...
Periods
-------------
PK=PeriodID
PeriodStatus {Active or Closed}
...
_________________
John Blood
Behold! Learning |
 |
|
peterr
Posts: 5971
|
| Posted: 01/02/2005, 1:20 PM |
|
John,
First, a suggestion to remove your email address from your signature (and therefore forum postings) as there are many spam bots scanning the Web for valid email addresses. This greatly increases the chance of spam, worms and viruses being sent to your mailbox.
As for your main question, I don't fully understand this: "the editable grid
should show 'blank' outcome records for all students matching the
conditions".
Do you want to show records that don't exist yet (which is not possible), or you want to show some partially empty records that exist in the database? If they are partially empty then which fields aren't empty in those records?
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
JohnB
Posts: 3
|
| Posted: 01/02/2005, 3:04 PM |
|
Thank you Peter for your quick reply and for the email suggestion. I'll leave my email address off future posts.
Your question gets to the crux of the matter. At the time of entry, I'd like to present a list of the students for which periodic reports are due (by that user), and then allow the user to enter their periodic reports from the list, to be sure they are all entered. Some users will be facing 3-5 students for quarterly entry and others may have up to 300. Users must not see each other's entries (federal data privacy restrictions looming here). Because of the large number of records required of some programs, using individual record forms for each student's periodic report would be far too cumbersome for the user. In my mind, using some kind of editable grid seems the best solution. But, I'm open to suggestions.
Would this work and is it practical?:
1) Users enter students and report that all their students are entered
2) A 'create' query is run for all entered students to create 'blank' periodic report records for all active students in that period entering the student FirstName, LastName, and DateOfBirth into each record created.
3) When the user goes to enter their periodic report, the editable grid then contains all the active student periodic report records ready for update shown by a concatenation of student LastName, FirstName and ordered by student LastName.
4) Now, users will enter additional students before the next periodic reporting entry and so a similar create query will then have to run prior to each period.
Gating the entry by period (active or closed) is done so that we can keep track of the records and limit the user from entering over a previous periodic report filed for a student. We are also drawing queries/reports out showing summary data for all the periodic reports entered by user and their agency. Perhaps the period lock isn't so necessary?
I have rolled this thing around in my head again and again and I have been confused by the editable grid thinking it might just do what I'm hoping.
Another route would be the individual record, but I really hate to do that as the refresh times between records and screens would make data entry loathesome. Is there a way to make the editable query solution work out?
Any suggestions? I do have a screenshot of an early mock-up if it would help anyone.
Please let me know if I need to be more clear. I am probably not using all the terms I should use, so please let me know if I need to explain something differently.
Peter, thank you again for your fast reply, and a 'thank you' to anyone else who has some guidance.
John
_________________
John Blood
Behold! Learning |
|
|
|
peterr
Posts: 5971
|
| Posted: 01/02/2005, 10:58 PM |
|
John,
I think that your approach should work well, meaning that you would first create empty records for the selected (or all active) students, then show those empty grid in an editable grid.
It may be difficult to specify exact steps without actually implementing such projects and testing several logic variants, however theoretically you could:
Implement #2 in the After Initialize event of any page and run some SQL that will create all necessary records, then redirect to another page. An example of executing custom SQL is at http://docs.codecharge.com/studio/html/ProgrammingTechn...eCustomSQL.html
Then #3 can be done as you described, by using an editable grid to display those semi-empty records that were just created.
Another approach could be to create simple editable grid that displays existing records (progress reports) with basically zero records (because they don't exist) but set the "Empty Rows" property of the Grid to 10 or other high number. This way the users will see 10 empty records which they can fill out and submit at once. I'm just not sure if this solution would work in your case.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
JohnB
Posts: 3
|
| Posted: 01/02/2005, 11:25 PM |
|
Peter,
Thanks! It's nice to hear my logic is probably sound. Now, I need some help with the SQL. Can you recommend anyone who could help me with this? I've sent a message off to tech support (I have a support agreement), but I have a feeling this is less tech support and more consulting. So I'll understand if they can't help.
I'm going to discuss your logic variants with the client tomorrow morning and see which way they'd like to go. My guess is that they will go for whichever is faster to program. The empty grid wins there, and I suppose I could use a drop-down box control that contains the concatenation.
Now, I'm not programmer-enough to handle the details on this and this being my first CCS project I'm a bit under-experienced to take this to the level it needs to go as quickly as possible. Is anyone interested in helping for a fee? I would ask you to help me learn what you're doing as you do it, or afterwards. Peter, please don't be modest if you feel you could help.
If anyone is interested, send a private message to me and I'll get back to you with email address and/or toll-free phone number.
By the way Peter, thank you for removing my email addresses from the previous posts. Much appreciated.
Thanks again everyone. All other comments, questions and suggestions are welcome!
THANKS!
John
_________________
John Blood
Behold! Learning |
|
|
|
|
