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
491 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
  • 5
  • 2
  • 2
10 Comments
 
LVL 24

Expert Comment

by:chaau
Comment Utility
how do you identify the FK you are now entering?
0
 

Author Comment

by:wlwebb
Comment Utility
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 24

Expert Comment

by:chaau
Comment Utility
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
 

Author Comment

by:wlwebb
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:wlwebb
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
MODERATOR<br />PLEASE CLOSE THE QUESTION.  I FIGURED THIS ONE OUT.  TOOK 8 QUERIES.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now