By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

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

P: 8
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!
Jan 5 '12 #1

✓ answered by Rabbit

There's not much to elaborate on. You just have to convert what I said into the SQL syntax. Something like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM someTable AS T1
  3. LEFT JOIN someTable AS T2
  4. ON T1.Location = T2.Location
  5.    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"

Share this Question
Share on Google+
11 Replies


Rabbit
Expert Mod 10K+
P: 12,324
You can join the table to itself on the location and date to prior date.
Jan 5 '12 #2

P: 8
Can you elaborate a bit more?
Jan 5 '12 #3

Rabbit
Expert Mod 10K+
P: 12,324
There's not much to elaborate on. You just have to convert what I said into the SQL syntax. Something like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM someTable AS T1
  3. LEFT JOIN someTable AS T2
  4. ON T1.Location = T2.Location
  5.    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"
Jan 5 '12 #4

P: 8
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?
Jan 6 '12 #5

Rabbit
Expert Mod 10K+
P: 12,324
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.
Jan 6 '12 #6

P: 8
Okay I've corrected the last issue. Now when i apply the "AND (T1.DateField - 1) = T2.DateField" Syntax, the fields go blank.
Jan 9 '12 #7

Rabbit
Expert Mod 10K+
P: 12,324
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.
Jan 9 '12 #8

NeoPa
Expert Mod 15k+
P: 31,263
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.
Jan 9 '12 #9

P: 8
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?
Jan 9 '12 #10

Rabbit
Expert Mod 10K+
P: 12,324
Sometimes, the SQL can't be graphically represented. In this case, it is because of the complex join condition.
Jan 9 '12 #11

NeoPa
Expert Mod 15k+
P: 31,263
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.
Jan 9 '12 #12

Post your reply

Sign in to post your reply or Sign up for a free account.