Parameter Query: Limit Parameter

[Lkswrt54g]

Hey guys, I need to create a limit parameter that needs to ask for a dollar
limit each time it is executed.

For example, if she limits the list to customers that have a total 2007
sales of greater than $200, the list may include over 50 customers. But if
she sets the limit at $1,000, the list may only include 5 customers. She
needs to be able to try different sales limits until the list is around 15
customers. To do this, a parameter query is needed. The query needs to ask
for a dollar limit each time it is executed so the manager can try different
amounts until the right number of customers is selected. For the query to
work next year and the years after, she also needs to enter the year.

Also in the instructions it states The limit parameter must be preceded by a
"greater than" symbol like: >[Limit?] because we want to select the records
with total sales that are greater than the limit.

 

[John Spencer]

So do you want a simple parameter query or do you want to base the parameters
on a form? The absolute simplest is to use a parameter query.


In design view
Field: Sales
Criteria: >= [Enter Minimum Sales Amount]

Is the "year" field a datetime field, a text field, or a number field?
Assuming that the "year field" is text or a number.

Field: SalesYear
Criteria: [Enter Sales Year]

In SQL that would look like
SELECT *
FROM YourTable
WHERE Sales >= [Enter Minimum Sales Amount]
AND SalesYear = [Enter Sales Year]

Of course, if you are trying to get the top 15 customers you could just use

SELECT TOP 15 *
FROM YourTable
WHERE SalesYear = [Enter Sales Year]
ORDER BY Sales DESC

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[mike@work]

this is easy to solve and u can make it look really good to...

create a form with a dropdown listbox containing different ammounts, 500,
1000, 1500 etc. then have some kind of continue button, which will open a
form, linked to a query. in the criterea of the amount field in the query, u
can use the builder to point to the listbox on your form, so it would look
something like...

MyForm![AmountListBox]. the newly opened form will show the results and u can put a return button on it so that the user can select a new amount from the original form and hit the continue button again.

Hey guys, I need to create a limit parameter that needs to ask for a dollar
limit each time it is executed.

For example, if she limits the list to customers that have a total 2007
sales of greater than $200, the list may include over 50 customers. But if
she sets the limit at $1,000, the list may only include 5 customers. She
needs to be able to try different sales limits until the list is around 15
customers. To do this, a parameter query is needed. The query needs to ask
for a dollar limit each time it is executed so the manager can try different
amounts until the right number of customers is selected. For the query to
work next year and the years after, she also needs to enter the year.

Also in the instructions it states The limit parameter must be preceded by a
"greater than" symbol like: >[Limit?] because we want to select the records
with total sales that are greater than the limit.

 

[Dale Fye]

Another way to approach this, if you only want to work with a specified
number of customers (you mention ~ 15), then instead of filtering on a dollar
amount, you could sort by the dollar amount and select the Top 15.

This would give you the top 15 customers (and any customers whose dollars
spent) equals that of the 15th customer.

The SQL might look like:

SELECT TOP 15 Cust_LastName, Cust_FirstName, SUM(Cust_Spent) as Sales
FROM Customers INNER JOIN CustomerSales
ON Customers.Cust_ID = CustomerSales.Cust_ID
WHERE YEAR(CustomerSales.SalesDate) = 2007
GROUP BY Cust_LastName, Cust_FirstName
ORDER BY SUM(Cust_Spent)

Dale

 

[lkswrt54g]

This is for my college course, and he told us to not make a form. Here is a
background on the assignment, if you want to read it I'd appreciate it. The
first response I got said to enter >=[Enter Minimum Sales Amount] in the
criteria, what happens when I do that is it shows just a bunch of random
values ranging from 15,000 to 600, maybe my queries are royally messed up?
Someone e-mail me if possible or have aim? My email is (e-mail address removed)

Almost half of Pro Camera’s suppliers have announced new versions or models
of their products which is going to make many of the current stock items
obsolete. The Marketing Manager of Pro Camera has decided to get rid of the
items that will be replaced by the new products. Several techniques will be
used starting with a discount program using discount coupons, followed by a
private sale for frequent customers, followed by a sidewalk sale in front of
the store. The manager already has a coupon letter but now needs an
invitation letter for the private sale. The sale will occur on Saturday
morning (you pick the date) from 8:00 am to noon and feature discounts from
20% to 70%! This invitation will only be sent to customers whose total
purchases for 2007 exceed a certain amount. The manager would like to send
out around 15 invitations but she doesn't know what total dollar amount limit
would result in the right number of customers selected. For example, if she
limits the list to customers that have a total 2007 sales of greater than
$200, the list may include over 50 customers. But if she sets the limit at
$1,000, the list may only include 5 customers. She needs to be able to try
different sales limits until the list is around 15 customers. To do this, a
parameter query is needed. The query needs to ask for a dollar limit each
time it is executed so the manager can try different amounts until the right
number of customers is selected. For the query to work next year and the
years after, she also needs to enter the year.

Now for the query he wants us to Query: qryCustomersByTotalSales (40%)

Create a parameter query (with the sale year being one parameter and the
total sales limit per customer being the second parameter) to show a listing
of these customers. Include (but not limited to) the following information:
 Name (first and then last strung together into one field)
 Street Address
 Location (city, state, and zip strung together into one field)
 Sale year
 Total sales (sum of the quantity times the price from the Line Item table)
The limit parameter must be preceded by a "greater than" symbol like:

[Limit?] because we want to select the records with total sales that are

greater than the limit

 

[lkswrt54g]

I think I may have figured it out, unless this is not the right way to do it
but it is somehow working. I had to create a new field called total sales
Total Sales: [tblSaleLineItem]![ItemQty]*[tblSaleLineItem]![ItemUnitPrice]
what i was doing was I was entering the criteria under this field. What I
ended up doing wat entering thec criteria under the itemunitprice field,
would that be a way of doing it?

 

[Dale Fye]

It is generally frowned upon within most institutions to have someone else do
your homework for you, which is what you are asking us to do.

--

email address is invalid
Please reply to newsgroup only.

This is for my college course, and he told us to not make a form. Here is a
background on the assignment, if you want to read it I'd appreciate it. The
first response I got said to enter >=[Enter Minimum Sales Amount] in the
criteria, what happens when I do that is it shows just a bunch of random
values ranging from 15,000 to 600, maybe my queries are royally messed up?
Someone e-mail me if possible or have aim? My email is (e-mail address removed)

Almost half of Pro Camera’s suppliers have announced new versions or models
of their products which is going to make many of the current stock items
obsolete. The Marketing Manager of Pro Camera has decided to get rid of the
items that will be replaced by the new products. Several techniques will be
used starting with a discount program using discount coupons, followed by a
private sale for frequent customers, followed by a sidewalk sale in front of
the store. The manager already has a coupon letter but now needs an
invitation letter for the private sale. The sale will occur on Saturday
morning (you pick the date) from 8:00 am to noon and feature discounts from
20% to 70%! This invitation will only be sent to customers whose total
purchases for 2007 exceed a certain amount. The manager would like to send
out around 15 invitations but she doesn't know what total dollar amount limit
would result in the right number of customers selected. For example, if she
limits the list to customers that have a total 2007 sales of greater than
$200, the list may include over 50 customers. But if she sets the limit at
$1,000, the list may only include 5 customers. She needs to be able to try
different sales limits until the list is around 15 customers. To do this, a
parameter query is needed. The query needs to ask for a dollar limit each
time it is executed so the manager can try different amounts until the right
number of customers is selected. For the query to work next year and the
years after, she also needs to enter the year.

Now for the query he wants us to Query: qryCustomersByTotalSales (40%)

Create a parameter query (with the sale year being one parameter and the
total sales limit per customer being the second parameter) to show a listing
of these customers. Include (but not limited to) the following information:
 Name (first and then last strung together into one field)
 Street Address
 Location (city, state, and zip strung together into one field)
 Sale year
 Total sales (sum of the quantity times the price from the Line Item table)
The limit parameter must be preceded by a "greater than" symbol like:

[Limit?] because we want to select the records with total sales that are

greater than the limit

 

[Evi]

I'm glad you explained that this was a homework assignment. My imagination
boggled at some poor manager entering amount after amount in the hope that
it would finally make 15. But if this assignment was designed by a teacher
then all is explained: there is no need to find a real-world solution! .

I suspect that your teacher will be looking for a Totals query somewhere in
this.

Tip: it is often useful to base one query on another especially when you
want to group things in a Totals query

Evi

I think I may have figured it out, unless this is not the right way to do it
but it is somehow working. I had to create a new field called total sales
Total Sales: [tblSaleLineItem]![ItemQty]*[tblSaleLineItem]![ItemUnitPrice]
what i was doing was I was entering the criteria under this field. What I
ended up doing wat entering thec criteria under the itemunitprice field,
would that be a way of doing it?

Hey guys, I need to create a limit parameter that needs to ask for a dollar
limit each time it is executed.

For example, if she limits the list to customers that have a total 2007
sales of greater than $200, the list may include over 50 customers. But if
she sets the limit at $1,000, the list may only include 5 customers. She
needs to be able to try different sales limits until the list is around 15
customers. To do this, a parameter query is needed. The query needs to ask
for a dollar limit each time it is executed so the manager can try different
amounts until the right number of customers is selected. For the query to
work next year and the years after, she also needs to enter the year.

Also in the instructions it states The limit parameter must be preceded by a
"greater than" symbol like: >[Limit?] because we want to select the records
with total sales that are greater than the limit.