Prev: Hiding Headers In Multiple Column Reports
Next: Convering from Access97 to Access2010
From: Robert Jacobs on 19 Jul 2010 10:19
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!!!!
From: Salad on 19 Jul 2010 11:16
Robert Jacobs wrote:
> 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!!!!

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.


From: Robert Jacobs on 19 Jul 2010 12:01
On Jul 19, 10:16 am, Salad <sa...(a)oilandvinegar.com> wrote:
> Robert Jacobs wrote:
> > 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!!!!
>
> 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.

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...
From: Salad on 19 Jul 2010 12:50
Robert Jacobs wrote:
> On Jul 19, 10:16 am, Salad <sa...(a)oilandvinegar.com> wrote:
>
>>Robert Jacobs wrote:
>>
>>>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!!!!
>>
>>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.
>
>
> 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...

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.
From: Robert Jacobs on 19 Jul 2010 13:05
On Jul 19, 11:50 am, Salad <sa...(a)oilandvinegar.com> wrote:
> Robert Jacobs wrote:
> > On Jul 19, 10:16 am, Salad <sa...(a)oilandvinegar.com> wrote:
>
> >>Robert Jacobs wrote:
>
> >>>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!!!!
>
> >>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.
>
> > 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...
>
> 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.

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...
 |  Next  |  Last
Pages: 1 2
Prev: Hiding Headers In Multiple Column Reports
Next: Convering from Access97 to Access2010