Solved Interesting QUERY problem

[mloucel]

Hello Gurus.

I have a very interesting problem on a query I can't solve.
For time purposes, and to make it easy, I'll use 4 fields as Example:

InitialDate
FN
LN
ApprovedD

Now this query already displays the first 90 days of InitialDate.

The Issue:

I need: based on those 90 days, limit more my query with the following:

1) Display the very first 15 days (Date() -15) from InitialDate, regardless if ApprovedD has any date or not.
--- I need to check from today's date, regardless if InitialDate will display only 10 days or 2 days out of those 15.

2) Then once I have those 15 days (or 2 or 3 or 10 saved whatever the result from #1), I need to display the rest of the 90 days (Day 16 to 90) from InitialDate " BUT ONLY if ApprovedD has a value (Is Not Null) "

In Excel I was able to do it, during Sorting I can manually sort by date, then pick only the first 90 days, then on ApprovedD manually click the check mark that will give me the results I wanted, but it was all manual, some I did every 3 months, so I was already prepared before time.

I don't really know if that is even possible in access, but any help will be appreciated.

 

[GaP42]

One approach would be to write 2 queries and then combine them - ie as a union query.
The first is to query your existing query for records where the initial date < = 15 days from the current date.
The second would query the existing query for records where the initial date > 15 days from the initial current date and ApprovedD is not null.
Ensure both have the same no of columns and in the same order.
Create a union query to combine each. Sort by initial date?

 

[plog]

Words rarely working in describing how you want a query to work. Example data is the way to go. Give us two sets of data:

A. Starting data from your data source. Include data source and field names and enough rows to demonstrate all cases

B. Expected results from A. Show us what data you expect to end up with when you feed the data from A above into your query.

Again, two sets of sample data--starting and expected results.

 

[cheekybuddha]

SELECT
  InitialDate,
  FN,
  LN,
  ApprovedD
WHERE InitialDate >= Date() - 15
   OR (
     InitialDate >= Date() - 90
     AND
     ApprovedD IS NOT NULL
   )
;

 

[cheekybuddha]

Now this query already displays the first 90 days of InitialDate.

Can you post the full SQL of this original query?

 

[dalski]

Wherever possible avoid a union query & go via CheekyBuddha's logic. If you used a union query you would be loosing the ability to edit data later. You'd likely reference this query in different applications throughout your application only with diminished functionality (in not being able to update your records). Union queries are awesome, but if it's possible with a standardized query; use a standard query IMHO.

 

[cheekybuddha]

& go via CheekyBuddha's logic.

I'm not sure my logic is correct! WE need to hear from @mloucel for clarification of what is required.

 

[dalski]

I'm not sure my logic is correct! WE need to hear from @mloucel for clarification of what is required.

Sure, but as queries go it isn't that bad a query & quite a simple one. He certainly shouldn't be using a union query for the reasons mentioned & you've kindly indicated an elegant solution with logic he can follow in a simple standardized query; not even a sub-query... A few mere lines.

EDIT - Credit for a Union Query is deserved for making users aware of things they may not be aware of.

 

[arnelgp]

you said you already has a resulting query? you only want to group them?
create an Expression on your original 90 days query:

Expr1: Date() - InitialDate

now create new query to add the Group:

SELECT [90daysQ].ID,
     [90daysQ].InitialDate, 
     [90daysQ].FN, 
     [90daysQ].LN, [90daysQ].ApprovedD, IIf([Expr1]<16,"1-15","16-90") AS [Group]
FROM 90daysQ
ORDER BY [90daysQ].Expr1;

replace 90daysQ with the name of the query on your post1.

 

[mloucel]

you said you already has a resulting query? you only want to group them?
create an Expression on your original 90 days query:

Expr1: Date() - InitialDate

now create new query to add the Group:

SELECT [90daysQ].ID,
     [90daysQ].InitialDate,
     [90daysQ].FN,
     [90daysQ].LN, [90daysQ].ApprovedD, IIf([Expr1]<16,"1-15","16-90") AS [Group]
FROM 90daysQ
ORDER BY [90daysQ].Expr1;

replace 90daysQ with the name of the query on your post1.

Bingo...

You gave me the solution, not exactly like that, but you gave me the path.
I don't have to account for the very first 15 days, WHO CARES, those are already there in my 90 day query, exactly as I want them, I just need to account for the last days 16-90 those are the ones that will have the "Is Not Null" not the first 15 days.

So I just need to apply the logic to any data beyond the first 15 days.

This line gave me the GOTCHA moment:
"[90daysQ].LN, [90daysQ].ApprovedD, IIf([Expr1]<16,"1-15","16-90") AS [Group]"

Thank you @arnelgp

 

[mloucel]

I'm not sure my logic is correct! WE need to hear from @mloucel for clarification of what is required.

Thanks but @arnelgp gave me the path to the solution, check #11, I was making things more complicated, and the solution was there all the time, I really don't need to care for the first 15 days, those have to be just the way they are, I only need to concentrate in any data form day 16 to 90, that's where I need the "Is Not Null" moment, @arnelgp show me his solution and right there I had the gotcha moment.

I LOVE THIS COMMUNITY, thank you all for your input.

 

[mloucel]

By the way this was the solution:
in the ApprovedDate column under Criteria:

(ReferDate >= Date() - 21 And ApprovedDate Is Not Null) Or ReferDate < Date() - 21

I decided to expand the criteria to 21 days.

Thank you ALL.