June 21, 2005 at 8:44 am
Potentially all of them...except fields that contain 'date entered', 'modified by', 'created by' etc....
June 21, 2005 at 9:02 am
Still too complex. As a few members here have said, when the query is too complex, stop cursing at the query and change the form behing it. Simplicity and atomicity are always good in a program.
June 21, 2005 at 9:13 am
The form is just one form though. It has maybe 15 total fields and pulls information from three tables. I don't understand what is wrong with that. All the data is related to each other. It is more simple to the user if I keep everything in one form.
It would be confusing if I had all the schoolrequest data in one form, academicoffice in another, and diaryentry data in yet another form.
If the data was not related, then yes, I would set up three different forms. But when a user pulls up info for what schoolrequest ID, he/she needs to see the associated contact info(from academicoffice) and the associated diary info(from diaryentry).
Forgive me for my ignorance, but I have no training with SQL, I am all self-taught.
Thank you,
M
June 21, 2005 at 9:46 am
You're the programmer, you must give them what they need, not what they want. They need to be able to do X, then let 'em do X but it doesn't mean that X must all be done in one screen. If you think you can do it that way then please be my guest. I know it can be done but it's a lot more work. But looks like you're gonna have to learn it the hard way.
June 21, 2005 at 9:59 am
With the multiple selects, why not select into a Temp Table for query 1 (#TempTable1), Temp Table for Query 2 (#TempTable2) each having a key field value that you would normally use in a join later on , i.e. (output SQ.SLsendToId as AOid)
Then do a master query on the temporary tables, having only the fields you want to show on the screen all joined on the key field you put in each table. I do this all the time and it works with pretty good efficiency as long as the temp tables are not huge. Seems to me if you are pulling only one student's records with each query, the temp tables should be rather short and it would work rather quickly.
June 21, 2005 at 10:06 am
After thought, what can become difficult is that a query returns multiple records say for the diary entries or a Notes table. Then you would have to string all the notes together and display them in a text box or something. I agree with Remi to some extent here, this can turn into a real time pit. I understand where he is coming from having spent countless hours perfecting some glitzy interface for the 1 out of 100 users who actually use it as designed and programmed.
June 21, 2005 at 10:20 am
SLFine,
I think my biggest issue now is that I felt as if I was getting somewhere(with Remi's invaluable guidance) with the original query, but now I'm totally lost as what to do or try next.
I am trying to make this as user-friendly as possible.
Thanks,
M
June 21, 2005 at 10:46 am
Try with your original design. You'll see the problems you encounter and you'll gain experience from that.
June 21, 2005 at 11:36 am
I'm still going for it...although I'm thinking of splitting the one query into two. So the first one would be:
SELECT SQ.*
, ST.AOcontactNameLast AS SendTo
, BT.AOcontactNameLast AS BillTo
, R.AOcontactNameLast AS Requestor
, DE.*
FROM schoolrequest SQ
LEFT JOIN academicoffice ST ON SQ.SLsendToId=ST.AOid
LEFT JOIN academicoffice BT ON SQ.SLbillToId=BT.AOid
LEFT JOIN academicoffice R ON SQ.SLrequestorId=R.AOid
WHERE SQ.SLid=" & SLid
And the second query would look like:
SELECT DEdescription, DEenteredDate FROM diaryentry
WHERE DEKeyValue=" & SLid
Sound good?
M
June 21, 2005 at 11:38 am
Sure, as long as it allows your users to do what they need.
June 22, 2005 at 8:20 am
I think I got it working:
strSQL="SELECT SQ.*, DEdescription, DEenteredDate
, ST.AOcontactNameLast AS SendTo
, BT.AOcontactNameLast AS BillTo
, R.AOcontactNameLast AS Requestor
, DE.*" &_
FROM schoolrequest SQ" &_
LEFT JOIN academicoffice ST ON SQ.SLsendToId=ST.AOid
LEFT JOIN academicoffice BT ON SQ.SLbillToId=BT.AOid
LEFT JOIN academicoffice R ON SQ.SLrequestorId=R.AOid
LEFT JOIN diaryentry DE ON SQ.SLid=DE.DEkeyValue
AND DE.DEdeletedDate IS NULL
WHERE SQ.SLid="& wrid & "AND DEkeyValue=" & SLid
June 22, 2005 at 8:41 am
Great.
June 22, 2005 at 8:45 am
Thanks for all your help Remi.
June 22, 2005 at 8:55 am
HTH. Good luck with the rest of the work.
Viewing 14 posts - 46 through 58 (of 58 total)
You must be logged in to reply to this topic. Login to reply