First Post- Query Execution Problms

[AcceSs-AbLe]

Hi,
I am trying to execute a query based on following information, however Access is not cooperating with me. Please Help!!

[Effective Date2]=12/31/2002
Beginning of year= Date of Product Start
tblAllinfo- Raw Data Info
tblProductInfo- Product Information
tblAllinfo and tblProdctInfo are joined by [Product Code]
[Unit Value]-NAV Value

FirstofUV: First(round([tblallInfo].[Unit Value],5))- This looks for the first Unit Value in the entire year.

The problem I am having is, Certain Products were started during middle of the year with starting value of 10. However, I cannot use 10 as my starting value for my calculations, I need access to pull the values for the date after the starting date. Example(10.00043) How can I achieve this?
Basically I want access to skip 10 and goto the next value in the query which would be 10.00043
This message was edited by AcceSs-AbLe on 2003-01-28 13:55

 

[Russell Hauf]

So do you want to get the second-lowest value of Unit Value? If so, something like this should work

Add the field [Unit Value] to your query. Click on the Totals button (sigma on the toolbar), and select Min for the "Total:" line. Add the same field again, only this time in the Total: line, put WHERE, and in the Criteria: line put:

> (Select MIN([Unit Value]) FROM tblAllInfo)

Now right-click on the table in the query, and select Properties. Where it says "Alias", replace the name of the table with something else (I usually just use "X" - no quotes).

So the SQL would look something like:

SELECT Min(X.[Unit Value]) AS MinUnitValue
FROM tblAllInfo AS X
WHERE X.[Unit Value] > (SELECT MIN([Unit Value]) FROM tblAllInfo);

HTH,

Russell

 

[AcceSs-AbLe]

Thank you for your reply.

I tried to follow what you recommended but Access was unable to bring the right number. Basically I am trying to get the Unit Value after the "FirstofUnit Value", For example, if the product launches on February 4th, the unit value is 10.00 now on february 5th, the value can be either 10.003 or 9.995, I just want Access to retrieve that value. Sorry for the confusion, I greatly appreciate your help.

Thank you.

 

[Russell Hauf]

So the answer to the question I asked above is "no". So you want the value as of the next DATE after the first DATE?

 

[AcceSs-AbLe]

Yes thats it!!

 

[Russell Hauf]

Ok, in Access, this will take 2 queries (at least I couldn't figure a way to do it in one). My table name is tbl012803, and it contains the fields fldProduct, fldDate, and fldUnitValue. Substitute your table name and field names. Ok, query 1:

SELECT X.fldProduct, X.fldUnitValue, X.fldDate
FROM tbl012803 AS X
WHERE (((X.fldDate)>(SELECT MIN([fldDate]) FROM tbl012803 WHERE X.fldProduct=tbl012803.fldProduct)))
GROUP BY X.fldProduct, X.fldUnitValue, X.fldDate;

Paste the text above into the SQL view of a query.
(don't change X or Y in these queries, just the names that I mentioned above).
Close and Save the query (I saved mine as qry012803a -- change all instances of qry012803a in this next query to what you name yours). Now create a new query and paste this into the SQL view:

SELECT Y.fldProduct, Y.fldUnitValue, Y.fldDate
FROM qry012803a AS Y
WHERE (((Y.fldDate)=(SELECT MIN([fldDate]) from qry012803a WHERE qry012803a.fldProduct=Y.fldProduct)))
GROUP BY Y.fldProduct, Y.fldUnitValue, Y.fldDate;

This should give you the values you want.

HTH,

Russell