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

[fadilrexhepi]

HI,

I am having an issue with a select query as I want one column to show the MAX and "00" value of the same Column. How do I go about having this task completed.

Any help is greatly apprecaited

For example, the column has 00,01,02,03,04,05,*1,*2,*3,*4,*5 so I want a query that will help me return values of "00" and "05" on this example
00
05

Further example, this is what I have now
SELECT qryPR34_UNION.PR34001, qryPR34_UNION.PR34002, qryPR34_UNION.PR34003, qryPR34_UNION.PR34005, qryPR34_UNION.PR34006, qryPR34_UNION.PR34007, qryPR34_UNION.PR34013, qryPR34_UNION.PR34014, qryPR34_UNION.PR34098, qryPR34_UNION.PR34099, qryPR34_UNION.PR34100, qryPR34_UNION.PR34101
FROM qryPR34_UNION
WHERE (((qryPR34_UNION.PR34002)>"0") AND ((qryPR34_UNION.PR34003)>"0") AND ((qryPR34_UNION.PR34006)<"0") AND ((qryPR34_UNION.PR34099)>0));

on the WHERE command, I need field PR34005 to give me the "00" value or the MAX value (on my example the result will have to give me records that have "00" and "05" value assigned on PR34005


Please help

Fadil

 

[fadilrexhepi]

I am stuck on this query for three days now. I really am desperate for help on this task so I can move forward with building my database.

All I need is to know in the WHERE syntax, how will I need to add also an OR command where qryPR34_UNION.PR34005 returns the "00" (not min but 00) value or qryPR34_UNION.PR34005 returns the MAX value.

Thank you very much
Fadil

 

[CJ_London]

I don't think anyone understands what you are trying to achieve.

on the WHERE command, I need field PR34005 to give me the "00" value or the MAX value (on my example the result will have to give me records that have "00" and "05" value assigned on PR34005

the query will always return a max value so the or ='00' will never happen

Perhaps provide some more data examples and the expected results

 

[fadilrexhepi]

Thank you for your time to response.
I can't attach a file otherwise I would send a sample database.

What I'm trying to achieve is that I have a select query that is reading from a table and it already has some criteria's on the query like >0 and <0 on certain columns. The issue I am facing is with one column where I need to retrieve only '00' values and MAX values of that column, and all this data is in the same column.
That column contains Project Change Order numbers, therefore I am trying to retrieve the '00' which is the original contract value and the latest change order which is the MAX value of that column.
I hope I was able to explain it!

Regards,
Fadil

 

[CJ_London]

as previously requested

Perhaps provide some more data examples and the expected results

This may not be a matter of modifying your query slightly but of completely rewriting it

you can always do a screenshot

 

[vbaInet]

I think fadilrexhepi is trying to get the min and max values from this range "00,01,02,03,04,05,*1,*2,*3,*4,*5", but only those that begin with 0.

So min would be 00 and max is 05 - only those two records.

 

[fadilrexhepi]

Hi,
I have a screenshot of two slides that explain how my select query looks and what is the table/query showing now, but I can't upload the snapshots. I am clicking on the picture logo and it asks me for a http link

 

[fadilrexhepi]

For example I have a table query that showing me currently in one column the following records:
00
01
02
03
04
*1
*2
*3
*4

So, there are thousand of rows associated with different project numbers so what I am looking to get is for example from the above records I wrote I want the query to show me only the rows with 00 value of that column and the rows with the MAX value of that column which in the above case is 04
So, the result will give me all rows associated with 00 and 04 in this case

 

[jdraw]

fadilrexhepi,

cross posted
http://www.accessforums.net/queries/min-max-value-return-same-column-52971.html

When you cross post, please let readers know that you have done so. It isn't that cross posting is wrong, it is the not informing readers that you have done so that is considered a lack of etiquette.

Please review this re cross posting.

Good luck with your project.

 

[CJ_London]

@vbaInet

I think fadilrexhepi is trying to get the min and max values from this range "00,01,02,03,04,05,*1,*2,*3,*4,*5", but only those that begin with 0.

Ah, my interpretation was he wanted anything with 00 or the max number (so 15 is higher than 05)

@fadilrexhepi
I can't really follow your since I don't understand what your existing criteria is intended to do - you refer to a column (no name) but your criteria refers to 4 columns.

but suggest something like

 SELECT *
 FROM qryPR34_UNION
 WHERE (PR34002="00" OR PR34002=(SELECT Max( PR34002) FROM qryPR34_UNION AS T WHERE ProjectID=qryPR34_UNION.ProjectID))

Your qryPR34_UNION does not appear to have a field which identifies the project so I have made one up, please substitute or remove if not required

 

[fadilrexhepi]

Hi jdraw,

I am new to the msaccess forums therefore I didn't know to which one to hold on to.
I thought it was just like years ago when I needed excel help I went to multiple forums and now for Excel I only stayed with two chandoo.org and smallman.com where I found Guru's which will work on a project and ability to pay them through pay pal.

As far as ms-access, I have been struggling with this project for three days now so today I went and posted this in three different forums thinking there will be different GURU's that can help me, as I wrote above on my comment, I am desperate for help here.
Believe me, I understand your point and I am ethical on these things and will continue to be, but at this stage, struggling with this filter query for three days and not being able to move forward, I was able to reach out to hundreds of forums and I even asked for paid help but I was re-directed to someone charging hundreds of USD for a whole project, whereas at this point I want just to finish a query, not contract a whole project

Regards,
Fadil

 

[vbaInet]

@CJ_London: actually it looks like Fadil wants the entire range, so any numbers beginning with 0. Is that correct Fadil?

 

[vbaInet]

To clarify, you want an output of:

00
01
02
03
04

Right?

 

[CJ_London]

I am clicking on the picture logo and it asks me for a http link

you need to be in the advanced editor,

1. click on 'manage attachments'
2. browse to the file you want to upload and upload
3. once uploaded, right click on the file name just above the 'manage attachments' button and select 'Copy Short Cut'
4. click on the image icon and paste

 

[fadilrexhepi]

@CJ_London and @vbaInet
Thank you very much for helping me on this task. I will try to explain all the fields hopefully I will be able to.
The field named qryPR34_UNION.PR34001 is my ProjectID number and basically at the end once all filters are done, I will filter/sort by that field to see the data.

Every project has many rows so I am trying to create a query that will show me what are the rows associated with the original estimate of each project hence the '00' criteria, and also to show which one is the latest estimate, hence the MAX value I was referring to.

For example:
WHERE (((qryPR34_UNION.PR34002)>"0") AND ((qryPR34_UNION.PR34003)>"0") AND ((qryPR34_UNION.PR34006)<"0") AND ((qryPR34_UNION.PR34099)>0));
This is already fixed, I mean these columns above with WHERE criteria are already filtering the data what I was looking for, but in addition to these filters, I need one more which will filter me the data from the column named PR34005 which I didn't write above because I don't know where.
So, the column PR34005 has the original estimate number of each project which is numbered 00 and has a latest estimate number that it could be from 01 to 99. This field also has numbers that start with star *1,*2 etc, and I don't need those.

So, if the field PR34005 (estimate number) has the following records
00
00
01
01
02
03
04
04
*1
*2
*3
*4
From all these rows, I will need the result to give me every row that is associated with 00 as well as 04 in this example. So, my result for the PR34005 would have been:
00
00
04
04

I hope I was able to explain it

Thank you very much
Fadil

 

[fadilrexhepi]

Here are the snapshots.
As you can see on Slide1, it shows that I already have PR34002, PR34003, PR34006 and PR34009 already filtering data that I need as end result.

After all these filtering, in addition I need the PR34005 which is the estimate revision, to show me the result of what is the original estimate which is associated with the number 00 and also show me the latest estimate number which could be from 01 to 99. This field also has data such as *1,*2,*3 and I don't need this data but I can not delete it since the table is coming from another source.

So, my Project number field is PR34001.

Regards,
Fadil

 

[vbaInet]

From all these rows, I will need the result to give me every row that is associated with 00 as well as 04 in this example. So, my result for the PR34005 would have been:
00
00
04
04

Makes sense already.

You need:
1. Query to return the minimum number of PR34005
2. Query to return the maximum number of PR34005
3. Join the two queries to your table via PR34005 field.

 

[CJ_London]

SELECT *
FROM qryPR34_UNION
WHERE (PR34005="00" OR PR34005=(SELECT Max( PR34002) FROM qryPR34_UNION AS T WHERE PR34001=qryPR34_UNION.PR34001) AND (((qryPR34_UNION.PR34002)>"0") AND ((qryPR34_UNION.PR34003)>"0") AND ((nz(qryPR34_UNION.PR34006))<"0") AND ((qryPR34_UNION.PR34099)>0));

try the above - note I'm not sure if PR340006 is null or a zero length string in your data so I've used Nz

 

[fadilrexhepi]

Hi,
I will try this query once I get to office since I am trying the database through VPN connection to map to office network and it is going very very slow.
I will get back to office on Sunday and try this.
I really appreciate your time on helping me on this task. I hope we can make this query work

Warmest regards,
Fadil

 

[spikepl]

I am trying the database through VPN connection to map to office network and it is going very very slow.

So you have set index in your tables on the appropriate columns, right?