Duplicate values from Combo box criteria

  • Thread starter Thread starter FirstVette52
  • Start date Start date
F

FirstVette52

I am using Access 2007 and have a Combo Box that lists the ClassID and
Instructor in 2 columns. I am referencing this in a Query designed to
extract as many lines of results as exist for that combination. I know I
need to reference the Combo in both the ClassID field of the query for the
first column, and the Instructor field for the 2nd column of the Combo Box,
but I don't know how.

Putting it in with just a reference to the Class ID field gets me 16
duplicates for each matching record in the table.

Thanks for your time in advance
 
If I understand you want to have two elements of criteria picked by the combo
box selection.
One way is in the combo Row Source put this --
SELECT [ClassID] & [Instructor] AS [XX], [ClassID], [Instructor] FROM
YourTable;
Column Widths - 0"; 0.5"; 2.5"
Reference column 0.
 
I am getting an error: 'Undefined function '[Forms]![FormName]![Combo]' in
expressiion.' after entering a reference to a specific column in the WHERE
clause:

[Forms]![FormName]![Combo1](0)

This happens whether I reference a single column in a single criteria or
both columns on separate criteria for both fields.

Thanks for your reply
--
FirstVette52


KARL DEWEY said:
If I understand you want to have two elements of criteria picked by the combo
box selection.
One way is in the combo Row Source put this --
SELECT [ClassID] & [Instructor] AS [XX], [ClassID], [Instructor] FROM
YourTable;
Column Widths - 0"; 0.5"; 2.5"
Reference column 0.
--
KARL DEWEY
Build a little - Test a little


FirstVette52 said:
I am using Access 2007 and have a Combo Box that lists the ClassID and
Instructor in 2 columns. I am referencing this in a Query designed to
extract as many lines of results as exist for that combination. I know I
need to reference the Combo in both the ClassID field of the query for the
first column, and the Instructor field for the 2nd column of the Combo Box,
but I don't know how.

Putting it in with just a reference to the Class ID field gets me 16
duplicates for each matching record in the table.

Thanks for your time in advance
Click to expand...
Click to expand...
 
Post your query SQL.

--
KARL DEWEY
Build a little - Test a little


FirstVette52 said:
I am getting an error: 'Undefined function '[Forms]![FormName]![Combo]' in
expressiion.' after entering a reference to a specific column in the WHERE
clause:

[Forms]![FormName]![Combo1](0)

This happens whether I reference a single column in a single criteria or
both columns on separate criteria for both fields.

Thanks for your reply
--
FirstVette52


KARL DEWEY said:
If I understand you want to have two elements of criteria picked by the combo
box selection.
One way is in the combo Row Source put this --
SELECT [ClassID] & [Instructor] AS [XX], [ClassID], [Instructor] FROM
YourTable;
Column Widths - 0"; 0.5"; 2.5"
Reference column 0.
--
KARL DEWEY
Build a little - Test a little


FirstVette52 said:
I am using Access 2007 and have a Combo Box that lists the ClassID and
Instructor in 2 columns. I am referencing this in a Query designed to
extract as many lines of results as exist for that combination. I know I
need to reference the Combo in both the ClassID field of the query for the
first column, and the Instructor field for the 2nd column of the Combo Box,
but I don't know how.

Putting it in with just a reference to the Class ID field gets me 16
duplicates for each matching record in the table.

Thanks for your time in advance
Click to expand...
Click to expand...
Click to expand...
 
SELECT Survey_Results.[BBCourse Course], Survey_Results.[BBCourse
Instructor], Survey_Results.Q1, Survey_Results.Q2, Survey_Results.Q3,
Survey_Results.Q4, Survey_Results.Q5, Survey_Results.Q6, Survey_Results.Q7,
Survey_Results.Q8, Survey_Results.Q9, Survey_Results.Q10, Survey_Results.Q11,
Survey_Results.Q12, Survey_Results.Q13, Survey_Results.Q14,
Survey_Results.Q15, Survey_Results.Q16
FROM Survey_Results, [Tbl Questions]
WHERE (((Survey_Results.[BBCourse Course])=[Forms]![Survey Result
MAIN]![Combo28](0)) AND ((Survey_Results.[BBCourse
Instructor])=[Forms]![Survey Result MAIN]![Combo28](1)));

Thanks for your help

--
FirstVette52


KARL DEWEY said:
Post your query SQL.

--
KARL DEWEY
Build a little - Test a little


FirstVette52 said:
I am getting an error: 'Undefined function '[Forms]![FormName]![Combo]' in
expressiion.' after entering a reference to a specific column in the WHERE
clause:

[Forms]![FormName]![Combo1](0)

This happens whether I reference a single column in a single criteria or
both columns on separate criteria for both fields.

Thanks for your reply
--
FirstVette52


KARL DEWEY said:
If I understand you want to have two elements of criteria picked by the combo
box selection.
One way is in the combo Row Source put this --
SELECT [ClassID] & [Instructor] AS [XX], [ClassID], [Instructor] FROM
YourTable;
Column Widths - 0"; 0.5"; 2.5"
Reference column 0.
--
KARL DEWEY
Build a little - Test a little


:

I am using Access 2007 and have a Combo Box that lists the ClassID and
Instructor in 2 columns. I am referencing this in a Query designed to
extract as many lines of results as exist for that combination. I know I
need to reference the Combo in both the ClassID field of the query for the
first column, and the Instructor field for the 2nd column of the Combo Box,
but I don't know how.

Putting it in with just a reference to the Class ID field gets me 16
duplicates for each matching record in the table.

Thanks for your time in advance
Click to expand...
Click to expand...
Click to expand...
 
Try this --
WHERE [Survey_Results].[BBCourse Course] & [Survey_Results].[BBCourse
Instructor] = [Forms]![Survey Result MAIN]![Combo28](0);

They were concatenated in column zero of the combo so you concatenate them
here to compare.

Why do you have [Tbl Questions] in the FROM statement?

--
KARL DEWEY
Build a little - Test a little


FirstVette52 said:
SELECT Survey_Results.[BBCourse Course], Survey_Results.[BBCourse
Instructor], Survey_Results.Q1, Survey_Results.Q2, Survey_Results.Q3,
Survey_Results.Q4, Survey_Results.Q5, Survey_Results.Q6, Survey_Results.Q7,
Survey_Results.Q8, Survey_Results.Q9, Survey_Results.Q10, Survey_Results.Q11,
Survey_Results.Q12, Survey_Results.Q13, Survey_Results.Q14,
Survey_Results.Q15, Survey_Results.Q16
FROM Survey_Results, [Tbl Questions]
WHERE (((Survey_Results.[BBCourse Course])=[Forms]![Survey Result
MAIN]![Combo28](0)) AND ((Survey_Results.[BBCourse
Instructor])=[Forms]![Survey Result MAIN]![Combo28](1)));

Thanks for your help

--
FirstVette52


KARL DEWEY said:
Post your query SQL.

--
KARL DEWEY
Build a little - Test a little


FirstVette52 said:
I am getting an error: 'Undefined function '[Forms]![FormName]![Combo]' in
expressiion.' after entering a reference to a specific column in the WHERE
clause:

[Forms]![FormName]![Combo1](0)

This happens whether I reference a single column in a single criteria or
both columns on separate criteria for both fields.

Thanks for your reply
--
FirstVette52


:

If I understand you want to have two elements of criteria picked by the combo
box selection.
One way is in the combo Row Source put this --
SELECT [ClassID] & [Instructor] AS [XX], [ClassID], [Instructor] FROM
YourTable;
Column Widths - 0"; 0.5"; 2.5"
Reference column 0.
--
KARL DEWEY
Build a little - Test a little


:

I am using Access 2007 and have a Combo Box that lists the ClassID and
Instructor in 2 columns. I am referencing this in a Query designed to
extract as many lines of results as exist for that combination. I know I
need to reference the Combo in both the ClassID field of the query for the
first column, and the Instructor field for the 2nd column of the Combo Box,
but I don't know how.

Putting it in with just a reference to the Class ID field gets me 16
duplicates for each matching record in the table.

Thanks for your time in advance
Click to expand...
Click to expand...
Click to expand...
 
<<Putting it in with just a reference to the Class ID field gets me 16
duplicates for each matching record in the table.
After I posted and was thinking about it, it is [Tbl Questions] in the
FROM statement giving you the duplicates.

--
KARL DEWEY
Build a little - Test a little


KARL DEWEY said:
Try this --
WHERE [Survey_Results].[BBCourse Course] & [Survey_Results].[BBCourse
Instructor] = [Forms]![Survey Result MAIN]![Combo28](0);

They were concatenated in column zero of the combo so you concatenate them
here to compare.

Why do you have [Tbl Questions] in the FROM statement?

--
KARL DEWEY
Build a little - Test a little


FirstVette52 said:
SELECT Survey_Results.[BBCourse Course], Survey_Results.[BBCourse
Instructor], Survey_Results.Q1, Survey_Results.Q2, Survey_Results.Q3,
Survey_Results.Q4, Survey_Results.Q5, Survey_Results.Q6, Survey_Results.Q7,
Survey_Results.Q8, Survey_Results.Q9, Survey_Results.Q10, Survey_Results.Q11,
Survey_Results.Q12, Survey_Results.Q13, Survey_Results.Q14,
Survey_Results.Q15, Survey_Results.Q16
FROM Survey_Results, [Tbl Questions]
WHERE (((Survey_Results.[BBCourse Course])=[Forms]![Survey Result
MAIN]![Combo28](0)) AND ((Survey_Results.[BBCourse
Instructor])=[Forms]![Survey Result MAIN]![Combo28](1)));

Thanks for your help

--
FirstVette52


KARL DEWEY said:
Post your query SQL.

--
KARL DEWEY
Build a little - Test a little


:

I am getting an error: 'Undefined function '[Forms]![FormName]![Combo]' in
expressiion.' after entering a reference to a specific column in the WHERE
clause:

[Forms]![FormName]![Combo1](0)

This happens whether I reference a single column in a single criteria or
both columns on separate criteria for both fields.

Thanks for your reply
--
FirstVette52


:

If I understand you want to have two elements of criteria picked by the combo
box selection.
One way is in the combo Row Source put this --
SELECT [ClassID] & [Instructor] AS [XX], [ClassID], [Instructor] FROM
YourTable;
Column Widths - 0"; 0.5"; 2.5"
Reference column 0.
--
KARL DEWEY
Build a little - Test a little


:

I am using Access 2007 and have a Combo Box that lists the ClassID and
Instructor in 2 columns. I am referencing this in a Query designed to
extract as many lines of results as exist for that combination. I know I
need to reference the Combo in both the ClassID field of the query for the
first column, and the Instructor field for the 2nd column of the Combo Box,
but I don't know how.

Putting it in with just a reference to the Class ID field gets me 16
duplicates for each matching record in the table.

Thanks for your time in advance
Click to expand...
Click to expand...
Click to expand...
 
I'm not sure I understand why, but it works smooth as silk. Reports look
great and you've put an end to 5 days of torment with this puppy. I cannot
thank you enough.
--
FirstVette52


KARL DEWEY said:
<<Putting it in with just a reference to the Class ID field gets me 16
duplicates for each matching record in the table.
After I posted and was thinking about it, it is [Tbl Questions] in the
FROM statement giving you the duplicates.

--
KARL DEWEY
Build a little - Test a little


KARL DEWEY said:
Try this --
WHERE [Survey_Results].[BBCourse Course] & [Survey_Results].[BBCourse
Instructor] = [Forms]![Survey Result MAIN]![Combo28](0);

They were concatenated in column zero of the combo so you concatenate them
here to compare.

Why do you have [Tbl Questions] in the FROM statement?

--
KARL DEWEY
Build a little - Test a little


FirstVette52 said:
SELECT Survey_Results.[BBCourse Course], Survey_Results.[BBCourse
Instructor], Survey_Results.Q1, Survey_Results.Q2, Survey_Results.Q3,
Survey_Results.Q4, Survey_Results.Q5, Survey_Results.Q6, Survey_Results.Q7,
Survey_Results.Q8, Survey_Results.Q9, Survey_Results.Q10, Survey_Results.Q11,
Survey_Results.Q12, Survey_Results.Q13, Survey_Results.Q14,
Survey_Results.Q15, Survey_Results.Q16
FROM Survey_Results, [Tbl Questions]
WHERE (((Survey_Results.[BBCourse Course])=[Forms]![Survey Result
MAIN]![Combo28](0)) AND ((Survey_Results.[BBCourse
Instructor])=[Forms]![Survey Result MAIN]![Combo28](1)));

Thanks for your help

--
FirstVette52


:

Post your query SQL.

--
KARL DEWEY
Build a little - Test a little


:

I am getting an error: 'Undefined function '[Forms]![FormName]![Combo]' in
expressiion.' after entering a reference to a specific column in the WHERE
clause:

[Forms]![FormName]![Combo1](0)

This happens whether I reference a single column in a single criteria or
both columns on separate criteria for both fields.

Thanks for your reply
--
FirstVette52


:

If I understand you want to have two elements of criteria picked by the combo
box selection.
One way is in the combo Row Source put this --
SELECT [ClassID] & [Instructor] AS [XX], [ClassID], [Instructor] FROM
YourTable;
Column Widths - 0"; 0.5"; 2.5"
Reference column 0.
--
KARL DEWEY
Build a little - Test a little


:

I am using Access 2007 and have a Combo Box that lists the ClassID and
Instructor in 2 columns. I am referencing this in a Query designed to
extract as many lines of results as exist for that combination. I know I
need to reference the Combo in both the ClassID field of the query for the
first column, and the Instructor field for the 2nd column of the Combo Box,
but I don't know how.

Putting it in with just a reference to the Class ID field gets me 16
duplicates for each matching record in the table.

Thanks for your time in advance
Click to expand...
Click to expand...
Click to expand...
 
Putting two table that are not joined or criteria multiplies the records of
one by the number of records in the other.
It is known as Cartesian effect. It is useful if you want to have maximum
possible combination of two fields. You would use one field from each table.
--
KARL DEWEY
Build a little - Test a little


FirstVette52 said:
I'm not sure I understand why, but it works smooth as silk. Reports look
great and you've put an end to 5 days of torment with this puppy. I cannot
thank you enough.
--
FirstVette52


KARL DEWEY said:
<<Putting it in with just a reference to the Class ID field gets me 16
duplicates for each matching record in the table.
After I posted and was thinking about it, it is [Tbl Questions] in the
FROM statement giving you the duplicates.

--
KARL DEWEY
Build a little - Test a little


KARL DEWEY said:
Try this --
WHERE [Survey_Results].[BBCourse Course] & [Survey_Results].[BBCourse
Instructor] = [Forms]![Survey Result MAIN]![Combo28](0);

They were concatenated in column zero of the combo so you concatenate them
here to compare.

Why do you have [Tbl Questions] in the FROM statement?

--
KARL DEWEY
Build a little - Test a little


:

SELECT Survey_Results.[BBCourse Course], Survey_Results.[BBCourse
Instructor], Survey_Results.Q1, Survey_Results.Q2, Survey_Results.Q3,
Survey_Results.Q4, Survey_Results.Q5, Survey_Results.Q6, Survey_Results.Q7,
Survey_Results.Q8, Survey_Results.Q9, Survey_Results.Q10, Survey_Results.Q11,
Survey_Results.Q12, Survey_Results.Q13, Survey_Results.Q14,
Survey_Results.Q15, Survey_Results.Q16
FROM Survey_Results, [Tbl Questions]
WHERE (((Survey_Results.[BBCourse Course])=[Forms]![Survey Result
MAIN]![Combo28](0)) AND ((Survey_Results.[BBCourse
Instructor])=[Forms]![Survey Result MAIN]![Combo28](1)));

Thanks for your help

--
FirstVette52


:

Post your query SQL.

--
KARL DEWEY
Build a little - Test a little


:

I am getting an error: 'Undefined function '[Forms]![FormName]![Combo]' in
expressiion.' after entering a reference to a specific column in the WHERE
clause:

[Forms]![FormName]![Combo1](0)

This happens whether I reference a single column in a single criteria or
both columns on separate criteria for both fields.

Thanks for your reply
--
FirstVette52


:

If I understand you want to have two elements of criteria picked by the combo
box selection.
One way is in the combo Row Source put this --
SELECT [ClassID] & [Instructor] AS [XX], [ClassID], [Instructor] FROM
YourTable;
Column Widths - 0"; 0.5"; 2.5"
Reference column 0.
--
KARL DEWEY
Build a little - Test a little


:

I am using Access 2007 and have a Combo Box that lists the ClassID and
Instructor in 2 columns. I am referencing this in a Query designed to
extract as many lines of results as exist for that combination. I know I
need to reference the Combo in both the ClassID field of the query for the
first column, and the Instructor field for the 2nd column of the Combo Box,
but I don't know how.

Putting it in with just a reference to the Class ID field gets me 16
duplicates for each matching record in the table.

Thanks for your time in advance
Click to expand...
Click to expand...
Click to expand...
 
Back
Top