By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,600 Members | 1,079 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,600 IT Pros & Developers. It's quick & easy.

Query help.

P: n/a
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.
Jan 14 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Jan 14 '07 #2

P: n/a
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" <do**@email.mewrote in message
news:65*******************@fe06.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.


Jan 14 '07 #3

P: n/a

"Rick Brandt" <ri*********@hotmail.comwrote in message
news:5o*****************@newssvr11.news.prodigy.ne t...
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.
Jan 15 '07 #4

P: n/a
Jimmy wrote:
"Rick Brandt" <ri*********@hotmail.comwrote in message
news:5o*****************@newssvr11.news.prodigy.ne t...
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
Jan 15 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.