SELECT DISTINCT in one field

Hi Experts,
Here is a query i'm using in a combobox to find records in a subform.

SELECT [accession record].[Accession number], [Author]  FROM [accession record];

accession recrod is the primary key, so i need it in the query for linking child fields in the subform.

The results i get in the combobox are something like this..(accession record is hidden, only author is seen)

author1
author1
author2
author3
author4
author4
author5

now i thought of using a SELECT DISTINCT, but it doesnt work because the 'accession number'(primary) is distinct anyways.
so what i need is a query that gives me the DISTINCT authors,but i also need it to return the accession numbers because of the 'link child fields' as i said above.

thanks
badarzamanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Arthur_WoodCommented:
sounds like you need a two step process:

1)Select an Author - list the [Accession Numbers] for THAT author,
and then

2)select one of those Accession Numbers, to see the complete details.

the problem you have is that a single Authour can have several Accession Number, so if you get a DISTINCT author, which of the accession numbers do you want to show?

AW
0
badarzamanAuthor Commented:
shoot..i didn't actually consider that..
im hoping that there is a way that after getting distinct authors, in the subform below i should see all the records of that author.
0
badarzamanAuthor Commented:
i've found something about doing a SELECT DISTINCT on authors and then UNION select accession..but i could get it to work the way i wanted it.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Arthur_WoodCommented:
the problem with the latter approach is then you get
authors and accession numbers intermixed, in a SINGLE column, with NO WAY to determine wich Accession Numbers go with which authors .

make to combo boxes:
cboAuthor:

Select Disinct Author from [Accsssion Record] as the row source for the first

and
cboAccession:
Select [Accession Number] from [Accession Record] where Author = '" & cboAuthor & "'" as the rowsource of the second.

then have a Sub-form to show the Details linked to the Main form based on Accession Number as selected in cboAccession


AW


0
badarzamanAuthor Commented:
ok..im going to try it out now...im almost sure that it'll work. I'm hoping that i can do this and then hide the cboAccession so that the user only sees the authors.
0
badarzamanAuthor Commented:
well id didnt work the way i was required to do it. Thanks anyway a lot for your efforts AW.

Here's how i solved it.
I created a combo which selects distinct authorsfrom the table.
Then i based the subform on a query that selects * where authors = author selected in combo.
and then i set an onClick event for the child to ReQuery..so that the subform changes whenever the user selects a record.

(I'm posting in the Cleanup TA to PAQ this questiona and refund the points.)
0
DigitalXtremeCommented:
I will allow 72 hours for any expert objections and if none will PAQ this question and refund points. Thank You :)

DigitalXtreme
CS Moderator
0
nico5038Commented:
Hmm, you should use the AfterUpdate event iso th OnClick.

But best would have been to use a "straight forward" subform and to manually fill the link fields with the combo's name (link master) and the [author] (link child).

Thus access will do the synchro an no requery is necessary.

Nic;o)
0
badarzamanAuthor Commented:
nope..cant really do that because the author is not the primary key and i had to do this for many other searches like title search, and ISBN search. I've figured it out anyway...so please PAQ this question.
0
nico5038Commented:
The primary key isn't needed in such a case.
Having multiple combo's won't be possible with your described "subform based on author query" approach either.

Please DigitalXtreme delete this Q as there's no real valuable information in it.

Nic;o)
0
badarzamanAuthor Commented:
sorry, i wasnt clear...i dont have multiple combo's i have different forms and each has 1 combo. i used 'subform based on author qry' as an example.

Can u elaborate why requery is not preferred compared to access's built-in sync?

You're right, there is no real info here..as i've come up with a working solution myself. thanks anyway..
0
nico5038Commented:
Your query will need to be changed when the formname is changed and you'll have to create as many subforms as there are fields. The requery also requires code that needs to be interpreted by access.
Using the link will save the code and interpretation and only one subform is needed with a "plain" query.

Normally for this problem however I use one mainform with e.g. two or three single select combo's, but then I issue a filter command to the subform in the combo's afterupdate event, saving me the trouble of creating multiple form/subforms...

Nic;o)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
badarzamanAuthor Commented:
you scored yet again. you always take these points..:-)

can you elaborate a bit about this?

"then I issue a filter command to the subform in the combo's afterupdate event"

So what syntax do i use for the filter in the afterUpdate?
0
nico5038Commented:
Assuming the first combo column has the filter value use in the after update for a text field:

me.<subformname>.form.filter = "[fieldname]='" & me.combo1 & "'"
me.<subformname>.form.filteron = true

A reset button with:
me.<subformname>.form.filteron = false

Often comes in handy.

Nic;o)
0
badarzamanAuthor Commented:
hey nico..i didnt actually get what you said in your last post. can u show me how its done?
i'll send you the db..try changing 'mfrm_Author Search' such that it includes the functionality of 'mfrm_Title Search'.
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.