How can I retrieve a previous record based on multiple variables?

[adkolta]

I'm trying to create a field in a Query which essentially looks up "yesterday's" performance for a "specific location". I've read other blogs that suggest a DLookup; however it is being based on an auto number Pimary Key. This will not yeild the correct field as each row is not necessarily in chronological order.

To simplify - I have 3 fields [LOCATION],[DATE],[UNITS OPEN]There are 6 different locations of which i'm keeping history. Does anyone know of an elaborate expression that will match up Location-1(of 6) with Location-1(of 6) and display the prior day's [UNITS OPEN]?

Thanks in advance for any assistance!

[Rabbit]

You can join the table to itself on the location and date to prior date.

[adkolta]

Can you elaborate a bit more?

[Rabbit]

There's not much to elaborate on. You just have to convert what I said into the SQL syntax. Something like this:

SELECT *
FROM someTable AS T1
LEFT JOIN someTable AS T2
ON T1.Location = T2.Location
   AND (T1.DateField - 1) = T2.DateField

Where the LEFT JOIN is when I said "join the table to itself" and everything after ON is when I said "on the location and date to prior date"

[adkolta]

I think i'm getting it, only now an error message appears -
The specified field "" could refer to more than one talbe listed in the From clause of your SQL statement.

When i joined the table to itself, i basically added the same query "MOVEMENT Query" which then renamed itself "MOVEMENT Query_1" I joined the two location fields, then in SQL view i populated the syntax you suggested replacing T1 & T2 with the appropriate table & field names. Any idea what i'm doing wrong?

[Rabbit]

You're probably attempting to include a field from the table without qualifying which table to use. Since you have two table with the same field names, you have to tell it which one you want.

[adkolta]

Okay I've corrected the last issue. Now when i apply the "AND (T1.DateField - 1) = T2.DateField" Syntax, the fields go blank.

[Rabbit]

You're probably picking fields from the right side of the join instead of the left side of the join. It's hard to say without seeing any of the code.

[NeoPa]

IE. If you post the code you're using, along with the explanation, then we are in a much better position to be able to help you. Currently we are not in a good position at all of course due to lack of relevant information.

[adkolta]

Hey guys... USER ERROR. When i created the new (duplicate) table, i forgot to correct the title of my joins. The new "yestereday's" field is working perfectly. THANK YOU!!!!

One last question. After manually updating the code in SQL view, I am no longer able to view my query in Design View? Is that normal?

[Rabbit]

Sometimes, the SQL can't be graphically represented. In this case, it is because of the complex join condition.

[NeoPa]

Adkolta:
After manually updating the code in SQL view, I am no longer able to view my query in Design View? Is that normal?

This is usually the case when an element of SQL has been introduced that is simply not supported by the graphical interface. An examples of this would be use of the UNION keyword. Such queries cannot be represented graphically by Access so it won't allow switching to graphical mode. Typically though, once SQL code has been updated it can be switched between SQL and graphical mode at will.