Write now here is my structure, part of the problem is that I a novice
Access user, and my supervisor wants a lot of variable built in. There
are some subTables that you don't really apply to this issue.
QryStaff List (Built off of ODBC Linked Tables)
======================
client_id
last_name
first_name
Discipline
Position
Dept
login-id
team_name
date_of_hire
date_of_term
active
tblEducationOfferings
==================
ID# (primary key autonumber)
CourseTitle
CourseDescription
InitialOffering (date field)
Presenter
DeliveryMethod
TeachingMethod
LeadPlanner
KBN#
CEHours
ContactHours
tblClassInput (junction)
=================
entry# (primary key autonumber)
last_name (links to qryStaff Listing)
first_name (links to qry Staff Listing)
client_ID (links to qry Staff Listing)
CourseTitle (links to tblEducationOfferings)
Date Completed
Location
The problem I am forseeing is that the link for client_ID only gives
me a random list of numbers, when I or a volunteer is doing data
entry, we have no way of knowing which ID belongs to which person. I
could leave it off of the form but I'm not sure that's the best
solution. Or else I wouldn't be able to know which Stephanie Smith's
record I am updating.
Please provide your actual table and field names. Typically your tables will
have a primary key which I almost always use an autonumber primary key. For
instance I would expect table in your application like:
tblEmployees
================
empEmpID primary key auto number
empFirstName
empLastName
tblCourses
=====================
couCouID autonumber primary key
couTitle
couDescription
tblEmpCourses (junction table)
===================
emcEmCID autonumber primary key
emcEmpID links to tblEmployees.empID
emcCouID links to tblCourses.couID
emcDate
On a form based on tblCourses, you would build a subform based on
tblEmpCourses. This subform would have a combo box to select an emcEmpID
value. The combo box would have a row source like:
SELECT empEmpID, empLastName & ", " & empFirstName
FROM tblEmployees
ORDER BY empLastName, empFristName;
--
Duane Hookom
Microsoft Access MVP
Steve said:
Thanks, Duane, I think that I may be confused or not saying what I
mean to say. You first post made sense, and I didn't know that all of
those problems presented themselves in Access I've used VLOOKUP and
HLookup in Excel for years and haven't really had any problems.
My what I have decided to do now is just create, a basic drop down
menu refereing to a field on another table in Access.(well query
actually, but the same thing) when I use the wizard to set this up it
doesn't show me all of the field inside that wizard.
Also, is there any way to concatinate two field into one drop down for
instance on my Staff list I have a seperate field for Last name and
First name. Will it work if I use this formula for my combo box.
SELECT ([Staff Listing].[last_name]) & ", " & ([Staff Listing].
[first_name]) FROM [Staff Listing];
On Nov 27, 11:43 am, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
Many of us veteran developers hate lookup fieldshttp://
www.mvps.org/access/lookupfields.htm. Consider using forms with combo
boxes.
:
I have a query built off of multiple Linked table that provides Staff
information for my database. I am building a table that is basically a
junction table, between this query and another table, Education/
Courses, to show what employee has taken what classes.
When I use the Look-up Wizard to create a field on the new table
(ClassInput), it is not showing all of the fields of my query, some of
which are very important, i.e. First_Name, Last_Name etc. Does anyone
know why this would be happening.
Steve
.- Hide quoted text -
- Show quoted text -
.
