Query help.

[Jimmy]

I need a little help writing a combo box query.

On my form is a combobox which is to draw its records from two tables. In
the primary table, TableA there are two columns, tblAID & tblAName. In the
second table, TableB there are multiple records for some of the tblAID
records, plus there are many TableA records that have no records in TableB.

The two tables are joined by tblAID in a one to many relationship (as
below):

TableA TableB
tblAID-(1)------| tblBID
tblAName |---(Many)-tblAID
tblBCost

What I want is for the combo box to show every record in TableB as:
tblAID, tblAName, tblBCost

And every other record in TableA as:
tblAID, tblAName, (tblBCost would be null)

I can write the query to get all of the records from TableA or TableB, but
whenever I put both tables in the same query I get only the records from
TableB.

[Rick Brandt]

Re: Query help.

Jimmy wrote:

I need a little help writing a combo box query.
>
On my form is a combobox which is to draw its records from two
tables. In the primary table, TableA there are two columns, tblAID &
tblAName. In the second table, TableB there are multiple records for
some of the tblAID records, plus there are many TableA records that
have no records in TableB.
The two tables are joined by tblAID in a one to many relationship (as
below):
>
TableA TableB
tblAID-(1)------| tblBID
tblAName |---(Many)-tblAID
tblBCost
>
What I want is for the combo box to show every record in TableB as:
tblAID, tblAName, tblBCost
>
And every other record in TableA as:
tblAID, tblAName, (tblBCost would be null)
>
I can write the query to get all of the records from TableA or
TableB, but whenever I put both tables in the same query I get only
the records from TableB.

If you double-click the join line and choose "show all records in TableA and
matching records in TableB" then you shoud get what you want PROVIDING that you
don't apply criteria to any fields in TableB. That effectively transforms the
join back to an inner join. To avoid that you have to add "Or is null" to all
criteria applied to fields from TableB.


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[tina]

Re: Query help.

sounds like the tables in the query are linked with an INNER JOIN. open the
query in Design view. on the menu bar, click View | SQL View. in the SQL
pane that opens, look for the words INNER JOIN, and change the word INNER to
LEFT.

hth


"Jimmy" <dont@email.mew rote in message
news:65xqh.4430 5$873.35794@fe0 6.news.easynews .com...

I need a little help writing a combo box query.
>
On my form is a combobox which is to draw its records from two tables. In
the primary table, TableA there are two columns, tblAID & tblAName. In the
second table, TableB there are multiple records for some of the tblAID
records, plus there are many TableA records that have no records in

TableB.

>
The two tables are joined by tblAID in a one to many relationship (as
below):
>
TableA TableB
tblAID-(1)------| tblBID
tblAName |---(Many)-tblAID
tblBCost
>
What I want is for the combo box to show every record in TableB as:
tblAID, tblAName, tblBCost
>
And every other record in TableA as:
tblAID, tblAName, (tblBCost would be null)
>
I can write the query to get all of the records from TableA or TableB, but
whenever I put both tables in the same query I get only the records from
TableB.
>
>

[Jimmy]

Re: Query help.


"Rick Brandt" <rickbrandt2@ho tmail.comwrote in message
news:5oxqh.1269 $O02.1160@newss vr11.news.prodi gy.net...

If you double-click the join line and choose "show all records in TableA
and matching records in TableB" then you shoud get what you want PROVIDING
that you don't apply criteria to any fields in TableB. That effectively
transforms the join back to an inner join. To avoid that you have to add
"Or is null" to all criteria applied to fields from TableB.
>

Yep, this did the trick, but with one cavaet (my fault by ommision!) There
is another field in TableB, tblBSizeID which is in reality a lookup to
another table tblSizes. As it is right now, when I include tblSizeID in the
query, the results give me the primary key column for tblSizes since that is
how the tables are joined. I simply added tblSizes to the query so that the
results could display the text associated with tblSizeID but access refuses,
saying that the sql statement contains ambiguous outer joins.

[Rick Brandt]

Re: Query help.

Jimmy wrote:

"Rick Brandt" <rickbrandt2@ho tmail.comwrote in message
news:5oxqh.1269 $O02.1160@newss vr11.news.prodi gy.net...

If you double-click the join line and choose "show all records in
TableA and matching records in TableB" then you shoud get what you
want PROVIDING that you don't apply criteria to any fields in
TableB. That effectively transforms the join back to an inner
join. To avoid that you have to add "Or is null" to all criteria
applied to fields from TableB.

Yep, this did the trick, but with one cavaet (my fault by ommision!)
There is another field in TableB, tblBSizeID which is in reality a
lookup to another table tblSizes. As it is right now, when I include
tblSizeID in the query, the results give me the primary key column
for tblSizes since that is how the tables are joined. I simply added
tblSizes to the query so that the results could display the text
associated with tblSizeID but access refuses, saying that the sql
statement contains ambiguous outer joins.

Just change the join type going to the third table.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com