linking two tables

[Mark]

Please help I am acomplete novice. I have created two tables, but I would
like to take some data from the first table, and use it in the second table.
Please can anyone help

 

[Klatuu]

Mark,
Can you please be more specific? What data do you want to put in the second
table and how do you identify those data?

 

[Mark]

I have two fields first name and last name, that I want to use in another
table that I have created.

Hope this is the answer that you want?

Mark

 

[Klatuu]

Do you want to add new records to the second table with the names or add the
names to records that already exist in the second table? If you want to add
the names to existing record in the second table, how will you know which
records you want to add them to?

Sorry to be so quizzy, but we need to know these sorts of things to know how
to tackle the problem.

 

[Mark]

Hi,

I wish to take first name and last name from table one, and use just these
two fields, in table two along with other fields I have created. I will not
require to add new records, I will not need the records used in table two, if
they are not in table one.

I will sum up. I require first name and last name fields, to be imported
into table two, to save me typing the informationm out again. That is the
only information I want from table one.

Mark

 

[Klatuu]

Okay, I understand that part now, thanks. The one important question you did
answer is how you will know which record in table 2 to add the names to?

 

[Mark]

I want table one to determine the make up of table two, so if the are twernty
four names in table one, then I want it to take the twenty four names, and
then add the fields of table two.

I hope this is clearier
Kind Regards

Mark

 

[Klatuu]

Sorry, Mark, I am even more confused. Lets's start from the top. You have
two tables, Table 1 and Table 2. Table 1 has two fields you want to use in
Table 2. They are FirstName and LastName. You have other fields in Table 2
you want to combine with the name fields from Table 1.
If there are 24 names in Table 1, then Table 2 should have 24 rows with the
24 names from Table 2 plus the name fields from Table 1.
So, are the names added to records that exist in Table 2 or are new records
create in Table 2, or are we talking about creating a whole new Table 3?
And, I still don't get how you know which names from Table 1 go into which
rows in Table 2.

 

[Mark]

Okay hopefully going tio simfly this, and give you the sceanario. I have set
up a two databases all ready, database one cotains personal details (ie name
address etc), database two cointains a list of badges that the kids can get,
now what I want to to do is take the names from table one and the badges from
table two and effectively create table three which will cotainer the names
and the badges.

Hope this is now clear?

Mark

 

[Klatuu]

Okay, that is much more clear; however, the one question I keep asking that
maybe you are not understanding is:

How do you know which kid get's which badge or badges? And, can a kid get
more than one badge? And can a kid get more than one badge at a time?

 

[Mark]

I have set the badges up in table two, as a yes/ no field. I was hoping that
using table three I could set the yes(if they have the badge) / no if they
dint have the badge. They have the opportunity to get all badges, but
basically I want the table to say yes or no if they have got. The can't gain
the badge more than once.

Hope this helps.

Mark

 

[Klatuu]

Actually, Table 2 should be just a list of possible badges and Table 1 should
be all the Kids, and their information. Table 3 should be Kid/Badge. Then,
you will need a form/subform for managing this. The main form should be
based on Table 1 and where you select a kid, and the subform should be based
on Table 3. In your subform, you should have a combo box based on a query
that shows all the possible badges. That combo's query should be based on
table 2 that would include on the current Kid, and the badges not yet
received.

That is the broad picture, If you need more detail, post back.

Thanks for your patience in helping me understand the problem. I think you
have explained it well now.

 

[Mark]

Excellent, will do that, but how do I do that. I have very little knowledge
of access,

mark

 

[Klatuu]

Ok, Mark. This will take a little work, and I suggest we do it in stages.
Stage 1 will be to create the main form where we look up kids. Do these
steps, then when you have that working, we can go on to the next stage:
1. Create Table 1. I suggest you name it tblKids (or something like that).
Create the fields you want in that table. Start with an Autonumber field and
name it KID_ID and make it the Primary Key.
2. Create the main form. Name it frmKidBadges. Use the wizard to put the
fields on this form that you want to see.
Once you have that working, post back and we will go from there.

Good Luck.

 

[Mark]

Hi,

I appreciate your help with this. I have done the fist two stages.

Kind Regards

Mark

 

[Klatuu]

Great, now let's create Table 2 to keep the names of all the badges. Call it
something like tlbBadges. Use these fields:
BADGE_ID - This should be Autonumber and the Primary key
BADGE_NAME - Text - This is the description of the badge
add any other fields you need.
Create a form using the wizard like you did for the Kid's form so you can
add or edit badges.

Now, we will create Table 3. Call it tblKidBadges
It will only need 2 fields:
KID_ID - Number, Long
BADGE_ID - Number, Long
Make the combination of the two fields the Primary Key. You do this by
selecting both fields then clicking the Primary Key Icon.

Once you complete that, we will set up relationships and add our subform.

 

[Mark]

Hi,

Thanks once again, these tables have been created and done.

Kind Regards

Mark

 

[Klatuu]

Great! Now we are moving along.
The next step is to create the relationships for the 3 tables. From your
main menu, select Tools->Relationships.
You will see a form pop up the will say "Relationships" in the upper left
corner.
Right click and you will get a menu. "Select Show Table" You will get a
Dialog that shows all the tables in your database. Ignore the Queries and
Both tabs.
Select each table in order tblKids, tblBadges, tblKidBadges
In tblKids, Click on KID_ID and drag to KID_ID in tblKidBadges - You should
get a One To Many from tblKids to tblKidBadges
In tblBadges, Click on BADGE_ID and drag to BADGE_ID in tblKidBadges - You
should get a One To Many from tblBadges to tblKidBadges.

 

[Mark]

Hi

Done that!!

Hopefull nearly finished.

Mark

 

[Diogo Demarchi]

Hi,

I wish to take first name and last name from table one, and use just these
two fields, in table two along with other fields I have created. I will not
require to add new records, I will not need the records used in table two, if
they are not in table one.

I will sum up. I require first name and last name fields, to be imported
into table two, to save me typing the informationm out again. That is the
only information I want from table one.

Mark