469,307 Members | 1,681 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,307 developers. It's quick & easy.

Using data from one Access database in another

jh
The situation:

A database is wanted. It's purpose is to show some co-workers' skills.

Therefore I will need to make a database that contains names and the posts
"skill1", "skill2", "skill3" etc. (YES/NO-posts)
This should be easy; one table in a simple database, but...

In the company we already have another database containing the co-workers
work-ID-number and their names. I would like to use this existing
information in the new database, then we will not need to maintain another
database with names.
(and no, I'm not allowed to extend the already existing database with
skills)

Therefore; a new database which "collects" certain information from an old
database.
The new database must of course synchronise with the old, so the name
information always will be the same in the 2 databases, meanwhile the new
database can hold the "skill1", "skill2", "skill3" etc. to a certain
co-worker.

Hoping for some tips.

Regards
hansen

Nov 13 '05 #1
5 1331
Create your new database.

Link the Worker table from the other database:
File | Get External | Link

Create a Skill table with fields:
SkillID Text Name of the skill (primary key)

Create a WorkerSkill table, with fields:
WorkerID foreign key to the WorkerID in the linked table.
SkillID foreign key to the Skill table.

Create a main form bound to the Worker table.
Add a subform bound to the WorkerSkill table.
In the subform, include a combo that has the Skill table as its RowSource.

In the subform, you can then select as many skills as apply to the worker in
the main form, one per row.

(This is a *way* better data structure than having lots of yes/no fields in
one table. For example you don't need to change the entire data structure
just to add one more skill.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"jh" <vc> wrote in message
news:43*********************@dread16.news.tele.dk. ..
The situation:

A database is wanted. It's purpose is to show some co-workers' skills.

Therefore I will need to make a database that contains names and the posts
"skill1", "skill2", "skill3" etc. (YES/NO-posts)
This should be easy; one table in a simple database, but...

In the company we already have another database containing the co-workers
work-ID-number and their names. I would like to use this existing
information in the new database, then we will not need to maintain another
database with names.
(and no, I'm not allowed to extend the already existing database with
skills)

Therefore; a new database which "collects" certain information from an old
database.
The new database must of course synchronise with the old, so the name
information always will be the same in the 2 databases, meanwhile the new
database can hold the "skill1", "skill2", "skill3" etc. to a certain
co-worker.

Hoping for some tips.

Regards
hansen

Nov 13 '05 #2
Hi Allen, and thanks. It doesn't seem to work; maybe I've misunderstood a
detail or two. I have some questions :-)

"Allen Browne" <Al*********@SeeSig.Invalid> skrev i en meddelelse
news:43***********************@per-qv1-newsreader-01.iinet.net.au...
Create your new database.

Link the Worker table from the other database:
File | Get External | Link
ok
Create a Skill table with fields:
SkillID Text Name of the skill (primary key)
Do you mean 3 fields (SkillID, Text and Name of skill)? which is primary
key?
Create a WorkerSkill table, with fields:
WorkerID foreign key to the WorkerID in the linked table.
SkillID foreign key to the Skill table.
Should I choose "referential integrity" or one of the 3 options in the
dialogue box "join type"?
Create a main form bound to the Worker table.
Add a subform bound to the WorkerSkill table.
In the subform, include a combo that has the Skill table as its RowSource.
ok
In the subform, you can then select as many skills as apply to the worker
in the main form, one per row.

(This is a *way* better data structure than having lots of yes/no fields
in one table. For example you don't need to change the entire data
structure just to add one more skill.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Nov 13 '05 #3
Embedded answers.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mr. Hansen" <mr@hansen.com> wrote in message
news:43*********************@dtext02.news.tele.dk. ..
Hi Allen, and thanks. It doesn't seem to work; maybe I've misunderstood a
detail or two. I have some questions :-)

"Allen Browne" <Al*********@SeeSig.Invalid> skrev i en meddelelse
news:43***********************@per-qv1-newsreader-01.iinet.net.au...
Create your new database.

Link the Worker table from the other database:
File | Get External | Link
ok
Create a Skill table with fields:
SkillID Text Name of the skill (primary key)


Do you mean 3 fields (SkillID, Text and Name of skill)? which is primary
key?


No. That's one field (the way it appears in table design view.)
Create a WorkerSkill table, with fields:
WorkerID foreign key to the WorkerID in the linked table.
SkillID foreign key to the Skill table.
Should I choose "referential integrity" or one of the 3 options in the
dialogue box "join type"?


2 fields above.

Yes, in the Relationships window, you will create a relation with RI to the
Skill table.

You will not be able to create a relation with RI to the attached Worker
table.
Create a main form bound to the Worker table.
Add a subform bound to the WorkerSkill table.
In the subform, include a combo that has the Skill table as its
RowSource.


ok
In the subform, you can then select as many skills as apply to the worker
in the main form, one per row.

(This is a *way* better data structure than having lots of yes/no fields
in one table. For example you don't need to change the entire data
structure just to add one more skill.)

Nov 13 '05 #4
> No. That's one field (the way it appears in table design view.)

ah, sure. :-)
Create a WorkerSkill table, with fields:
WorkerID foreign key to the WorkerID in the linked table.
SkillID foreign key to the Skill table.
Should I choose "referential integrity" or one of the 3 options in the
dialogue box "join type"?


2 fields above.

I mean which to choose? (screenshot here:
http://www.cse.dmu.ac.uk/~mjdean/not...s/image033.jpg)
Yes, in the Relationships window, you will create a relation with RI to
the Skill table.

You will not be able to create a relation with RI to the attached Worker
table.


It doesn't allow me to choose RI between Skill table and WorkerSkill Table.

Almost there :-)

Nov 13 '05 #5
The join type in the Relationships window is irrelevant. (All it does is set
the default join in future queries. I don't find that useful: if I need to
create out joins, I want to do that manually.)
You should be able to create a relation with enforced RI between your Skill
and WorkerSkill table if:
- Both tables are in the same mdb file (not linked).
- The fields are the same type and size in both tables.
- The field from the Skill table (probably SkillID) is the primary key.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mr. Hansen" <mr@hansen.com> wrote in message
news:43*********************@dtext02.news.tele.dk. ..
No. That's one field (the way it appears in table design view.)


ah, sure. :-)

Create a WorkerSkill table, with fields:
WorkerID foreign key to the WorkerID in the linked table.
SkillID foreign key to the Skill table.

Should I choose "referential integrity" or one of the 3 options in the
dialogue box "join type"?


2 fields above.

I mean which to choose? (screenshot here:
http://www.cse.dmu.ac.uk/~mjdean/not...s/image033.jpg)
Yes, in the Relationships window, you will create a relation with RI to
the Skill table.

You will not be able to create a relation with RI to the attached Worker
table.


It doesn't allow me to choose RI between Skill table and WorkerSkill
Table.

Almost there :-)

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

38 posts views Thread by Remco Groot Beumer | last post: by
2 posts views Thread by Bob | last post: by
1 post views Thread by xcelmind | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.