query problem

  • Thread starter Thread starter lmcc007
  • Start date Start date
L

lmcc007

I have a tblContacts table with three fields pulling from a lookup table
called tlkpFirstNames. (I did this because many of the names are the same
and for accuracy in spelling.)

Fields:
FirstNameID will get name from tlkpFirstNames
MiddleNameID will get name from tlkpFirstNames
NickNameID will get name from tlkpFirstNames

Relationship:
FirstNameID = tlkpFirstNames
MiddleNameID = tlkpFirstNames_1
NickNameID = tlkpFirstNames_2

I am trying to create a query that will concatenate the fields into one
called FullName (First, NickName, and Middle…).

All I get is the numbers (the bound column information). I tried entering
[FirstNameID].[Column](1) but it’s not working.

How do I create a query pulling out this information?
 
Thanks, this helps me a lot! So, don't do the names fields this way--do them
as text fiellds and don't worry about the errors and repeated data?

MGFoster said:
lmcc007 said:
I have a tblContacts table with three fields pulling from a lookup table
called tlkpFirstNames. (I did this because many of the names are the same
and for accuracy in spelling.)

Fields:
FirstNameID will get name from tlkpFirstNames
MiddleNameID will get name from tlkpFirstNames
NickNameID will get name from tlkpFirstNames

Relationship:
FirstNameID = tlkpFirstNames
MiddleNameID = tlkpFirstNames_1
NickNameID = tlkpFirstNames_2

I am trying to create a query that will concatenate the fields into one
called FullName (First, NickName, and Middle…).

All I get is the numbers (the bound column information). I tried entering
[FirstNameID].[Column](1) but it’s not working.

How do I create a query pulling out this information?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I wouldn't do it that way, but....

You don't show the structure of the 2 tables - I'll assume something
like this (logically there shud be 2 "name" tables):

tlkpFirstNames - Middle name uses this table
FirstNameID - integer
FirstName - text

tlkpLastNames
LastNameID - integer
LastName - text

tblContacts
FirstNameID - integer links to tlkpFirstNames
MiddleNameID - integer links to tlkpFirstNames
LastNameID - integer links to tlkpLastNames
... etc. ...

To get the Names you'd have a query like this:

SELECT FN.FirstName,
DLookup("FirstName", "tlkpFirstNames", "FirstNameID=" &
C.MiddleNameID) As MiddleName, LN.LastName
FROM (tblContacts As C INNER JOIN tlkpFirstNames AS FN
ON C.FirstNameID = FN.FirstNameID) INNER JOIN tlkpLastNames AS LN
ON C.LastNameID = LN.LastNameID
WHERE ... any criteria ...

All this would be a lot easier if you just allowed the user to enter the
name as Text into the tblContacts. There'll probably be errors in the
look-up table name entries - especially the Last Name since there are so
many last names and various spellings.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSqM9XIechKqOuFEgEQI8lgCdHLN4GIqfMNd1e3eaYfKzVuJ0yswAoJcf
F4X/8S7YUR31DyUwAop86phK
=5sgj
-----END PGP SIGNATURE-----
Click to expand...
 
Isn't that extra work. I have accumulated a list of names.

The reason I was doing it that way because I was using a store bought
contact mgmt program but I had a lot of typos. I would search for name and
couldn't find it because I misspelled it and end up entering the information
twice and on some occasions three or more. And if I cold-call, I would get
that occasional "didn't you just call me and so on."

Basically, I need a clean database without errors and duplicates. Believe
it or not people hate it when you misspell their name.

Maybe I am making it difficult on myself so let me think about it some more.


MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Using Text name columns (fields) in Contacts tables is the "usual"
practice, since there are so many possibilities for name spellings. The
duplicates and errors can be lessened or eliminated by user training. I
usually have the users look up the name before entering a new one -
that's training. Most databases programs have some sort of search
capability. You can set up one for names - usually last name, first
name searches. Example:

Search for
Last Name: Jones

Output shows all Joneses in db in alphabetical order. Add some other
details to the output, like addresses or company names, and the user has
an easier job of selecting the correct name from the output, or
determining that they need to enter a new name. Output can be reduced
by adding the first name's first initial:

Search for
Last Name: Jones First Name: F*

Teach users how to use wild cards in their searches. I usually have a
small explanation in a label on the search form.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSqa+b4echKqOuFEgEQIgSgCfVB6Y5tbT89b/GPlxh0Eq0WcCxSwAn3ZD
L/tPr5B8pyA94fVUMrXzhHov
=X/en
-----END PGP SIGNATURE-----
Thanks, this helps me a lot! So, don't do the names fields this way--do them
as text fiellds and don't worry about the errors and repeated data?

MGFoster said:
lmcc007 wrote:
I have a tblContacts table with three fields pulling from a lookup table
called tlkpFirstNames. (I did this because many of the names are the same
and for accuracy in spelling.)

Fields:
FirstNameID will get name from tlkpFirstNames
MiddleNameID will get name from tlkpFirstNames
NickNameID will get name from tlkpFirstNames

Relationship:
FirstNameID = tlkpFirstNames
MiddleNameID = tlkpFirstNames_1
NickNameID = tlkpFirstNames_2

I am trying to create a query that will concatenate the fields into one
called FullName (First, NickName, and Middle…).

All I get is the numbers (the bound column information). I tried entering
[FirstNameID].[Column](1) but it’s not working.

How do I create a query pulling out this information?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I wouldn't do it that way, but....

You don't show the structure of the 2 tables - I'll assume something
like this (logically there shud be 2 "name" tables):

tlkpFirstNames - Middle name uses this table
FirstNameID - integer
FirstName - text

tlkpLastNames
LastNameID - integer
LastName - text

tblContacts
FirstNameID - integer links to tlkpFirstNames
MiddleNameID - integer links to tlkpFirstNames
LastNameID - integer links to tlkpLastNames
... etc. ...

To get the Names you'd have a query like this:

SELECT FN.FirstName,
DLookup("FirstName", "tlkpFirstNames", "FirstNameID=" &
C.MiddleNameID) As MiddleName, LN.LastName
FROM (tblContacts As C INNER JOIN tlkpFirstNames AS FN
ON C.FirstNameID = FN.FirstNameID) INNER JOIN tlkpLastNames AS LN
ON C.LastNameID = LN.LastNameID
WHERE ... any criteria ...

All this would be a lot easier if you just allowed the user to enter the
name as Text into the tblContacts. There'll probably be errors in the
look-up table name entries - especially the Last Name since there are so
many last names and various spellings.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSqM9XIechKqOuFEgEQI8lgCdHLN4GIqfMNd1e3eaYfKzVuJ0yswAoJcf
F4X/8S7YUR31DyUwAop86phK
=5sgj
-----END PGP SIGNATURE-----
Click to expand...
Click to expand...
Click to expand...
 
Back
Top