Search combo Box

  • Thread starter Thread starter Jacinto
  • Start date Start date
J

Jacinto

I've created an Access 2000 DB. The main form contains a
subform. I've put a search combo box which searches by a
field of the main form (underlining parent table).
When I try to create a new search combo box to search by a
field on the subform, the data on the search-combo box is
repeated. That is, if the Employee "John Smith" has
handled 3 sales, then "John Smith" will appear 3 times in
the search-combo box.

I tried a distinct but it does not help. Any help would be
very appreciated

Jacinto
 
Post the SQL of the query that is the row source of the combo box. Likely we
can provide a modification that will work for you.
 
Below is the query statement. I would like to search by
salesman = lastname + firstname. The relatiosnhips are as
follow:
tblClientCompany,tblSales(1-to-many)
tblSalespeople, tblSales (1-to-many)
Main form: tblClientCompany
Subform: tblSales

SELECT DISTINCT tblClientCompany.companyId, First
([LastName] & " " & [FirstName]) AS Salesman FROM
tblClientCompany INNER JOIN (tblSalespeople INNER JOIN
tblSales ON tblSalespeople.SalespersonId =
tblSales.SalespersonId) ON tblClientCompany.companyId =
tblSales.companyId
GROUP BY tblClientCompany.companyId
ORDER BY First([LastName] & " " & [FirstName]);

Thanks for your help!!!

Jacinto
 
Try this:

SELECT tblClientCompany.companyId, First
([LastName] & " " & [FirstName]) AS Salesman FROM
tblClientCompany INNER JOIN (tblSalespeople INNER JOIN
tblSales ON tblSalespeople.SalespersonId =
tblSales.SalespersonId) ON tblClientCompany.companyId =
tblSales.companyId
GROUP BY First([LastName] & " " & [FirstName])
ORDER BY First([LastName] & " " & [FirstName]);
--
Ken Snell
<MS ACCESS MVP>

Below is the query statement. I would like to search by
salesman = lastname + firstname. The relatiosnhips are as
follow:
tblClientCompany,tblSales(1-to-many)
tblSalespeople, tblSales (1-to-many)
Main form: tblClientCompany
Subform: tblSales

SELECT DISTINCT tblClientCompany.companyId, First
([LastName] & " " & [FirstName]) AS Salesman FROM
tblClientCompany INNER JOIN (tblSalespeople INNER JOIN
tblSales ON tblSalespeople.SalespersonId =
tblSales.SalespersonId) ON tblClientCompany.companyId =
tblSales.companyId
GROUP BY tblClientCompany.companyId
ORDER BY First([LastName] & " " & [FirstName]);

Thanks for your help!!!

Jacinto
-----Original Message-----
Post the SQL of the query that is the row source of the combo box. Likely we
can provide a modification that will work for you.

--
Ken Snell
<MS ACCESS MVP>




.
Click to expand...
Click to expand...
 
Ken,

Thanks for your help!!!
The query still does not work!

-----Original Message-----
Try this:

SELECT tblClientCompany.companyId, First
([LastName] & " " & [FirstName]) AS Salesman FROM
tblClientCompany INNER JOIN (tblSalespeople INNER JOIN
tblSales ON tblSalespeople.SalespersonId =
tblSales.SalespersonId) ON tblClientCompany.companyId =
tblSales.companyId
GROUP BY First([LastName] & " " & [FirstName])
ORDER BY First([LastName] & " " & [FirstName]);
--
Ken Snell
<MS ACCESS MVP>

Below is the query statement. I would like to search by
salesman = lastname + firstname. The relatiosnhips are as
follow:
tblClientCompany,tblSales(1-to-many)
tblSalespeople, tblSales (1-to-many)
Main form: tblClientCompany
Subform: tblSales

SELECT DISTINCT tblClientCompany.companyId, First
([LastName] & " " & [FirstName]) AS Salesman FROM
tblClientCompany INNER JOIN (tblSalespeople INNER JOIN
tblSales ON tblSalespeople.SalespersonId =
tblSales.SalespersonId) ON tblClientCompany.companyId =
tblSales.companyId
GROUP BY tblClientCompany.companyId
ORDER BY First([LastName] & " " & [FirstName]);

Thanks for your help!!!

Jacinto
-----Original Message-----
Post the SQL of the query that is the row source of the combo box. Likely we
can provide a modification that will work for you.

--
Ken Snell
<MS ACCESS MVP>

"Jacinto" <[email protected]> wrote
Click to expand...
in
message
I've created an Access 2000 DB. The main form contains a
subform. I've put a search combo box which searches by a
field of the main form (underlining parent table).
When I try to create a new search combo box to search by a
field on the subform, the data on the search-combo
Click to expand...
box
is
repeated. That is, if the Employee "John Smith" has
handled 3 sales, then "John Smith" will appear 3
Click to expand...
times
in
the search-combo box.

I tried a distinct but it does not help. Any help
Click to expand...
would
be
very appreciated

Jacinto


.
Click to expand...
Click to expand...


.
Click to expand...
 
Jacinto,

If I understand this correctly you _should_ be getting multiple companyID
records for each salesperson (LastName & FirstName). The companyId is related
to Sales and SalesPerson is related to Sales. So assuming that a SalesPerson
has made Sales to many Companies you should have a record for each combination
of SalesPerson and ClientCompany that were involved in a sale.

If you want just one record per salesperson then you either need to use a WHERE
clause to identify one company, drop the companyid from The Select and Group by
clauses, or use First on tblClientCompany.CompanyID and Group By (or use First)
on SalesPerson

Below is the query statement. I would like to search by
salesman = lastname + firstname. The relatiosnhips are as
follow:
tblClientCompany,tblSales(1-to-many)
tblSalespeople, tblSales (1-to-many)
Main form: tblClientCompany
Subform: tblSales

SELECT DISTINCT tblClientCompany.companyId, First
([LastName] & " " & [FirstName]) AS Salesman FROM
tblClientCompany INNER JOIN (tblSalespeople INNER JOIN
tblSales ON tblSalespeople.SalespersonId =
tblSales.SalespersonId) ON tblClientCompany.companyId =
tblSales.companyId
GROUP BY tblClientCompany.companyId
ORDER BY First([LastName] & " " & [FirstName]);

Thanks for your help!!!

Jacinto
-----Original Message-----
Post the SQL of the query that is the row source of the combo box. Likely we
can provide a modification that will work for you.

--
Ken Snell
<MS ACCESS MVP>




.
Click to expand...
Click to expand...
 
Sorry....that's what I get for not testing!

SELECT First([LastName] & " " & [FirstName]) AS Salesman
FROM tblClientCompany INNER JOIN (tblSalespeople INNER JOIN
tblSales ON tblSalespeople.SalespersonId =
tblSales.SalespersonId) ON tblClientCompany.companyId =
tblSales.companyId
GROUP BY First([LastName] & " " & [FirstName])
ORDER BY First([LastName] & " " & [FirstName]);
--
Ken Snell
<MS ACCESS MVP>

Ken,

Thanks for your help!!!
The query still does not work!

-----Original Message-----
Try this:

SELECT tblClientCompany.companyId, First
([LastName] & " " & [FirstName]) AS Salesman FROM
tblClientCompany INNER JOIN (tblSalespeople INNER JOIN
tblSales ON tblSalespeople.SalespersonId =
tblSales.SalespersonId) ON tblClientCompany.companyId =
tblSales.companyId
GROUP BY First([LastName] & " " & [FirstName])
ORDER BY First([LastName] & " " & [FirstName]);
--
Ken Snell
<MS ACCESS MVP>

Below is the query statement. I would like to search by
salesman = lastname + firstname. The relatiosnhips are as
follow:
tblClientCompany,tblSales(1-to-many)
tblSalespeople, tblSales (1-to-many)
Main form: tblClientCompany
Subform: tblSales

SELECT DISTINCT tblClientCompany.companyId, First
([LastName] & " " & [FirstName]) AS Salesman FROM
tblClientCompany INNER JOIN (tblSalespeople INNER JOIN
tblSales ON tblSalespeople.SalespersonId =
tblSales.SalespersonId) ON tblClientCompany.companyId =
tblSales.companyId
GROUP BY tblClientCompany.companyId
ORDER BY First([LastName] & " " & [FirstName]);

Thanks for your help!!!

Jacinto
-----Original Message-----
Post the SQL of the query that is the row source of the
combo box. Likely we
can provide a modification that will work for you.

--
Ken Snell
<MS ACCESS MVP>

message
I've created an Access 2000 DB. The main form contains a
subform. I've put a search combo box which searches by a
field of the main form (underlining parent table).
When I try to create a new search combo box to search
by a
field on the subform, the data on the search-combo box
is
repeated. That is, if the Employee "John Smith" has
handled 3 sales, then "John Smith" will appear 3 times
in
the search-combo box.

I tried a distinct but it does not help. Any help would
be
very appreciated

Jacinto


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top