Forms and database setup

[Lostguy]

I need some help Barney-style with this. Using Access 2003, I am
missing something basic and it is kicking my a--.

I have a new business. It has 10 different job positions. I know those
in advance so I design a tblJob with fields named JobID (autonumber/
pk) and JobTitle (text).Using that table, I type in the 10 job titles
(Job1,Job2, etc.) into the JobTitle field. The AutoNumber numbers
those 1-10.

I know that I will have employees, so I create a tblEmployees with
fields EmployeeID(pk/autonumber), LastName, and JobID(long integer
number/FK from tblJob/ DefaultValue: Null)

I set up the relationship as one (JobID from tblJob) to many (JobID in
tblEMployees) because many employees could have the same one job. I
also enforce ref int (for every job, there has to be an employee who
has it), and cascade update (if a job title gets renamed, the rename
will be reflected by all the associated employees).

Now, I start bringing in the employees. What I would like to do is
have a form where I can a) type in the name of the employee (Smith)
and then b) select their job from a dropdown (Job8). I then should be
able to go back a table and see that relationship (that Smith has Job
8,or at least the record numbers for those in 2 columns)

1) Is the proper join type for this #2?

2) I can't seem to set up the form for this. Since I am typing data
into one table, and using a dropdown from another table, it doesn't
seem to work right. I tried to insert a subform with just the two
fields (LastName and JobTitle), but I keep getting errors or it won't
let me type in one or both of the fields. Should I be using subfomrs
or would a combo box do?

3) When I type in "Smith", it goes in the tblEmployees in the LastName
field with Autonumber 1. When select JOb 8, where does that value get
stored? Where does the Smith-to-Job8 relationship get stored?

4) Why would you ever base a form on a query? Don't you use forms to
display and input data INTO a table, and queries to extract/view (but
not input) data FROM a table???

I am using all the information I get! Thanks!

VR/

Lost

 

[Tom Wickerath]

Hi Lostguy,

I set up the relationship as one (JobID from tblJob) to many (JobID in
tblEMployees) because many employees could have the same one job.

So an employee with never have more than one job? My gut feeling tells me
that you might want to set up a many-to-many (M:N) relationship, but you know
your business better.

I also enforce ref int (for every job, there has to be an employee who
has it), ...

That's very good that you are enforcing RI (Referential Integrity), because
creating relationships without enforced RI is little more than an exercise in
drawing lines. However, RI will not require that for every job there has to
be an employee who has it. Having enforced RI means that it would be
impossible to enter a non-existant JobID into a record in the tblEmployees
table. A 1:M relationship actually allows zero to many related records.

...and cascade update (if a job title gets renamed, the rename
will be reflected by all the associated employees).

You do not need the Cascade Update option enabled with your design. The
reason is that an autonumber is meaningless. There is no reason to change it
and, in fact, Access will not let you change it. (Access does allow you to
append a new Autonumber value of your choosing, as long as it has not already
been used, but it will not let you change an existing value directly).
Cascade Update only comes into play when you have a text-based primary and
foreign key combination. An example is in the Customers and Orders tables in
the sample Northwind database. In order to update a CustomerID (the five
character ID value, not the Customer name), you need to have Cascade Update
enabled.

1) Is the proper join type for this #2?

Personally, I would use an Inner Join for the Tools | Relationships view.
You can always use one of the outer joins, if necessary, in a query that
serves as the recordsource for a report.

2.) Should I be using subforms or would a combo box do?

An employees form with a combo box that allows one to select the job would
work. Start by creating a form based on the Employees table. The form wizard
will pop out a form with three text boxes, one each for EmployeeID, LastName
and JobID. Open this form in design view. Right-click the JobID text box, and
then select Change To | Combo Box. If you do not already have the Properties
dialog displayed, then toggle it on by clicking on View | Properties, or by
pressing the F4 button. Select the combo box. You should see Combo box in the
blue title bar of the Properties dialog. Select the Other tab. Give the combo
box a reasonable name, such as cboJobs.

The combo box will have properties like this:

Format tab
Column Count: 2
Column Widths: 0"; 1.25"
List Rows: 25

Data tab
Control Source: JobID <---This should be the JobID
in the Employees table (ie.,
the foreign key)
Row Source Type: Table/Query
Row Source: SELECT JobID, JobTitle FROM tblJobs ORDER BY JobTitle
Bound Column: 1

3) When I type in "Smith", it goes in the tblEmployees in the LastName
field with Autonumber 1. When select JOb 8, where does that value get
stored?

It should be stored in the foreign key field, ie. [tblEmployees].[JobID]

4) Why would you ever base a form on a query?

Lots of reasons. First, a query is used to provide order to records from a
table. So, your employees form can display employees sorted alphabetically,
even though you will enter them into the table in the order that they are
hired.

Queries can include criteria to filter down the number of records. For
example, you might later decide that you want to have a field to indicate
whether or not the employee is a current employee or a former employee. A
query could include a criteria on this field, to filter out former employees.

You may not need all of the fields from the table for a particular form.
It's best to restrict recordsets to only the needed fields. If you later
advance your skills to creating Multiuser Access applications, where a
network wire separates you from your data (ie. a "split" FE [Front-End] / BE
[Back-End] application), then you'll likely want to follow the golden rule,
which is to request only the data needed.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Rick Brandt]

I have a new business. It has 10 different job positions. I know those
in advance so I design a tblJob with fields named JobID (autonumber/ pk)
and JobTitle (text).Using that table, I type in the 10 job titles
(Job1,Job2, etc.) into the JobTitle field. The AutoNumber numbers those
1-10.

I know that I will have employees, so I create a tblEmployees with
fields EmployeeID(pk/autonumber), LastName, and JobID(long integer
number/FK from tblJob/ DefaultValue: Null)

I set up the relationship as one (JobID from tblJob) to many (JobID in
tblEMployees) because many employees could have the same one job. I also
enforce ref int (for every job, there has to be an employee who has it),
and cascade update (if a job title gets renamed, the rename will be
reflected by all the associated employees)

If you are storing the JobID in the employees table (as you should be)
then you don't really need cascade update here. That would only apply if
you changed the JobID in the jobs table and since it's an AutoNumber you
cannot do that anyway.

Now, I start bringing in the employees. What I would like to do is have
a form where I can a) type in the name of the employee (Smith) and then
b) select their job from a dropdown (Job8). I then should be able to go
back a table and see that relationship (that Smith has Job 8,or at least
the record numbers for those in 2 columns)

1) Is the proper join type for this #2?

You need a one-to many between jobs and employees as you stated but the
join is not really relevant to how your form works. If you store an 8 in
the JobID of an employee then you will see an 8 in the table for that
record. The fact that you are using a ComboBox to make the entry really
has nothing to do with that, nor does the relationship.

2) I can't seem to set up the form for this. Since I am typing data into
one table, and using a dropdown from another table, it doesn't seem to
work right. I tried to insert a subform with just the two fields
(LastName and JobTitle), but I keep getting errors or it won't let me
type in one or both of the fields. Should I be using subfomrs or would a
combo box do?

You appear to be getting worked up over the RowSource table for your
ComboBox. All your ComboBox is doing with the jobs table is getting its
list of choices from there. If the ComboBox is bound to the field in
your employees table then you are good to go. Any entry you make should
be stored in that field.

3) When I type in "Smith", it goes in the tblEmployees in the LastName
field with Autonumber 1. When select JOb 8, where does that value get
stored?

In the JobID field of the employees table.

Where does the Smith-to-Job8 relationship get stored?

In the emplyees table.

Relationships only create *rules* about what data can be stored in the
field on one side of the relationship. That's pretty much all they do.
In your case the relationship would enforce that any numeric JobID you
enter into your employees table must be a numeric value that is found in
the jobs table. If you only used your form with its ComboBox to enter
employee data then the ComboBox is already forcing you to enter a value
that is found in the jobs table. The enforced relationship just makes
that a rule *at the table level*.

4) Why would you ever base a form on a query? Don't you use forms to
display and input data INTO a table, and queries to extract/view (but
not input) data FROM a table???

To the extent that queries are suggested as form RecordSources it is
usually just a SELECT query against the ONE table that you would
otherwise have used. That allows you to sort, (permanently) filter, and
include some calculated fields for use on the form. Multi-table queries
generally are not good to use as Form RecordSources, though simple two
table queries can be used to bring in some lookup values.

 

[Lostguy]

Rick and Tom,

I did everything you recommended, and it works just fine now!

Thanks!

VR/

Lost (but gaining some ground)

 

[Tom Wickerath]

Great! Glad to hear of your success!


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________