1 *A 1 13357 SCIALDO MAUREEN 125 S 4TH AVE
B 201 13357 SCIALDO MAUREEN 125 S 4TH AVE
2 *A 2 13357 PADDOCK CHARLES 127 S 4TH AVE
B 200 13357 PADDOCK CHARLES 127 S 4TH AVE
3 *A 3 13357 JONES PATRICIA 129 S 4TH AVE
B 199 13357 JONES PATRICIA 129 S 4TH AVE
4 *A 4 13357 HAGGERTY DAVID 131 S 4TH AVE
B 198 13357 HAGGERTY DAVID 131 S 4TH AVE
5 *A 5 13357 LONIS PAULA 135 S 4TH AVE
B 197 13357 LONIS PAULA 135 S 4TH AVE
6 *A 6 13357 HIGGINS CHRIS 139 S 4TH AVE
B 196 13357 HIGGINS CHRIS 139 S 4TH AVE
When you look at this the B should be under the A so number1 has two records A and B etc.
Okay, here is a copy of some of the rows in my database. I know in excel i can add a column before outrec and then add a row, then i can put a formula in as =if(b2=" ", A1, b2). This formula after copied down will put a 1 under the 1, a 2 under the 2, etc.So with that said I can't use excel , I need to use access, and I'm wondering if I could use the same formula somehow in a query. I'm trying to research IIF but with no luck.
Last edited by gvee; 09-30-08 at 15:11.
Reason: [CODE] tags added for readability
SET that_first_column = x.that_first_column
FROM my_table x
JOIN my_table y
ON x.common_field_1 = y.common_field_1
AND x.common_field_2 = y.common_field_2
AND x.common_field_N = y.common_field_N
Thanks George. I just have a question, I changed the [my table] to the table name and [that_first_column ] to I assume the column that has the number in it. I changed the coding to read the other column names, which I'm assuming that was what you were going for. So I ended up with this, but when I run it it comes up with an error -Syntax error (missing operator) in query expression x.outrec and then the rest of the code starting with FROM. Can you help me with this, I would appreciate it.
UPDATE [copy mergerpt]
SET outrec = x.outrec
FROM [copy mergerpt] x
INNER JOIN [copy mergerpt] y
ON x.scrfile = y.scrfile
AND x.srcrec = y.srcrec
AND x.zip+4 = y.zip+4
AND x.lastname = y.lastname
AND x.firstname = y.firstname
AND x.address = y.address