Query Confusion

[Julius]

I need help with this asap, I am running on a deadline that is almost up.
Question. I have a field name Category Code and I have the critera set
[Enter Category Code]. I have Seven departments, Dental, Vision, Lipa Dental,
Lipa Vision, LIPASAG, SAG, and Training all of which are in the same table.
If I take off [Enter Category Code] and leave it Null I can pull data on all
dept. There are some instances where I may only need to pull data from 3, 4,
or 5 of those depts. Is there a way I can have the same Field Category Code
in the query listed 7 times. and is it possible to create a form based on
this which will allow me to enter what dept I may need to pull data from.
Remember I have one table, one query. Please please advise.

 

[Golfinray]

The best way might be to use a form. On the form put a combo box. Let the
wizard set that up for you to your category code field. Right click on the
combo box to get to properties. Go to the events properties and click on the
afterupdate event. Click the little button out to the right, start the code
builder, and type:
Me.filter = "[category code] = """ & Me.combo# & """"
Me.filteron = true
The combo# will be listed, like combo10 or combo0

 

[Clifford Bass]

Hi Julius,

Assuming that category = department you could do something like this:

SELECT tblSomeTable.[Category Code]
FROM tblSomeTable
WHERE (((tblSomeTable.[Category Code]) In ([Category 1:],[Category
2:],[Category 3:],[Category 4:],[Category 5:],[Category 6:],[Category 7:])));

When run enter the appropriate codes (i.e. Dental and then Vision) and
leave the others blank.

That is just the start. It does not return anything if all are left
blank, so you would need to figure that out. And, yes, you can create a form
and convert those parameters into references to text boxes on the form. For
example [Category 1:] would become something like
Forms!frmMyParameterForm![txtCategory 1]. Probably one method to help the
user would be to create seven check boxes on the form, one for each
department. The user would check each one he wants. You could also add
check-all and an uncheck-all buttons. Also add seven hidden boxes that
correspond to each of the check boxes. These would be the boxes your query
uses. As each check box is checked or unchecked you could populate or remove
the appropriate department code.

This is just one way of many that you could use.

Hope it helps,

Clifford Bass

 

[Julius]

Thank u Clifford I still have couple of questions though: 1st: when you use
the term tblSomeTable, is that suppose to be my table name, If so then it
should read SELECT Category Code Table. [Category Code] FROM Category Code
Table. 2nd Question. I have only one field name Category Code which I can
enter only one dept at a time if I put [Enter Category Code], if I leave it
Null, then when I run my reports it shows all the dept data. Please help me
on where I am suppose to put WHERE (((tblSomeTable.[Category Code]) In
([Category 1:],[Category
2:],[Category 3:],[Category 4:],[Category 5:],[Category 6:],[Category 7:])));

Correct me if I am wrong but looking at the above would mean I have to have
a Category Code field for each dept on my query right? Sorry for being lost
in space I am just not clear on your response. The way you state I should
setup the form is exactly what I am looking for, I am just lost on the first
part of the setup. Please Please advise.

Hi Julius,

Assuming that category = department you could do something like this:

SELECT tblSomeTable.[Category Code]
FROM tblSomeTable
WHERE (((tblSomeTable.[Category Code]) In ([Category 1:],[Category
2:],[Category 3:],[Category 4:],[Category 5:],[Category 6:],[Category 7:])));

When run enter the appropriate codes (i.e. Dental and then Vision) and
leave the others blank.

That is just the start. It does not return anything if all are left
blank, so you would need to figure that out. And, yes, you can create a form
and convert those parameters into references to text boxes on the form. For
example [Category 1:] would become something like
Forms!frmMyParameterForm![txtCategory 1]. Probably one method to help the
user would be to create seven check boxes on the form, one for each
department. The user would check each one he wants. You could also add
check-all and an uncheck-all buttons. Also add seven hidden boxes that
correspond to each of the check boxes. These would be the boxes your query
uses. As each check box is checked or unchecked you could populate or remove
the appropriate department code.

This is just one way of many that you could use.

Hope it helps,

Clifford Bass

I need help with this asap, I am running on a deadline that is almost up.
Question. I have a field name Category Code and I have the critera set
[Enter Category Code]. I have Seven departments, Dental, Vision, Lipa Dental,
Lipa Vision, LIPASAG, SAG, and Training all of which are in the same table.
If I take off [Enter Category Code] and leave it Null I can pull data on all
dept. There are some instances where I may only need to pull data from 3, 4,
or 5 of those depts. Is there a way I can have the same Field Category Code
in the query listed 7 times. and is it possible to create a form based on
this which will allow me to enter what dept I may need to pull data from.
Remember I have one table, one query. Please please advise.

 

[Clifford Bass]

Hi Julius,

Yes, subsititue your table's name for tblSomeTable. Since it contains
spaces in its name enclose it in square brackets. That gives:

SELECT [Category Code Table].[Category Code]
FROM [Category Code Table]
WHERE ((([Category Code Table].[Category Code]) In ([Category 1:],[Category
2:],[Category 3:],[Category 4:],[Category 5:],[Category 6:],[Category 7:])));

Perhaps the best way to understand is to start a new query in design
mode and cancel the select table dialog. It will put you into SQL mode.
Copy and paste the above text into the SQL window and then go to design mode.
Add any additional fields that you want to display and then go to datasheet
mode. Hopefully it will work without errors.

Clifford Bass

Thank u Clifford I still have couple of questions though: 1st: when you use
the term tblSomeTable, is that suppose to be my table name, If so then it
should read SELECT Category Code Table. [Category Code] FROM Category Code
Table. 2nd Question. I have only one field name Category Code which I can
enter only one dept at a time if I put [Enter Category Code], if I leave it
Null, then when I run my reports it shows all the dept data. Please help me
on where I am suppose to put WHERE (((tblSomeTable.[Category Code]) In
([Category 1:],[Category
2:],[Category 3:],[Category 4:],[Category 5:],[Category 6:],[Category 7:])));

Correct me if I am wrong but looking at the above would mean I have to have
a Category Code field for each dept on my query right? Sorry for being lost
in space I am just not clear on your response. The way you state I should
setup the form is exactly what I am looking for, I am just lost on the first
part of the setup. Please Please advise.

Hi Julius,

Assuming that category = department you could do something like this:

SELECT tblSomeTable.[Category Code]
FROM tblSomeTable
WHERE (((tblSomeTable.[Category Code]) In ([Category 1:],[Category
2:],[Category 3:],[Category 4:],[Category 5:],[Category 6:],[Category 7:])));

When run enter the appropriate codes (i.e. Dental and then Vision) and
leave the others blank.

That is just the start. It does not return anything if all are left
blank, so you would need to figure that out. And, yes, you can create a form
and convert those parameters into references to text boxes on the form. For
example [Category 1:] would become something like
Forms!frmMyParameterForm![txtCategory 1]. Probably one method to help the
user would be to create seven check boxes on the form, one for each
department. The user would check each one he wants. You could also add
check-all and an uncheck-all buttons. Also add seven hidden boxes that
correspond to each of the check boxes. These would be the boxes your query
uses. As each check box is checked or unchecked you could populate or remove
the appropriate department code.

This is just one way of many that you could use.

Hope it helps,

Clifford Bass

 

[Julius]

Good day Clifford, you are the man, that info you told me to put into the SQL
works like a charm, that is exactly what I want to do. As far as the form I
have read your instructions over and over and I am still a little stump.
Question do I create a bound or unbound form, I was assuming bound because of
the Category Code parameters. I am not sure what
frmMyParameterForm![txtCategory1] means, I am going to continue to try
building the form. Thank you so much and any further assistance in that area
would be greatly appreciated.

Hi Julius,

Yes, subsititue your table's name for tblSomeTable. Since it contains
spaces in its name enclose it in square brackets. That gives:

SELECT [Category Code Table].[Category Code]
FROM [Category Code Table]
WHERE ((([Category Code Table].[Category Code]) In ([Category 1:],[Category
2:],[Category 3:],[Category 4:],[Category 5:],[Category 6:],[Category 7:])));

Perhaps the best way to understand is to start a new query in design
mode and cancel the select table dialog. It will put you into SQL mode.
Copy and paste the above text into the SQL window and then go to design mode.
Add any additional fields that you want to display and then go to datasheet
mode. Hopefully it will work without errors.

Clifford Bass

Thank u Clifford I still have couple of questions though: 1st: when you use
the term tblSomeTable, is that suppose to be my table name, If so then it
should read SELECT Category Code Table. [Category Code] FROM Category Code
Table. 2nd Question. I have only one field name Category Code which I can
enter only one dept at a time if I put [Enter Category Code], if I leave it
Null, then when I run my reports it shows all the dept data. Please help me
on where I am suppose to put WHERE (((tblSomeTable.[Category Code]) In
([Category 1:],[Category
2:],[Category 3:],[Category 4:],[Category 5:],[Category 6:],[Category 7:])));

Correct me if I am wrong but looking at the above would mean I have to have
a Category Code field for each dept on my query right? Sorry for being lost
in space I am just not clear on your response. The way you state I should
setup the form is exactly what I am looking for, I am just lost on the first
part of the setup. Please Please advise.

Hi Julius,

Assuming that category = department you could do something like this:

SELECT tblSomeTable.[Category Code]
FROM tblSomeTable
WHERE (((tblSomeTable.[Category Code]) In ([Category 1:],[Category
2:],[Category 3:],[Category 4:],[Category 5:],[Category 6:],[Category 7:])));

When run enter the appropriate codes (i.e. Dental and then Vision) and
leave the others blank.

That is just the start. It does not return anything if all are left
blank, so you would need to figure that out. And, yes, you can create a form
and convert those parameters into references to text boxes on the form. For
example [Category 1:] would become something like
Forms!frmMyParameterForm![txtCategory 1]. Probably one method to help the
user would be to create seven check boxes on the form, one for each
department. The user would check each one he wants. You could also add
check-all and an uncheck-all buttons. Also add seven hidden boxes that
correspond to each of the check boxes. These would be the boxes your query
uses. As each check box is checked or unchecked you could populate or remove
the appropriate department code.

This is just one way of many that you could use.

Hope it helps,

Clifford Bass

 

[Clifford Bass]

Hi Julius,

I think I would use an unbound form with unbound controls. If you name
your form "frmMyParameterForm" and on it you have a control named
"txtCategory1"
you can reference that control in your query, as long as the form is open.
In your query you could have a condition (from the SQL view) "Where [Category
Code Table].[Category Code] = Forms![frmMyParameterForm]![txtCategory1]".

Hopefully that clarifies a bit.

Clifford Bass

Good day Clifford, you are the man, that info you told me to put into the SQL
works like a charm, that is exactly what I want to do. As far as the form I
have read your instructions over and over and I am still a little stump.
Question do I create a bound or unbound form, I was assuming bound because of
the Category Code parameters. I am not sure what
frmMyParameterForm![txtCategory1] means, I am going to continue to try
building the form. Thank you so much and any further assistance in that area
would be greatly appreciated.

Hi Julius,

Yes, subsititue your table's name for tblSomeTable. Since it contains
spaces in its name enclose it in square brackets. That gives:

SELECT [Category Code Table].[Category Code]
FROM [Category Code Table]
WHERE ((([Category Code Table].[Category Code]) In ([Category 1:],[Category
2:],[Category 3:],[Category 4:],[Category 5:],[Category 6:],[Category 7:])));

Perhaps the best way to understand is to start a new query in design
mode and cancel the select table dialog. It will put you into SQL mode.
Copy and paste the above text into the SQL window and then go to design mode.
Add any additional fields that you want to display and then go to datasheet
mode. Hopefully it will work without errors.

Clifford Bass

 

[Julius]

Bless u sir for you have been so very helpful. I have one last question how
do I fit this: Where ((([Category Code Table].[Category Code]
=Forms![frmMyParameterForm]![txtCategory1],[txtCategory2],[txtCategory3],[txtCategory4],[txtCategory5],[txtCategory6],[txtCategory7],[txtCategory8])));
into this code

SELECT [Category Code Table].[Category Code], Employees.EmployeeID,
Employees.LastName, Employees.FirstName, Employees.Team, Count([New Monitor
Query Sum of Fields - step 2W].SLastName) AS CountOfSLastName, Count([New
Monitor Query Sum of Fields - step 2W].RecordNumber) AS CountOfRecordNumber,
Avg([New Monitor Query Sum of Fields - step 2W].Expr67) AS Expr1 INTO
[Monitoring by Agent Group Table]
FROM [Category Code Table] INNER JOIN ([New Monitor Query Sum of Fields -
step 2W] INNER JOIN Employees ON [New Monitor Query Sum of Fields - step
2W].EmployeeID = Employees.EmployeeID) ON [Category Code Table].[Category
Code] = [New Monitor Query Sum of Fields - step 2W].[Category Code]
WHERE ((([New Monitor Query Sum of Fields - step
2W].MonitorDate)>=[Forms]![Weighted Heavy DRF]![Start Date?] And ([New
Monitor Query Sum of Fields - step 2W].MonitorDate)<=[Forms]![Weighted Heavy
DRF]![End Date?] ))
GROUP BY [Category Code Table].[Category Code], Employees.EmployeeID,
Employees.LastName, Employees.FirstName, Employees.Team
HAVING ((([Category Code Table].[Category Code]) In ([Category 1:],[Category
2:],[Category 3:],[Category 4:],[Category 5:],[Category 6:],[Category 7:])))
ORDER BY Employees.LastName;

I keep getting either comma or ) syntax error

Hi Julius,

I think I would use an unbound form with unbound controls. If you name
your form "frmMyParameterForm" and on it you have a control named
"txtCategory1"
you can reference that control in your query, as long as the form is open.
In your query you could have a condition (from the SQL view) "Where [Category
Code Table].[Category Code] = Forms![frmMyParameterForm]![txtCategory1]".

Hopefully that clarifies a bit.

Clifford Bass

Good day Clifford, you are the man, that info you told me to put into the SQL
works like a charm, that is exactly what I want to do. As far as the form I
have read your instructions over and over and I am still a little stump.
Question do I create a bound or unbound form, I was assuming bound because of
the Category Code parameters. I am not sure what
frmMyParameterForm![txtCategory1] means, I am going to continue to try
building the form. Thank you so much and any further assistance in that area
would be greatly appreciated.

Hi Julius,

Yes, subsititue your table's name for tblSomeTable. Since it contains
spaces in its name enclose it in square brackets. That gives:

SELECT [Category Code Table].[Category Code]
FROM [Category Code Table]
WHERE ((([Category Code Table].[Category Code]) In ([Category 1:],[Category
2:],[Category 3:],[Category 4:],[Category 5:],[Category 6:],[Category 7:])));

Perhaps the best way to understand is to start a new query in design
mode and cancel the select table dialog. It will put you into SQL mode.
Copy and paste the above text into the SQL window and then go to design mode.
Add any additional fields that you want to display and then go to datasheet
mode. Hopefully it will work without errors.

Clifford Bass

 

[Clifford Bass]

Hi Julius,

In this part:

HAVING ((([Category Code Table].[Category Code]) In ([Category 1:],[Category
2:],[Category 3:],[Category 4:],[Category 5:],[Category 6:],[Category 7:])))

change each [Category n:] to Forms![frmMyParameterForm]![txtCategoryn] where
n is the appropriate number.

That should do it I would think.

Clifford Bass

 

[Julius]

You my good man are very good. Ok so I have the SQL correct, it is now
showing
HAVING ((([Category Code Table].[Category Code]) In
([frmMyParameterForm]![txtCategory1]. I created an unbound form and I put 8
text boxes that I converted to combo boxes and the Select goes to the
Category Code table. I need to be able to run the report based on any of
those 8 combo box selection or did I do it wrong. Right now instead of using
the combo boxes I keep getting prompt to enter Category 1 through 8. How do
I get form to use the combo boxes and stop getting the prompt boxes.

Hi Julius,

In this part:

HAVING ((([Category Code Table].[Category Code]) In ([Category 1:],[Category
2:],[Category 3:],[Category 4:],[Category 5:],[Category 6:],[Category 7:])))

change each [Category n:] to Forms![frmMyParameterForm]![txtCategoryn] where
n is the appropriate number.

That should do it I would think.

Clifford Bass

Bless u sir for you have been so very helpful. I have one last question how
do I fit this: Where ((([Category Code Table].[Category Code]
=Forms![frmMyParameterForm]![txtCategory1],[txtCategory2],[txtCategory3],[txtCategory4],[txtCategory5],[txtCategory6],[txtCategory7],[txtCategory8])));
into this code

SELECT [Category Code Table].[Category Code], Employees.EmployeeID,
Employees.LastName, Employees.FirstName, Employees.Team, Count([New Monitor
Query Sum of Fields - step 2W].SLastName) AS CountOfSLastName, Count([New
Monitor Query Sum of Fields - step 2W].RecordNumber) AS CountOfRecordNumber,
Avg([New Monitor Query Sum of Fields - step 2W].Expr67) AS Expr1 INTO
[Monitoring by Agent Group Table]
FROM [Category Code Table] INNER JOIN ([New Monitor Query Sum of Fields -
step 2W] INNER JOIN Employees ON [New Monitor Query Sum of Fields - step
2W].EmployeeID = Employees.EmployeeID) ON [Category Code Table].[Category
Code] = [New Monitor Query Sum of Fields - step 2W].[Category Code]
WHERE ((([New Monitor Query Sum of Fields - step
2W].MonitorDate)>=[Forms]![Weighted Heavy DRF]![Start Date?] And ([New
Monitor Query Sum of Fields - step 2W].MonitorDate)<=[Forms]![Weighted Heavy
DRF]![End Date?] ))
GROUP BY [Category Code Table].[Category Code], Employees.EmployeeID,
Employees.LastName, Employees.FirstName, Employees.Team
HAVING ((([Category Code Table].[Category Code]) In ([Category 1:],[Category
2:],[Category 3:],[Category 4:],[Category 5:],[Category 6:],[Category 7:])))
ORDER BY Employees.LastName;

I keep getting either comma or ) syntax error

 

[Clifford Bass]

Hi Julius,

I think I like that option--using combo boxes. It sounds like your
query is not finding the combo boxes. Did the conversion carry over the
names of the text boxes so the combo boxes now have the same names as the
text boxes? Or did you rename them to something different? If so, you will
need to change all of the "txtCategoryn"'s in your query to reflect the
change.

Clifford Bass

 

[Julius]

Good morning Clifford, Not sure what you mean about the conversation carry
over. What I did was created a blank form not bound to anything, then I
added 8 text boxes in which I converted to combo boxes. (The eight boxes are
unbound). Then what I did was for each combo box was made the row source the
following: Select [Category Code] From [Category Code Table];. I did not
create 8 categories I was still using the main one for the different
categories. My question is this. even though last week we did the SQL and it
successfully put parameter prompt boxes that works awesome. I want to put
those parameters on a form instead of getting the prompt boxes. Do I have to
get rid of the SQL and just create 8 different lines for the main table and
call them something like Category1, Category2 all the way to 8 or can I still
use the main Category Code field for all 8. I hope I wasn't confusing.

Hi Julius,

I think I like that option--using combo boxes. It sounds like your
query is not finding the combo boxes. Did the conversion carry over the
names of the text boxes so the combo boxes now have the same names as the
text boxes? Or did you rename them to something different? If so, you will
need to change all of the "txtCategoryn"'s in your query to reflect the
change.

Clifford Bass

You my good man are very good. Ok so I have the SQL correct, it is now
showing
HAVING ((([Category Code Table].[Category Code]) In
([frmMyParameterForm]![txtCategory1]. I created an unbound form and I put 8
text boxes that I converted to combo boxes and the Select goes to the
Category Code table. I need to be able to run the report based on any of
those 8 combo box selection or did I do it wrong. Right now instead of using
the combo boxes I keep getting prompt to enter Category 1 through 8. How do
I get form to use the combo boxes and stop getting the prompt boxes.

 

[Clifford Bass]

Hi Julius,

Let's step back a step. Did the query work using the text boxes you
created? If so, when you converted them to comboboxes, what happened to
their names? If not, what is the name of your form and what are the names of
your combo boxes?

Clifford Bass

Good morning Clifford, Not sure what you mean about the conversation carry
over. What I did was created a blank form not bound to anything, then I
added 8 text boxes in which I converted to combo boxes. (The eight boxes are
unbound). Then what I did was for each combo box was made the row source the
following: Select [Category Code] From [Category Code Table];. I did not
create 8 categories I was still using the main one for the different
categories. My question is this. even though last week we did the SQL and it
successfully put parameter prompt boxes that works awesome. I want to put
those parameters on a form instead of getting the prompt boxes. Do I have to
get rid of the SQL and just create 8 different lines for the main table and
call them something like Category1, Category2 all the way to 8 or can I still
use the main Category Code field for all 8. I hope I wasn't confusing.

Hi Julius,

I think I like that option--using combo boxes. It sounds like your
query is not finding the combo boxes. Did the conversion carry over the
names of the text boxes so the combo boxes now have the same names as the
text boxes? Or did you rename them to something different? If so, you will
need to change all of the "txtCategoryn"'s in your query to reflect the
change.

Clifford Bass

 

[Julius]

No sir, the query did not work using the text boxes. I used unbound
textboxes and in the row source, I used a table that had the categorycode.
The name was the same which was Category Code, the name of the form is
frmparameterForm. I was thinking it would be best if I created 8 different
category and assignment them then recreate the form. Thoughts?

Hi Julius,

Let's step back a step. Did the query work using the text boxes you
created? If so, when you converted them to comboboxes, what happened to
their names? If not, what is the name of your form and what are the names of
your combo boxes?

Clifford Bass

Good morning Clifford, Not sure what you mean about the conversation carry
over. What I did was created a blank form not bound to anything, then I
added 8 text boxes in which I converted to combo boxes. (The eight boxes are
unbound). Then what I did was for each combo box was made the row source the
following: Select [Category Code] From [Category Code Table];. I did not
create 8 categories I was still using the main one for the different
categories. My question is this. even though last week we did the SQL and it
successfully put parameter prompt boxes that works awesome. I want to put
those parameters on a form instead of getting the prompt boxes. Do I have to
get rid of the SQL and just create 8 different lines for the main table and
call them something like Category1, Category2 all the way to 8 or can I still
use the main Category Code field for all 8. I hope I wasn't confusing.

Hi Julius,

I think I like that option--using combo boxes. It sounds like your
query is not finding the combo boxes. Did the conversion carry over the
names of the text boxes so the combo boxes now have the same names as the
text boxes? Or did you rename them to something different? If so, you will
need to change all of the "txtCategoryn"'s in your query to reflect the
change.

Clifford Bass

 

[Clifford Bass]

Hi Julius,

I would not use a record source at all. Next question: What are the
names of the eight combo boxes?

Clifford Bass

 

[Julius]

Sorry for the delay, the names are Category1, Category2, all the way up to
Category8. Oh and I am using a row source not a record source. I know I am
being a headache and I truly apologize for that, I wanted to be able to use a
form to select my choices as oppose of getting 8 parameter boxes where I have
to type in each category.

 

[Clifford Bass]

Hi Julius,

No problem. My turn to slightly misunderstand--not thinking carefully
on the distinction between row source (a property of a combo box) vs. record
source (a property of a form or report).

Anyway, try the following for your HAVING clause:

HAVING [Category Code Table].[Category Code] In
([Forms]![frmparameterForm]![Category1],[Forms]![frmparameterForm]![Category2],[Forms]![frmparameterForm]![Category3],[Forms]![frmparameterForm]![Category4],[Forms]![frmparameterForm]![Category5],[Forms]![frmparameterForm]![Category6],[Forms]![frmparameterForm]![Category7],[Forms]![frmparameterForm]![Category8])

This is essentially what I meant by "change each [Category n:] to
Forms![frmMyParameterForm]![txtCategoryn]".

Hopefully that will get you the results you desire!

Clifford Bass