[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 466
  • Last Modified:

Query of four tables to return record closest to specific date

Hi there,

I'm working in Access 2003 and need to write a query that will select the records from one of four tables that is closest to (past only, not future) a date specified in a dialogue box.

Basically, these four tables store payment installment information for different contracts. Some contracts only have one or two installments, others may have three or four. As a result, all contracts will have a record in Table 1, but not necessarily the other three tables. There is a unique code for each contract which is included in each table for which there is an installment for that contract.

I need to write a query that, when I enter a particular date, will return the installment information from any of the four tables based on the closest date from each contract to the particular date specified.

Can anyone help?
0
angelheadedhipster
Asked:
angelheadedhipster
  • 4
  • 3
  • 2
  • +1
3 Solutions
 
Mike EghtebasDatabase and Application DeveloperCommented:
Wth a uinon query, you can combine all four tables and then do the search if you don't have large number of records in the each table.  The other option will be to have


Dim dteTemp as date
Dim strTable as string

dteTemp = InputBox("Enter date to search:")

if dteTemp>= #1/1/1996# and  dteTemp< #1/1/1998# then strTable = "Table1"

if dteTemp>= #1/1/1998# and  dteTemp< #1/1/2000# then strTable = "Table2"
.
.

Now having the table name picked.  A routine could be put together (which is the focus of your question).  As I wait for your response and feedback on the above point (size of your table), I would work on the rest of the solution.

Mike

0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Assuming you are using the second method and are able to pick the name of the table based on the data you enter to a input box.

Dim dteLow As Date
Dim dteHi As Date
dim dteSelected As Date
Dim dteTemp as date
Dim strTable as string

dteTemp = InputBox("Enter date to search:")

if Isdate(dteTemp) Then
    if dteTemp>= #1/1/1996# and  dteTemp< #1/1/1998# then strTable = "Table1"
    if dteTemp>= #1/1/1998# and  dteTemp< #1/1/2000# then strTable = "Table2"
    .
    .
Else
  MsgBox "You need to enter a valid date"
  Exit Sub
End if

If strTable ="" then
  MsgBox "There is no data for '" & dteTemp & "' in the tables."
  Exit Sub
End IF

dteLow = Dmin("[DateField]",strTable, "[DateField] <= #" & dteTemp & "#")
dteHi = Dmax("[DateField]",strTable, "[DateField] >= #" & dteTemp & "#")

If dteLow = dteTemp or dteHi = dteTemp Then
  dteSelected = dteTemp
Elseif datediff("d", dteLow,dteTemp) < datediff("d", dteTemp,dteHi) then
  dteSelected = dteLow
Else
  dteSelected = dteHi
End if

So far dteSelected is discovered.  In the next post, I will include a code to poci the entire record.

Mike
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
If the date range of the table may change, then change:

if dteTemp>= #1/1/1996# and  dteTemp< #1/1/1998# then strTable = "Table1"

to

if dteTemp>= DMin("[DateField]", "Table1") and  dteTemp<= DMax("[DateField]", "Table1") then strTable = "Table1"

and so fort.  Also, it is assumed dates in the tables are unique and don't repeat.
-------------------

To display the desired records, create a query from one of the tables and call it qResults with SQL like:

Select * From Table1

This will show everything from Table1, but using QueryDef we will use dteSelected and revise its SQL on runtime.

(Add following the the end of previose code..)

Dim strSQL As String
Dim strTemp As String

strSQL = "Select * From [" & strTable & "] Where d=# " & dteSelected  & "#"
CurrentDb.QueryDefs("qResults").SQL = strSQL

DoCmd.OpenQuery "qResults"

Mike
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Patrick MatthewsCommented:
Hi angelheadedhipster,

I must admit that this sounds like a bad design to me; why not have just a single table for the installments?

Regards,

Patrick
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Patrick,

I think he is dealing with historical and archived data and as time goes on, there might be some more tables.  As you know, archiving is very good idea if there is only occasional use for the data.

Mike
0
 
GRayLCommented:
Create and save this query as Q1:

SELECT * FROM tab1
UNION
SELECT * FROM tab2
UNION
SELECT * FROM tab3
UNION
SELECT * FROM tab4

Now run:

Select ContractID, ContractDate FROM Q1
GROUP BY ContractID HAVING Min(ContractDate - [Enter Date]);

Does that do it?
0
 
Patrick MatthewsCommented:
Mike,

Interesting thought on archiving, but even if that were the source of the four-table structure I would still
argue that there has to be a better design available than that.

:)

Patrick
0
 
angelheadedhipsterAuthor Commented:
Thank you all for your input - although I have to admit I didn't understand much of it :) I don't know much about SQL and manually writing queries.

The reason the data is in four different tables is because the data is entered via four online forms and the issue of the primary key. But it doesn't have to stay that way. Basically the query is the most important part, I will rearrange everything else if I have to!

I combined all the data into one table and tested this out with <=[date] And >=DateAdd("d","-364",[date]) which works fine. But how do I get the query to return only the most recent of the records (most of the installment dates are one year apart, but not all)?

Thanks!
0
 
GRayLCommented:
My second query is not correct.  Try this:

PARAMETERS [EnterDate] DateTime;
SELECT b.ContractID, b.ContractDate FROM Q1 AS b WHERE Abs(b.ContractDate - [EnterDate]) IN (SELECT Min(Abs(a.ContractDate - [EnterDate])) AS MinVal FROM Q1 AS a GROUP BY a.ContractID)

0
 
GRayLCommented:
This worked on my test table.  It looks simple but it drove me nuts getting the syntax right.  Still using the Q1 UNION query.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now