Update Query or Make-Table Query

  • Thread starter Thread starter RyGuy
  • Start date Start date
R

RyGuy

How can I go from this:
Number Letter
1 AA
1
1
2 BBB
2
2

To this:
Number Letter
1 AA
1 AA
1 AA
2 BBB
2 BBB
2 BBB

I am aware of normalization, database design, etc.


Thanks so much!!

Regards,
Ryan---
 
Try using a subquery to read the non-null Letter row from the same table.
This kind of thing:

UPDATE Table1SET [Letter] =
(SELECT Letter FROM Table1 AS Dupe
WHERE Dupe.[Number] = Table1.[Number]
AND Dupe.[Letter] Is Not Null)
WHERE Table1.Letter Is Null;

Sometimes Access refuses to handle an Update with a subquery.
If you run into that, use DLookup() instead:

UPDATE Table1 SET [Letter] = DLookup("Letter", "Table1",
"([Number] = " & [Number] & ") AND (Letter Is Not Null)")
WHERE Table1.Letter Is Null;
 
Wow!! Powerful stuff!!!!!!!!!
Thank you very much Allen!!

Regards,
Ryan---

--
RyGuy


Allen Browne said:
Try using a subquery to read the non-null Letter row from the same table.
This kind of thing:

UPDATE Table1SET [Letter] =
(SELECT Letter FROM Table1 AS Dupe
WHERE Dupe.[Number] = Table1.[Number]
AND Dupe.[Letter] Is Not Null)
WHERE Table1.Letter Is Null;

Sometimes Access refuses to handle an Update with a subquery.
If you run into that, use DLookup() instead:

UPDATE Table1 SET [Letter] = DLookup("Letter", "Table1",
"([Number] = " & [Number] & ") AND (Letter Is Not Null)")
WHERE Table1.Letter Is Null;

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

RyGuy said:
How can I go from this:
Number Letter
1 AA
1
1
2 BBB
2
2

To this:
Number Letter
1 AA
1 AA
1 AA
2 BBB
2 BBB
2 BBB

I am aware of normalization, database design, etc.


Thanks so much!!

Regards,
Ryan---
Click to expand...
Click to expand...
 
Back
Top