Given up - need some assistance

[Rnykster]

I know a little about Access and have made several single table
databases. Been struggling for about a month to do a multiple table
database with no success. Help!

There are two tables. First has about 30 fields. Every entry in this
table will be unique. Second table has about 7 fields and is for
reference - strictly a look up type table. I want to use one field,
say FAMILY in the first table to look up any one of the 400 items in
the second table. Based on that one match, I want the form to
automatically show the other 6 fields that coincide with that record
in the second table. Does that make sense?

This is all in one form. I want to be able to type in the letters in
field FAMILY and have it seek the appropriate match. Based on that
match, I want the other 6 fields to show up (be visible) but not be
editable. I have no problem getting the form to use the second table
to look up the FAMILY field, but cannot for the life of me figure out
how to display the rest of the data associated with that record in the
second table.

Can someone help guide me to get this to work? I've tried using combo
boxes, subforms, adding shared fields to each table, played with
relationships, and nothing works. I managed to get a subform showing
all the table two data in the form, but I couldn't get it to relate to
the field in the first table.

To simplfy, say I have two tables. One and Two. First table has five
fields, A,B,C,D,E. Second table has some fields, A,D,M,N,O. These are
all text fields except "A". A would be the autonumber primary field of
each table which has nothing in common. The common field is "D". I
want a combo box in the single form database to look up the value of
"D" in the second table. Based on the chosen value for that record, I
want text fields to show the corresponding values of M, N, and O. How
can this be done?

Thanks.

[Steve]

A table with 30 fields throws up a bright red flag. 99.99% chance it is
incorrectly designed. I recommend you post your table for the people here to
look at and make suggestions. You need to make sure you are starting out on
the right foot with your database.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdata sheet.com


"Rnykster" <Rn******@yahoo .comwrote in message
news:11******** **************@ k79g2000hse.goo glegroups.com.. .

>I know a little about Access and have made several single table
databases. Been struggling for about a month to do a multiple table
database with no success. Help!

There are two tables. First has about 30 fields. Every entry in this
table will be unique. Second table has about 7 fields and is for
reference - strictly a look up type table. I want to use one field,
say FAMILY in the first table to look up any one of the 400 items in
the second table. Based on that one match, I want the form to
automatically show the other 6 fields that coincide with that record
in the second table. Does that make sense?

This is all in one form. I want to be able to type in the letters in
field FAMILY and have it seek the appropriate match. Based on that
match, I want the other 6 fields to show up (be visible) but not be
editable. I have no problem getting the form to use the second table
to look up the FAMILY field, but cannot for the life of me figure out
how to display the rest of the data associated with that record in the
second table.

Can someone help guide me to get this to work? I've tried using combo
boxes, subforms, adding shared fields to each table, played with
relationships, and nothing works. I managed to get a subform showing
all the table two data in the form, but I couldn't get it to relate to
the field in the first table.

To simplfy, say I have two tables. One and Two. First table has five
fields, A,B,C,D,E. Second table has some fields, A,D,M,N,O. These are
all text fields except "A". A would be the autonumber primary field of
each table which has nothing in common. The common field is "D". I
want a combo box in the single form database to look up the value of
"D" in the second table. Based on the chosen value for that record, I
want text fields to show the corresponding values of M, N, and O. How
can this be done?

Thanks.

[Tom van Stiphout]

On Wed, 20 Jun 2007 16:41:21 -0700, Rnykster <Rn******@yahoo .com>
wrote:

This smells like bad database design. The more typical scenario is
called one-to-many where one record in tblOne has many records in
tblTwo, linked by tblOne.PrimaryK ey = tblTwo.ForeignK ey.

If you don't get the db design right, the rest of your app will suffer
tremendously. Why not hire an expert for a few hours or days to get
the foundation right, then you can take it from there. "Microsoft
Solution Provider" in your Yellow Pages may be a place to start.

-Tom.

<clip>

>To simplfy, say I have two tables. One and Two. First table has five
fields, A,B,C,D,E. Second table has some fields, A,D,M,N,O. These are
all text fields except "A". A would be the autonumber primary field of
each table which has nothing in common. The common field is "D". I
want a combo box in the single form database to look up the value of
"D" in the second table. Based on the chosen value for that record, I
want text fields to show the corresponding values of M, N, and O. How
can this be done?

Thanks.

[Neil]

There are two tables. First has about 30 fields. Every entry in this

table will be unique. Second table has about 7 fields and is for
reference - strictly a look up type table. I want to use one field,
say FAMILY in the first table to look up any one of the 400 items in
the second table. Based on that one match, I want the form to
automatically show the other 6 fields that coincide with that record
in the second table. Does that make sense?

This is all in one form. I want to be able to type in the letters in
field FAMILY and have it seek the appropriate match. Based on that
match, I want the other 6 fields to show up (be visible) but not be
editable. I have no problem getting the form to use the second table
to look up the FAMILY field, but cannot for the life of me figure out
how to display the rest of the data associated with that record in the
second table.

Can someone help guide me to get this to work? I've tried using combo
boxes, subforms, adding shared fields to each table, played with
relationships, and nothing works. I managed to get a subform showing
all the table two data in the form, but I couldn't get it to relate to
the field in the first table.

To simplfy, say I have two tables. One and Two. First table has five
fields, A,B,C,D,E. Second table has some fields, A,D,M,N,O. These are
all text fields except "A". A would be the autonumber primary field of
each table which has nothing in common. The common field is "D". I
want a combo box in the single form database to look up the value of
"D" in the second table. Based on the chosen value for that record, I
want text fields to show the corresponding values of M, N, and O. How
can this be done?

OK, your mistake here is that you're using a non-primary key field in both
table to relate the two tables. You need to use the primary key field in the
second table (lookup table) to relate to a non-primary key field in the
first table. That non-primary key field in the first table that relates to
the primary key field in the second table is called a "foreign key field" in
the first table.

So, using your above field, field "D" in the first table ("Table1") would
relate to field "A" in the second table ("Table2"). Keep field "A" in the
second table as an autonumber primary key field in that table. But change
field "D" in the first table to be a Number of type Long Integer. Make sure
it has no default value (instead of the "0" that Access puts there by
default).

Now place both tables in your form's underlying query, joining them on
Table1.D = Table2.A. Make sure to make it an outer join from Table1 to
Table2. That way, if Table1 has a Null value in field "D" (user hasn't
entered a value yet), you'll still be able to display records from Table1

Now add the fields from Table1 that you want to use (including field D) to
your query, as well as the six other fields from Table2 you want to use (but
don't add the primary key field, A, since that will be the same as the one
the users are using for lookup).

After all those fields are in your underlying query, simply add them to your
form. Make the controls based on the 6 fields from the second table
read-only by setting their Locked property to True or their Enabled property
to False.

In the combo box based on Table1, field D, use Table2 as its RowSource,
making sure that field A from Table2 corresponds to the bound column of the
combo box. You can make the combo box be as many columns as you'd like, as
long as Field A corresponds to the bound column.

Now use your form. When the user selects a value for field D from the combo
box, the corresponding values from Table2 will "kick in" and the data in the
6 fields will be displayed.

Neil

>
Thanks.

[Rnykster]

Now use your form. When the user selects a value for field D from the combo

box, the corresponding values from Table2 will "kick in" and the data in the
6 fields will be displayed.

Neil

Thanks for the reply Neil. I made the changes you recommended. My main
form is not based on any query. Did make a query and tied the two
tables together. Everything looked good in the query. Got the main
field "D" working in the form using a combo box. Still can't get the
remaining table 2 fields to display though. Even tried creating a
brand new from based on the query. Can't get it working there either.
I ask a local company they or anyone else is available for hire so I
can sit down with them to fix the problem. No reply. I'll try
tinkering with more options. There has to be a way to have Access to
do a simple if this equals that, display this routine. Then again, I
remember creating a database a few years ago in Access (No, I'm not
brand new to Access), and wanting a field to keep track of the date of
the latest change of each record. In Lotus, it was easily accomplished
with available options - a quick drop and drag. I had to do much
research - a month wasted and eventually detailed help came from the
newsgroup. I had to do lenghty visual basic coding to make it work. I
keep thinking a two table database with lookups for the second table
should be easy...

[Neil]

"Rnykster" <Rn******@yahoo .comwrote in message
news:11******** **************@ x35g2000prf.goo glegroups.com.. .

>Now use your form. When the user selects a value for field D from the
combo
box, the corresponding values from Table2 will "kick in" and the data in
the
6 fields will be displayed.

Neil

Thanks for the reply Neil. I made the changes you recommended. My main
form is not based on any query. Did make a query and tied the two
tables together.

Well, whether you use a saved query or you create an ad-doc query through
the form's Recordsource property, your form is still based on a query.

Everything looked good in the query. Got the main
field "D" working in the form using a combo box. Still can't get the
remaining table 2 fields to display though. Even tried creating a
brand new from based on the query. Can't get it working there either.

Well, you need to make sure you followed all the steps I gave you. Are your
primary keys set up correctly? Is the bound column in the combo box the same
as the primary key for Table2? Did you follow all the other steps exactly?
If you follow all the steps exactly, it should work. So you need to go back
over the note I sent and double check that everything is done as I wrote
you.

Also, you can go into the underlying query (through the form's Recordsource
property), and then open the query in Datasheet view. From there, either
create a new record for Table1 (which you can later delete), or go to an
existing record for Table1 which has no value for Field D. Enter a value for
Field D and move to the next field. Do the corresponding records from Table2
appear? If not, then there's something wrong with your underlying tables or
query; if they do, then there's something wrong with your form or combo box.

If I had to guess, I'd say that you're not using the primary key value for
Table2 in Field D of Table 1. But that would just be a guess. Could be
something else as well. You need to make sure that you follow all steps
exactly and double-check your work.

Neil

[Rnykster]

If I had to guess, I'd say that you're not using the primary key value for

Table2 in Field D of Table 1. But that would just be a guess. Could be
something else as well. You need to make sure that you follow all steps
exactly and double-check your work.
Neil

Success! I had everything set up right. Trouble was I kept trying
to use combo boxes or text boxes and changing the properties of table
fields to fix the problem. The fields I expected to see data in, had
Name# in them. The trouble was in the form properties. It wasn't
using the right source. Once I based the form source on the query of
both tables, everything worked just fine. Thanks for your help Neil.

[Neil]

Sure, no problem. Glad it worked out for you.

Neil

"Rnykster" <Rn******@yahoo .comwrote in message
news:11******** *************@q 69g2000hsb.goog legroups.com...

>If I had to guess, I'd say that you're not using the primary key value
for
Table2 in Field D of Table 1. But that would just be a guess. Could be
something else as well. You need to make sure that you follow all steps
exactly and double-check your work.
Neil

Success! I had everything set up right. Trouble was I kept trying
to use combo boxes or text boxes and changing the properties of table
fields to fix the problem. The fields I expected to see data in, had
Name# in them. The trouble was in the form properties. It wasn't
using the right source. Once I based the form source on the query of
both tables, everything worked just fine. Thanks for your help Neil.