Returning a value based on another value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
richardwo said:
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
Click to expand...

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
 
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
 
hi Richard,

richardwo wrote:
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.
Click to expand...
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.
Click to expand...
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 <--

--
 
richardwo said:
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.
Click to expand...

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.
Click to expand...

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

Regards,
Keith.
www.keithwilby.com
 
Back
Top