some query fields are blank

[dorothyh]

As stated,
I created a query that join 2 tables. but when I add a field from the av table
the field is blank.

av table has name,color, deth
[master Without Matching av] query has name,color

SELECT [master Without Matching av].name,
[master Without Matching av].color,
av.deth
FROM [master Without Matching av]
LEFT JOIN av ON [master Without Matching av].name = av.name
WHERE (((av.name) Is Null));

Why does the data not show for av.deth? The query execute do the column is empty. (Access 2000)

 

[aleb]

Your query name is the answer to your question. Master without matching AV
That means: "show me the records from Master table which don't have related records in AV table". Surely if you select any field from av table it would be blank.

 

[dorothyh]

Some blank columns join field

How does it say that? I am dong a left join. Does a left join means something different in access then other relational databases?
If the 2 tables are joins, I should be able to pull columns from both tables.
This is just a small example. In the real tables I have 6 columns I need to view from each table. A left join in other rlational database show you all the records that met the condition and all the records from the left table.

This is just a name of a query.

 

[aleb]

No ... left join doesn't say that you are looking for unmatched records

but this does

WHERE (((av.name) Is Null));

You are able to pull the columns from both table it just the fact that there nothing in this columns to show ( I mean av table )
Simply remove the criterion.

 

[dorothyh]

Some blank columns join field

I will explain from the beginning:

I am trying to put information together from records that did not get loaded.
The records that did not get loaded are in the query [Fail Log]. I also need to get the information from the system that gets me information about what media it came from (info like drive number,drive type,etc.)

my code:

select [FAIL LOG].NAME,
[FAIL LOG].PATH,
[FAIL LOG].ERROR1,
[FAIL LOG].ERROR2,
[FAIL LOG].ERROR3
FROM [FAIL LOG] LEFT JOIN AV ON [FAIL LOG].NAME + [FAIL LOG].PATH = AV.NAME + AV.PATH
WHERE (((AV.NAME + AV.PATH) IS NULL));

This works fine. However, I need to display data from the AV table as well.

so, I code:

select [FAIL LOG].NAME,
[FAIL LOG].PATH,
av.av_media_id,
av.av_job_id,
av.av_media_phys_id,
[FAIL LOG].ERROR1,
[FAIL LOG].ERROR2,
[FAIL LOG].ERROR3
FROM [FAIL LOG] LEFT JOIN AV ON [FAIL LOG].NAME + [FAIL LOG].PATH = AV.NAME + AV.PATH
WHERE (((AV.NAME + AV.PATH) IS NULL));


Yes, I want every message [FAIL LOG] and get the information from AV
where [FAIL LOG].NAME + [FAIL LOG].PATH = AV.NAME + AV.PATH

[FAIL LOG] has NAME,PATH,ERROR1,ERROR2,ERR0R3
AV has NAME,PATH,AV_MEDIA_ID,AV_MEDIA_JOB_ID,AV_MEDIA_PHYS_ID

The query execute but the AV columns are empty.
I do not see what is wrong. Please help!!

 

[FoFa]

most likely this is your problem:
WHERE (((AV.NAME + AV.PATH) IS NULL))

Try this:
select [FAIL LOG].NAME,
[FAIL LOG].PATH,
av.av_media_id,
av.av_job_id,
av.av_media_phys_id,
[FAIL LOG].ERROR1,
[FAIL LOG].ERROR2,
[FAIL LOG].ERROR3
FROM [FAIL LOG]
LEFT JOIN AV ON ([FAIL LOG].NAME = AV.NAME
AND [FAIL LOG].PATH = AV.PATH);

 

[dorothyh]

I will try it tonight but the uniqueness is in the combinations of the two fields.
There are hundreds of records with the same name and thousands with the same path but the uniqueness is in the combination of the two.

I think I have tried this but I will check it again.

 

[FoFa]

There is no difference other than typically queries work faster when you do not do things like concatenate fields. This is comparing the value of both fields to both fields on a record to record basis. Trust me
What is the differance between F1 = A AND F2 = B or F1 & F2 = AB?

 

[dorothyh]

some fields are blank

This does not fix the problem. The field are still blank.