?
Solved

SELECT DISTINCT in one field

Posted on 2003-02-28
15
Medium Priority
?
574 Views
Last Modified: 2008-03-06
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
0
Comment
Question by:badarzaman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 2
  • +1
15 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 8044174
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
 

Author Comment

by:badarzaman
ID: 8044500
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
 

Author Comment

by:badarzaman
ID: 8044513
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
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 8046379
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
 

Author Comment

by:badarzaman
ID: 8048225
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
 

Author Comment

by:badarzaman
ID: 8052970
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
 

Expert Comment

by:DigitalXtreme
ID: 8053111
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
 
LVL 54

Expert Comment

by:nico5038
ID: 8098178
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
 

Author Comment

by:badarzaman
ID: 8103435
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
 
LVL 54

Expert Comment

by:nico5038
ID: 8103586
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
 

Author Comment

by:badarzaman
ID: 8105381
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
 
LVL 54

Accepted Solution

by:
nico5038 earned 100 total points
ID: 8105658
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
 

Author Comment

by:badarzaman
ID: 8111940
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
 
LVL 54

Expert Comment

by:nico5038
ID: 8113752
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
 

Author Comment

by:badarzaman
ID: 8134677
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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question