General discussion

Locked

?!! Singular Query in Access

By Cazen ·
I need help creating an Access97 query. The query is intended to provide a list of machine id numbers for a combobox rowsource. Essentially providing a picklist based on data already entered. But I want duplicate values listed only once. How can I do a "If Count >1 then return 1" on each item returned in the query. Does that make sense?

This conversation is currently closed to new comments.

9 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

?!! Singular Query in Access

by C_M_S In reply to ?!! Singular Query in Acc ...

Hi there,
First, the old drudge. Save a copy of your database so you can get back.
=Now, the easiest way to solve your problem would probably be to make a lookup list.
Go to design view of the table that contains the field you are wanting to use. Click the "Data Type" box for this field and on the bottom of the list is the "Lookup Wizard". Select this and it will take you through the process of creating a lookup list. Name it with the same name as the original to just replace it.Now select your combo box and refer to this field in the "Row Source" property. This should meet your desired requirements.
Note: If you have any forms or reports utilizing this field that you also want to use the lookup list, you will have to delete the old control and drag a new instance of it from the "Field List", otherwise the form will continue to act it always has.

Hope this helps,
Mike

Collapse -

?!! Singular Query in Access

by Cazen In reply to ?!! Singular Query in Acc ...

Cool method - but still returns duplicate values. I need it to return every value that exists in that field but only one instance of each even if it exists many times.

Collapse -

?!! Singular Query in Access

by M.R.Chambers In reply to ?!! Singular Query in Acc ...

Do you know SQL?
Go to the SQL view of the query (click the little arrow next to the Table View button on the toolbar and select SQL)

Type SELECT DISTINCT field(s)
FROM table(s)
WHERE whatever conditions you need

The SELECT DISTINCT part should only display any given value only once.

Hope that helps,
M.R.Chambers

Collapse -

?!! Singular Query in Access

by Cazen In reply to ?!! Singular Query in Acc ...

Poster rated this answer

Collapse -

?!! Singular Query in Access

by Bob Sellman In reply to ?!! Singular Query in Acc ...

On your combo box table/query source, click the builder (...) to bring up the Access query builder.

Select the table that contains your id numbers and move the id number to the query builder grid called "field".

Right click on the Sort cell under the id field and left click Totals from the menu that pops up. You'll now have a new row labelled Totals just above the Sort row. Totals will say "Group by". This means that no matter how many times id is in the source table, it will only be listed once in the query. You can then set Sort to ascending or whatever you want.

If your users will be adding id numbers (as well as using existing ones), don't forget to set the on got focus property of the combo box to run the VBA code:

me.cboMyComboBox.requery

This will force the combo box to rebuild the query so that any newly added id number will be included.

Collapse -

?!! Singular Query in Access

by Cazen In reply to ?!! Singular Query in Acc ...

Excellent! This is the answer I wanted. Thanks very much.
Thanks also to M.R. Chambers and 29 Mike. I believe proper SQL statements will work but I'm not very familiar with them Yet. Mike also has the answer I was looking for - just too late and less detailed. Anyway - Thanks guys.

Collapse -

?!! Singular Query in Access

by C_M_S In reply to ?!! Singular Query in Acc ...

Hi again,
Design a new query.
Select the field you are interested in as the only field.
Right click in the grid and select "Totals"
Note that a new row "Total" is added
Leave "Group By" as the selection
Use the query as the "Row Source" in the combo box properties.

Good Luck,
Mike

Collapse -

?!! Singular Query in Access

by Cazen In reply to ?!! Singular Query in Acc ...

Poster rated this answer

Collapse -

?!! Singular Query in Access

by Cazen In reply to ?!! Singular Query in Acc ...

This question was closed by the author

Back to Software Forum
9 total posts (Page 1 of 1)  

Related Forums