MIN ("00") or MAX Value to return in a same column (1 Viewer)

[fadilrexhepi]

I tried the code that was given to me and it gave me syntax error, so what I'm doing now is I'm just trying the first part of the code and see if it works

From the last code that was given to me I am only trying this part to see if that returns the '00' and MAX values of the column PR34005

WHERE (PR34005="00" OR PR34005=(SELECT Max(PR34005) FROM qryPR34_UNION AS T WHERE PR34001=qryPR34_UNION.PR34001));
Currently the database is running the query and it is taking long time because I am not in the office today, so through VPN to connect to a big data it is taking time.

By the way, what do you mean by setting INDEX on the appropriate columns? What should I do?

Regards,
Fadil

 

[spikepl]

I'm too lazy to retype stuff you can read yourself. Google access indexes

 

[fadilrexhepi]

In this case I won't be able to index many fields since the tables are coming from SQL Server through ODBC connection from tables that I do not control, and are controlled from the finance team, but when I check on the tables that I bring on the query's couple of fields have the key assigned to the fields therefore I assume many fields are indexed

Regards,
Fadil

 

[CJ_London]

what do you mean by setting INDEX on the appropriate columns? What should I do?

Setting the index in your table design for all fields you either join on or regularly sort or filter on.

In particular PR34001 but also PR34002, PR34003, PR34005, PR34006 and PR34099 in the context of this query.

An alternative which may be quicker is

 SELECT qryPR34_UNION.*
FROM qryPR34_UNION INNER JOIN (SELECT PR34001, Max( PR34005)  AS MaxPR34005 FROM qryPR34_UNION GROUP BY PR34001) AS T ON qryPR34_UNION.PR34001=T.PR34001
 WHERE (qryPR34_UNION.PR34002="00" OR qryPR34_UNION.PR34005=t.maxPR34005)  AND (((qryPR34_UNION.PR34002)>"0") AND ((qryPR34_UNION.PR34003)>"0") AND ((nz(qryPR34_UNION.PR34006))<"0") AND ((qryPR34_UNION.PR34099)>0));

just seen your comment re this is coming from SQL server - if it is that slow, perhaps better to create a view in SQL server or set up a passthrough query.

One other thought, if this table is actually a union query then I don't think indexing will work anyway - I seem to recall that by their nature, union queries cannot utilise indexing themselves when being sorted and filtered (although cab be used when creating the individual 'components'.

 

[fadilrexhepi]

Usually on one MS Excel forum I use, there is a place / link to donate money to the Excel Guru's that helped with resolving the task / project and as a way to say thanks a donation can be done. Is there such thing in this forum where the participating MS Access guru's that will solve the problem, have their pay pall account listed where we can donate small amount of funds as a sign to say thanx

 

[CJ_London]

so far as I know there is no such form on this forum - we're just happy to help!