combo box blank

[smg]

I have a combo box based on Table/Query as follows:
Select Distinct Assessment.[Assessment No],Assessment.[COO Officer]FROM Assessment;
It displays duplicates COO Officer. If I remove the Assessment No field, the combo box is blank.


How do I fix this to just show distinct COO Officer?
I have ready many posts but don't seem to find an answer that works. I do have Unique Values set to Yes.

[Dave14867]

Can you post the database so we can look at it?

Dave

[smg]

here is the database, please see the frmAssessment to view the Select Officer combo box at top of form. Thank you!

[Dave14867]

smg,

Your table structure can be better. I think you should have a table for the COO name, which you have, then another for Assessments they have completed, which is similar to the Assessments table you have but slightly different. The assessments table should be a list of all possible assessments, description, and a foreign key of CooID_PK (Primary Key of Officer Table.

You should get in the habit of Naming the Primary Key as a Unique Name for each table. The Assessments Table should the have a Field called CooID_FK and a type of Number so you can set up the relationship between the 2 tables, This will make construction Queries, Forms & reports easier for you in the future, and make additions far easier too if new Assessments are added later.

I am sure others will have similar input, I will work on what I would recommend while others have a chance to take a look.

Thanks

Dave

[Dave14867]

smg,

I believe I have got the combo boxes ( or new ones I created) working. Take a look at the Copy of frmAssessment and select the Date then the officer and see if this is what you want? Other code on the form will have to be modified accordingly but I wanted to make sure this is the behavior you wanted first.

CONOPS Assessments11.zip
Thanks

Dave

[smg]

Dave, thank you for working on this. I don't see these combo boxes doing anything different then they did before. I can get them to list each date and each officer, however, I cannot get them listed distinctly. It appears they are not using the table structure you mention in your first response where you said I should have a table for the COO name, then another for Assessments they have completed, which is similar to the Assessments table you have but slightly different. The assessments table should be a list of all possible assessments, description, and a foreign key of CooID_PK (Primary Key of Officer Table.
What am I missing?

[smg]

I don't want users to have to know the date in order to narrow down the officer. I want users to be able to search assessments by date or by officer.

[Dave14867]

smg,

I didn't change the table structure in what I sent back, but I would advise it if it were my database. I the original file you attached, I could get bot combo boxes to give results, just multiples because there are multiple dates and multiple instances of officers because of the way the table are structured.

My changes list the multiple dates as your original but then only list the Officers that are in the Table for the date choosen

Dave

Dave

[smg]

Would you change the data structure as you suggest so I can see what I need to do to get distinct dates and officer in separate combo boxes?

[Dave14867]

smg,

Do you want just 1 date if there are multiple entries and then a list of Officers that have entries on that date?

Dave

[smg]

Yes, if filtering by date first. But I would also like to be able to filter by Officer independent of date. Once filter criteria is selected I would like to see all the relevant assessments.

[Dave14867]

smg,

I suggest you open the first file you sent to me, it was working the way you described in both combo boxes I believe, at least how I am understanding what you want. Open the one in your first attachment in this post.

Dave

[smg]

Dave, The way I expect these combo boxes to work is to display in the drop down distinct dates or distinct officers and not duplicates of each. I also expect it to actually filter just for the assessments for the date selected or officer selected. What it does is returns the first assessment for that date or officer but doesn't just return those specified. For instance, there are a total of 14 assessments in the database, three of which are dated 3/28/19. When I select 3/28/19 in the date combo box, it returns an assessment with that date but then the next assessment is not the next one dated 3/28/19, it is still returning 1 of 14. Using filter by form filters exactly like expected when selecting date or officer or anything else. Is there a way to automate filter by form so the user doesn't have to remember advanced, toggle, etc? Or, I was interested in your suggestion for better table setup in order for combo boxes to work accurately in my database.

[Dave14867]

smg,

The last file I sent I believe gives you what you want if I am understanding you correctly, maybe I am not. Just look at the 2 combo boxes boxes I added, not the form below, I haven't done anything with that, so it doesn't update anything, just concentrate on the combo boxes.

Dave

[smg]

The file you sent shows duplicates in the date field, does not filter for just those dates and the Officer drop down is blank