Hello all. Using access 2003; I have an update query that I want to modify. Ex: table one fields= ordernum and quote table two=12 other fields including ordernum and quote. A join is based on the ordernum and the quote field in table two is blank. I am updating the quote field in table two but if the ordernum is blank or does not have a match to table one; I'd like the quote field to default to "xxx". How do I write this. I started typing something like this into the criteria field: iff([tblOne.ordernum] is null or no match; [tblOne.quote] ="xyz") but of course I got an error. Some guidance please.
You already figured out that your join is useless. You can't make an inner join between two tables when the field values aren't supposed to agree.
Let's say your table1 is a customer master table, and table2 is order master. You might also have a table3 for order details; a table4 for returns and credits; etc. In table1, make the customer num a unique index. In table2, make the ordernum a unique index. In the Relationships screen, create a one-to-many relationship between the customer num in table1 and the customer num in table2. Assuming that ordernum is generated by your system (not the customer's system), make a one-to-many relationship from table2 ordernum to table3 ordernum. Also from table2 ordernum to table3 ordernum, from table2 ordernum to table4 ordernum, etc. Table2, as order master, is the table from which all relationships regarding orders must originate.
You won't be able to do the above now, because in all probability the indexes won't create for one reason or another. If it doesn't work, simply replicate the tables without the data, modify as above, and then use update/append queries to add the rest of the data to the correct tables. You may also need to add/repair some data manually, so be aware of that.