Thanks in advance, expert advisers!!!

I have a query that I run a report from which returns all records that
have a 'Renew by Date' in the last 365 days, as well as the next 60
days (showing me any software in my tables that have expired in the
past year, or need to be renewed within the next 60 days). This shows
me the Name of the software, the Renew by Date, and the CD Key.

My problem is, I have hundreds of the same the same product that renew
on the same date... so when I run the report, it shows hundreds of the
same thing needing to be renewed.

So, I'd like to filter this further, only showing me one instance of
this software, based on the CD Key (I can't do the name, as I might
have 5 different CD Keys for Microsoft Office Professional, all with
the same Renew by Date, but different CD Keys - so it won't remind me
to renew each instance...)

Here's what I have now:
SELECT Software.swCDKey, Software.StopDisplay, DateDiff('d',Date(),
[RenewByDate]) AS Expr1, Software.*
FROM Software
WHERE (((Software.StopDisplay)=0) AND ((DateDiff('d',Date(),
[RenewByDate])) Between -365 And 60));

(If you're wondering, the StopDisplay is a check box on the record
that you can check if you don't want it to be shown on the renewal
report - so this only shows those records that don't have that box
checked)

I have added DISTINCT after SELECT, but it doesn't change the
results. I have also tried to just do:

SELECT DISTINCT Software.swCDKey
FROM Software
WHERE (((Software.StopDisplay)=0) AND ((DateDiff('d',Date(),
[RenewByDate])) Between -365 And 60));


But when I try to run my report, it asks me to enter data for each of
the fields listed in the report, instead of auto filling. What I
really need is for this query to do exactly what it does now, but only
show me 1 instance of a product with a specific CD Key... ANY HELP IS
GREATLY APPRECIATED!!! THANK YOU AGAIN, IN ADVANCE!!!!

Robert Jacobs wrote:
You have 2 SQL statements. Do both fail or just 1? I noticed on the
first statement you specify some fields from table Software and the last
"field" is Software.* or basically the entire table. Is "Software" a
table or a query? The reason I ask is...where is the SoftwareKey
parameter? I'm not sure why it would be prompting you to enter data for
each field instead of echoing #Name or #Error.

On Jul 19, 10:16*am, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:
I honestly can't give you the answer to this - I created the queries
in Wizard mode (I only know how to work with it in Design view - SQL
view is new to me...) The first SQL statement is the one that is in
use right now - and it was created by the wizard, so I have no clue.
I just need to know how to modify that SQL statement to only give me
one instance that includes that CD Key, and only if it meets the other
requirement (renewal date is within last 365 days to within next 90
days)

I'm very sorry that I'm not very competent in this area...

Robert Jacobs wrote:
Hmmm. How do you run this query? From the database window, from within
a report, or from a form?

If you are running the query from the database window, an example where
I am prompted to enter a parameter might look like this
SELECT TableName.* FROM TableName WHERE ID = [Enter ID];

The [Enter ID], when run. prompts me to enter in the ID I am searching
for. If calling the query/report coming from a form, it might be
ID = Forms!YourFormName!IDFieldName

I most likely would change
SELECT Software.swCDKey, Software.StopDisplay, DateDiff('d',Date(),
[RenewByDate]) AS Expr1, Software.*
to
SELECT , Software.*, DateDiff('d',Date(),[RenewByDate]) AS Expr1
as the .* will pick up every field in the table.

On Jul 19, 11:50*am, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:
I run the query from a Report... It displays all records with expiring
or expired renewal dates. The query (when viewed in datasheet view)
shows me every field in the table, but only the records with expiring
or expired warranties. The Report uses this query to only show me
these same records, displaying only the Name of the software, CD Key,
and renew by date. I just can't get the Report to show me only 1
instance of each CD Key.

I did what you said, and changed my Select statement. It now looks
like:

SELECT Software.*, DateDiff('d',Date(),[RenewByDate]) AS Expr1
FROM Software
WHERE (((Software.StopDisplay)=0) AND ((DateDiff('d',Date(),
[RenewByDate])) Between -365 And 90));

And it gives me the same result, so I'll stick with your way. Is
there a way to add to the WHERE statement something like "AND swCDKey
is distinct/unique" Of course, that's not how it would be written,
but it's the result I would like to have. Is it possible even? I
don't want to type in the CD Key when I run the report, I want the
report to show me all of the CD Keys that have expired or are expiring
- just one instance of each CD Key...

Robert Jacobs wrote:

Seems to me that the cd key is irrelevant and getting in the
way so you can not check for duplicates. Maybe knowing how
many cd keys would be useful, but each individual cd key
does not appear to be useful.

If that thought is valid, then try something like:

SELECT Count(swCDKey) As units, Software.swName,
DateDiff('d',Date(), [RenewByDate]) AS Days
FROM Software
WHERE StopDisplay=0
AND DateDiff('d', Date(), [RenewByDate]) Between -365
And 60
GROUP BY swName, DateDiff('d',Date(), [RenewByDate])

--
Marsh

Robert Jacobs wrote:

Where do you run the query from in the report? As the report's
recordsource?

Here;s an example where I enter an ID manually for a report not calling
it from a form.
Private Sub Report_Open(Cancel As Integer)
Dim strID As String
strID = InputBox("Enter ID")
strID = Trim(strID)
If strID > "" Then strID = "TableID = " & CLng(strID)
Me.Filter = strID
Me.FilterOn = (strID > "")

End Sub

Maybe what could do is create a new query. Add the Software table.
Drag only the software key into the column. From the menu select
View/Totals and select GroupBy for the column. Now you have 1 record
per software key. Save as Query1. Create a new query. Add the table
Software and Query1. Create a link between the two. Drag your fields
down. Save. Use this query as your report's recordsource.

On Jul 19, 12:31*pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:
Alright - I got it to work in one query - I used your idea to GroupBy,
and it's actually OK if I GroupBy all columns - because I'll never
have a Name of a product different than another, using the same CD key
- so it really didn't matter how I got it dwindled down. so
basically, it shows only one instance if the Name (swTypeVersion), CD
Key (swCDKey), and Renewal Date (RemewByDate) all match (which is
perfect for what I'm doing). Here's how the code ended up looking:

SELECT DateDiff('d',Date(),[RenewByDate]) AS Expr1, Software.swCDKey,
Software.swTypeVersion, Software.RenewByDate
FROM Software
GROUP BY DateDiff('d',Date(),[RenewByDate]), Software.swCDKey,
Software.swTypeVersion, Software.RenewByDate, Software.StopDisplay
HAVING (((DateDiff('d',Date(),[RenewByDate])) Between -365 And 90) AND
((Software.StopDisplay)=0));


Don't ask me what it all does, but the designer put it all together
for me. So, again, thank you very much - everything is working
perfectly now!!!! You guys are awesome!

Robert Jacobs wrote:
That HAVING clause should be a WHERE clause.

In this case the result will be the same, but it will be
slower because HAVING is applied after the query does all
the work for every record in the table. On the other hand,
WHERE is used to select only the needed records before the
query starts to do all the grouping work.

It would also be faster to Index the StopDisplay and
RenewalDate fields and change the HAVING to:
WHERE RenewByDate Between DateAdd('d', -365, Date()) And
DateAdd('d', 60, Date()) AND StopDisplay = 0

--
Marsh