It’s a little tricky in query design view as you have to do it in such a way
that it has the same effect as using parentheses in SQL. In the latter the
SQL would look something like this:
SELECT *
FROM YourTable
WHERE
[Account Manager] = [Forms]![FRM_REPORTS2]![Text_Account Manager]
AND
([Food Line 1] > 0 OR [Food Line 2] > 0 OR [Food Line 3] > 0);
You'll see that the Boolean OR operations are all wrapped in parentheses
which makes them evaluate independently of the Account manager criterion,
between which and the parenthesised expression there is a Boolean AND
operation. To get the same effect in query design view ( I hope I'm right
here as I would always do it directly in SQL view myself ) you would enter
the parameter [Forms]![FRM_REPORTS2]![Text_Account Manager] three times in
separate criteria rows under the Account manger column, then enter > 0 three
times on the same rows, but in one case under the Food Line 1 column, in one
under Food Line 2 and in one under Food Line 3.
You can do it more easily in design view however by entering everything in
the current criteria row under Account manger as one long expression:
[Forms]![FRM_REPORTS2]![Text_Account Manager] AND ([Food Line 1] > 0 OR
[Food Line 2] > 0 OR [Food Line 3] > 0)
If you then save the query in design view, when you reopen it in design view
you'll find Access has magically moved it all around. The end result should
be the same, however.
If you do enter it all as one expression make sure you get the field names
exactly right. An unexpected parameter prompt is often merely the result of
something like a minor misspelling, or the omission of a space or inclusion
of an extra one.
Ken Sheridan
Stafford, England
Kate said:
Hey Ken
Thanks for the sppedy reply. My query isn't based on sums but just pulls
the values across. The reason I've written the query is because I want to
filter by account manager-I then base a report on this query so the account
managers can see sales by customers. My table does have column headings
which are food line1 etc etc which then have customers in the rows and the
values against the relevant customer and line.
I realise I've definately not done this the most effective way but am
muddling through at a mostly-beginner level!
I've put in your WHERE criteria (fyi I've put this under my
[Forms]![FRM_REPORTS2]![Text_Account Manager]) criteria which is already
there. Am I putting it in the correct place. When I press 'Run', it asked
me for the account manager (as expected) but then asked me for the parameter
value for Food Line3?
Any ideas?
Ken Sheridan said:
You need to put some extra criteria in the query so that it excludes the rows
where any of the food line values are > 0. This is done by means of the
Boolean OR operator Just how you add these extra criteria to the query
depends on whether the values are aggregates (e.g. SUM) with the query being
grouped by each customer, or whether its ungrouped and the zeros are values
in individual rows in the table. If its the former they would go in a HAVING
clause (which acts on the data after its grouped), e.g.
HAVING SUM([Food Line 1]) > 0 OR SUM([Food Line 2]) > 0 OR SUM([Food Line
2]) > 0
If it’s the latter it would go in a WHERE clause:
WHERE [Food Line 1] > 0 OR [Food Line 2] > 0 OR [Food Line 2] > 0
If you can post the SQL for the query here we should be able to see what's
required.
However, I do suspect your table(s) may suffer from a fundamental design
flaw. Unless the queries in question are cross-tabs it does sound as if you
have separate columns in a table for the different food and beverage lines.
This is what's known in the jargon as 'encoding data as column headings'. A
fundamental principle of the database relational model is that data are
stored as values at column positions in rows in tables, and in no other way.
This is not merely a theoretical requirement, but does have significant
effects on the functionality of the database. To correct it the table almost
certainly needs 'decomposing' into a number of related tables where the data
values are in rows rather than expressed as column headings. This is not as
frightening as it might sound as this can be very largely automated with a
few 'action' queries.
Ken Sheridan
Stafford, England
:
I posted a question the other day which was answered very promptly so thought
I'd try again!
I've got a database which contains hundreds of customers and their orders.
I've written some queries (very basic!) to pull together customer listed to
certain account managers. However, I now want to supress any customers which
haven't ordered anything.
So, for example, some of the column headings are food line1, food line2,
food line3, beverage line1, beverage line 2 etc. I now want the query to
just show food lines 1-3. This is fine, but as I've defined it for a 'x'
account manager (which is pulled from a form) - it shows me every customer.
I don't want to see customers with blank or zero values in food lines1-3.
How can I do this?
Thanks in advance!!
