I have the following query (I am sorry if it is not formatted correctly)
As far as I know - for each q_akz it will
order them by q_decision_date then q_decision_number then q_bkz_id
But it will only give me the highest number in the q_id.
I am pretty sure this works but it is inefficient / slow.
I intend to add more conditions to it like this.
I am currently testing this in the query builder at the moment and this above query sometime works and sometime crashes Access. The thing is that the condition is going to get even more complicated. Further this Query is going to be the source query for a form.
tbl_questionnaire is a linked SQL Server table with 79,000 records with q_id as a primary key
So my question what can I do to make this more efficient? Or how should I be doing this.
Code:
SELECT *
FROM tbl_questionnaire AS mt1
WHERE mt1.q_id=(SELECT top 1 mt2.q_id FROM tbl_questionnaire as mt2
WHERE mt1.q_akz=mt2.q_akz
ORDER BY mt2.q_decision_date desc, mt2.q_decision_number desc, mt2.q_bkz_id);
order them by q_decision_date then q_decision_number then q_bkz_id
But it will only give me the highest number in the q_id.
So on the above only one row would be returned and that is row 6q_id ------ q_akz - - - - q_decision_date -- q_decision_number -- q_bkz_id
1 - - - - - 2000K058 - - - - 10/01/2005 - - - - - - - - - - 1 - - - - - - - - - 2356
2 - - - - - 2000K058 - - - - 20/01/2006 - - - - - - - - - - 2 - - - - - - - - - 2356
3 - - - - - 2000K058 - - - - 20/01/2007 - - - - - - - - - - 3 - - - - - - - - - 2356
4 - - - - - 2000K058 - - - - 20/01/2004 - - - - - - - - - - 1 - - - - - - - - - 5898
5 - - - - - 2000K058 - - - - 20/01/2006 - - - - - - - - - - 2 - - - - - - - - - 5898
6 - - - - - 2000K058 - - - - 20/01/2007 - - - - - - - - - - 3 - - - - - - - - - 5898
I am pretty sure this works but it is inefficient / slow.
I intend to add more conditions to it like this.
Code:
SELECT *
FROM tbl_questionnaire AS mt1
WHERE mt1.q_id=(SELECT top 1 mt2.q_id FROM tbl_questionnaire as mt2
WHERE mt1.q_akz=mt2.q_akz
AND q_used_by_third_party<3
AND q_can_use=1
AND q_hierarchie_level_2 Like '*H*'
ORDER BY mt2.q_decision_date desc, mt2.q_decision_number desc, mt2.q_bkz_id);
tbl_questionnaire is a linked SQL Server table with 79,000 records with q_id as a primary key
So my question what can I do to make this more efficient? Or how should I be doing this.