Accessing data from other fields in another Table (1 Viewer)

Hi There,

I wonder if you could answer this conundrum fro me please.

I have a database with a number of tables 2 of which are:

# Personal Members Table (with all their details and addresses and contact details)
# Organisation Table (including who are members of this Org)

My conundrum is this; In the Org table I have just created a 'Treasurer' Field and want to choose a name from the Members Table so that I do not have to put the data in twice (which is what has happened in the past.)

I have linked the tables and but within the Members table the Field names for the Personal Name are as follows <FirstName> <LastName> and there is another for the <Org> they are connected with...

In the Members table it would also be good to have the ability to hover the mouse over the <org> field (or click) on it and then underneath a box appears with everyone who is a member of that org. I have seen this working on another database but cannot seem to replicate it here for some reason?

Can anyone point me in the right direction or advise me please.

Many thanks

Fanflame

add a numeric id to the members table, and use that in related tables - rather than the name itself.

gemma-the-husky said:

add a numeric id to the members table, and use that in related tables - rather than the name itself.

Click to expand...

Many thanks for your advice Gemma-the-Husky: The members table does have a numeric ID which is the auto-created primary key... but how do I add that into the other field? At the moment it is a text box "ordinary" filed and I can only type in it...

i.e. Members Table
the chaps name is: John Doe his ID (primary key) 193

On the Org Table I can only type in John Doe or 193 but this just records exactly what I type and doesn't reference anything??

Thanks for your continued assistance.

Fanflame

Hi FF

I'd be inclined to put the person's position/job title in the personal members table rather than creating more fields in the org table. Then if you want to know who the treasurer is for a particular or you link the two tables and filter based on the member.position.

I would then have a lookup table for the available positions to select from when populating the member table so as to avoid typos and variations of label. like membership officer vs m'ship off etc. Also the benefit to this is if you want to add a new position later you just need to add a record to the list of positions.

Ron

Hope you get the use of the keys sorted. The other thing you mention is hovering over the data in the table. Using tables directly is a big no-no. You really do not want any database user, yourself included, to be using tables for input purposes. You should use forms.

Forms allow you to control and validate your data in a way you cannot achieve when you use tables directly. Much safer

ronc said:

Hi FF

I'd be inclined to put the person's position/job title in the personal members table rather than creating more fields in the org table. Then if you want to know who the treasurer is for a particular or you link the two tables and filter based on the member.position.

I would then have a lookup table for the available positions to select from when populating the member table so as to avoid typos and variations of label. like membership officer vs m'ship off etc. Also the benefit to this is if you want to add a new position later you just need to add a record to the list of positions.

Ron

Click to expand...

Thanks Ron,
that is a good idea I will have a think about this.

Fanflame

gemma-the-husky said:

Hope you get the use of the keys sorted. The other thing you mention is hovering over the data in the table. Using tables directly is a big no-no. You really do not want any database user, yourself included, to be using tables for input purposes. You should use forms.

Forms allow you to control and validate your data in a way you cannot achieve when you use tables directly. Much safer

Click to expand...

Hi Gemma,

Thanks for your reply.

I do have a form that other users access to read the data that I have input.

However, what I was/am trying to achieve on this multi-organisation Table and cannot seem to is this:

Every person is on the members table so that I can keep their home and work details and also what organisation they belong to separate. This enables me to mail them and update their records independently.

Under relationships:
# I have the Organisation shared as a one-to-many.
# Note: The Organisation is also the Primary Key on that Table

If I open the Organisation table next to the actual Field called 'Organisation' is a small + and if I click that + a line drops down with all the people from the Members Table who are members of that Organisation. I didn't set that function it just happened and I am not sure what I did but it is great!

I was trying to get some way of not having to double input the Treasurers name in the 'treasurers' filed but to pick it from a list. Just can't seem to work it out. I realise that if it is possible I can use it on so many other job functions etc.

Difficult to explain and sorry if it sound gobble-de-gook.

Fanflame