Design Problem -- only get partial results on multiple join

[Evi]

Have a look down this list Giraffe and see if they are all true

Your main form needs to be based on TblAgreement. (or a query based only on
that table)
Your Subform needs to be based on TblAgreementWith (or its query) and
linked by AgreementId
Add all the fields from TblAgreementWith for now to this subform, you can
always Hide unwanted columns using the Format menu.
Your combo needs to be based on TblOrg (or its query) and should have from
TblOrg, OrgID, Other fields from TblOrg. In Properties on the Data tab, its
ControlSource should be OrgID.
Next to Bound Column, it should say 1.

Evi

Hi Evi:

Thank you. I've very excited that this may actually work. So I go in to my
form to add this new subform and for each existing record the right agreement
# shows up with the right organization (Yes!). Then I go to add a new
record, and I am unable to choose an organization in my new subform. My
column headings show up (agreementno and organization), however, no drop down
options for either column, so I am unable to choose the organization that
goes with my new agreement.

I checked the properties and the Master and Child Links are okay
(AgreementID). What else should I check?

Thanks Evi, I couldn't have made it this far without you.

G

Hi G, That sounds really good.

Evi

Hi Evi:

Boy, to have you looking over my shoulder would sure have made this sooo
much easier to solve. Sorry for the long thread ....

Okay, here's what I have:

My tables are setup exactly as you describe, with the exception of in my
TblAgreementWith I also have an AgreementWithID as PK; however, I

don't
THINK

that'll make a difference .... right?

I THINK I created the datasheet form correctly. Here's what I have:

Data based on TblAgreementWith
AgreementWithID
AgreementID
OrgID
Combobox: TblAgreement.AgreementID (2nd field chosen AgreementNo)
Combobox: TblOrg.OrgID (2nd field, OrgLine1; 3rd field, OrgLine2; 4th
field, POC)

In View mode of this datasheet I have

AgreementWithID, AgreementID, OrgID, AgreementNo, OrgLine 1 (if I can get
this fixed, I will be combining several fields in one for my Orgs)

If I go to either AgreementNo or OrgLine1, I get a dropdown option and

I
can

actually change either one on a record.

And, guess what Evi? ALL my Org show up ... even the tests I setup as we've
been working through this. This is a FIRST. I'm very excited.

Please tell me I have it right so far.

G

:

I have this horrible feeling that we are both misunderstanding something. Oh
for a crystal ball so I could see what is happening!
Let's check that we've got the structure right. (the thread is

getting
long

and my brain is short!)
TblAgreement should contain each Agreement only once PK AgreementID
TblOrg should have each org only once - PK OrgID
TblAgreement With should have the foreign key fields AgreementID and OrgID

The structure of your Form should be like this (basically)
Give this a try and you'll see what I mean.

Create a datasheet form based on the AgreementWith table. Don't add fields
from the other tables just yet until you get a real feel for this

sort
of

structure.
Add a combo to it in Design View. It should be based on TblAgreement

(or
on

a query that is based directly on that table - don't add fields from the
other tables for now)
It's first field should be AgreementID, pull the column closed in the
Wizard, its next field (the one which you will actually see in the combo)
can be any comination of fields in TblAgreement which will help you to
identify that Agreement. When you get to the relevant page in the Wizard,
agree to store the value in AgreementID

Similarly, add a combo based on TblOrg or a query based only on that table-
again make the first field OrgID, hide it and then add the OrgName

use the combos to fill in the 2 Foreign Key fields in TblAgreementWith.

Is this bit working, G?

Evi


Hi Evi:

I'm obviously missing something. First, I chose not to concatenate my
fields, I wanted to see if I could get the principle of this to work
first.

I created the query [qOrg] putting 2 tables in the query [tOrg] &
[tOrgWith], figuring I needed the tOrgWith table because it's a
conjunction
table for tAgreements & tOrg, even tho I didn't use any fields

from
it.

From
tOrg I chose OrgID, Org, POC.

I made my combo box in my fAgreements, not as a subform, but as a combo
box.
I chose to use my above query, chose my 3 fields, related them by OrgID,
chose to remember the value for a later use, went in to the Column Widths
of
the Properties box and made my first column (my OrgID) 0". So now only my
Org shows up (don't know why POC's not showing up). However, the Org
that's
showing up is not the correct Org for the Agreement (on none of the
agreements). Do I need to rechoose each Org for each agreement?
Secondly,
just to test this, I went in to my Org table and added a new Org, when
back
to my form and it does not show up . And lastly, some agreements have
more
then one Org, how can I do this in the combo box?

What am I missing?

Thanks for your help Evi.

G




:

You 're using the wrong OrgID for your combo. You need the OrgID from
TblOrg
in your combo - not the OrgID from TblAgreementWith.
Don't use any fields from TblAgreementWith if you are trying to put
OrgID
into TblAgreementWith using your combo. That's like trying to scratch
your
left elbow with your left elbow!

If OrgID - your Primary Field key -is Unique (as it must be, if

it
is a

Primary Key) then having each OrgName mentioned twice can be overcome
until
you have time to redesign your db. All you need you need to do is to
create
a query for your combo.
If your Org Table has OrgID, OrgName and POC then add OrgID to your
query
first.

In the next column type
OrgFOC: [Org] &" " & [POC]

You can concatenate as many fields as you need here to give each record
a
unique name in your combo. A handy tip I learnt on the Report newsgroup
is
to link your fields using

Trim(([Org] + " ") & ([POC] + " ") & ([AnotherField]))


If any of the fields are null then + inside the () means that

it
won't

show
anything, including the space.
Use the Trim to get rid of a final space if POC has a value but
AnotherField
doesn't.



When you make the combo, add OrgID and then your concatenated

field
but

close the OrgID column by dragging it closed in the Wizard so

that
it

won't
show in your combo.

Will this help?

If your Org table does not have a Unique Primary Key field

(OrgID)
then

Access can't possibly know which of the two OrgIDs to link to your
AgreementWith table.
It can be rectified but I won't start on that (long) process until I
know it
is needed.
I've had to do it a quite a few times, when I got a design wrong


Evi



Hi Evi:

I don't think you're missing anything important ... I think I've
missed
something pretty critical somewhere.

My combo box is based on the Org Table & OrgID is the Foreign

Key
to

Org
ID
in the Agree Table. After reading your reply, I'm beginning

to
think

I
have
a bigger problem then I started this inquiry with (argh!).

Evi, thanks to your help, I can see that my original design of this DB
was
flawed, and I can't thank you enough for helping me. You've

been
very

gracious with your time and advice -- I hope will you bear

with me
a

little
more.

For starters, part of the multiple listings in Org Table is me ....
when I
originally created this DB 4 years ago, I didn't realize

things
would

blossom
in to what this has become (nor did I have the knowledge on

DBs I
have

today). So, for my Org Table, if I had the same Org, however, a
different
field office or point-of-contact, I listed the Org twice, with

new
new

field
office or POC. That's PART of the problem. I know how to

link an
Org

table
to an OrgAdd table, however, for this situation, won't that require a
combo
box within a combo box -- can you do that?

The other part of the problem, the same Org if it has more

then
one

agreement, I do not know. So I'd like to start with what my

query
for

the
combo box looks like:

tOrgWith (this is my conjuction table between Org and Agree)
OrgWithID [PK]
AgreeID
OrgID

tOrg
OrgID [PK] [FK to tOrgWith.OrgWithID]
OrgLine1
OrgLine2

I use
tOrg.Org ASC (with an IIf statement joining OrgLine1 & OrgLine2)
tOrg.POC
tOrgWith.OrgWithID
tOrgWith.AgreeID
tOrgWith.OrgID

For this query, if I do not use the the 3 fields in the

OrgWith
Table,

nothing will show up in my combo box.

Secondly, my combo box is built in to a separate subform from the
Agree
Table. The subform contains:

tOrgWith.OrgWithID
tOrgWith.AgreeID
tOrgWith.OrgID
OrgWith combo [based off of above query]

I added the combo box to get my Org name to show up, because I
wouldn't
know
what ID # which Org was.

So that's the intricacies of how all this works (or doesn't work).

Once gain, Evi, I thank you for all your help.

 

[GIraffe]

Hi Evi:

For starters, I am **extremely** thankful for your patience and help and I'm
sorry if I've been trying. I've not worked with conjuction tables before,
so this is all very new.

I can answer True to each one of your statements. And this morning, after I
realized I had all true answers to your questions, I just about threw the
towel in...thinking I was not going to be able to get this to work.

After going over our last exchanges and seeing that everything you have
given me was correct, I decided I had nothing to lose and re-created the form
from scratch. I added the new subform we did and, I'm not sure why, but it
works. I then added my amendments subform and it still works. I tested this
by adding a new agreement and it works, and finally, I added a new org and it
*still* works.

I'm not sure if something got blitzed in my previous form and trying to
bring in new subforms was just confusing the process or what. All I know is
it works now.

A humble and sincere thanks to you, Evi. This is the most difficult
challenge I've had yet on Access and without your help, I never would have
made it through this.

And only because I don't know why it's working now and (I admit I'm
wondering if it's a fluke), I've provided my subform structure below to see
if anything jumps out at you that's going to come back to bite me.

Here's my subform structure [for the record, I am not using any queries for
this subform]:

Data based on TblAgreementWith [this is my conjuction table]
AgreementWithID [PK]
AgreementID [PK from TblAgreement]
OrgID [PK TblOrg]
Combobox: TblOrg.OrgID (2nd field, OrgLine1)
In properties on the data tab for the combo box, I have:
ControlSource: OrgID
RowSource: Select TblOrg.OrgID; TblOrg.OrgLine1 FROM TblOrg ORDER BY
[OrgLine1];
BoundColumn: 1

In properties on the data tab for the subform, I have:
ControlSource: TblAgreementWith

In View mode of this datasheet I have
AgreementWithID, AgreementID, OrgID, AgreementNo, OrgLine 1

Sincerely,
G

Have a look down this list Giraffe and see if they are all true

Your main form needs to be based on TblAgreement. (or a query based only on
that table)
Your Subform needs to be based on TblAgreementWith (or its query) and
linked by AgreementId
Add all the fields from TblAgreementWith for now to this subform, you can
always Hide unwanted columns using the Format menu.
Your combo needs to be based on TblOrg (or its query) and should have from
TblOrg, OrgID, Other fields from TblOrg. In Properties on the Data tab, its
ControlSource should be OrgID.
Next to Bound Column, it should say 1.

Evi

Hi Evi:

Thank you. I've very excited that this may actually work. So I go in to my
form to add this new subform and for each existing record the right agreement
# shows up with the right organization (Yes!). Then I go to add a new
record, and I am unable to choose an organization in my new subform. My
column headings show up (agreementno and organization), however, no drop down
options for either column, so I am unable to choose the organization that
goes with my new agreement.

I checked the properties and the Master and Child Links are okay
(AgreementID). What else should I check?

Thanks Evi, I couldn't have made it this far without you.

G

Hi G, That sounds really good.

Evi

Hi Evi:

Boy, to have you looking over my shoulder would sure have made this sooo
much easier to solve. Sorry for the long thread ....

Okay, here's what I have:

My tables are setup exactly as you describe, with the exception of in my
TblAgreementWith I also have an AgreementWithID as PK; however, I don't
THINK
that'll make a difference .... right?

I THINK I created the datasheet form correctly. Here's what I have:

Data based on TblAgreementWith
AgreementWithID
AgreementID
OrgID
Combobox: TblAgreement.AgreementID (2nd field chosen AgreementNo)
Combobox: TblOrg.OrgID (2nd field, OrgLine1; 3rd field, OrgLine2; 4th
field, POC)

In View mode of this datasheet I have

AgreementWithID, AgreementID, OrgID, AgreementNo, OrgLine 1 (if I can get
this fixed, I will be combining several fields in one for my Orgs)

If I go to either AgreementNo or OrgLine1, I get a dropdown option and I
can
actually change either one on a record.

And, guess what Evi? ALL my Org show up ... even the tests I setup as
we've
been working through this. This is a FIRST. I'm very excited.

Please tell me I have it right so far.

G

:

I have this horrible feeling that we are both misunderstanding
something. Oh
for a crystal ball so I could see what is happening!
Let's check that we've got the structure right. (the thread is getting
long
and my brain is short!)
TblAgreement should contain each Agreement only once PK AgreementID
TblOrg should have each org only once - PK OrgID
TblAgreement With should have the foreign key fields AgreementID and
OrgID

The structure of your Form should be like this (basically)
Give this a try and you'll see what I mean.

Create a datasheet form based on the AgreementWith table. Don't add
fields
from the other tables just yet until you get a real feel for this sort
of
structure.
Add a combo to it in Design View. It should be based on TblAgreement (or
on
a query that is based directly on that table - don't add fields from the
other tables for now)
It's first field should be AgreementID, pull the column closed in the
Wizard, its next field (the one which you will actually see in the
combo)
can be any comination of fields in TblAgreement which will help you to
identify that Agreement. When you get to the relevant page in the
Wizard,
agree to store the value in AgreementID

Similarly, add a combo based on TblOrg or a query based only on that
table-
again make the first field OrgID, hide it and then add the OrgName

use the combos to fill in the 2 Foreign Key fields in TblAgreementWith.

Is this bit working, G?

Evi


Hi Evi:

I'm obviously missing something. First, I chose not to concatenate my
fields, I wanted to see if I could get the principle of this to work
first.

I created the query [qOrg] putting 2 tables in the query [tOrg] &
[tOrgWith], figuring I needed the tOrgWith table because it's a
conjunction
table for tAgreements & tOrg, even tho I didn't use any fields from
it.
From
tOrg I chose OrgID, Org, POC.

I made my combo box in my fAgreements, not as a subform, but as a
combo
box.
I chose to use my above query, chose my 3 fields, related them by
OrgID,
chose to remember the value for a later use, went in to the Column
Widths
of
the Properties box and made my first column (my OrgID) 0". So now
only my
Org shows up (don't know why POC's not showing up). However, the Org
that's
showing up is not the correct Org for the Agreement (on none of the
agreements). Do I need to rechoose each Org for each agreement?
Secondly,
just to test this, I went in to my Org table and added a new Org, when
back
to my form and it does not show up . And lastly, some agreements have
more
then one Org, how can I do this in the combo box?

What am I missing?

Thanks for your help Evi.

G




:

You 're using the wrong OrgID for your combo. You need the OrgID
from
TblOrg
in your combo - not the OrgID from TblAgreementWith.
Don't use any fields from TblAgreementWith if you are trying to put
OrgID
into TblAgreementWith using your combo. That's like trying to
scratch
your
left elbow with your left elbow!

If OrgID - your Primary Field key -is Unique (as it must be, if it
is a
Primary Key) then having each OrgName mentioned twice can be
overcome
until
you have time to redesign your db. All you need you need to do is to
create
a query for your combo.
If your Org Table has OrgID, OrgName and POC then add OrgID to your
query
first.

In the next column type
OrgFOC: [Org] &" " & [POC]

You can concatenate as many fields as you need here to give each
record
a
unique name in your combo. A handy tip I learnt on the Report
newsgroup
is
to link your fields using

Trim(([Org] + " ") & ([POC] + " ") & ([AnotherField]))


If any of the fields are null then + inside the () means that it
won't
show
anything, including the space.
Use the Trim to get rid of a final space if POC has a value but
AnotherField
doesn't.



When you make the combo, add OrgID and then your concatenated field
but
close the OrgID column by dragging it closed in the Wizard so that
it
won't
show in your combo.

Will this help?

If your Org table does not have a Unique Primary Key field (OrgID)
then
Access can't possibly know which of the two OrgIDs to link to your
AgreementWith table.
It can be rectified but I won't start on that (long) process until I
know it
is needed.
I've had to do it a quite a few times, when I got a design wrong


Evi



Hi Evi:

I don't think you're missing anything important ... I think I've
missed
something pretty critical somewhere.

My combo box is based on the Org Table & OrgID is the Foreign Key
to
Org
ID
in the Agree Table. After reading your reply, I'm beginning to
think
I
have
a bigger problem then I started this inquiry with (argh!).

 

[Evi]

That looks great,Giraffe. You've really stuck at it!
It is possible that your form was just poorly. I've had that once or twice
when a report or query suddenly acted strangely and unpredictably. It was
nearly always easiest to recreate the thing from scratch after which it
behaved as it should. I guess there are just some things we are not meant to
know.
Evi

Hi Evi:

For starters, I am **extremely** thankful for your patience and help and I'm
sorry if I've been trying. I've not worked with conjuction tables before,
so this is all very new.

I can answer True to each one of your statements. And this morning, after I
realized I had all true answers to your questions, I just about threw the
towel in...thinking I was not going to be able to get this to work.

After going over our last exchanges and seeing that everything you have
given me was correct, I decided I had nothing to lose and re-created the form
from scratch. I added the new subform we did and, I'm not sure why, but it
works. I then added my amendments subform and it still works. I tested this
by adding a new agreement and it works, and finally, I added a new org and it
*still* works.

I'm not sure if something got blitzed in my previous form and trying to
bring in new subforms was just confusing the process or what. All I know is
it works now.

A humble and sincere thanks to you, Evi. This is the most difficult
challenge I've had yet on Access and without your help, I never would have
made it through this.

And only because I don't know why it's working now and (I admit I'm
wondering if it's a fluke), I've provided my subform structure below to see
if anything jumps out at you that's going to come back to bite me.

Here's my subform structure [for the record, I am not using any queries for
this subform]:

Data based on TblAgreementWith [this is my conjuction table]
AgreementWithID [PK]
AgreementID [PK from TblAgreement]
OrgID [PK TblOrg]
Combobox: TblOrg.OrgID (2nd field, OrgLine1)
In properties on the data tab for the combo box, I have:
ControlSource: OrgID
RowSource: Select TblOrg.OrgID; TblOrg.OrgLine1 FROM TblOrg ORDER BY
[OrgLine1];
BoundColumn: 1

In properties on the data tab for the subform, I have:
ControlSource: TblAgreementWith

In View mode of this datasheet I have
AgreementWithID, AgreementID, OrgID, AgreementNo, OrgLine 1

Sincerely,
G

Have a look down this list Giraffe and see if they are all true

Your main form needs to be based on TblAgreement. (or a query based only on
that table)
Your Subform needs to be based on TblAgreementWith (or its query) and
linked by AgreementId
Add all the fields from TblAgreementWith for now to this subform, you can
always Hide unwanted columns using the Format menu.
Your combo needs to be based on TblOrg (or its query) and should have from
TblOrg, OrgID, Other fields from TblOrg. In Properties on the Data tab, its
ControlSource should be OrgID.
Next to Bound Column, it should say 1.

Evi

Hi Evi:

Thank you. I've very excited that this may actually work. So I go in

to
my

form to add this new subform and for each existing record the right agreement
# shows up with the right organization (Yes!). Then I go to add a new
record, and I am unable to choose an organization in my new subform. My
column headings show up (agreementno and organization), however, no

drop
down

options for either column, so I am unable to choose the organization that
goes with my new agreement.

I checked the properties and the Master and Child Links are okay
(AgreementID). What else should I check?

Thanks Evi, I couldn't have made it this far without you.

G

:

Hi G, That sounds really good.

Evi

Hi Evi:

Boy, to have you looking over my shoulder would sure have made

this
sooo

much easier to solve. Sorry for the long thread ....

Okay, here's what I have:

My tables are setup exactly as you describe, with the exception of

in
my

TblAgreementWith I also have an AgreementWithID as PK; however, I don't
THINK
that'll make a difference .... right?

I THINK I created the datasheet form correctly. Here's what I have:

Data based on TblAgreementWith
AgreementWithID
AgreementID
OrgID
Combobox: TblAgreement.AgreementID (2nd field chosen AgreementNo)
Combobox: TblOrg.OrgID (2nd field, OrgLine1; 3rd field, OrgLine2; 4th
field, POC)

In View mode of this datasheet I have

AgreementWithID, AgreementID, OrgID, AgreementNo, OrgLine 1 (if I

can
get

this fixed, I will be combining several fields in one for my Orgs)

If I go to either AgreementNo or OrgLine1, I get a dropdown option

and
I

can
actually change either one on a record.

And, guess what Evi? ALL my Org show up ... even the tests I setup as
we've
been working through this. This is a FIRST. I'm very excited.

Please tell me I have it right so far.

G

:

I have this horrible feeling that we are both misunderstanding
something. Oh
for a crystal ball so I could see what is happening!
Let's check that we've got the structure right. (the thread is getting
long
and my brain is short!)
TblAgreement should contain each Agreement only once PK AgreementID
TblOrg should have each org only once - PK OrgID
TblAgreement With should have the foreign key fields AgreementID and
OrgID

The structure of your Form should be like this (basically)
Give this a try and you'll see what I mean.

Create a datasheet form based on the AgreementWith table. Don't add
fields
from the other tables just yet until you get a real feel for

this
sort

of
structure.
Add a combo to it in Design View. It should be based on

TblAgreement
(or

on
a query that is based directly on that table - don't add fields

from
the

other tables for now)
It's first field should be AgreementID, pull the column closed

in
the

Wizard, its next field (the one which you will actually see in the
combo)
can be any comination of fields in TblAgreement which will help

you
to

identify that Agreement. When you get to the relevant page in the
Wizard,
agree to store the value in AgreementID

Similarly, add a combo based on TblOrg or a query based only on that
table-
again make the first field OrgID, hide it and then add the OrgName

use the combos to fill in the 2 Foreign Key fields in TblAgreementWith.

Is this bit working, G?

Evi


Hi Evi:

I'm obviously missing something. First, I chose not to concatenate my
fields, I wanted to see if I could get the principle of this

to
work

first.

I created the query [qOrg] putting 2 tables in the query [tOrg] &
[tOrgWith], figuring I needed the tOrgWith table because it's a
conjunction
table for tAgreements & tOrg, even tho I didn't use any fields from
it.
From
tOrg I chose OrgID, Org, POC.

I made my combo box in my fAgreements, not as a subform, but as a
combo
box.
I chose to use my above query, chose my 3 fields, related them by
OrgID,
chose to remember the value for a later use, went in to the Column
Widths
of
the Properties box and made my first column (my OrgID) 0". So now
only my
Org shows up (don't know why POC's not showing up). However,

the
Org

that's
showing up is not the correct Org for the Agreement (on none

of
the

agreements). Do I need to rechoose each Org for each agreement?
Secondly,
just to test this, I went in to my Org table and added a new

Org,
when

back
to my form and it does not show up . And lastly, some

agreements
have

more
then one Org, how can I do this in the combo box?

What am I missing?

Thanks for your help Evi.

G




:

You 're using the wrong OrgID for your combo. You need the OrgID
from
TblOrg
in your combo - not the OrgID from TblAgreementWith.
Don't use any fields from TblAgreementWith if you are trying

to
put

OrgID
into TblAgreementWith using your combo. That's like trying to
scratch
your
left elbow with your left elbow!

If OrgID - your Primary Field key -is Unique (as it must be,

if
it

is a
Primary Key) then having each OrgName mentioned twice can be
overcome
until
you have time to redesign your db. All you need you need to

do
is to

create
a query for your combo.
If your Org Table has OrgID, OrgName and POC then add OrgID

to
your

query
first.

In the next column type
OrgFOC: [Org] &" " & [POC]

You can concatenate as many fields as you need here to give each
record
a
unique name in your combo. A handy tip I learnt on the Report
newsgroup
is
to link your fields using

Trim(([Org] + " ") & ([POC] + " ") & ([AnotherField]))


If any of the fields are null then + inside the () means

that
it

won't
show
anything, including the space.
Use the Trim to get rid of a final space if POC has a value but
AnotherField
doesn't.



When you make the combo, add OrgID and then your

concatenated
field

but
close the OrgID column by dragging it closed in the Wizard

so
that

it
won't
show in your combo.

Will this help?

If your Org table does not have a Unique Primary Key field (OrgID)
then
Access can't possibly know which of the two OrgIDs to link

to
your

AgreementWith table.
It can be rectified but I won't start on that (long) process until I
know it
is needed.
I've had to do it a quite a few times, when I got a design

wrong

Evi



Hi Evi:

I don't think you're missing anything important ... I

think
I've

missed
something pretty critical somewhere.

My combo box is based on the Org Table & OrgID is the

Foreign
Key

to
Org
ID
in the Agree Table. After reading your reply, I'm

beginning
to

think
I
have
a bigger problem then I started this inquiry with (argh!).