concatenate????

L

Lin

I have 3 tables tblBM, tblPCP, tblChildren.
What I need to do is have some way of having the tblBM inherit all the
surnames from tblPCP and tblChildren. I have tried using a few queries none
worked. Now in each table CaseNum is the unique ID. Can this be done, or
should I do something completely different. Please help. Much thanks in
advance.
 
K

Ken Snell [MVP]

Probably it's doable, but you'll need to tell us more about the table
structures (all three of them) and what you mean by "inherit".
 
J

John Vinson

I have 3 tables tblBM, tblPCP, tblChildren.
What I need to do is have some way of having the tblBM inherit all the
surnames from tblPCP and tblChildren. I have tried using a few queries none
worked. Now in each table CaseNum is the unique ID. Can this be done, or
should I do something completely different. Please help. Much thanks in
advance.
Click to expand...

You should almost certainly do something completely different: use
Access as it is designed, as a relational database.

Storing data (surnames) redundantly in two tables is NOT necessary,
and in fact it's bad design. Store the name once, and once only,
preferably in a table of People, and then use a Query joining this
table to your tblBM to display the names.

Also, one to one relationships are QUITE uncommon; if you're using
them for Subclassing or for field-level security you may be doing so
legitimately, but I fear that your tblChildren has fields named
Child1, Child2, Child3... which is *incorrect*. If you have a one
(case) to many (children) relationship, you should model it as a one
to many relationship - tblChildren would have its own Primary Key, and
a CaseNum as a foreign key linking it to the table of cases (tblBM??).
Each record in tblChildren would have information about one child; if
there are three children involved in a case, there would be three
records in this table.

You would then use Queries, Forms, and Reports to bring the data from
the tables together for display purposes. It is NOT necessary (nor
even a good idea) to pull all the information into one table!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
L

Lin

Ok Basically We have
TblMain, CaseNum(unique)
tblBM with BMID(unique), CaseNum(linked to Main/not unique), Surname,
GivenName.
Then tblPCP with PCPID(unique), CaseNum(linked to Main/not unique), Surname,
GivenName, and
tblChildID(unique), CaseNum(linked to Main/not unique), Surname, GivenName.

In our Agency BM will be listed under all surnames related to her family.

Hope this is helpful
 
K

Ken Snell [MVP]

So I assume that "inherit" means you want to store the surname info into the
tblBM based on what's in the other two tables? Why do you need to do that?
You already have the surnames stored in those two tables, and they are
linked to tblBM by the CaseNum. Just use a query to display the information
from those two tables. No need at all to store those names in the tblBM
table.

Tell us what you want to display and we'll help you develop that query.
--

Ken Snell
<MS ACCESS MVP>
 
L

Lin

Thanks Ken, your right about the link, my confussion is when I tried to
create the query, It was not displaying the info I needed. Basically I need
the bm to query for BMfirst Name and Other+Children last name. Even though
they are keyed and related by CaseNum. It was not showing me this. What I
did was have all three table in query, using casenum, other surname, child
surname, bm surname and bm given name. Then I thought maybe I needed to
create another query on that query but that didn't work either.

Regards.
 
Top