MS Query - range of po's by date but only want to pull the newest date

[dreadnaught]

I am running a query using ms query I am not sure this is the correct forum or not but I am trying to pull a part number, vendor number from the newest po. I can bring back say the last five po's with no problem but I only want it to bring back the po with the newest date. Anyone have any suggestions??

 

[sfvb]

I'm not sure what database you're querying against but you can try something similar to this:

SELECT part_no, vendor_no
FROM po
WHERE date = (SELECT max(date) FROM po)

 

[sfvb]

The above assumes that date is actually a datetime column and is unique for each record

 

[Hasit]

I am assuming here, that you have pulled the information from a database back into excel. If thats that case, you need to set up an array formula, that tests a bunch of stuff so that you get the date for the latest PO. If you provide more details of the environment I should be able to help.

 

[dreadnaught]

Hasit

it is in excel that I am pulling it back to. I am pulling from a as400 system. There is a specific date field. I am trying to limit on the date field but do not now if I can in MS Query. I am looking for a statement to put into MS Query to limit this, can you help?

 

[Hasit]

You can do it in two ways. The first is through MS Query and ths second is a two step process through Excel. Depending on what you want to do with the data, the second method may be better as you have all the necessary data in the spreadsheet in one go against which you can apply different formulas etc. If you want to do this in Excel, let me know.

In any case, when you click on DATA->GET EXTERNAL DATA etc, make sure that the "Use the Query wizard to create/edit queries" box is UNCHECKED. This gives you the full MS Query screen.

Through this, you should be able to pull in the table, and each data column by adding tables and then selecting the column headings you want.

Once that is done, there is a button on the toolbar which looks like a funnel with an equals sign next to it. This button allows you to add criteria against which the column(s) you have added will be assessed. So you could specify that the date you wish to look for is greater than or equal to say today's date.

Additionally, if you wish to code a simple SQL statement, you can set up the criteria and then click on the button marked "SQL" which shows you the SQL statement for your criteria selection. Its pretty simple (something like SELECT xxx from TABLE yyy WITH etc etc). You can make changes directly to the SQL statement if you wish too.

The benefit of using the full MS Query this way allows you to see the results straight away before you return the data back to Excel. If the criteria doesn't give you the desired result, then you try again!

 

[dreadnaught]

hasit,

I am able to do that but if the table has say 5 different po's and I want the newest one how do I write the criterial to do that?

 

[Hasit]

How do you know (by looking at the PO), which one is the newest, other than through the date?

If thats the only way, click on the crirtia button and follow the prompts. Incidentally, you can add as many criteria to the column as you wish (e.g. date greater than yesterday AND date less than tomorrow).

I am not an SQL expert, but can define the statement if you send me what you generate through your initial selections.

 

[dreadnaught]

Hasit

Here is an example I have 5 po's with dates ranging form 11/01/99, 3/15/00, 5/12/00, 10/20/00, 5/13/01. I am looking to get the 5/13/01 po with its attached data. but I might have another product with different dates than above but I would still want the newest po cut. I hope that better explanes my problem. I am trying to write the criteria that would only pull from the newest date on each product

 

[Hasit]

OK, I thought so. You need to select on the date column, and you do not need to write anything. MS Query will allow you to select on the newest date through the criteria button.

I'll see if I can fashion an SQL statement, but it may be tomorrow now (its 5.10pm in the UK and I have a ton of work to complete before I get home).

 

[dreadnaught]

Hasit,

Thanks for your help I will be looking forward to your reply tomorrow.

Dreadnaught