Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SELECT DISTINCT in one field

Posted on 2003-02-28
15
Medium Priority
?
591 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
  • 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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
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…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

581 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