Combining SELECT statements
-
Potentially all of them...except fields that contain 'date entered', 'modified by', 'created by' etc....
-
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.
-
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
-
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.
-
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.
-
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.
-
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
-
Try with your original design. You'll see the problems you encounter and you'll gain experience from that.
-
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
-
Sure, as long as it allows your users to do what they need.
-
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
-
Great.
-
Thanks for all your help Remi.
-
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