What happened to the Messenger Plus! forums on msghelp.net?
Shoutbox » MsgHelp Archive » Skype & Technology » Tech Talk » MS Access - How to make a query showing filtered results

MS Access - How to make a query showing filtered results
Author: Message:
CookieRevised
Elite Member
*****

Avatar

Posts: 15519
Reputation: 173
– / Male / Flag
Joined: Jul 2003
Status: Away
O.P. MS Access - How to make a query showing filtered results
The title isn't that descriptive but I dunno how to describe in short what I need:

Ok, so it has been years since I touched MS Access.

I have a table (Table1) with some columns.
The first column consist of a unique autonumbering long integer (the index)

The last column is the problem here. And what my question is about.
This last column should contain a long integer which points back to some record in that very same table. No problem here, but:

How do you make a selection query to put in that last column which shows you the list of records in that very same table but without the current record (thus where the ID of the current record (the one which initiated the query) is not the same as the ID of the records pulled from the very same table).

Example:
#ID                 Name       Infant of
1234            Cookie        4567
3456            Dada
6789            BoBo            1234   



I know you can make joins which point back to the same table by adding that table twice in the relationship window. But what do you need to enter in the criteria field for that last column in the selection query.... "<>[Table1].[ID]" does not work

I have no clue...

(If the above isn't clear enough, I could attach an example db with what I have atm)

This post was edited on 05-11-2008 at 12:30 PM by CookieRevised.
.-= A 'frrrrrrrituurrr' for Wacky =-.
05-11-2008 12:27 PM
Profile PM Find Quote Report
Th3rmal
Veteran Member
*****

Peek-a-boo! I see you!!

Posts: 1226
Reputation: 26
32 / Male / Flag
Joined: Aug 2005
RE: MS Access - How to make a query showing filtered results
quote:
Originally posted by CookieRevised
I could attach an example db with what I have atm
i suggest you do that.

I might be able to help you (i have a SAC on MS Access tomorow :P) but i need an example of what you are trying to do, i think i have a slight idea, but i could be horribly wrong :P

This post was edited on 05-11-2008 at 12:35 PM by Th3rmal.
You have the intellect comparable to that of a rock. Be proud.
05-11-2008 12:34 PM
Profile E-Mail PM Web Find Quote Report
CookieRevised
Elite Member
*****

Avatar

Posts: 15519
Reputation: 173
– / Male / Flag
Joined: Jul 2003
Status: Away
O.P. RE: MS Access - How to make a query showing filtered results
It's not much though:

The thing I want to do: when you click in the last column you get a dropdown list with all the records of that very same table, but without the current record. When you select a record in that list, it's ID is written in that column...




thanks already


.zip File Attachment: db1test.zip (9.42 KB)
This file has been downloaded 186 time(s).

This post was edited on 05-11-2008 at 01:43 PM by CookieRevised.
.-= A 'frrrrrrrituurrr' for Wacky =-.
05-11-2008 01:40 PM
Profile PM Find Quote Report
markee
Veteran Member
*****

Avatar

Posts: 1621
Reputation: 50
35 / Male / Flag
Joined: Jan 2006
RE: MS Access - How to make a query showing filtered results
Try using the SQL feature.  Whenever I use Acces I try to stay away from the UI stuff and stick to using it like any other database so that it is just like what I already know :P.

The SQL is a different view of the queries.
[Image: markee.png]
05-11-2008 10:17 PM
Profile PM Find Quote Report
CookieRevised
Elite Member
*****

Avatar

Posts: 15519
Reputation: 173
– / Male / Flag
Joined: Jul 2003
Status: Away
O.P. RE: MS Access - How to make a query showing filtered results
I don't know enough SQL for that....  but if you have the equivalent SQL command for what I want I can always enter it that way of course.

(and part of the reason I use MS Access is exactly because of the 'easyness' and convenience of the UI stuff, so...)

This post was edited on 05-11-2008 at 11:13 PM by CookieRevised.
.-= A 'frrrrrrrituurrr' for Wacky =-.
05-11-2008 11:12 PM
Profile PM Find Quote Report
TheSteve
Full Member
***

Avatar
The Man from Japan

Posts: 179
Reputation: 23
39 / Male / Flag
Joined: Aug 2005
RE: MS Access - How to make a query showing filtered results
The lookup query could look like 
quote:
SELECT ID FROM Tabel1 WHERE ID!=42
Where 42 would need to be replaced by the current row's ID. The trouble is, how do you get data from the current row?  If there is a way to get data from the current row, it would need to be an Access specific syntax as this kind of thing doesn't apply to standard SQL.

Another issue that you might run in to is, even if you do get the items to show up in the list, when you select an item it doesn't put anything in the box. (At least not in my tests using a standard select everything query)

Is it possible to use a Form for data input?
05-12-2008 03:02 AM
Profile PM Web Find Quote Report
Adeptus
Senior Member
****


Posts: 732
Reputation: 40
Joined: Oct 2005
RE: MS Access - How to make a query showing filtered results
I don't work much with Access and certainly not with the graphical query designer.  I work a lot with SQL.  It sounds like the SQL query you want is:

SELECT B.ID, B.Name FROM MyTable A JOIN MyTable B on B.ID <> A.ID WHERE A.ID = something

That *sounds* like what you want, although it is somewhat odd and you are not describing it very well.

In any case, perhaps the SQL concepts shown in this example will get you started in the right direction -- you can alias table names and that is the only way you can join the same table to itself.  My Access experience is limited, but I can tell you that aliasing and joins are fully supported on SQL query level -- although I would have no idea how you go about doing this with the graphical query designer (you probably can't).
05-12-2008 04:15 AM
Profile E-Mail PM Find Quote Report
CookieRevised
Elite Member
*****

Avatar

Posts: 15519
Reputation: 173
– / Male / Flag
Joined: Jul 2003
Status: Away
O.P. RE: MS Access - How to make a query showing filtered results
quote:
Originally posted by TheSteve
The lookup query could look like 

SELECT ID FROM Tabel1 WHERE ID!=42

Where 42 would need to be replaced by the current row's ID. The trouble is, how do you get data from the current row?
Jep, that's exactly the problem

quote:
Originally posted by TheSteve
Another issue that you might run in to is, even if you do get the items to show up in the list, when you select an item it doesn't put anything in the box. (At least not in my tests using a standard select everything query)
Yes it does, in the table setup you specify what column of the selection query you use to put something in 'the box'.

quote:
Originally posted by TheSteve
Is it possible to use a Form for data input?
No, that's too easy :p

quote:
Originally posted by Adeptus
SELECT B.ID, B.Name FROM MyTable A JOIN
MyTable B on B.ID <> A.ID WHERE A.ID = something
...
you can alias table names and that is the only way you can join the same table to itself.
The sql query shown is almost exactly what I already have, except it still doesn't show what 'something' must be.

As for aliasing, that's also already done since I essentially create a self-join (thus where a field refers to another record in the same table)...

See example db in previous post. (though that example still has the join set wrong)

And to try and make things more clear, this is how it should look (thus without the highlighted row):
[Image: attachment.php?pid=907899]


Anyways, thanks for trying. Seems like it isn't that simple afterall. Although I know for sure it is possible since I've done in in the past (but lost all examples and stuff).

.png File Attachment: Image1.png (13.51 KB)
This file has been downloaded 299 time(s).
.-= A 'frrrrrrrituurrr' for Wacky =-.
05-14-2008 07:01 PM
Profile PM Find Quote Report
« Next Oldest Return to Top Next Newest »


Threaded Mode | Linear Mode
View a Printable Version
Send this Thread to a Friend
Subscribe | Add to Favorites
Rate This Thread:

Forum Jump:

Forum Rules:
You cannot post new threads
You cannot post replies
You cannot post attachments
You can edit your posts
HTML is Off
myCode is On
Smilies are On
[img] Code is On