SELECT TOP 1 problem

[Mirica_Victor]

Hy,
Problem: I have a few tables from wich I need to extract the value from one specific field and update it to another table (other from the first). The value in the field is the same in each record of the given table, but different in the next table.
I have a table wich contains the table names and the field that contains that data. For a single table I can do a "(select top 1 from

.[field] from

)" - this gives the corect info in a select query- , but this would mean to do a update qwery for each table (13), eather way, I cannot put that sql string to work in a update query because I get the "Operation must use an updateble query". There are no restrictions on the database, I have full acess, no limitations.

Anny ideas?

 

[NickHa]

Can you provide an example of what you want to do? I can't visualise what you need from the description given. I suggest you illustrate an entry from your table of tables and show how it relates to the other tables you have. From what I see, I don't think using "TOP 1" will do what you want, so you will probably need a loop, something like this

Private Sub testLoop()
Dim rst As Recordset
Dim strField As String, strTable As String
Set rst = CurrentDb.OpenRecordset("SELECT * FROM [table of tables];")
Do While Not rst.EOF
  Rem do what you need with the current table name and field name
  strTable = rst![Table Name]
  strField = rst![Field Name]
  Rem get the next table of tables entry
  rst.MoveNext
Loop
End Sub

 

[RainLover]

Hy,
Problem: I have a few tables from wich I need to extract the value from one specific field and update it to another table (other from the first). The value in the field is the same in each record of the given table, but different in the next table.
I have a table wich contains the table names and the field that contains that data. For a single table I can do a "(select top 1 from

.[field] from

)" - this gives the corect info in a select query- , but this would mean to do a update qwery for each table (13), eather way, I cannot put that sql string to work in a update query because I get the "Operation must use an updateble query". There are no restrictions on the database, I have full acess, no limitations.

Anny ideas?

Storing the same Data in more than one place points to in incorrectly designed set of tables.

To design a functional Database there are some concepts that should be followed.

The main one in your situation is "Normalisation" I would suggest that you read up on the sugject to gain an understanding. After that you may have some different questions to ask.

 

[Mirica_Victor]

Thank you for the support. I have solved the problem by creating 13 append querys, each one based on each table. The append is made in a table from wich I delete the info first. It appends the table name and the info in the needed field. This field contains the data for wich the info is contained. The tables are linked *.txt files with info about credits, guaranties, credit lines and other bank info. The information in these tables refers to a specific reporting day, as they are created daily, in the table there is a field that contains that date. This is the info that I need to check daily so I can go on with my activity. I need to check that each table has the corect reporting date.

 

[RainLover]

There is usually a workaround to solve problems like yours.

You found one.

I still suggest that you do some research. You will be better off in the future.

Good Luck with the project.