By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,965 Members | 2,043 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,965 IT Pros & Developers. It's quick & easy.

problem with SQL in Access

P: n/a
ET
Hello!

I have table with data that looks like this:

Col1 Col2 Col3
1 pro1 some1
2 some2
3 some3
4 pro2 some4
5 some5
6 some6
7 pro3 some7
8 some8
As you can see, not every row in Col2 column is full, most of them are
empty...
I'll need to update table in such way that every row in Col2 will have
data if it already doesn't have it, if it is empty then it needs to
have data from the first full Col2 row.
Finally it will look like this:

Col1 Col2 Col3
1 pro1 some1
2 pro1 some2
3 pro1 some3
4 pro2 some4
5 pro2 some5
6 pro2 some6
7 pro3 some7
8 pro3 some8
Can somebody advice me on how to do it, or what query to run?

Thank you.

Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Use an Update query to populate the Col2 field. In the Update query, use a
subquery to get the last non-blank value for Col2 in the same table.

1. Create a query into this table.

2. Change it to an Update query (Update on Query menu).
Access adds an Update row to the grid.

3. Drag Col2 into the grid.

4. In the Criteria row under this field, enter:
Is Null

5. In the Update row under this field, enter something like this:
(SELECT TOP 1 Col2 FROM Table1 AS Dupe
WHERE (Dupe.Col2 Is Not Null) AND (Dupe.Col1 > Table1.Col1)
ORDER BY Dupe.Col1)

6. Replace "Table1" with the name of your table in that expression.

7. Run the query to populate the field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ET" <ve****@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Hello!

I have table with data that looks like this:

Col1 Col2 Col3
1 pro1 some1
2 some2
3 some3
4 pro2 some4
5 some5
6 some6
7 pro3 some7
8 some8
As you can see, not every row in Col2 column is full, most of them are
empty...
I'll need to update table in such way that every row in Col2 will have
data if it already doesn't have it, if it is empty then it needs to
have data from the first full Col2 row.
Finally it will look like this:

Col1 Col2 Col3
1 pro1 some1
2 pro1 some2
3 pro1 some3
4 pro2 some4
5 pro2 some5
6 pro2 some6
7 pro3 some7
8 pro3 some8
Can somebody advice me on how to do it, or what query to run?

Thank you.

Nov 13 '05 #2

P: n/a
"ET" <ve****@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Hello!

I have table with data that looks like this:

Col1 Col2 Col3
1 pro1 some1
2 some2
3 some3
4 pro2 some4
5 some5
6 some6
7 pro3 some7
8 some8
As you can see, not every row in Col2 column is full, most of them are
empty...
I'll need to update table in such way that every row in Col2 will have
data if it already doesn't have it, if it is empty then it needs to
have data from the first full Col2 row.
Finally it will look like this:

Col1 Col2 Col3
1 pro1 some1
2 pro1 some2
3 pro1 some3
4 pro2 some4
5 pro2 some5
6 pro2 some6
7 pro3 some7
8 pro3 some8
Can somebody advice me on how to do it, or what query to run?

Thank you.


It might be possible to use an update query on its own, and although an
update query is faster, a vba routine gives you more flexibility. So you
could create a form with a button on it named 'cmdUpdate' and put the
following code in its OnClick event.
First amend the MyTable part of the SQL string and make sure you do this on
a back-up copy of your data as you will be unable to take me to court if it
doesn't do as you expect.
Private Sub cmdUpdate_Click()

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strTemp As String
Dim strCode As String

strSQL = "SELECT * FROM MyTable ORDER BY Col1"

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset(strSQL)

strCode = "Unknown"

While Not rst.EOF

strTemp = Trim(Nz(rst.Fields("Col2"), ""))

If Len(strTemp) > 0 Then
strCode = strTemp
End If

rst.Edit
rst.Fields("Col2") = strCode
rst.Update

rst.MoveNext

Wend

MsgBox "Records Updated", vbInformation

Exit_Handler:

On Error Resume Next
rst.Close
Set rst = Nothing
Set dbs = Nothing

Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub
Nov 13 '05 #3

P: n/a
ET
Allen,

I did everything, all 7 steps, and query in SQL view looks look this:

UPDATE Table1 SET Table1.Col2 =
(
SELECT TOP 1 Col2 FROM Table1 AS Dupe
WHERE (Dupe.Col2 Is Not Null) AND (Dupe.Col1 > Table1.Col1)
ORDER BY Dupe.Col1
)
WHERE ((Table1.Col2) Is Null);

But, when I try to run the query, here is the error message:

"Operation must use an updateable query."

:(

Nov 13 '05 #4

P: n/a
ET
Justin,

your VB code did work, exactly what neeed to be done, perfect!
p.s.
I did make back-up copy of table, that is what I always do.

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.