Lookups

[andyb01]

Hi All

I am trying to develop a database for recording project risks and issues and need to assign an owner to each one. My design stage has three tables for project resources, issues and risks. The Resource table is basically ResourceID; Surname; Forename, GivenName (forename and surname concatenated) Status; Role and DateAdded. When I allocate a risk or issue I want to be able to select the ResourceID from a drop down list that references the Resource table (I have got this working) and then from that ResourceID value to automatically populate another field with the matching GivenName and that is where I am getting stuck. I have setup relationships between the tables based on ResourceID but can't see where I need to be with a query to do this. Someone a suggetsed a union query but not sure of the syntax for this. I know I could have a separate lookup for the GivenName but that invites data capture error and requires the user to undertake an extra input step that could be avoided with a bit of slick programming.

Hopefully the above provides enough information to identify the problem and possible solution - any help on this appreciated.

Thanks

Andy

 

[Haymarket]

Rather than record the GivenName, as this is a know concatenation, all you have to do is create a field in the database source sql

Givenname: [surname] & " " & [Firstname]

Or if you want to present the data with firstname first, then do it the otherway round.

 

[andyb01]

Thanks for the suggestion - I'll look into this.

Not sure however that this is going to solve the problem of automatically populating one field in a table based on the value input to another as part of lookup - unless I am missing something (which is entirely possible).

Andy

 

[andyb01]

Pat

Thanks - that is definitely what I want to achieve. I'll spend some time going through the sample to work out how you got there and then apply it to my own situation.

Regards

Andy