NEExt
09-21-2004, 12:29 AM
I'm designing an intranet web application to keep track of vacation days (approved and not approved) based on employee seniority.
All the initial requests were complied into the database and I ran a query ordered by day off, then employee seniority to determine approvals. That was phase 1 and it is working fine.
Phase 2 is now First Come First Serve. Basically, now that the initial ordering by seniority is done - additional employees requesting a particular day are FIFO - unsorted.
I built a seperate table to house these FIFO requests, and query it using the same query (just unsorted) but am having trouble figuring out how display the results of two queries in one ASP loop.
I thought there might be a way to combine the two queries into one, with data from the table vacation sorted by seniority and then data from the table vacation_fifo appended onto the end "unsorted". Below is the SQL I'm using for just the one vacation table.
strSQL = "SELECT vacation.day_off, vacation.status, e_info.e_seid, e_info.e_name, e_info.e_email, e_info.e_eod, e_info.stop, m_info.m_name, m_info.m_phone, m_info.m_email
FROM (m_info RIGHT JOIN e_info ON m_info.stop = e_info.stop) RIGHT JOIN vacation ON e_info.e_seid = vacation.e_seid
WHERE (((vacation.day_off) Between #" & startrequest & "# and #" & DateAdd("d",1,endrequest) & "#))
ORDER BY vacation.day_off, e_info.e_eod;"
I guess basically I want to sort the data from BOTH vacation and vacation_fifo by the field day_off, but write all of vacation sorted by e_eod, and then write vacation_fifo sorted by database entry date. I hope this makes sense. Thanks for any input you can provide.
All the initial requests were complied into the database and I ran a query ordered by day off, then employee seniority to determine approvals. That was phase 1 and it is working fine.
Phase 2 is now First Come First Serve. Basically, now that the initial ordering by seniority is done - additional employees requesting a particular day are FIFO - unsorted.
I built a seperate table to house these FIFO requests, and query it using the same query (just unsorted) but am having trouble figuring out how display the results of two queries in one ASP loop.
I thought there might be a way to combine the two queries into one, with data from the table vacation sorted by seniority and then data from the table vacation_fifo appended onto the end "unsorted". Below is the SQL I'm using for just the one vacation table.
strSQL = "SELECT vacation.day_off, vacation.status, e_info.e_seid, e_info.e_name, e_info.e_email, e_info.e_eod, e_info.stop, m_info.m_name, m_info.m_phone, m_info.m_email
FROM (m_info RIGHT JOIN e_info ON m_info.stop = e_info.stop) RIGHT JOIN vacation ON e_info.e_seid = vacation.e_seid
WHERE (((vacation.day_off) Between #" & startrequest & "# and #" & DateAdd("d",1,endrequest) & "#))
ORDER BY vacation.day_off, e_info.e_eod;"
I guess basically I want to sort the data from BOTH vacation and vacation_fifo by the field day_off, but write all of vacation sorted by e_eod, and then write vacation_fifo sorted by database entry date. I hope this makes sense. Thanks for any input you can provide.