?
Solved

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

Posted on 2013-01-29
10
Medium Priority
?
531 Views
Last Modified: 2013-02-05
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
0
Comment
Question by:wlwebb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
10 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 38833893
how do you identify the FK you are now entering?
0
 

Author Comment

by:wlwebb
ID: 38833952
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
 
LVL 25

Expert Comment

by:chaau
ID: 38833972
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:wlwebb
ID: 38834065
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
 

Author Comment

by:wlwebb
ID: 38837068
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
 
LVL 30

Expert Comment

by:hnasr
ID: 38840945
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
 

Accepted Solution

by:
wlwebb earned 0 total points
ID: 38842261
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
 
LVL 30

Expert Comment

by:hnasr
ID: 38843141
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
 

Author Closing Comment

by:wlwebb
ID: 38854355
MODERATOR<br />PLEASE CLOSE THE QUESTION.  I FIGURED THIS ONE OUT.  TOOK 8 QUERIES.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question