Interesting QUERY problem (4 Viewers)

[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?

 

[June7]

Sounds like a complex WHERE clause using OR and AND operators. Or maybe a UNION query will be needed.

Provide sample data. Can build a table in post or attach a file (Access, Excel, CSV)

 

[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.