Complicated Query

[talkiechick]

So I guess this is my final attempt to see if I can create certain results with a query without having to actually program.

My query basically has a large amount of data. Each quarter we have to submit information from the data within six different date ranges:

Less than 3 M
3M - 1 Y
1 Y - 3 Y
3Y - 5 Y
5 Y-15 Y
greater than 15 Y

The problem isn't pulling the date ranges, I have two date fields that i need to have the dates come from. I need the data to pull the most current date and allocate that row of data to one date range. However i cannot have the data duplicate, if it has been used in one date range, I cannot have it used again in another.
i've tried create date range query for each date field and then union the two fields together. My problem is really is i am getting duplicate data in the date ranges, and I can't figure out a way to put a clause in to pull the data for the most current date.
Can anyone help, or at least tell me it's too complicated?

(FYI the type of data in the query is an ID,Type, Name, Currency, Date1, Date2, & Status. Type, Status and Date1 and Date2 have criterias.)

 

[jdraw]

Look in Access help for Partition and examples.

 

[talkiechick]

I'm not sure that would pull from two date fields. Should i create two queries each with a partition for one date field, then union the queries?

 

[jdraw]

I'm not sure that would pull from two date fields. Should i create two queries each with a partition for one date field, then union the queries?

Can you post the sql for the query as you had it (before I mentioned Partition)?

 

[talkiechick]

SELECT [Loan Information].[Loan ID], [Loan Information].[Call Report Type], [Loan Information].[Maturity Date] AS [Date 3], [Loan Information].[Loan Status], "Maturity" AS Type
FROM [Loan Information]
GROUP BY [Loan Information].[Loan ID], [Loan Information].[Call Report Type], [Loan Information].[Maturity Date], [Loan Information].[Loan Status], "Maturity"
HAVING ((([Loan Information].[Call Report Type]) In (12,13,14)) AND (([Loan Information].[Maturity Date])<=#6/30/2010#) AND (([Loan Information].[Loan Status])="ACTIVE"));

That is one query for one date range. Here is the Second Query

SELECT [Loan Information].[Loan ID], [Loan Information].[Call Report Type], [Loan Information].[Rate Change Date], [Loan Information].[Loan Status], "Rate Change" AS Type
FROM [Loan Information]
GROUP BY [Loan Information].[Loan ID], [Loan Information].[Call Report Type], [Loan Information].[Rate Change Date], [Loan Information].[Loan Status], "Rate Change"
HAVING ((([Loan Information].[Call Report Type]) In (12,13,14)) AND (([Loan Information].[Rate Change Date])<=#6/30/2010#) AND (([Loan Information].[Loan Status])="ACTIVE"));

And here is the Union SQL
SELECT * FROM [A] UNION ALL SELECT * FROM ;

 

[jdraw]

These 2 queries have different fields - same number of fields, but different.
So when you Union these you get data with a mix of Maturity and Rate Change.
How does this relate to your Date ranges?

 

[talkiechick]

That is one date range, but my problem is because there are two date fields and six different ranges, i ended up creating 6 queries, i need the data to pull in "either or" fashion into the correct date range. However it need to pull into one date range and not duplicate into another. The problem is i cannot set a precedence for the more current date in the query.

Currently when i run my different queries i get duplicate rows of data, it show up in one date range for rate change date, then it shows up in a later date range for the maturity date.

So i am confused about where to go.

 

[jdraw]

Well, outside the box for a moment, your date ranges
Less than 3 M
3M - 1 Y
1 Y - 3 Y
3Y - 5 Y
5 Y-15 Y
greater than 15 Y

could be set up differently.
Depending on how absolutely accurate you have to be, less than 3 months is
(approx)less than 90 days from today ( or some other date);
3M to 1 yr is 91 days to 365 days from today (or some date).

In access you can have a Between operator

eg TheDateToResolve Between Date and Date+90

Don't know if this is really helpful, but thought I'd mention it.
Do you have sample data/database?

 

[talkiechick]

it's pretty confidential data, so I couldn't put the DB up with data, but i could possibly do it without the data. Would that be of any use?

Yeah i am wondering i could do IIF statements and be able to keep all the date ranges in one query for one date field, then do the same with the other date field and then Union them.

 

[boblarson]

If you put the database up, clear the real data out and put in a few bogus records.

 

[jdraw]

I just did a little modification to your first query. You have to remove your Maturity Date check from the HAVING clause.

SELECT Loan ID]
, [Call Report Type]
, [Maturity Date] AS [Date 3]
, [Loan Status]
, "Maturity" AS Type
, iif ([Maturity Date] Between Date() and Date() + 90, "Less than 3 M",
  iif ([Maturity Date] Between Date()+ 91 and Date() + 365,"3M - 1 Y",
  iif ([Maturity Date] Between Date() + 366 and Date() + 1095,"1 Y - 3 Y",
  iif ([Maturity Date] Between Date() + 1096 and Date()+ 1825,"3Y - 5 Y",
  iif ([Maturity Date] Between Date()+ 1826 and Date()+ 5475,"5 Y-15 Y",
  iif ([Maturity Date] >Date() +5475,"greater than 15 Y")))))) as DateRange
FROM [Loan Information]
GROUP BY [Loan ID]
, [Call Report Type]
, [Maturity Date]
, [Loan Status]
, "Maturity"
HAVING [Call Report Type] In (12,13,14) AND 
[Loan Status])="ACTIVE"

This will give you a DateRange column for each record. Again it may not be accurate enough for your purposes - you could adjust if required.

You could do the same thing to your second query using the RateChangedDate. When you UNION those queries, you'll have all the data you need, you'd have to do an Order By to get them in the Ranges.

I did a test on a small table, using this query

SELECT MaturityDatesTest.[Maturity Date],iif ([Maturity Date] Between Date() and Date() + 90, "Less than 3 M",
  iif ([Maturity Date] Between Date()+ 91 and Date() + 365,"3M - 1 Y",
  iif ([Maturity Date] Between Date() + 366 and Date() + 1095,"1 Y - 3 Y",
  iif ([Maturity Date] Between Date() + 1096 and Date()+ 1825,"3Y - 5 Y",
  iif ([Maturity Date] Between Date()+ 1826 and Date()+ 5475,"5 Y-15 Y",
  iif ([Maturity Date] >Date() +5475,"greater than 15 Y"))))))AS DateRange
FROM MaturityDatesTest;

Maturity Date DateRange
6/14/2010 Less than 3 M
7/12/2011 1 Y - 3 Y
5/11/2013 3Y - 5 Y
5/9/2014 3Y - 5 Y
7/25/2019 5 Y-15 Y

Good luck.

 

[talkiechick]

JDraw Thank you so much. I put that coding in, and it seems to be working. I will have to play with it more on Monday. I am sure, I hope if i need more help I can call on you. I am sure it needs more working, but the two queries and the union query is working.

Thanks Again!

 

[jdraw]

Glad to help. Good luck.

 

[talkiechick]

So this is my current SQL situation from playing the with DB a little. I have this and another query with the exact same SQL except the date field is [Maturity date] instead of [Rate Change Date]. Then i created a Union query. This has been great, except i am still have the problem of duplicate values.

After speaking to the actual people who will be the end users of this database, I was thinking that maybe i can set priorities with another If Statement in the Union Query, that if [Rate change date] was null which then it would pull the maturity date instead. Also if it is not null then it would categorize into the [rate change date] range. Does anyone think this is feasible?

SELECT [Loan Information].[Loan ID], [Loan Information].[Call Report Type], [Loan Information].[Rate Change Date] AS [Date 3], ([Loan Information]![Master Loan Balance]+[Loan Information]![Part_ Offset_1]+[Loan Information]![Part_Offset_2])+[Loan Information]![Discount] AS [Call Report Balance], [Loan Information].[Loan Status], "Rate Change" AS Type, IIf([Rate Change Date]<#3/31/2010#+91,"Less than 3 M",IIf([Rate Change Date] Between #3/31/2010#+92 And #3/31/2010#+365,"3M - 1 Y",IIf([Rate Change Date] Between #3/31/2010#+366 And #3/31/2010#+1095,"1 Y - 3 Y",IIf([Rate Change Date] Between #3/31/2010#+1096 And #3/31/2010#+1825,"3Y - 5 Y",IIf([Rate Change Date] Between #3/31/2010#+1826 And #3/31/2010#+5475,"5 Y-15 Y",IIf([Rate Change Date]>#3/31/2010#+5475,"greater than 15 Y")))))) AS DateRange
FROM [Loan Information]
GROUP BY [Loan Information].[Loan ID], [Loan Information].[Call Report Type], [Loan Information].[Rate Change Date], ([Loan Information]![Master Loan Balance]+[Loan Information]![Part_ Offset_1]+[Loan Information]![Part_Offset_2])+[Loan Information]![Discount], [Loan Information].[Loan Status], "Rate Change"
HAVING ((([Loan Information].[Call Report Type]) In (12,13,14)) AND (([Loan Information].[Rate Change Date]) Is Not Null) AND (([Loan Information].[Loan Status])="ACTIVE"));

 

[talkiechick]

What do you think of the idea i posted?

 

[jdraw]

Do you have a copy of your database for test purposes?
If not, then create one.

Adjust some table data to match the condition you're trying to test and try it.

A few things you can do witht he current database is to find out

How many "[Rate change date] was null "?
Why are any Dates in the database NULL"?

You could cleanse the data to update the date field Values as appropriate.

You could set up some query that will flag the fact that a Date is Null.

 

[talkiechick]

I am trying to upload the DB because "Your submission could not be processed because a security token was missing". Is there another way i can get it to you?

 

[talkiechick]

Jdraw,

I finally figured out why it wasn't let me attach it. It was because I need to downgrade the Access version, i am currently working on 2007 and it would only take 2003.

Hopefully this will help.

 

[boblarson]

Jdraw,

I finally figured out why it wasn't let me attach it. It was because I need to downgrade the Access version, i am currently working on 2007 and it would only take 2003.

Hopefully this will help.

It should let you upload any version (I've done so). Also, you can not worry about which version it is if you ZIP the file first and upload the .zip file.

 

[talkiechick]

Thanks it just wasn't uploading for some reason. Do you think there is a way to write an IIF state for the above attached DB to give priority to one date field, and if that date field is null it would allocate the data to the second date field?