Distinct Query Critieria

[sanywisons232]

I am trying to run a query with the following fields



- ID (Table1, Key)
- Fname (Table1, Unique field no duplicates)
- Lname (Table1, Unique field no duplicates)
- Address (Table1, Unique field no duplicates)
- City (Table1, Unique to entry)
- ID (Table2, Key, links people to table to occurrences)
- Job (Table2, Not unique many occurrences)
- Customer (Table2, Not unique many occurrences)

In my form I have Combo Boxes for the Job and Customer fields. I want to be able to pick a Job or Customer from the Combo Box and have it return only one Fname,Lname,Addres,City per Job or Customer.

Currently when I run the query it would return results that would show the same Fname,Lname,Address and City working on the same Job or Customer because often people (Fname,Lname,Address and City) would have worked multiple times on the same job or customer but I only care if they have worked on it at least once. I have done some Googling and it looks like I need to set up a Distinct query but it goes a bit over my head. Can anyone please help me out with this?

Also I really do not need the Job or Customer Field to display on this query I just need to be able to use the combo boxes to narrow to people that did whatever Job or Customer is selected at least once. I plan on adding a subform that will show each persons (Fname,Lname,Address and City) job/customer history.

[rpeare]

Do not use the ID as part if your query

SELECT Fname, Lname, Address, City FROM Table1
WHERE [JOB] = [Forms]![FormName]![JobFieldName] and [CUSTOMER] = [Forms]![FormName]![CustomerFieldName]
GROUP BY Fname, Lname, Address, City

[sanywisons232]

This is where I always have a hard part.

Where does:

SELECT Fname, Lname, Address, City FROM Table1
WHERE [JOB] = [Forms]![FormName]![JobFieldName] and [CUSTOMER} = [Forms]![FormName]![CustomerFieldName]
GROUP BY Fname, Lname, Address, City

Go in Access? Is it in the Design view of the Query like in the forms? So when I use the Total option above the criteria I just add:

[JOB] = [Forms]![FormName]![JobFieldName] and [CUSTOMER} = [Forms]![FormName]![CustomerFieldName]

After the Where spot?

[rpeare]

it's a query you could cut and paste it into a new query and as long as you replaced the actual form name, job id and customer id field names correctly it should work assuming your table is also named table1, if not you'd have to replace that with your actual table name as well. You didn't state where you were displaying these results (list box, subform, combo box) but you can either make it a stand alone query and base your list box, subform or combo box on the query or make the recordsource of the object the SQL statement.