Check (and get) value in other table field

[aanders]

I am new to access and english is not my mother tounge, so i apologize if something is not clear in my description.

Background
I have one query (lets call i Q1) and I have two tables with identical structure.
Table 1:
- id
- iddesc
- comment

Table 2
- id
- iddesc
- comment

Table 1 consists of an imported xslx-file. The idéa with Table 2 is to use it to edit content. Sometimes only one field is edited in Table 2 and in those cases i need to get the other values from Table 1. Q1 should (but doesnt) generate something like this:
Q1:
- id [always from Table 1]
- iddesc [from Table 2]
- comment [from Table 1]

My question
How do i, in a query, check if data exists in a field in Table 2 and if it does, add data from that field and not from Table 1?

 

[Cronk]

I am not sure what you are wanting to achieve in this.

Do you mean that you have imported data into table1 and you want to edit that data before appending it to Table2?

 

[aanders]

Cronk:

Table 1 is the gross of all data (6 times a year it will be updated and some rows will be added from a new xlsx-file). If i want to edit data i do that in table 2 by writing the id of the row i want to edit and the value i want to add.

The task of Q1 is to merge the two tables together. But if the field "comment" is left empty in Table 2 Q1 should check if the field "Comment" in Table 1 (with the same id) has data and in that case add that data.

 

[namliam]

you do this with update queries, unfortunately more than one I guess.
You simply join the two queries and find the records where table2.comment is null and update that from table1.

Then if you need to do the reverse, another query that does the reverse.

 

[Galaxiom]

Use an IIF statement in the query. The tables are joined on the ID.

IIF(table2.comment Is Null, table1.comment, table2.comment)

 

[aanders]

So i cant use =DLookUp() or something similar to "iif([Table2].[comment] > 0; [Table2].[comment]; [Table1].[comment])?

 

[aanders]

Galaxiom: Ahh. Since i am new to access i just needed the syntax (is Null). Thank you!!

 

[Galaxiom]

Dlookup would be very slow. Always use queries wherever possible.

Each record that uses a DLookup is like running another separate query.

The IIF I posted depends on the field being Null. If it could be a Zero Lenth String use:

IIF(Len(table2.comment & "") = 0, table1.comment, table2.comment)

 

[aanders]

Hmm. actually, that returns just half of my the data in Table 1. Any idea why?

 

[Galaxiom]

You will need an OUTER JOIN. Otherwise you will only get the records that are in both tables.

Right click the join line in the query designer and pick the description that sounds like what you want.

 

[aanders]

Galaximon:
I have an OUTER JOIN.

Right now i only have 1 row in Table 2. In Table 1 i have 4906 rows. Q1 returns 2235 rows.

 

[aanders]

I have identified the problem. If i use:
IIF(Len(table2.comment & "") = 0, table1.comment, table2.comment)

And both "table1.comment" and "table2.comment" are empty Q1 doesnt add this row.

 

[Cronk]

And if nothing is in both fields, what is supposed to be inserted?

I'd suggest you post your current SQL expression.

 

[aanders]

Cronk:
If nothing is in both fields... Lets say that i only have a value in the "id" field in Table 1 (and that that id doesnt exist in Table 2) the row with that id in Table1 and all the other (blank) fields (iddesc and comment) should appear in Q1.

My SQL:
SELECT Table1.iddesc, Table1.id
FROM Table1 LEFT JOIN Table2 ON Table1.id = Table2.id
WHERE (((Table1.comment)=IIf(Len([Table2].[comment] & "")=0,[Table1].[comment],[Table2].[comment])));

 

[Cronk]

Firstly, that SQL does not update anything.

It selects records from Table1 where the Table2 comment is blank, or the comment fields in both tables are the same.

Is that what you want?

 

[aanders]

Cronk,
sorry for my poor english. As i said, its not my mother tounge.

I want to create a question (Q1) that shows all data from Table 1. If there is data in a field in Table 2 on a row with the same id i want that data to replace the data in Table1. Something similar to:
if(table2.comment > 0;(show table2.comment); (otherwise show table1.comment).

 

[Cronk]

If you only want to display and not update Table1 then the following will replace a blank comment in Table1, with the comment in Table2 if both the record and comment exists in Table2, otherwise it will be blank as there is no comment in table 1 nor comment and/or record in Table2

Select Table1.ID, IIf(IsNull([Table1].[Comment]), [Table2].[Comment], [Table1].[Comment])
From Table1 left join Table2 on Table1.ID = Table2.ID;

 

[aanders]

Cronk!

That did it! Thank you!