New Guy with (likely) Simple Question

I'm just starting out here and I keep running into a basic problem.

I build a set of tables: First Names, Last Names, Member Status.
I bring these together in a table, Persons' Name & Status, where each
person gets an unique ID and the other info is brought into the table by
lookup.

So far, everything works well.

But then, I try to build the next step and hit a wall that I can't find
my way around.

If I build a form, or another table and I reference the Person ID and
the Persons' Name & Status table I get the numerical ID instead of the
text that the ID refers to.

The Question
-->How do I get a list box to display info that is associated in one
table by ID#s, when the data I actually want is another "layer down?"

(And please, go slow - I'm new.)

re: New Guy with (likely) Simple Question

MKDunaway,
Ok, first you need to explain to me how it helps to split first names, last
names & member status into independant tables. But . . . you did, and SQL
is a language designed to present data in a result even if the source data
appears vastly different. Pretty much what you need is a SELECT statement
(query) that pulls the data from your tables and presents it as one finished
result. There has to be something in each of the tables that SQL can match
so it knows which row matches to which. The help files talk about JOINS,
which is a short way of saying, "show me all the rows in these tables where
the person id is the same" or some such.
So, something like this should work:
SELECT FNAME_TBL.FIRST_NAME, LNAME_TBL.LAST_NAME, MEMBER_STATUS_TBL.STATUS
FROM FNAME_TBL, LNAME_TBL, MEMBER_STATUS_TBL, NAME_STATUS_TBL WHERE
FIRST_NAME_TBL.ID = NAME_STATUS_TBL.FNAME_ID AND LNAME_TBL.ID =
NAME_STATUS_TBL.LNAME_ID AND MEMBER_STATUS_TBL.STATUS_ID =
NAME_STATUS_TBL.STATUS_ID;
Basically, once the tables are joined you can use fields from any of the
tables you need. You said you are a beginner so my explanation may be a bit
more technical. If so, write back and I or someone else will try again.
--
Alan Webb
knoNOgeek@SPAMhotmail.com
"It's not IT, it's IS
<mkdunaway3@earthlink.net> wrote in message
news:5Uy8e.11278$44.11047@newsread1.news.atl.earth link.net...[color=blue]
> I'm just starting out here and I keep running into a basic problem.
>
> I build a set of tables: First Names, Last Names, Member Status.
> I bring these together in a table, Persons' Name & Status, where each
> person gets an unique ID and the other info is brought into the table by
> lookup.
>
> So far, everything works well.
>
> But then, I try to build the next step and hit a wall that I can't find my
> way around.
>
> If I build a form, or another table and I reference the Person ID and the
> Persons' Name & Status table I get the numerical ID instead of the text
> that the ID refers to.
>
> The Question
> -->How do I get a list box to display info that is associated in one table
> by ID#s, when the data I actually want is another "layer down?"
>
> (And please, go slow - I'm new.)[/color]

re: New Guy with (likely) Simple Question

Okay, do us a favor. Start at the *beginning*. What are you trying to
accomplish with the whole database? Then start looking at tables etc.
First things first, though.

re: New Guy with (likely) Simple Question

Thanks for responding Alan.
I'm not certain if I understood your reply correctly, but if I did I
think there's a bit of confusion. If I didn't there's a bit of
confusion too.

First, why separate the data into tables - lots of people to keep track
of, I'm just trying to save the space of multiple names.

Second: "show me all the rows in these tables where
the person id is the same."

The Per. Name & Stat. is the only table with a Person ID# field.
All of the other tables consist of just 2 fields, the data (names) and
an autonumber Primary key.

Perhaps I was not clear in describing that earlier. Surely I don't have
to put a Person ID field in each of the other tables, do I? One field
wouldn't be enough because it's a one to many relationship (Name to
Person IDs).

-----

pietlinden, thank you too for responding. I think I can guess at the
reasoning behind your question. If you are wondering if this is all
that I am going to do with the mdb, then you would be right to think
that I am making it too difficult for no reason. However, this is only
an example of a problem that will occur every time I add another
function to what will be a major database. I intend it to track
members, contacts, last contact, by who, classes, attendance, addresses,
skills, relations, financial records, etc. All to be accessed by many
different users with varying levels of access.
Too ambitious for a beginner? Yeah. But I used to do this stuff, it's
just been 5 or 6 years since I even looked at it and I've forgotten how
to do basic SQL. Given a week or two however, and I'll be back up to speed.

re: New Guy with (likely) Simple Question

"mkdunaway3@earthlink.net" <mkdunaway3@earthlink.net> wrote in
news:5Uy8e.11278$44.11047@newsread1.news.atl.earth link.net:
[color=blue]
> I'm just starting out here and I keep running into a basic problem.
>
> I build a set of tables: First Names, Last Names, Member Status.
> I bring these together in a table, Persons' Name & Status, where each
> person gets an unique ID and the other info is brought into the table by
> lookup.
>
> So far, everything works well.
>
> But then, I try to build the next step and hit a wall that I can't find
> my way around.
>
> If I build a form, or another table and I reference the Person ID and
> the Persons' Name & Status table I get the numerical ID instead of the
> text that the ID refers to.
>
> The Question
> -->How do I get a list box to display info that is associated in one
> table by ID#s, when the data I actually want is another "layer down?"
>
> (And please, go slow - I'm new.)[/color]

If I understand you correctly my name would be:

13,12

while my brother's might be

92,12

and another person name Lyle might be

13,167

So the the list box of first names would be bound to the first name field
of your master table (long) and be populated with a query of the first name
table (2 columns) with the first column being hidden (but the actual value
of the first name field in the master table) and the second column (the
actual text string of first names) being visible.

Well, it sounds efficient to me but unlikely to have been conceived by a
"please, go slow - I'm new". Regardless, perhaps you are just a natural
when it comes to efficiency.

One of the reasons we cometimes use linking tables for such schemes, is
that we can keep our db accurate over time. If my number is 109 and we keep
track of my first name in a linking table associating me, 109, and my first
name, 13 we can add a date field which keeps track of when? Thus if I
change my first name to 27 today a new (not a replacement) entry will be
made with today's date and 109,27. The database now knows that until today
my first name was 13, but beginning now it's 27. Queries, reports can use
this information to show proper time determined results.

--
Lyle
--

re: New Guy with (likely) Simple Question

Lyle's got the issue. Although, if people start changing their first
name a lot I might need another function - criminal background check.

Your answer Lyle, describes what I need to do well. My problem is I do
not recall how to make a query. So, maybe I'm not "new," let's just
say, senile instead.

What I need then, is as follows.

List box
Person ID#(hidden) / Last Name / First Name / Other relevant Info


What I don't understand is how to set up a query-
If Person ID# = X, then check X's Last Name ID#A, First Name ID#B.
Look up Last Name ID#A, First Name ID#B.
Display Last Name(A), First Name(B)



BTW, thanks everyone for all the help.

re: New Guy with (likely) Simple Question

I suppose this depends on the list box. To add or change names I suppose
you need two list boxes with row sources as

SELECT * FROM FirstNames

and

SELECT * FROM LastNames

.....

but to select a person
(assuming JET)

hmmmm ... I'm so rusty with JET I'll let someone else step in here.


mkdunaway3@earthlink.net wrote:[color=blue]
> Lyle's got the issue. Although, if people start changing their first
> name a lot I might need another function - criminal background check.
>
> Your answer Lyle, describes what I need to do well. My problem is I do
> not recall how to make a query. So, maybe I'm not "new," let's just
> say, senile instead.
>
> What I need then, is as follows.
>
> List box
> Person ID#(hidden) / Last Name / First Name / Other relevant Info
>
>
> What I don't understand is how to set up a query-
> If Person ID# = X, then check X's Last Name ID#A, First Name ID#B.
> Look up Last Name ID#A, First Name ID#B.
> Display Last Name(A), First Name(B)
>
>
>
> BTW, thanks everyone for all the help.[/color]


--
--
Lyle
--

re: New Guy with (likely) Simple Question

Okay, so this is a contact management solution? I hate to do this to
you, but could you outline what this thing is supposed to *do* for you?
it's just that it's impossible to critique a design without knowing
what the design is supposed to accomplish.

One thing that strongly influences design (in my limited experience at
least) is knowing what kinds of questions I need to ask. If I design
my tables and they can't answer the questions I need answers to, then
all my work is a waste of time (and generally someone else's money!).
So I would be inclined to start there.

Not sure if this will help, but you might want to either look at
something like "Database Design for Mere Mortals" by Michael Hernandez,
or look around for existing ERDs
for what you're attempting. Then at least you'll have a starting point
that you can extend/modify to meet your needs. (Anybody happen to
remember that website? Sorry, I just can't think of it.)

One final note and I'll shut my trap - one thing that's really
important is to TEST. Add as few records as you can to verify your
structure. then you can verify your queries etc manually. After
they're proven, you can dump gobs of data in. But I would definitely
recommend starting small. A few tables and business functions at a
time. Otherwise, it's just too easy to get confused and lost in the
whole thing.

hope that helps at least a little.

re: New Guy with (likely) Simple Question

MKDunaway,
There is a limit to the value of normalization. With the names of people
the trade-off between reducing redundant data to save space versus ease of
reporting (IMHO) leans toward just keeping first & last names in the same
table. As for how to write a query, the Query By Example view of
things--what most people interpret as the design view of a query--is just a
graphical means of describing to Access what you want so Access can
translate it into an SQL statement. For the purpose of presenting data in a
useful form there is one primary SQL statement--the SELECT statement. So
your choices for creating a query are either work with the QBE grid--not bad
and most of the time enough--or write your own SQL statements. In my
previous post I gave an example of a SELECT statement.
You stated in one of your posts that you gave us an example of what you
wanted to do. Cool, but it helps if in your example you are clear enough
that we know how to answer you. Now, I'll hazard a guess that you are
trying to puzzle out many-to-many relationships. These are typically built
with three tables, the two related tables and a third table that just lists
the records in the other two tables that are related. To use your example,
there would be a first name table, a last name table, and a third table I'll
call names. So, FirstName would have at least two columns, FNAME_ID and
FNAME. Ditto LastName. Names would have two columns both numbered, called
FNAME_ID, and LNAME_ID which would list the first names and last names that
go together. Opening and viewing Names wouldn't be useful because it would
only be a two column list of numbers. But using a query we could join the
three tables and present the matching first and last names from FirstName
and LastName where they have matching id's in Names. That's it in a
nutshell. Let me know if you need more help with this.
--
Alan Webb
knoNOgeek@SPAMhotmail.com
"It's not IT, it's IS"
<mkdunaway3@earthlink.net> wrote in message
news:CxM8e.8807$sp3.603@newsread3.news.atl.earthli nk.net...[color=blue]
> Thanks for responding Alan.
> I'm not certain if I understood your reply correctly, but if I did I think
> there's a bit of confusion. If I didn't there's a bit of confusion too.
>
> First, why separate the data into tables - lots of people to keep track
> of, I'm just trying to save the space of multiple names.
>
> Second: "show me all the rows in these tables where
> the person id is the same."
>
> The Per. Name & Stat. is the only table with a Person ID# field.
> All of the other tables consist of just 2 fields, the data (names) and an
> autonumber Primary key.
>
> Perhaps I was not clear in describing that earlier. Surely I don't have
> to put a Person ID field in each of the other tables, do I? One field
> wouldn't be enough because it's a one to many relationship (Name to Person
> IDs).
>
> -----
>
> pietlinden, thank you too for responding. I think I can guess at the
> reasoning behind your question. If you are wondering if this is all that
> I am going to do with the mdb, then you would be right to think that I am
> making it too difficult for no reason. However, this is only an example
> of a problem that will occur every time I add another function to what
> will be a major database. I intend it to track members, contacts, last
> contact, by who, classes, attendance, addresses, skills, relations,
> financial records, etc. All to be accessed by many different users with
> varying levels of access.
> Too ambitious for a beginner? Yeah. But I used to do this stuff, it's
> just been 5 or 6 years since I even looked at it and I've forgotten how to
> do basic SQL. Given a week or two however, and I'll be back up to speed.[/color]

re: New Guy with (likely) Simple Question

Thanks Alan, you cleared up the problem. It was a many-to-many issue,
and I had not recognized it as such. You are also most likely right on
the whole record format issue. It probably is not worth the effort to
separate the names tables - an example of too much effort for too little
return. I always have had difficulty recognizing that diminishing
returns wall when I've hit it. At least now I will remember to keep an
eye open for m2m relationships when I run into a snag.

Thanks again, and thanks all for the help!


mkdunaway