Connect Queries

[talkiechick]

I am sorry to keep bothering the smart gurus of this forum. I solved my date issue. So let me do a little explaining.

I have probably 12 plus queries that have a various amount of criteria within each query. The differing factor between the queries are the date ranges. less than 3 months, more than 3 month to a year, year to 3 years, etc.

In my original issue I could have the data pull date ranges from two different date fields, I have accomplished that. Now however, I need to have the queries all connect and the have each row of data only counted once. Currently each individual query functions on its own and it counting data that should only be counted in the correct range.The correct date fields which should be counted in each query should be the oldest (closest to the present) date, whether it be in maturity date or rate change date.

I wonder if this is too complicated for me.

Any suggestions?

 

[Vassago]

If I understand correctly, you want the results from each query in one query without duplicating results? Why not just create one query and using the OR function within the single Query to separate the criteria?

 

[talkiechick]

You understand me perfectly. The problem is i need a total for each date range. There is a currency amount associated with each distinct row of data. That is the problem, I can't figure out how to have each date range show in the same query and have totals for each of them.

Should i attempt using a long switch expression to accomplish it?

 

[Vassago]

You understand me perfectly. The problem is i need a total for each date range. There is a currency amount associated with each distinct row of data. That is the problem, I can't figure out how to have each date range show in the same query and have totals for each of them.

Should i attempt using a long switch expression to accomplish it?

Ahhh, I would use an IIF statement to create a unique value for each date range. For example:

IIF(MyDate < Dateadd("M",-3,Date()),"1 - Less than 3",
IIF(MyDate between Dateadd("M",-3,Date()) and Dateadd("Y",-1,Date()),"2 - 3 To 1 Year",
"etc."))

And then just do a total query for on the required fields. This way, the final result would be:

1 - Less than 3 25
2 - 3 to 1 Year 35

Etc...

 

[talkiechick]

The only problem i see with this solution is how i would incorporate the two date fields, and have it pick the more present date?

 

[Vassago]

The only problem i see with this solution is how i would incorporate the two date fields, and have it pick the more present date?

You may be able to use an OR statement. Maybe I'm not understanding how your date fields are set up.

 

[talkiechick]

Attached is a screen shot of how i have one of the queries set up.

the problem is I have two date fields and I want it to use both to pull the data into each date range, but only use the data once. I also need the priority of the date to fall to the date fields with the more present date value.

For example if Date1 's value is 4/1/10 then it should fall in the less than 3 month range because the date i am starting from is 3/31/10. How if the same row of data has a date 2 value for 10/25/2012 it would fall in the 1-3 yr range, but because Date 1's field is closer to the current date, I need the data to be allocated to the less than 3 month range.

Thanks for your help.

 

[talkiechick]

I am guessing there isn't solution to this, without manual programming.