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
508 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
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 24

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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

831 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