Column from other table displays default value even with no matching record?

AccessAllowed

New member
Local time
Today, 16:54
Joined
Jun 20, 2011
Messages
6
Hi all,
I'll try to come up with as simple an example as possible to illustrate what's going on.


So let's say I have three tables: First, Second, and Third.


The tables Second and Third each have a column with a default value of "X".


I'm creating a query that selects fields from First table that JOIN with corresponding fields from Second and Third, so that all records from First are represented and only matching records from Second and Thirdare returned. I also select the "X" fields from Second and Third.


So I run the query: and the "X" column from Second only displays "X" for records that have a match in Second (since I mean for these fields to be quick indicators for if the record also appears in the other tables, this is my ideal result). However, the "X" column from Thirddisplays "X" for all rows, regardless of whether the record has a match in Third or not.


I can get more specific if need be, but I really can't tell why the one is displaying according to matching records but the other is always on. The JOINs are set correctly and the properties for the columns all check out. Any ideas?
 
AA,

The default value for the column in the table is really not important
for the query.

Does your query look something like this?

Code:
Select a.PK,
       IIf(IsNull(b.PK), "", "X"),
       IIf(IsNull(c.PK), "", "X")
From  (Table1 as a Left Join Table2 as b On
         a.PK = b.PK) Left Join Table3 On
           a.PK = c.PK)

Wayne
 
^No, (though I wish I could take advantage of IF statements, we're on an archaic system that uses "|" delimitters and Access doesn't seem to like those), it's just selecting the columns (and in my case I'm trying to concatenate them)

The code looks like this:

Code:
SELECT 
"First, " & [Third].[X] & ", " & [Second].[X] 
AS Source, 
[First].* 
 
INTO [Combined Table]
 
FROM ([Third] 
RIGHT JOIN [First]
ON [Third].[Column] = [First].[Column]) 
 
LEFT JOIN [Second] 
ON [First].ID = [Second].[ID]
 
ORDER BY [First].[ID];
 
What exactly are you trying to do? Show us some sample data. You can work with any delimiter. If readers are to help or offer suggestion, they really have to understand what it is you are trying to do.
 
^ The goal is to feature a "Source" column that lists the other table(s) the record appears on. I was going to put in some sample data, but was having trouble recreating the problem (the actual file is pretty big with many tables and queries, so I wouldn't want to just post the whole thing).

This actually led me to a workaround, though: the 'table' whose pulled data isn't conforming to the matched records was actually another query; when I create a table out of those query results and pull the data from that new table, the results conform to matched data like I want/expect.

While I'm still not sure why it would make a difference, I do know how to get the results I want now, and it's a sign that I probably shouldn't be running queries within queries too often.

So... solved, kind of?

(Oh, and on the delimitters: it could be just my computer or system, but when I try to run IIf or any statements that use delimitters, I'm met with "The expression you entered has invalid vertical bars")
 

Users who are viewing this thread

Back
Top Bottom