Unique values for a TWO column combobox

[JoseO]

Hello -

Here's my dilemma: I have a table that keeps track of job postings and how many people applied for each posting. So, for instance, I can have 20 people apply for a job posting say....on July 18. On another column I have the shift name for that posting such as DAY Shift. So, I have 20 people that have applied for the Day Shift on July 18.

What I'd like my combobox to show me in just one instance of July 18 in column 1 and one instance of Day Shift in column two. I have tried using the wizard and then going to the properties of the combobox and attempted to edit the SQL statement to use the DISTINCT value to no avail. I hope this is making sense.

Thank you SO much for any and all help.

 

[MarkK]

use the DISTINCT value to no avail.

So what is the SQL you used, and how does it fail?

 

[JoseO]

Hi lagbolt,

Thanks for answering. Here's the SQL:

SELECT DISTINCT [FullTimersT].[PostingBeginDate], [FullTimersT].[SortsHiring] FROM FullTimersT ORDER BY [PostingBeginDate];

Where "FullTimersT" is my table, "PostingBeginDate" is column 1 and "SortsHiring" is column 2. When I use the wizard it keeps wanting to pull the primary key, designated "ID" in the FullTimersT table over.

Thanks again.

 

[JoseO]

Sorry....didn't answer the "how does it fail". It shows the values but not in a unique manner.

 

[Mr. Ziggins]

I don't think I fully understand the problem you are having, but you should try using the groupBy command instead of distinct. I tend to have better success with that over the DISTINCT command

 

[JoseO]

Thanks Mr. Ziggins. I don't have a clue as to how to use the GroupBy command. The problem is that I would like to show a two-column combobox based off of the FullTimersT table. However, I am not able to get the combobox to just show me unique values.

 

[Mr. Ziggins]

Ok

• From Design view
• click on the combo box
• go to its properties
• click the Data tab
• click the "..." to the right of the Row Source field
• that will bring up the query builder

I am assuming you have some sort of query already set up
at the top middle of the screen you will see a "∑" character with the word "Totals" under it

• Click the "∑"

That turns on grouping. Save and close. Add criteria as needed if it doesn't get you what you want. Another thing to note is it can't be used in conjunction with the Distinct callout.

 

[JoseO]

The GroupBy unfortunately did not work. I still get the same date 20 different times along with it's corresponding posting. Thanks though.

 

[Mr. Ziggins]

is your date field really a date/time field? Sometimes its formatted to only show you the date, but the REAL entry has the time with it!!!

I would check on that
Also you can use the conversion tools to force it to a date type format in a consitent mannor to do your original distinct idea.

 

[JoseO]

May a visual will help? Please see the attached. Thanks.

 

[Mr. Ziggins]

can you post your SQL statment? I have a feeling I know what the problem is, I just need to verify.

 

[JoseO]

I already did. Look at the 3rd reply in the thread.

 

[Mr. Ziggins]

Oh sorry, didn't see that.
Ok I am going back to the date possibly being entered as date AND time, not just date. How are the dates being entered in the first place? Make sure your date field on the table is not formatted to include time. And make sure your entry method does not include time. e.g. now() vs date().

Another thing to test is get rid of one of the colums, and test it for distinct values, then get rid of the other column and test it. That way you can verify which one it is not grouping together correctly. (hopefully that makes sense)

Sorry I haven't gotten you a quick answer, but figuring out what is truely going on isn't always easy.

 

[JoseO]

It is just entered as date. The DataType is formatted only to accept date - no time. To recap: Basically I have 20 people that applied for the job posting on 1/17/2013 for the DAY/TWI shifts. When I fire up my form and click on the combobox I just want one instance of 1/17/2103 and DAY/TWI.

 

[Mr. Ziggins]

Do you have it set to unique VALUE, or unique RECORD? Needs to be VALUE esp since you have an AutoNumber field "ID"

 

[JoseO]

Yes. It's set to unique values, not records.

 

[MarkK]

What happens if you use this in the rowsource of the combo?

SELECT PostingBeginDate, SortsHiring 
FROM FullTimersT 
GROUP BY PostingBeginDate, SortsHiring
ORDER BY PostingBeginDate;

 

[JoseO]

Thank you for the suggestion lagvolt. I've attached a picture of what the SQL statement returned. Please compare with the previous pic I sent a few postings back.

 

[MarkK]

And what are the ColumnCount and ColumnWidths property of the control?

 

[JoseO]

Column Count = 1
Column Width is null