Forms and 2 tables

[Hubbymax]

How do you get a form to call fields from 2 different tables? One
table contains Department info and the other table is for requisition
info. On the form I want to enter a Department number from a drop down
box and have the department info from the department table to be
automaticly entered. Also on the form and a sub form I enter
information about the departments requisition info. I want the info
from the saved in the recuisition table which also holds the requision
number. The department table does not have a requisition number field
so how do I tie the two together for report reasons and for the
ability to look back through requisions? These are my sticking blocks
to redesigning my program so only 1 table contains the department info
instead of trying to put it all into the Requisition table causing
duplicate records which hasn't been working out at all.

 

[John W. Vinson]

How do you get a form to call fields from 2 different tables? One
table contains Department info and the other table is for requisition
info. On the form I want to enter a Department number from a drop down
box and have the department info from the department table to be
automaticly entered.

Well... actually, you almost certainly DON'T. The requisition table should
contain the DepartmentID and *no* other information from the departments
table. What information do you want to be "automatically entered"? Or do you
want it to just be DISPLAYED?

Also on the form and a sub form I enter
information about the departments requisition info. I want the info
from the saved in the recuisition table which also holds the requision
number. The department table does not have a requisition number field

Well... certainly not. Otherwise each department would be able to have one,
and only one, requisition. This design restriction would get a bunch of
department heads very annoyed... <g>

The foreign key goes in the "many" side table: the Requisitions table should
have a DepartmentID field (and you would use a Combo Box based on the
departments table to enter it).

If you want to *DISPLAY* information about the department, you could either
use a main Form based on the departments table, with a Subform based on
Requisitions; or, you can include those fields that you want to see in the
combo box's Row Source query and display them (without storing them!!) on the
form by putting textboxes on the form with control sources like

=comboboxname.Column(n)

where n is the zero based index of the field that you want to display.

so how do I tie the two together for report reasons and for the
ability to look back through requisions? These are my sticking blocks
to redesigning my program so only 1 table contains the department info
instead of trying to put it all into the Requisition table causing
duplicate records which hasn't been working out at all.

Perhaps you could explain why you feel that you need to put department
information (other than the unique department ID) in the requisitions table at
all.

Take a look at some of these tutorials to see how this kind of issue is
commonly handled:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[Arvin Meyer [MVP]]

Base the form on a query that joins the 2 tables.

 

[Hubbymax]

Well... actually, you almost certainly DON'T. The requisition table should
contain the DepartmentID and *no* other information from the departments
table. What information do you want to be "automatically entered"? Or do you
want it to just be DISPLAYED?


Well... certainly not. Otherwise each department would be able to have one,
and only one, requisition. This design restriction would get a bunch of
department heads very annoyed... <g>

The foreign key goes in the "many" side table: the Requisitions table should
have a DepartmentID field (and you would use a Combo Box based on the
departments table to enter it).

If you want to *DISPLAY* information about the department, you could either
use a main Form based on the departments table, with a Subform based on
Requisitions; or, you can include those fields that you want to see in the
combo box's Row Source query and display them (without storing them!!) onthe
form by putting textboxes on the form with control sources like

=comboboxname.Column(n)

where n is the zero based index of the field that you want to display.


Perhaps you could explain why you feel that you need to put department
information (other than the unique department ID) in the requisitions table at
all.

Take a look at some of these tutorials to see how this kind of issue is
commonly handled:

Jeff Conrad's resources page:http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:http://allenbrowne.com/links.html#Tutorials

As usual, I was not very clear. I am a newbe. Yes, I only want the
department info to show up on the requisition form and want it to show
up by using a combo box based on department number and requisition
number. Anotherwords, if a person came to me and ask what they ordered
or there was a dispute as to what was ordered I would be able to go
back and call up that requisition for viewing. I did not mention that
I would also like the amout issued field in the requisition field to
deduct that amount from the amount in stock kept in the products
table. I will admit that this project is way over my abilities at this
time but it's a challenge I am enjoying.