Select Query - Adding New record

[Paul]

Hi All,
I have two tables and created a select query from that.

Table 1:
ID(primary)
Name (could be repeated)
Hobby

Table2:
ID
Name (primary)
PhoneNumber
Email

I combined the two tables so that If I enter a name that exist in table two,
phone number and email automatically fill in( that works just fine). But If
I enter a new name, and try to fill in the phone number and email from the
query, it doesn't do anything. Can this be done? Am I doing something
wrong? Thanks in advance for you help.

 

[John W. Vinson]

Hi All,
I have two tables and created a select query from that.

Table 1:
ID(primary)
Name (could be repeated)
Hobby

Table2:
ID
Name (primary)
PhoneNumber
Email

I combined the two tables so that If I enter a name that exist in table two,
phone number and email automatically fill in( that works just fine). But If
I enter a new name, and try to fill in the phone number and email from the
query, it doesn't do anything. Can this be done? Am I doing something
wrong? Thanks in advance for you help.

Yes, you're doing something wrong.

The name should not be *IN* table2 at all, much less primary! This will let
you enter one and only one phone number and/or email record for all of the Jim
Smith's in your database.

I'd suggest that since one person may have multiple phone numbers, and may
have multiple email addresses (unrelated to the phone numbers), that you
instead use something like:

People
PersonID <autonumber or other unique field, primary key>
LastName
FirstName <it's easier to search and sort two fields and you can always
combine them in a query>
<other biographical data as needed>

Phones
PersionID <link to People>
Phone
PhoneType <home, business, pager, cell, ...>

EMails
PersonID <link to People>
EMail

Use a Form based on People, with two subforms based on Phones and EMails for
your data entry. DON'T use table datasheets for much of anything except
debugging.

John W. Vinson [MVP]

 

[Paul]

Thanks John,
You have open up my understanding of Access to a whole new depth. I changed
a lot into subforms now. But for name of a location and it's address?
Should I use subform for this also? Because if i used subform for this, it
takes away more space on the form(even if I turned off the scrollbars and
everything; and the scroll on my mouse work only within subforms).

But if I used a query - two tables (main & address)

tblMain:
LocationID (in Query, linked with tblAddress.LocationID, but include
everything from tblMain)
Name
Job (location & address can be repeated for different people)

tblAddress:
LocationID(primary, because only one address per location)
Location
Address


qryMain
Name (tblMain)
Job(tblMain)
Location(tblAddress)
Address(tblAddress)

and the from datasource is the query; and my query includes other tables as
well.
so on my form, i have a combo box for location. when i select a location,
the address doesn't change with the location, instead the location change,
so now two locations would have one address.

Please help. I tried several methods already - in terms of querying.
Thanks. Sorry for the long email.

 

[John W. Vinson]

. But for name of a location and it's address?

If there's only one address per location, then just include the fields in the
same table. You don't need or want a subform in that case.

John W. Vinson [MVP]