Adding a parameter to an existing query

[Frustrated in AL]

I have a query that requires start and end date. I would like to add a
parameter to ask for a customer number. Customer Number is the title of the
Field that I need to derive the information from. I have included the SQL
below to help you help me. Thank you .

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime,[Enter
Customer Number];
SELECT [Enter Start Date] AS StartDate, [Enter End Date] AS EndDate,
T.Cause, Count(*) AS [Number of Appeals], Count(*)/(SELECT COUNT(*)
FROM [Resolved by Hospice Date Worked] As X
WHERE X.[Date Worked on]
Between [Enter Start Date] And [Enter End Date]
) AS [% of All Appeals], Sum(T.[Sales Amount]) AS [Cost of Appeals],
Sum([Sales Amount])/(SELECT SUM([Sales Amount])
FROM [Resolved by Hospice Date Worked] As Y
WHERE Y.[Date Worked on]
Between [Enter Start Date] And [Enter End Date]
) AS [% of $$ All Appeals]
FROM [Resolved by Hospice Date Worked] AS T
WHERE (((T.[Date Worked on]) Between [Enter Start Date] And [Enter End Date]))
GROUP BY T.Cause;

 

[Jeff Boyce]

You don't mention which table [CustomerNumber] lives in...

Would you be able to add something about:
{WHERE} [CustomerNumber] = [Enter Customer Number]
to your SQL statement?

Have you tried doing this in design view, and simply adding the [Enter
Customer Number] parameter to the Selection Criterion "cell" under the field
[CustomerNumber]?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Frustrated in AL]

Sorry, Jeff.

The original SQL that was working for my other query did not include [Enter
Customer Number] in the opening parameter statement. As my query exists, I
get the following fields: StartDate, EndDate, Cause, Number of Appeals, % of
All Appeals, Cost of Appeals, and % of $$ All Appeals. This includes all of
my groups.

I am trying to bring back a query that will give me this information on a
specific group. The [Customer Number] is in the same table as all of the
rest of the data ~ [Resolved by Hospice Date Worked].

Should be obvious that I do not know Access very well yet.

I appreciate your assistance and your patience.

You don't mention which table [CustomerNumber] lives in...

Would you be able to add something about:
{WHERE} [CustomerNumber] = [Enter Customer Number]
to your SQL statement?

Have you tried doing this in design view, and simply adding the [Enter
Customer Number] parameter to the Selection Criterion "cell" under the field
[CustomerNumber]?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a query that requires start and end date. I would like to add a
parameter to ask for a customer number. Customer Number is the title of
the
Field that I need to derive the information from. I have included the SQL
below to help you help me. Thank you .

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime,[Enter
Customer Number];
SELECT [Enter Start Date] AS StartDate, [Enter End Date] AS EndDate,
T.Cause, Count(*) AS [Number of Appeals], Count(*)/(SELECT COUNT(*)
FROM [Resolved by Hospice Date Worked] As X
WHERE X.[Date Worked on]
Between [Enter Start Date] And [Enter End Date]
) AS [% of All Appeals], Sum(T.[Sales Amount]) AS [Cost of Appeals],
Sum([Sales Amount])/(SELECT SUM([Sales Amount])
FROM [Resolved by Hospice Date Worked] As Y
WHERE Y.[Date Worked on]
Between [Enter Start Date] And [Enter End Date]
) AS [% of $$ All Appeals]
FROM [Resolved by Hospice Date Worked] AS T
WHERE (((T.[Date Worked on]) Between [Enter Start Date] And [Enter End
Date]))
GROUP BY T.Cause;

 

[Jeff Boyce]

I'll repeat my recommendation...

Open the query in design view (not SQL view).

Work with the design view to get first a single table's records.

Then add a second table and tell the query how to relate the records (join).
Add fields and selection as needed and get that working.

Then add the next table ...

Get each piece working first before trying to slam them all together.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry, Jeff.

The original SQL that was working for my other query did not include
[Enter
Customer Number] in the opening parameter statement. As my query exists,
I
get the following fields: StartDate, EndDate, Cause, Number of Appeals, %
of
All Appeals, Cost of Appeals, and % of $$ All Appeals. This includes all
of
my groups.

I am trying to bring back a query that will give me this information on a
specific group. The [Customer Number] is in the same table as all of the
rest of the data ~ [Resolved by Hospice Date Worked].

Should be obvious that I do not know Access very well yet.

I appreciate your assistance and your patience.

You don't mention which table [CustomerNumber] lives in...

Would you be able to add something about:
{WHERE} [CustomerNumber] = [Enter Customer Number]
to your SQL statement?

Have you tried doing this in design view, and simply adding the [Enter
Customer Number] parameter to the Selection Criterion "cell" under the
field
[CustomerNumber]?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a query that requires start and end date. I would like to add a
parameter to ask for a customer number. Customer Number is the title
of
the
Field that I need to derive the information from. I have included the
SQL
below to help you help me. Thank you .

PARAMETERS [Enter Start Date] DateTime, [Enter End Date]
DateTime,[Enter
Customer Number];
SELECT [Enter Start Date] AS StartDate, [Enter End Date] AS EndDate,
T.Cause, Count(*) AS [Number of Appeals], Count(*)/(SELECT COUNT(*)
FROM [Resolved by Hospice Date Worked] As X
WHERE X.[Date Worked on]
Between [Enter Start Date] And [Enter End Date]
) AS [% of All Appeals], Sum(T.[Sales Amount]) AS [Cost of Appeals],
Sum([Sales Amount])/(SELECT SUM([Sales Amount])
FROM [Resolved by Hospice Date Worked] As Y
WHERE Y.[Date Worked on]
Between [Enter Start Date] And [Enter End Date]
) AS [% of $$ All Appeals]
FROM [Resolved by Hospice Date Worked] AS T
WHERE (((T.[Date Worked on]) Between [Enter Start Date] And [Enter End
Date]))
GROUP BY T.Cause;

 

[Frustrated in AL]

I did try to work in the design view. I get the prompt for the Customer
Number, but it returns the data is for all Customers, not just the one
specified.
Or, I get no data.
Or, I get an error message that the expression is typed incorrectly . . . .
after entering the date range and Customer Number.

Obviously, I am not entering this properly, which is the assistance I am
requesting.

As to the other points:

All of the information that I am basing the working query and the revised
query on are pulling inforamation from the same table. Why do I want to
create another table?

My query that prompts me for the date range works fine. I am not trying to
"slam" anything together, just trying to further refine the results that I am
getting from an existing, functioning query.

I need to add "something" to have the data not only specific to a date
range, but to a specific Customer Number.

I do apoligize for my obvious inexperience and naiveté with Access and
appreciate you taking the time to help me.

I'll repeat my recommendation...

Open the query in design view (not SQL view).

Work with the design view to get first a single table's records.

Then add a second table and tell the query how to relate the records (join).
Add fields and selection as needed and get that working.

Then add the next table ...

Get each piece working first before trying to slam them all together.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

message I have a query that requires start and end date. I would like to add a
parameter to ask for a customer number. Customer Number is the title
of
the
Field that I need to derive the information from. I have included the
SQL
below to help you help me. Thank you .

PARAMETERS [Enter Start Date] DateTime, [Enter End Date]
DateTime;
SELECT [Enter Start Date] AS StartDate, [Enter End Date] AS EndDate,
T.Cause, Count(*) AS [Number of Appeals], Count(*)/(SELECT COUNT(*)
FROM [Resolved by Hospice Date Worked] As X
WHERE X.[Date Worked on]
Between [Enter Start Date] And [Enter End Date]
) AS [% of All Appeals], Sum(T.[Sales Amount]) AS [Cost of Appeals],
Sum([Sales Amount])/(SELECT SUM([Sales Amount])
FROM [Resolved by Hospice Date Worked] As Y
WHERE Y.[Date Worked on]
Between [Enter Start Date] And [Enter End Date]
) AS [% of $$ All Appeals]
FROM [Resolved by Hospice Date Worked] AS T
WHERE (((T.[Date Worked on]) Between [Enter Start Date] And [Enter End
Date]))
GROUP BY T.Cause;

 

[John W. Vinson]

I have a query that requires start and end date. I would like to add a
parameter to ask for a customer number. Customer Number is the title of the
Field that I need to derive the information from. I have included the SQL
below to help you help me. Thank you .

Assuming that there is a field [Customer Number] in the table [Resolved By
Hospice Date Worked], and that it's a Number datatype, try

PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime,[Enter
Customer Number] Number;
SELECT [Enter Start Date] AS StartDate, [Enter End Date] AS EndDate,
T.Cause, Count(*) AS [Number of Appeals], Count(*)/(SELECT COUNT(*)
FROM [Resolved by Hospice Date Worked] As X
WHERE X.[Date Worked on]
Between [Enter Start Date] And [Enter End Date] AND X.[Customer Number] =
[Enter Customer Number]
) AS [% of All Appeals], Sum(T.[Sales Amount]) AS [Cost of Appeals],
Sum([Sales Amount])/(SELECT SUM([Sales Amount])
FROM [Resolved by Hospice Date Worked] As Y
WHERE Y.[Date Worked on]
Between [Enter Start Date] And [Enter End Date] AND Y.[Customer Number]
= [Enter Customer Number]
) AS [% of $$ All Appeals]
FROM [Resolved by Hospice Date Worked] AS T
WHERE (((T.[Date Worked on]) Between [Enter Start Date] And [Enter End Date]))
AND T.[Customer Number] = [Enter Customer Number]
GROUP BY T.Cause;


I strongly suspect that a solution without three subqueries - perhaps doing
the summation on a report with appropriate sorting and grouping - may be a
simpler solution!

John W. Vinson [MVP]

 

[Jeff Boyce]

Is there a chance any of the underlying fields involved are "lookup" data
types? If so, this would mean that one value is being stored, but something
else is being displayed.

Were this mine, I would start out 'hard-coding' the customer number as a
selection criterion (no parameter, no prompt) and see if that works.

If it does, I'd build on it. If it didn't, I'd simplify it further to
figure out why giving a specific (and known-to-exist-in-the-data) customer
number wasn't working in the query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I did try to work in the design view. I get the prompt for the Customer
Number, but it returns the data is for all Customers, not just the one
specified.
Or, I get no data.
Or, I get an error message that the expression is typed incorrectly . . .
.
after entering the date range and Customer Number.

Obviously, I am not entering this properly, which is the assistance I am
requesting.

As to the other points:

All of the information that I am basing the working query and the revised
query on are pulling inforamation from the same table. Why do I want to
create another table?

My query that prompts me for the date range works fine. I am not trying
to
"slam" anything together, just trying to further refine the results that I
am
getting from an existing, functioning query.

I need to add "something" to have the data not only specific to a date
range, but to a specific Customer Number.

I do apoligize for my obvious inexperience and naiveté with Access and
appreciate you taking the time to help me.

I'll repeat my recommendation...

Open the query in design view (not SQL view).

Work with the design view to get first a single table's records.

Then add a second table and tell the query how to relate the records
(join).
Add fields and selection as needed and get that working.

Then add the next table ...

Get each piece working first before trying to slam them all together.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

message I have a query that requires start and end date. I would like to add
a
parameter to ask for a customer number. Customer Number is the
title
of
the
Field that I need to derive the information from. I have included
the
SQL
below to help you help me. Thank you .

PARAMETERS [Enter Start Date] DateTime, [Enter End Date]
DateTime;
SELECT [Enter Start Date] AS StartDate, [Enter End Date] AS EndDate,
T.Cause, Count(*) AS [Number of Appeals], Count(*)/(SELECT COUNT(*)
FROM [Resolved by Hospice Date Worked] As X
WHERE X.[Date Worked on]
Between [Enter Start Date] And [Enter End Date]
) AS [% of All Appeals], Sum(T.[Sales Amount]) AS [Cost of Appeals],
Sum([Sales Amount])/(SELECT SUM([Sales Amount])
FROM [Resolved by Hospice Date Worked] As Y
WHERE Y.[Date Worked on]
Between [Enter Start Date] And [Enter End Date]
) AS [% of $$ All Appeals]
FROM [Resolved by Hospice Date Worked] AS T
WHERE (((T.[Date Worked on]) Between [Enter Start Date] And [Enter
End
Date]))
GROUP BY T.Cause;

 

[Frustrated in AL]

Jeff,

I'm not sure what you mean by "lookup" data types. (I did mention that I'm
not very experienced with this, didn't I?)

What I have is a table based on research I do on weekly reports on appeals
that I am given on an Excel spreadsheet. Each week, I research the results
that are given to me and determine how we can reduce the number of appeals
that we get from our Customers by assigning "Responsibility" to a specific
department within my company, and then further breaking down the
responsibility by "Cause". All of this information is then Imported to my
existing table in Access.

I add to this table with every weekly report that I process. There are no
special properties given to any of the 18 fields on the table other than
monetary designation for formatting. I have several queries and reports that
I have successfully generated form this table, with the assistance of this
wonderful Discussion group and alterations I have been able to make on my own.

As I have addressed the issues within the company by specific department, I
now need to review the particular customers that are having problems and the
specific causes of their problems.

This is why I need to be able to use the functioning query that I have while
being able to refine the results to a specific customer. I’m sure that what
needs to be done is fairly simple, I just can’t see it and unfortunately, I
do not clearly understand what you are asking me: “lookup†data,
“hard-codingâ€.

I have data from February on this table and only need to gather information
on a quarterly basis.

Thank you again for taking the time and know that I have been working on
this on my own as I await your advice hoping that it will click for me.

Is there a chance any of the underlying fields involved are "lookup" data
types? If so, this would mean that one value is being stored, but something
else is being displayed.

Were this mine, I would start out 'hard-coding' the customer number as a
selection criterion (no parameter, no prompt) and see if that works.

If it does, I'd build on it. If it didn't, I'd simplify it further to
figure out why giving a specific (and known-to-exist-in-the-data) customer
number wasn't working in the query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I did try to work in the design view. I get the prompt for the Customer
Number, but it returns the data is for all Customers, not just the one
specified.
Or, I get no data.
Or, I get an error message that the expression is typed incorrectly . . .
.
after entering the date range and Customer Number.

Obviously, I am not entering this properly, which is the assistance I am
requesting.

As to the other points:

All of the information that I am basing the working query and the revised
query on are pulling inforamation from the same table. Why do I want to
create another table?

My query that prompts me for the date range works fine. I am not trying
to
"slam" anything together, just trying to further refine the results that I
am
getting from an existing, functioning query.

I need to add "something" to have the data not only specific to a date
range, but to a specific Customer Number.

I do apoligize for my obvious inexperience and naiveté with Access and
appreciate you taking the time to help me.

I'll repeat my recommendation...

Open the query in design view (not SQL view).

Work with the design view to get first a single table's records.

Then add a second table and tell the query how to relate the records
(join).
Add fields and selection as needed and get that working.

Then add the next table ...

Get each piece working first before trying to slam them all together.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

message I have a query that requires start and end date. I would like to add
a
parameter to ask for a customer number. Customer Number is the
title
of
the
Field that I need to derive the information from. I have included
the
SQL
below to help you help me. Thank you .

PARAMETERS [Enter Start Date] DateTime, [Enter End Date]
DateTime;
SELECT [Enter Start Date] AS StartDate, [Enter End Date] AS EndDate,
T.Cause, Count(*) AS [Number of Appeals], Count(*)/(SELECT COUNT(*)
FROM [Resolved by Hospice Date Worked] As X
WHERE X.[Date Worked on]
Between [Enter Start Date] And [Enter End Date]
) AS [% of All Appeals], Sum(T.[Sales Amount]) AS [Cost of Appeals],
Sum([Sales Amount])/(SELECT SUM([Sales Amount])
FROM [Resolved by Hospice Date Worked] As Y
WHERE Y.[Date Worked on]
Between [Enter Start Date] And [Enter End Date]
) AS [% of $$ All Appeals]
FROM [Resolved by Hospice Date Worked] AS T
WHERE (((T.[Date Worked on]) Between [Enter Start Date] And [Enter
End
Date]))
GROUP BY T.Cause;

 

[Jeff Boyce]

It sounds like none of your fields are "lookup" data types. If you imported
existing data to generate the table, they wouldn't be.

Open your query in design view. Where the parameter prompt is located
(selection criterion under your field), erase it, and substitute a known
value. Save the query as some new name. Run this "hard-coded" parameter
query. Do you get what you want/need?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

I'm not sure what you mean by "lookup" data types. (I did mention that
I'm
not very experienced with this, didn't I?)

What I have is a table based on research I do on weekly reports on appeals
that I am given on an Excel spreadsheet. Each week, I research the
results
that are given to me and determine how we can reduce the number of appeals
that we get from our Customers by assigning "Responsibility" to a specific
department within my company, and then further breaking down the
responsibility by "Cause". All of this information is then Imported to my
existing table in Access.

I add to this table with every weekly report that I process. There are no
special properties given to any of the 18 fields on the table other than
monetary designation for formatting. I have several queries and reports
that
I have successfully generated form this table, with the assistance of this
wonderful Discussion group and alterations I have been able to make on my
own.

As I have addressed the issues within the company by specific department,
I
now need to review the particular customers that are having problems and
the
specific causes of their problems.

This is why I need to be able to use the functioning query that I have
while
being able to refine the results to a specific customer. I'm sure that
what
needs to be done is fairly simple, I just can't see it and unfortunately,
I
do not clearly understand what you are asking me: "lookup" data,
"hard-coding".

I have data from February on this table and only need to gather
information
on a quarterly basis.

Thank you again for taking the time and know that I have been working on
this on my own as I await your advice hoping that it will click for me.

Is there a chance any of the underlying fields involved are "lookup" data
types? If so, this would mean that one value is being stored, but
something
else is being displayed.

Were this mine, I would start out 'hard-coding' the customer number as a
selection criterion (no parameter, no prompt) and see if that works.

If it does, I'd build on it. If it didn't, I'd simplify it further to
figure out why giving a specific (and known-to-exist-in-the-data)
customer
number wasn't working in the query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I did try to work in the design view. I get the prompt for the Customer
Number, but it returns the data is for all Customers, not just the one
specified.
Or, I get no data.
Or, I get an error message that the expression is typed incorrectly . .
.
.
after entering the date range and Customer Number.

Obviously, I am not entering this properly, which is the assistance I
am
requesting.

As to the other points:

All of the information that I am basing the working query and the
revised
query on are pulling inforamation from the same table. Why do I want
to
create another table?

My query that prompts me for the date range works fine. I am not
trying
to
"slam" anything together, just trying to further refine the results
that I
am
getting from an existing, functioning query.

I need to add "something" to have the data not only specific to a date
range, but to a specific Customer Number.

I do apoligize for my obvious inexperience and naiveté with Access and
appreciate you taking the time to help me.

:

I'll repeat my recommendation...

Open the query in design view (not SQL view).

Work with the design view to get first a single table's records.

Then add a second table and tell the query how to relate the records
(join).
Add fields and selection as needed and get that working.

Then add the next table ...

Get each piece working first before trying to slam them all together.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

message I have a query that requires start and end date. I would like to
add
a
parameter to ask for a customer number. Customer Number is the
title
of
the
Field that I need to derive the information from. I have
included
the
SQL
below to help you help me. Thank you .

PARAMETERS [Enter Start Date] DateTime, [Enter End Date]
DateTime;
SELECT [Enter Start Date] AS StartDate, [Enter End Date] AS
EndDate,
T.Cause, Count(*) AS [Number of Appeals], Count(*)/(SELECT
COUNT(*)
FROM [Resolved by Hospice Date Worked] As X
WHERE X.[Date Worked on]
Between [Enter Start Date] And [Enter End Date]
) AS [% of All Appeals], Sum(T.[Sales Amount]) AS [Cost of
Appeals],
Sum([Sales Amount])/(SELECT SUM([Sales Amount])
FROM [Resolved by Hospice Date Worked] As Y
WHERE Y.[Date Worked on]
Between [Enter Start Date] And [Enter End Date]
) AS [% of $$ All Appeals]
FROM [Resolved by Hospice Date Worked] AS T
WHERE (((T.[Date Worked on]) Between [Enter Start Date] And
[Enter
End
Date]))
GROUP BY T.Cause;