Querying multple criteria but allowing them to not be blank

G

grantschneider

I have four different types of salesmen, SVP VP SC and EA. For each
sale that is recorded, any combination of these types of salesmen can
be entered in. However, they are not all always part of a sale so
sometimes there are mutiple blank columns in a sale.

I have two problems:

1. I am using a form to query different combinations of sales people.
If I leave the VP, SC and EA fields blank, and try to query just a
specific SVP's sale, I only get back the records where there is a
value in the other three fields. I want to make it so that I can leave
the others blank if I want and search all records of a corresponding
field in the form.

Do I change the criteria or can I create checkboxes to disable the
fields or something?

2. Also, the SVP's are labeled by number. When I search for SVP "1" it
gives me back SVPs 1,10,11,12 and 13 (there are only 13). How do I
change the criteria? In the table where I query from this column is
set as a number, not a string.




This is my SQL Code:
PARAMETERS [Forms]![Card Form]![SVP] Text ( 255 ), [Forms]![Card Form]!
[VP] Text ( 255 ), [Forms]![Card Form]![SC] Text ( 255 ), [Forms]!
[Card Form]![EA] Text ( 255 );

SELECT [Card Sale List].SVP, [Card Sale List].VP, [Card Sale List].[#
of Cards], [Card Sale List].Aircraft, [Card Sale List].[Card Product],
[Card Sale List].[Card Type],

IIf([card type]="Renewal",[# of Cards]*[Points]*0.5,[# of
Cards]*[Points]) AS TCOM, [TCOM]*0.25 AS [SVP Com], [TCOM]*0.75 AS [VP
Com], [Card Sale List].EA, IIf([EA] Is Not Null,25,0) AS [EA Com],
[Card Sale List].SC, IIf([SC] Is Not Null,[TCOM]*0.15,0) AS [SC Com]

FROM [Card Sale List] INNER JOIN [Card Points] ON [Card Sale
List].Aircraft = [Card Points].Aircraft

WHERE ((([Card Sale List].SVP) Like [Forms]![Card Form]![SVP] & "*")
AND (([Card Sale List].VP) Like [Forms]![Card Form]![VP] & "*") AND
(([Card Sale List].EA) Like [Forms]![Card Form]![EA] & "*") AND
(([Card Sale List].SC) Like [Forms]![Card Form]![SC] & "*"));
 
K

Ken Sheridan

Test each parameter independently for NULL using 'OR <parameter> IS NULL'

WHERE ([Card Sale List].SVP = [Forms]![Card Form]![SVP]
OR [Forms]![Card Form]![SVP] IS NULL)
AND ([Card Sale List].VP = [Forms]![Card Form]![VP]
OR [Forms]![Card Form]![VP] IS NULL)
AND ([Card Sale List].EA = [Forms]![Card Form]![EA]
OR [Forms]![Card Form]![EA] IS NULL)
AND ([Card Sale List].SC = [Forms]![Card Form]![SC]
OR [Forms]![Card Form]![SC] IS NULL)

Each parenthesised expression in the above will evaluate to TRUE for those
rows where the value of the column in question matches the value entered in
the control on the form or where the control is Null, in effect making each
parameter optional.

By using the equality operator rather than the Like operator you'll ensure
only matches for the value entered are returned rather than getting the
specious returns when you enter a value of 1.

Having said that its worth pointing out that having separate columns for the
different types of salesmen is not a good design. Its what's known in the
jargon as 'encoding data as column headings', whereas in a relational
database data should only be stored as explicit values at column positions in
rows in tables, what's called 'the information principle' to use another
piece of jargon I think what you might have here is a many-to-many
relationship between Card Sales List and Salesmen and a many-to-one
relationship between Salesmen and Salesmen Types. A many-to-many
relationship is modelled by a table (called Sold By in my example below)
which references the keys of the tables representing the two related entity
types in one-to-many relationships, so a suitable model would, if I
understand your scenario correctly, be something like this:

Card Sale List----<Sold By>-----Salesmen>-----Salesmen Types

where the < and > signs represent the 'many' side of each relationship (the
'referencing tables' to use another bit of jargon).

Ken Sheridan
Stafford, England

[email protected] said:
I have four different types of salesmen, SVP VP SC and EA. For each
sale that is recorded, any combination of these types of salesmen can
be entered in. However, they are not all always part of a sale so
sometimes there are mutiple blank columns in a sale.

I have two problems:

1. I am using a form to query different combinations of sales people.
If I leave the VP, SC and EA fields blank, and try to query just a
specific SVP's sale, I only get back the records where there is a
value in the other three fields. I want to make it so that I can leave
the others blank if I want and search all records of a corresponding
field in the form.

Do I change the criteria or can I create checkboxes to disable the
fields or something?

2. Also, the SVP's are labeled by number. When I search for SVP "1" it
gives me back SVPs 1,10,11,12 and 13 (there are only 13). How do I
change the criteria? In the table where I query from this column is
set as a number, not a string.




This is my SQL Code:
PARAMETERS [Forms]![Card Form]![SVP] Text ( 255 ), [Forms]![Card Form]!
[VP] Text ( 255 ), [Forms]![Card Form]![SC] Text ( 255 ), [Forms]!
[Card Form]![EA] Text ( 255 );

SELECT [Card Sale List].SVP, [Card Sale List].VP, [Card Sale List].[#
of Cards], [Card Sale List].Aircraft, [Card Sale List].[Card Product],
[Card Sale List].[Card Type],

IIf([card type]="Renewal",[# of Cards]*[Points]*0.5,[# of
Cards]*[Points]) AS TCOM, [TCOM]*0.25 AS [SVP Com], [TCOM]*0.75 AS [VP
Com], [Card Sale List].EA, IIf([EA] Is Not Null,25,0) AS [EA Com],
[Card Sale List].SC, IIf([SC] Is Not Null,[TCOM]*0.15,0) AS [SC Com]

FROM [Card Sale List] INNER JOIN [Card Points] ON [Card Sale
List].Aircraft = [Card Points].Aircraft

WHERE ((([Card Sale List].SVP) Like [Forms]![Card Form]![SVP] & "*")
AND (([Card Sale List].VP) Like [Forms]![Card Form]![VP] & "*") AND
(([Card Sale List].EA) Like [Forms]![Card Form]![EA] & "*") AND
(([Card Sale List].SC) Like [Forms]![Card Form]![SC] & "*"));
Click to expand...
 
G

grantschneider

Test each parameter independently for NULL using 'OR <parameter> IS NULL'

WHERE ([Card Sale List].SVP = [Forms]![Card Form]![SVP]
OR [Forms]![Card Form]![SVP] IS NULL)
AND ([Card Sale List].VP = [Forms]![Card Form]![VP]
OR [Forms]![Card Form]![VP] IS NULL)
AND ([Card Sale List].EA = [Forms]![Card Form]![EA]
OR [Forms]![Card Form]![EA] IS NULL)
AND ([Card Sale List].SC = [Forms]![Card Form]![SC]
OR [Forms]![Card Form]![SC] IS NULL)

Each parenthesised expression in the above will evaluate to TRUE for those
rows where the value of the column in question matches the value entered in
the control on the form or where the control is Null, in effect making each
parameter optional.

By using the equality operator rather than the Like operator you'll ensure
only matches for the value entered are returned rather than getting the
specious returns when you enter a value of 1.

Having said that its worth pointing out that having separate columns for the
different types of salesmen is not a good design. Its what's known in the
jargon as 'encoding data as column headings', whereas in a relational
database data should only be stored as explicit values at column positions in
rows in tables, what's called 'the information principle' to use another
piece of jargon I think what you might have here is a many-to-many
relationship between Card Sales List and Salesmen and a many-to-one
relationship between Salesmen and Salesmen Types. A many-to-many
relationship is modelled by a table (called Sold By in my example below)
which references the keys of the tables representing the two related entity
types in one-to-many relationships, so a suitable model would, if I
understand your scenario correctly, be something like this:

Card Sale List----<Sold By>-----Salesmen>-----Salesmen Types

where the < and > signs represent the 'many' side of each relationship (the
'referencing tables' to use another bit of jargon).

Ken Sheridan
Stafford, England



[email protected] said:
I have four different types of salesmen, SVP VP SC and EA. For each
sale that is recorded, any combination of these types of salesmen can
be entered in. However, they are not all always part of a sale so
sometimes there are mutiple blank columns in a sale.
Click to expand...
I have two problems:
Click to expand...
1. I am using a form to query different combinations of sales people.
If I leave the VP, SC and EA fields blank, and try to query just a
specific SVP's sale, I only get back the records where there is a
value in the other three fields. I want to make it so that I can leave
the others blank if I want and search all records of a corresponding
field in the form.
Click to expand...
Do I change the criteria or can I create checkboxes to disable the
fields or something?
Click to expand...
2. Also, the SVP's are labeled by number. When I search for SVP "1" it
gives me back SVPs 1,10,11,12 and 13 (there are only 13). How do I
change the criteria? In the table where I query from this column is
set as a number, not a string.
Click to expand...
This is my SQL Code:
PARAMETERS [Forms]![Card Form]![SVP] Text ( 255 ), [Forms]![Card Form]!
[VP] Text ( 255 ), [Forms]![Card Form]![SC] Text ( 255 ), [Forms]!
[Card Form]![EA] Text ( 255 );
Click to expand...
SELECT [Card Sale List].SVP, [Card Sale List].VP, [Card Sale List].[#
of Cards], [Card Sale List].Aircraft, [Card Sale List].[Card Product],
[Card Sale List].[Card Type],
Click to expand...
IIf([card type]="Renewal",[# of Cards]*[Points]*0.5,[# of
Cards]*[Points]) AS TCOM, [TCOM]*0.25 AS [SVP Com], [TCOM]*0.75 AS [VP
Com], [Card Sale List].EA, IIf([EA] Is Not Null,25,0) AS [EA Com],
[Card Sale List].SC, IIf([SC] Is Not Null,[TCOM]*0.15,0) AS [SC Com]
Click to expand...
FROM [Card Sale List] INNER JOIN [Card Points] ON [Card Sale
List].Aircraft = [Card Points].Aircraft
Click to expand...
WHERE ((([Card Sale List].SVP) Like [Forms]![Card Form]![SVP] & "*")
AND (([Card Sale List].VP) Like [Forms]![Card Form]![VP] & "*") AND
(([Card Sale List].EA) Like [Forms]![Card Form]![EA] & "*") AND
(([Card Sale List].SC) Like [Forms]![Card Form]![SC] & "*"));- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks Ken,

The reason I have to have four different columns is because different
sales people work with eachother at different times. So I cant create
just one column for each team and leave it at that. For commission
purposes, I must know exactly who worked on which deals which makes it
very difficult.
 
K

Ken Sheridan

Nonetheless it can still be done while respecting the information principle.
I don't want to be proscriptive about this, however, and if you are happy
with your current model then stick with it. Just be aware of the potential
implications of the departure from the theoretical model.

Ken Sheridan
Stafford, England
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

problem with parameter query 3
problem with forms and queries 2
Running sums and other help 0
Running sums 12
Form not working with query 6
Finding matching records on a form 0
form not working with query 0
Search on entered or blank criteria 5

Top