Unique Values Query

[Kavy]

Good day all

I have a query which is the row source for a combo box on a form. It has the fields "Trans Num", and "Vendor".

The combo box is formatted to only show the field "Vendor" which is the description, the other field is a unique number.

My problem is, there are multiple "Trans Nums" for a given "Vendor", and so my combo box displays all the vendors multiple times (each Vendor is displayed the amount of times there is a given "Trans Num" for it).

Is there a way of only showing one Vendor, despite the fact it having multiple "Trans Nums"? I have tried using the "Unique Value" option in Access, but it didn't work.

Thank you

 

[xenou]

If Trans Num is not the unique number, then what is? Why even include Trans Num in the row source query at all?

 

[Kavy]

If Trans Num is not the unique number, then what is? Why even include Trans Num in the row source query at all?

Sorry, Trans num is a unique number, but a single Vendor can have multiple trans num's tied to it, i.e. Transnum represents a transactions, and you can have multiple transactions with the same vendor.

The reason I am including it, is because the table the form is built on, has the field Trans num on it.


For now, since that field is only a read-only field, I just turned off the enable property of the combo box, so the user doesn't see the multiple listing issues, just the default one.

 

[Norie]

Is there a table for the vendors which includes their unique Trans number?

If there is use that, or a query based on it, for the combobox row source.

 

[xenou]

I don't understand why unique values (DISTINCT) wouldn't work.
What's the SQL for the combo box row source?

 

[Kavy]

Thank you for your responses

I think it was late and I was over thinking it. I have since gone a completely different path - I think!

Thanks again!

Kavy

 

[Norie]

Kavy

Out of curiosity what path did you take?

Do you mean you aren't using a combobox now?

 

[Kavy]

Norie,

This is going to sound dumb, but I have re-read my question a few times and can't seem to get my head around what my thinking was last night. I have done a 180!

 

[Norie]

Kavy

Between this and the new thread I'm honestly getting a bit confused.

See my response in the other thread.

If that doesn't help, or I'm missing something, then I think we might need more information.

I've got a feeling you might even be overthinking things.

 

[Kavy]

I was, thank you!