Returning a value based on another value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

richardwo - 08 Mar 2007 09:13 GMT

Morning,

I am working on a project management database, and am having difficulties
designing a lookup and would appreciate your help.

The first field in the table (tbl_ProjectDetails) is the primary key (ID)
which is the unique id number for a given project. The second field
(ProjectName) is the name of the project. I would like to deisgn a formula or
VBA code that automatically lookups the project "ID" value and returns the
corresponding name of the project in the "ProjectName" field.

I have created a seperate table (lu_ProjectName) which contains "ID" and
"ProjectName" (for each project ID) for the purpose of looking up the value
in the main table, but I don't know how to link the two so that "ProjectName"
in the table "tbl_ProjectDetails" automatically returns the name of the
project given in the lookup table.

I would really welcome any help with this.

Thanks, rich

Reply to this Message

Keith Wilby - 08 Mar 2007 09:52 GMT

> Morning,
>
[quoted text clipped - 19 lines]
>
> Thanks, rich

Hi Rich,

I think you've got this "arse about face" as it were - your main table
should have a field to contain the unique ID of the project and your lookup
table should contain a PK field and a ProjectName field. You can then use a
combo box bound to the main table with the lookup table as its row source.
Where you need to display the project name (in reports for example) just
include the lookup table in the query.

Is that what you meant or have I missed the point?

Keith.
www.keithwilby.com

Reply to this Message

richardwo - 08 Mar 2007 10:28 GMT

Thanks the advice Keith,

I've done as you suggested, now there is a list of project IDs with the
corresponding name displayed in the datasheet view of the table. However,
what I would like is for the project name to be automatically entered into
the table after the project ID has been entered. There are many projects, so
I would like to eliminate the process of scrolling through a list to select
the correct project name each time.

I hope this makes sense, I'm started to confuse myself.

Cheers, rich

> > Morning,
> >
[quoted text clipped - 33 lines]
> Keith.
> www.keithwilby.com

Reply to this Message

Stefan Hoffmann - 08 Mar 2007 10:48 GMT

hi Richard,

However,

> what I would like is for the project name to be automatically entered into
> the table after the project ID has been entered. There are many projects, so
> I would like to eliminate the process of scrolling through a list to select
> the correct project name each time.
>
> I hope this makes sense, I'm started to confuse myself.

Yes, but you are mixing some things.

> I've done as you suggested, now there is a list of project IDs with the
> corresponding name displayed in the datasheet view of the table.

Don't use lookups in a table directly.

http://www.mvps.org/access/tencommandments.htm

This leads us to the concept of normalization. You need one table to
define your project (Project):

ID (PK), ProjectName, other attributes

You have called this table lu_ProjectNamem which is misleading in some way.

Your table ProjectDetail is has a 1:n relation to it:

ID (PK), Project_ID (FK), other attributes.

FK means it is a foreign key to the project table managed with
referential integrity.

For your work you may create a query:

SELECT D.*, P.ProjectName
FROM Project P INNER JOIN ProjectDetail D
ON P.ID = D.Project_ID

mfG
--> stefan <--

--

Reply to this Message

Keith Wilby - 08 Mar 2007 11:03 GMT

> hi Richard,
>
>> displayed in the datasheet view of the table.

> Don't use lookups in a table directly.

Whoops, I hadn't picked up on that ... good call Stefan.

Keith.

Reply to this Message

Keith Wilby - 08 Mar 2007 11:01 GMT

> Thanks the advice Keith,
>
> I've done as you suggested, now there is a list of project IDs with the
> corresponding name displayed in the datasheet view of the table. However,
> what I would like is for the project name to be automatically entered into
> the table after the project ID has been entered.

You need to include the lookup table in the form's query. You also need to
set up your combo box for 2 columns. The combo's row source should be a
query containing the lookup table's PK field and the project name field. If
you then set the column width property to 0;1 then the PK field will be
hidden and you should just see the project name field.

Essentially, you are storing the project lookup table's PK in the main table
and using the lookup to display the project name on the form.

Hope that makes some sense.

> There are many projects, so
> I would like to eliminate the process of scrolling through a list to
> select
> the correct project name each time.

I'm not sure what you mean - that's what a combo box is for.

Regards,
Keith.
www.keithwilby.com

Reply to this Message