combobox SQL query

[victorh]

Hello everybody,

I am new to this forum. Currently, I am designing a database for the company I work at. It is the first time I use ACCESS and it is ACCESS 97 (!).

Now my first ( of probably many following ) questions :

I have a table with a key data field. Now I want to set up a combobox which lists all entries of a data field in that table ( but it's not the key field ). But, I do not want duplicate entries.

The SQL query for the combobox as created by the wizard looks like this :

SELECT DISTINCTROW NEW_ID.[Project ID], NEW_ID.[Work ID]
FROM NEW_ID;

I only want to display the "Work ID" entries, without duplicates. "Project ID" is the key. I already tried to change DISTINCTROW into DISTINCT but that does not work because every entry in "Project ID" is unique, only the "Work ID" entries can repeat.

If I omit "NEW_ID.[Project ID]" in the query, nothing is displayed...


So, what should I do?! Please help, many thanks in advance!!

Victor

 

[ShaneMan]

Hey Victor,

Hello everybody,

I am new to this forum. Currently, I am designing a database for the company I work at. It is the first time I use ACCESS and it is ACCESS 97 (!).

Now my first ( of probably many following ) questions :

I have a table with a key data field. Now I want to set up a combobox which lists all entries of a data field in that table ( but it's not the key field ). But, I do not want duplicate entries.

The SQL query for the combobox as created by the wizard looks like this :

SELECT DISTINCTROW NEW_ID.[Project ID], NEW_ID.[Work ID]
FROM NEW_ID;

I only want to display the "Work ID" entries, without duplicates. "Project ID" is the key. I already tried to change DISTINCTROW into DISTINCT but that does not work because every entry in "Project ID" is unique, only the "Work ID" entries can repeat.

If I omit "NEW_ID.[Project ID]" in the query, nothing is displayed...


So, what should I do?! Please help, many thanks in advance!!

Victor

If ProjectID is the primary key and what your wanting to display is only the WorkID, then I would think you would need to put ProjectID in the first column, WorkID in the second column and do away with NewID all together. Then set your cboBox column count to 2 and it's column widths to 0;1 (or whatever widths work for you. DISTINCT is what you want to use to block duplicate entries, however it's looking at the bound column (ProjectID) so if there are duplicates in the WorkID it will not prevent the duplicates from showing.

HTH,
Shane

 

[victorh]

Hello ShaneMan,

first off, thanks for your reply.

But that is exactly my problem : "Project ID" is the key but I still want to eliminate duplicate entries of "Work ID".

So what you are saying is that there is no solution? Not even a workaround?

 

[ShaneMan]

Hey Victor,

None that I have ever been able to do. Maybe someone else will jump in if they have some idea's. The only way that I know to accomplish what your wanting to do is set the SQL to SELECT DISTINCT and have WorkID as the only column.

Shane

 

[nateobot]

Do you need to have [Project ID] in the dropdown box? If not then change your combobox query to:

SELECT NEW_ID.[Work ID]
FROM NEW_ID
GROUP BY NEW_ID.[Work ID];

If you need to have [Project ID] in there than you are stuck displaying all the records.

Also it is good practice to not have spaces in field names.

 

[victorh]

No, I don't need "Project ID" in the list.

Before, I tried to omit "NEW_ID.[Project ID]" from the SQL statement of the combobox and it would have no entries at all. I guess, because "Project ID" is the primary key...?

So I will try your suggestion, nateobot, thanks for that!

Victor


PS : And I will eliminate the spaces in my field names. I come from traditional programming ( C++ and the like ) and was "amazed" that spaces are possible...