Access equiv to the vLookup in a Query

[Scorpio11]

How do I perform a lookup in a query?



Essentially i am wanting to perform what in excel would be a vlookup function where i pull the information from one query and put it into another query using the date as the lookup criteria.

Eg: Query 1 has the information and Query 2 is where i want to put it.

Qry 1
Date Value
19 May 2010 996.00
21 June 2010 1101.01
31 August 2010 701.03

Now in Query 2 I want to perform the lookup so the result will look like this

Qry2
Date Value
20 June 2010 996.00
21 June 2010 1101.01
22 June 2010 1101.01
...etc

Any help would be appreciated.

[Scorpio11]

The Formula I am using to accumplish this is:

(Select[TargetCell] From [TargetQuery] Where [Date]<[TargetQuery].Date)

but i am not getting a result - no result is coming up and if i use <=, access complains about returning too many results.

[jzwp11]

Can you explain in words what you want data you want the query to return?

If you just want data of a certain date range, you can use 1 parameter query

SELECT field1, field2...
FROM yourTableorQuery
WHERE yourdate between [DATE1] and [DATE2]

When you run the query, SQL will prompt you for the DATE1 and DATE2 parameters (i.e. variables)

SQL did not like the query you attempted because only 1 result can be returned when you nest a query like you have shown. There are other ways to return more results, but we have to understand what you are after.

[Scorpio11]

What i am trying to do is this.

The query filters out all the income received from the share. I want to then calculate a return of investments based on the cost of the investment at the time the income was received.

This would then require the lookup to pull in a single value based on the date, not multiple.

But i realised another way to do it last night so i no longer need any help with this particular thing.

however i am trying to do a running total on the income query filtering for different years - ie the running total restarts at the beginning of the new year - but when i run the query, access crashes. Any idea as to why - because it is beginning to irk me slightly . The forumula is not any different than the others you have helped me with.

[jzwp11]

Can you provide the SQL text of the query that crashed Access?

[Scorpio11]

you mean my formula?

CumDiv: (Select Sum(Dividend) From [Qry:SecuritiesIncome] As Q6 Where Q6.Year=[Qry:SecuritiesIncome].Year And Q6.SecID=[Qry:SecuritiesIncome].SecID)

[jzwp11]

It looks like this is 1 field of the query as you are viewing it from the design grid. Switch to SQL view and copy the text of the query from there.

[Scorpio11]

Apologies

SELECT [Qry:SecuritiesIncome].Date, [Qry:SecuritiesIncome].SecID, [Qry:SecuritiesIncome].[Sec-TrxType], [Qry:SecuritiesIncome].Description, (Select Sum(Dividend) From [Qry:SecuritiesIncome] As Q6 Where Q6.Year=[Qry:SecuritiesIncome].Year And Q6.SecID=[Qry:SecuritiesIncome].SecID) AS CumDiv, [Qry:SecuritiesIncome].Interest, [Qry:SecuritiesIncome].CumCost, [Qry:SecuritiesIncome].Year
FROM [Qry:SecuritiesIncome];

[jzwp11]

Can you provide the SQL text of [Qry:SecuritiesIncome] as well since you are nesting this one. Basically I am after all of the fields listed in the SELECT clause of [Qry:SecuritiesIncome].

[Scorpio11]

I have attached the entire database

The query giving me grief is called "test"

I've just realised that my formula won't give me running total per year on the query that i am wanting, but that does not explain the crash

[jzwp11]

I saw several issues. First, you had sectrx-type as a lookup at the table level. Lookups at the table level cause problems, so it is best not to have them. Additionally year and date are reserved words in Access and should not be used as field names. If they are they must be enclosed by square brackets []. Also, there is not need to have the year as a separate field in your query because you can use the year() function on your date field to extract the year. I fixed the queries in the attached DB. I'll leave it to you to go back to the tables and fix the field names (and all subsequent references to them).