Can i load information from one table to another by grouping?

  • Thread starter Thread starter aConcernedStudent
  • Start date Start date
A

aConcernedStudent

I want to to link information somehow. For instance, when i added an existing
field i was hoping since it was a primary key that it would carry over the
other information from the other table automatically. Can it be done?
 
Yes. here is a classic 2 'One' tables 1 'Many' table structure
TblFruit
with the Primary Key
FruitID (Autonumber)
FruitName (eg apple, orange, pear)

and TblColour
ColourID (autonumber Primary Key)
FColour (eg Red, Orange, Pink)

Your third table could be
TblFruitColour
FCID (Autonumber primary Key)
FruitID (number field, linked from TblFruit)
ColorID(numberfield, linked from tblColour

in the main query window, open the Relationships window.
Drag FruitID from TblFruit to FruitID in TblFruitColour
Join ColourID from TblColour to ColourID in TbFruitColour

Create a new query.
Click the New Table button
Add all three tables to the query grey area.

add all the fields from TblFruitColour to the white grid.
Add all the fields *except* the Primary key fields from the other 2 tables.

To input the data, you could (since exams are looming) type the fruit and
colours directly into TblFruit and TblColour in any order

for the third table,
Click on the closed TblFruitColour
Go to Insert Form

Choose Create Form, Autoform Datasheet.
You now have an ugly looking form.
In form design view, add a combo box.
The wizard should kick in.
Choose 'I want the combo to look up the values in a table or query'
Choose to get your data from TblFruit,
Add FruitID and Fruit to the combo
The Wizard should automatically hide the FruitID column
Choose to 'Store the value in this field' and choose FruitID

Repeat the above with a combo for TblColour, storing the Value in ColourID

Now you can choose the correct colour for the fruit.

Method 2:

click on the closed TblColour

Go to Insert Form
Choose Autoform Columnar (ie a Single form). Save the form as FrmColour

Open the new form in *Design view*
Drag the Detail section a bit longer.
Arrange the Window so that you can see the form in design view and the main
database window.
Drag TblFruitColour from the Database window onto the form.
This will create a subform.
The wizard should let kick in and suggest that you link this new Subform to
the main form by ColourID
Save the subform as FrmColourSub

Save and close the new form .

Open FrmColourSub
Add a combo from Tblfruit as you did before

You can now input your data with your fruit combo, only this time, you can
just turn to any colour and add any fruits that fit with it.

Open your query to see your linked data in a list

Decide that this is a naff way of using your new skill and think of a better
way of using it (eg keeping track of your PlayStation games)

Evi
 
Back
Top