By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,855 Members | 1,988 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,855 IT Pros & Developers. It's quick & easy.

Using data from one Access database in another

P: n/a
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
Share this Question
Share on Google+
5 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
> 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

P: n/a
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.