Second combo in cascade shows multiple intances

[RexesOperator]

Once I get this solved I will have all my forms set up! Yippeee! Thanks for all your help and patience.

I am using Access 2003.

My 4 main tables are one to many - Companies->Contacts->Transactions->Sites. I have one other table, OurInfo, that has a one to many relationship with Transactions.

I have a query that has the fields CompanyID, ContactID, TransactionsID, SitesID (the relevant pk fields) - amongst others.

On my form I have cascading combo boxes that the user can use to select the Company Name. The second combo correctly shows all the contacts for that company. BUT it shows the name of each contact for the number of transactions associated with that contact. So if a contact has 15 transactions the contact's name shows up in the list 15 times.

It's not a huge problem, but I'd be curious to know why it exists and (if possible) how to fix it.

 

[missinglinq]

Maybe use SELECT DISTINCT instead of just SELECT?

 

[RexesOperator]

I have that on the Companies combo. I tried it on the Contacts combo - no joy.

 

[boblarson]

The problem could be that you are selecting too much information about the contact, so make sure you are selecting only enough in the query for that contact list to identify the contact and then I would use a grouping query so that will give you one instance of that contact, if the Select Distinct doesn't work for you.

 

[RexesOperator]

The only fields I have selected from the Contact table are ContactID, First Name and Last Name.

What is a grouping query?

 

[boblarson]

The only fields I have selected from the Contact table are ContactID, First Name and Last Name.

Yes, but in the query for the combo rowsource is that the ONLY table in the query?

What is a grouping query?

You select the Sigma

to group by the fields selected.

 

[RexesOperator]

After checking on "grouping" queries I have the following in an unbound combox for ContactID. This RowSource is actually created when the After_Update code on the CompanyID box fires:

cboContactID.RowSource = "SELECT DISTINCT qryTransactions.ContactID, qryTransactions.FIRSTNAME, qryTransactions.LASTNAME " &_
"FROM qryTransactions " &_
"GROUP BY qryTransactions.ContactID " &_
"WHERE qryTransactions.CompanyID = '" & cboCompanyID.Value & "' " &_
"ORDER BY qryTransactions.ContactID;"


I am getting a syntax error on'" & cboCompanyID.Value & "' "

 

[boblarson]

I am getting a syntax error on'" & cboCompanyID.Value & "' "

If CompanyID is a numeric datatype then remove the single quotes - so you would have:

WHERE qryTransactions.CompanyID =" & cboCompanyID.Value & " " &_

 

[RexesOperator]

CompaId is text, but I added DISTINCT to the above and bingo! It all works.
Thanks very much to both of you.