badarzaman
asked on
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
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
ASKER
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.
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.
ASKER
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.
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
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
ASKER
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.
ASKER
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.)
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.)
I will allow 72 hours for any expert objections and if none will PAQ this question and refund points. Thank You :)
DigitalXtreme
CS Moderator
DigitalXtreme
CS Moderator
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)
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)
ASKER
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.
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)
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)
ASKER
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..
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..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
Assuming the first combo column has the filter value use in the after update for a text field:
me.<subformname>.form.filt
me.<subformname>.form.filt
A reset button with:
me.<subformname>.form.filt
Often comes in handy.
Nic;o)
me.<subformname>.form.filt
me.<subformname>.form.filt
A reset button with:
me.<subformname>.form.filt
Often comes in handy.
Nic;o)
ASKER
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
i'll send you the db..try changing 'mfrm_Author Search' such that it includes the functionality of 'mfrm_Title Search'.
thanks
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