Shoutbox

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)

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 ) 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

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

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 .

The SQL is a different view of the queries.

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...)

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?

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).

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

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):



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).