Question A Very Specific Filter!

[NickDenyer]

Hi Guys,

I've only been on this forum for a short while but already the answers I have gotten on other questions have been excellent! I've asked two questions now, this is my third, and the only way I'm going to master Access is to keep on asking!!!!!!

So my next question I have a table and an identical query of information, and one of the columns contains names; with some names appearing more than others. I've linked these to a Combo-Box.

Is there any way I can:

1) When opening the combo, it will list names that, for instance, appear 7 times, only once?

2) Then, produce a report (or a form with Fields added, whatever is better/easier) from selecting one name, (that, for example, may have 7 records associated with it) using a GO button, that lists that person's name and ALL the information (e.g. 7 records) associated with that one name???

Much obliged!!! And one day hopefully I'll be able to answer these questions for someone else!

Cheers

 

[jdraw]

Do some research on SELECT DISTINCT and see if that helps.

see http://www.techonthenet.com/sql/distinct.php

 

[pr2-eugin]

Hi Nick, We are all here to lean. So surely in few days you will be answering in this forum too. (Just like I did) !!

BTW.. what is with you and ComboBoxes.. LOL !! Getting back, I believe you have the ComboBox lookup query as

SELECT Customers.[FIRST NAME]
FROM Customers;

If that is the case, it is simple, just add the word DISTINCT to it.

SELECT DISTINCT Customers.[FIRST NAME]
FROM Customers;

If you do not have that, then use the mentioned Query in the lookup. Once that is out of the way, create a new Query, say, reportQry. Have it something like..

SELECT Customers.[CUSTOMER ID], Customers.[POLICY STATUS], Customers.[FIRST NAME], Customers.[LAST NAME], Customers.[POST CODE], Customers.TEL, Customers.PASSWORD 
FROM Customers 
WHERE (Customers.[FIRST NAME])=" & [Forms]![Your_Form_Name]![FirstName] & ")

On the clik of the button you can have the code as,

Private Sub goButton_Click()
DoCmd.OpenQuery ("reportQry")
End Sub

.

Hope this is not confusing.

 

[NickDenyer]

Damn Comboboxes!!!! I've only been tampering with Access for two weeks and already they are the bain of my existence! When I first saw them I thought, 'Ah I get it, thats a very simple premise...'. Famous last words, eh?

I actually discovered how to form groups in reports and this looks a lot better than what I was proposing; so consider this thread sorted I will look into DISTINCT in my own time, so thanks to everyone. However, one last outstanding remark...

Is there any way to remove duplicate entries in combos?

Thanks

 

[pr2-eugin]

Hello Nick, That is what distinct does for you; it eliminates the duplicates and returnes only unique (technically one of each value).

Check the RowSource property, and just add the word DISTINCT to it. It should sort you out.

 

[NickDenyer]

Hi Pr2-Eugin!

I realise that noew; I jumped the gun before reading :-S How embarrassing!!!!! It works wonders thanks again, see you tomorrow lol

 

[pr2-eugin]

You are welcome !