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
528 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
Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

691 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