VBA Query Use Date from last records of a Specific Foreign Key To SELECT The LAST Record of Each Date Between those Dates of a different Foreign Key

Hello again all!!!!

Trying to understand how these get written... struggling......  This is a 2nd followup question, this one follow up to:
vba To get last Record of Each day with a Foreign Key to select certain record types.


Now then, assume still that I have
Table [dta]
Fields:
[ID] - primary key autonumber
[Fk1] - Foreign Key
[Info1]
[Info2]
[Info3]
[Info4]
[Void] - Y/N default 0
[Date]

Further assume that I have data (leaving the Info fields off for the moment)
NOTE - ATTACHING A 1 TABLE + 1 QUERY DB with this following table info (changed Date to TrDate since Date is a reserved name)....

ID --- FK1 ---- Void  ----- Date
1 ------ 3 -------- 0 --------- 1/1/2013
2 ------ 3 -------- 0 --------- 1/1/2013
3 ------ 5 -------- 0 --------- 1/1/2013 ***********
4 ------ 3 -------- 0 --------- 1/1/2013
5 ------ 3 -------- 0 --------- 1/2/2013
6 ------ 3 -------- 0 --------- 1/2/2013
7 ------ 3 -------- 0 --------- 1/2/2013
8 ------ 3 -------- 0 --------- 1/3/2013
9 ------ 3 -------- 0 --------- 1/3/2013
10 ------ 3 -------- 0 --------- 1/4/2013
11 ------ 3 -------- 0 --------- 1/4/2013
12 ------ 5 -------- 0 --------- 1/4/2013
13 ------ 3 -------- 0 --------- 1/4/2013
14 ------ 3 -------- 0 --------- 1/5/2013
15 ------ 3 -------- 0 --------- 1/5/2013

NOW THEN,

What I need to do is this, LETS SAY I was just now entering the last FK 5 info on that 1/4/2013 date.  
I am needing to :

Have a query that selects the last prior FK 5 Record and shows the Info Fields INFO3 and Info4 as Negative Amounts. AND

ALSO Selects the last FK = 3 Record for each day INCLUDING the date of that Beginning Fk5 through but NOT including the new FK 5 date showing Info 3 and 4 as Positive numbers

AND then the info of the new Fk5 I'm just now entering.

So in My example it would Select:

ID
3 Info as Negative (last Prior FK 5 to the one I am now entering)
4 Info as Positive (last #3 for date of the last Prior FK #5
7 Info as Positive
9 Info as Positive
12 INFO BEing currently input as the newest FK5
Qry-To-Select-Records-between-Fo.accdb
wlwebbAsked:
Who is Participating?
 
wlwebbAuthor Commented:
The info fields are irrelevant to the question.  The issue is the ID, FK1 and Date fields.

ALSO
The expected result was in-fact included in the original question posting
0
 
chaauCommented:
how do you identify the FK you are now entering?
0
 
wlwebbAuthor Commented:
Cjaau.

On a Form there will be a combobox that they select from 5 or so options.  If they select #5 then this qry will update once they have saved the data
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
chaauCommented:
that is fine. What I am asking is: will there be a column in a table where your FK column refers to to indicate that this value is currently active.
0
 
wlwebbAuthor Commented:
Yes. When the user Selects that option a new record in the table will be created and the record will be saved with me.form.dirty=false
0
 
wlwebbAuthor Commented:
Ok this is what I "Attempted".  I guess the success is "at least it didn't blow anything up!!!".  BUT it isnt the result I described.  What I ended up with is a duplication of all the data ....  The only data I got was the info from 1/1/13 and 1/4/13 (nothing on any date in between)

I guess it would really help if I understood these "derived tables".  I can see that part of what I am missing in my code is how to SELECT the MIN FK #5's ID AND THEN using that in my SELECT statement for getting all last records for a date that are FK #3 records that are IDs Greater than the Min FK 5 but less than the Max FK 5......


SELECT t1.*, t2.*
FROM 
dta AS t1 INNER JOIN (SELECT Max(t1a.[ID]) AS MAXID, t1a.[TrDate] FROM dta AS t1a WHERE t1a.FK1=5 And t1a.Void=0 GROUP BY t1a.[TrDate])  AS X ON t1.ID = X.MAXID, 
dta AS t2 INNER JOIN (SELECT Max(t2a.[ID]) AS MAX2ID, t2a.[TrDate] FROM dta AS t2a WHERE t2a.FK1=3 And t2a.Void=0 GROUP BY t2a.[TrDate])  AS Y ON t2.ID = Y.MAX2ID

ORDER BY t1.TrDate;

Open in new window

0
 
hnasrCommented:
Your table shows null values for info's. So -ve and +ve nfos is confusing.

List few input records
And list the expected output from the query.
0
 
hnasrCommented:
The original post includes the input data.
Wanted a similar table of the expected output. Then you may add comments on how each record is selected.

Please list here the 8 queries to help visitors to this thread. It also helps understand the question.
0
 
wlwebbAuthor Commented:
MODERATOR<br />PLEASE CLOSE THE QUESTION.  I FIGURED THIS ONE OUT.  TOOK 8 QUERIES.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.