Form for custom queries

P

PJFry

I maintain a database of contract pay rates for my firm. We use the data to
determine the starting point to contract negotiations with consultants. Our
current MO is to have an advisor send me a request to find out what the
average rate would be for workers where the industry is Law Enforcement, the
job category is Administrative or Finance and the job is located in Region
One or Florida. Once I have the answer(s) I drop it in an Excel sheet and
send it back.

The SQL may make the question clearer:
SELECT tIndustry.Industry, tCategory.txtCategory, tMRM.dblBillRate,
tMRM.dblPayRate, tMRM.lngOrderID, tMRM.txtJobTitle, tRegions.City,
tRegions.State, tRegions.Region, ([dblBillRate]/[dblPayRate])-1 AS MarkUp,
IIf([MarkUp]>1,"Exclude-High",IIf([MarkUp]<0.3,"Exclude-Low","Include")) AS
Flag, sqFinancial.CountOfID
FROM tCategory RIGHT JOIN (tIndustry INNER JOIN (tClient INNER JOIN ((tMRM
INNER JOIN tRegions ON (tMRM.txtState = tRegions.State) AND (tMRM.txtCity =
tRegions.City)) INNER JOIN sqFinancial ON tMRM.txtJobTitle =
sqFinancial.txtJobTitle) ON tClient.ID = tMRM.lngClient) ON tIndustry.ID =
tClient.lngIndustry) ON tCategory.ID = tMRM.lngPrOCategory
WHERE (((tIndustry.Industry)="Law Enforment") AND
((tCategory.txtCategory)="Administrative" Or
(tCategory.txtCategory)="Finance") AND ((tRegions.State)="FL")) OR
(((tIndustry.Industry)="Law Enforment") AND
((tCategory.txtCategory)="Administrative" Or
(tCategory.txtCategory)="Finance") AND ((tRegions.Region)=1));

I want to create a form that allows the users to select regions, if any, the
states, if any, job categories, etc. But I don’t how to create a SQL
statement where there could be one limit or ten limits, depending on the
request. I imagine there has to be a way to do this, but I am not sure where
to start.

All that being said, is this even the best way to approach the problem. I
want the users to be able extract the data themselves, but do it in a way
that is intuitive and simple. Alternate suggestions welcome!

Thanks in advance,
PJ
 
A

AKphidelt

Hey PJFry, I'm a little lost on where you are having a problem. Are you
getting an error message when running this query or is nothing showing up
when there is something supposed to be showing up. I have a query that has
over 10 different limits so it is possible. Just need to know a little more
info.
 
P

PJFry

I haven't done anything yet. Before I put work into the process I wanted to
get a conceptual idea of what to do. For example, there are ten different
industries to choose from. If a user chooses two of the ten, how is that
information translated to a query?

Thanks!
PJ


AKphidelt said:
Hey PJFry, I'm a little lost on where you are having a problem. Are you
getting an error message when running this query or is nothing showing up
when there is something supposed to be showing up. I have a query that has
over 10 different limits so it is possible. Just need to know a little more
info.

PJFry said:
I maintain a database of contract pay rates for my firm. We use the data to
determine the starting point to contract negotiations with consultants. Our
current MO is to have an advisor send me a request to find out what the
average rate would be for workers where the industry is Law Enforcement, the
job category is Administrative or Finance and the job is located in Region
One or Florida. Once I have the answer(s) I drop it in an Excel sheet and
send it back.

The SQL may make the question clearer:
SELECT tIndustry.Industry, tCategory.txtCategory, tMRM.dblBillRate,
tMRM.dblPayRate, tMRM.lngOrderID, tMRM.txtJobTitle, tRegions.City,
tRegions.State, tRegions.Region, ([dblBillRate]/[dblPayRate])-1 AS MarkUp,
IIf([MarkUp]>1,"Exclude-High",IIf([MarkUp]<0.3,"Exclude-Low","Include")) AS
Flag, sqFinancial.CountOfID
FROM tCategory RIGHT JOIN (tIndustry INNER JOIN (tClient INNER JOIN ((tMRM
INNER JOIN tRegions ON (tMRM.txtState = tRegions.State) AND (tMRM.txtCity =
tRegions.City)) INNER JOIN sqFinancial ON tMRM.txtJobTitle =
sqFinancial.txtJobTitle) ON tClient.ID = tMRM.lngClient) ON tIndustry.ID =
tClient.lngIndustry) ON tCategory.ID = tMRM.lngPrOCategory
WHERE (((tIndustry.Industry)="Law Enforment") AND
((tCategory.txtCategory)="Administrative" Or
(tCategory.txtCategory)="Finance") AND ((tRegions.State)="FL")) OR
(((tIndustry.Industry)="Law Enforment") AND
((tCategory.txtCategory)="Administrative" Or
(tCategory.txtCategory)="Finance") AND ((tRegions.Region)=1));

I want to create a form that allows the users to select regions, if any, the
states, if any, job categories, etc. But I don’t how to create a SQL
statement where there could be one limit or ten limits, depending on the
request. I imagine there has to be a way to do this, but I am not sure where
to start.

All that being said, is this even the best way to approach the problem. I
want the users to be able extract the data themselves, but do it in a way
that is intuitive and simple. Alternate suggestions welcome!

Thanks in advance,
PJ
Click to expand...
Click to expand...
 
B

Bob Quintal

I haven't done anything yet. Before I put work into the process I
wanted to get a conceptual idea of what to do. For example, there
are ten different industries to choose from. If a user chooses
two of the ten, how is that information translated to a query?

Thanks!
PJ


AKphidelt said:
Hey PJFry, I'm a little lost on where you are having a problem.
Are you getting an error message when running this query or is
nothing showing up when there is something supposed to be showing
up. I have a query that has over 10 different limits so it is
possible. Just need to know a little more info.

PJFry said:
I maintain a database of contract pay rates for my firm. We
use the data to determine the starting point to contract
negotiations with consultants. Our current MO is to have an
advisor send me a request to find out what the average rate
would be for workers where the industry is Law Enforcement, the
job category is Administrative or Finance and the job is
located in Region One or Florida. Once I have the answer(s) I
drop it in an Excel sheet and send it back.

The SQL may make the question clearer:
SELECT tIndustry.Industry, tCategory.txtCategory,
tMRM.dblBillRate, tMRM.dblPayRate, tMRM.lngOrderID,
tMRM.txtJobTitle, tRegions.City, tRegions.State,
tRegions.Region, ([dblBillRate]/[dblPayRate])-1 AS MarkUp,
IIf([MarkUp]>1,"Exclude-High",IIf([MarkUp]<0.3,"Exclude- Low","In
clude")) AS Flag, sqFinancial.CountOfID
FROM tCategory RIGHT JOIN (tIndustry INNER JOIN (tClient INNER
JOIN ((tMRM INNER JOIN tRegions ON (tMRM.txtState =
tRegions.State) AND (tMRM.txtCity = tRegions.City)) INNER JOIN
sqFinancial ON tMRM.txtJobTitle = sqFinancial.txtJobTitle) ON
tClient.ID = tMRM.lngClient) ON tIndustry.ID =
tClient.lngIndustry) ON tCategory.ID = tMRM.lngPrOCategory
WHERE (((tIndustry.Industry)="Law Enforment") AND
((tCategory.txtCategory)="Administrative" Or
(tCategory.txtCategory)="Finance") AND ((tRegions.State)="FL"))
OR (((tIndustry.Industry)="Law Enforment") AND
((tCategory.txtCategory)="Administrative" Or
(tCategory.txtCategory)="Finance") AND ((tRegions.Region)=1));

I want to create a form that allows the users to select
regions, if any, the states, if any, job categories, etc. But
I don’t how to create a SQL statement where there could be
one limit or ten limits, depending on the request. I imagine
there has to be a way to do this, but I am not sure where
to start.

All that being said, is this even the best way to approach the
problem. I want the users to be able extract the data
themselves, but do it in a way that is intuitive and simple.
Alternate suggestions welcome!

Thanks in advance,
PJ
Click to expand...
Click to expand...
Click to expand...
The way that I have handled this is to create a filter form to enter
the criteria, and a continuous view form or report to show the
results

The SQL for the Results form/report does not have the Where Clause.

You then use a series of IF statements in the On_Click event of a
Show_Results to create a where clause item only if the control on
the filter form is not null.

IF not isnull(me.txtboxIndustry) then
stWhereClause = st\WhereClause & _
" AND tIndustry.Industry =""" _
& me.txtboxIndustry & """ "
end if
IF not isnull(me.txtboxState) then
stWhereClause = st\WhereClause & _
" AND tRegions.State =""" _
& me.txtboxState & """ "
end if

'all done, remove the first " AND "
StWhereClause = mid(stwhereClause,5)
'And open the results form
DOCMD.OpenForm "frmResults",,,stWhereClause

*****
If you need multiple possibilities for a single field, you will have
to add logic to get all the selections from a multiselect listbox,
put the multiple values into a list an use the IN predicate.

AND "tCategory.txtCategory IN ('Administrative','Finance')"

-
Bob Quintal

PA is y I've altered my email address.
 
Top