Suppose there are 2 tables:

1) Position

2) Personnel who refer to the Position.

For one record Position may account for several records from the Staff.

The task is to update [All staff] in the Position with glued in one line dependent records from the Staff.

Suppose there are 1 entry for 2 lines of the full name of the Personnel, we glue them together and write down the Position in the parent record.

How to do it quickly? Used database Access.

Now I use Interop, open the RecordSet from the Position, and go from top to bottom, and for each record I open the second RecordSet from the query 'Select PID, FIO From [Staff] where PID =' rst.fields [ID] .value and go from top to bottom and glue the values, and then return and update them in the first RecordSet and it seems to me that this is too slow and you can do it as quickly as possible.

Probably through ADO.NET it would be faster, but it seems to me that if the Access Database is healthy, then it is too fat to load everything into memory at once ...

UPD: If you still load everything into memory and work through ADO.NET, will I get a significant speed gain?

  • one
    Access to refuse in any way? - Mirdin
  • one
    @Mirdin, production necessity ... - iluxa1810
  • one
    Not good, the algorithm can be accelerated by transferring the calculations to the server, and most likely almost any MSSQL, MySQL, etc. will do. But Acces is a database for storing phone numbers. - Mirdin pm
  • one
    The question is interesting. Do not want to ask it in a more general form (I mean correct the title)? "How can ms-access build lines in a group?", For example. So the question, it seems to me, will be more useful. I understand that it is not necessary for you to solve this with means of sql + vb, but still .. - pegoopik

3 answers 3

You can write a VB function that will glue the string together. Details can be read here:

http://hiprog.com/index.php?option=com_content&task=view&id=334&Itemid=35

Body functions:

Public Function UnionStr1(ID, Fam) Static IDOld, FamUnion If IDOld <> ID Then IDOld = ID FamUnion = Null End If FamUnion = (FamUnion + ", ") & Fam UnionStr1 = FamUnion End Function

Request example:

 Select ID, Last(UnionStr1(ID,Fam)) AS FamUnion FROM Tab1 GROUP BY ID;

In the same article there is an interesting way. Only by means of ms-access sql, with an intermediate table. Although it is possible in your case, its role will be played by the Person table (posts). It looks like this:

 Метод 3. Заполнение таблицы при помощи запросов (по Митину). http://c85.cemi.rssi.ru/Access/AnsPointDetail.idc?QID=14147 Оригинальный и неочевидный метод. Выполняется с помощью двух запросов. 1. Запись в Tab2 уникальных ID без фамилий. Текст запроса WriteID: INSERT INTO Tab2 (ID) Select DISTINCT ID FROM Tab1; 2. Запись списков фамилий. Текст запроса TabUnion5: UPDATE Tab2 INNER JOIN Tab1 On Tab2.ID = Tab1.ID Set Tab2.FamUnion = ([Tab2].[FamUnion]+", ") & [Tab1].[Fam]; Оценка скорости: по-видимому, работает быстрее метода 1 за счет отсутствия вызова специальных функций. Недостаток: "Однако в T-SQL это непрокатывает" (Митин).

We must indulge, it looks interesting.

UPD: The point is that because of the "stupidity" ms-access SET will be executed as many times as the records in the attached table (not the one that is updated). Due to T-SQL "cleverness", this method will not work in T-SQL .

To the downsides of this method, I would add the need for maintenance when upgrading to the new version of access . If MS decides to bring even a little bit of ms-access to the ANSI-SQL standard, this method may stop working.

On the other hand, maybe they will do something for concatenating strings as well :) Although in this issue even MS SQL Server deprived of attention so far. MySQL , PostgreSQL , Oracle have been able to do this for a long time.

  • MS SQL also can do concatenation. - Mirdin
  • @Mirdin, you mean FOR XML PATH ('') is not that. This method almost always involves self-connection. And if you need to condense several columns, then several self-joins. MS SQL does not have an aggregate function that would do this. It is not so difficult to find in the Internet, written in the CLR, but it does not know how out of the box ... Three other DBMSs have their own aggregate functions. MS SQL until 2012 even could not count the cumulative result ... And FOR XML is still not intended for this. There is no other choice ... - pegoopik
  • yes, I mean FOR XML PATH (''), for an infrequently encountered task (well, personally in my experience), that's right. - Mirdin
  • >> MS SQL until 2012, even the cumulative result could not be considered ... UZHOS, UZHOS, UZHOS ... you are still working with the database, and not working with the spreadsheet :) - Mirdin
  • @Mirdin, do not know what character experience you have. I wrote a huge number of reports - in my work the cumulative result and concatenation by group were very much in demand. And the fact that MS is not able to do this - greatly slowed down the work of requests. So yes, "UZHOS". Since 2012, the server has become much more pleasant. It was a big step forward. - pegoopik

I would do this:

Let's say

 в таблице персонал(Person) есть три поля: Person_ID, Person_FIO, Position_ID в таблице должность(Position) есть три поля: Position_ID, Position_Name, Position_StaffList

To begin with, we write a request for a DataReader (if I understand correctly this is the C # analogue of the Recordset from Basic):

 SELECT pos.Position_ID, pos.Position_Name, prs.Person_FIO FROM Position AS pos LEFT OUTER JOIN Person AS prs ON pos.Position_ID = prs.Position_ID ORDER BY pos.Position_ID, prs.Person_FIO;

And service class

 internal class Pos { public Pos() { StaffList = new StringBuilder(); } public int? Position_ID {get; set;} public string Position_Name {get; set;} public StringBuilder StaffList {get; set;} }

Open the reader for our request.

 List<Pos> positions = new List<Pos>(); int previos_pos = -1; if (reader.HasRows) { int id; string position_name; string fio; Pos current; while (reader.Read()) { id = reader.GetInt32(0); position_name = reader.GetString(1); fio = reader.GetString(2); if (id != previos_pos) { current = new Pos(); positions.Add(current); current.Position_ID = id; current.Position_Name = position_name; current.StaffList.Append(fio); } else { current.StaffList.AppendFormat("{0},{1}" , current.StaffList, , fio ); } } }

Close our reader. Now we have a complete list of positions and it remains only to update your position table from it. Since we do not dodim the database with intermediate queries (which can be really long if there are many positions), we should get some acceleration.

  • Your solution is interesting, but it does not solve an important problem: "if the Access Database is healthy, then it is too fat to load everything into memory at once ...". If it is possible to process a large amount of data at the server level, it is better to do it there. - pegoopik
  • @pegoopik, "if the Access Database is healthy, it is too fat to load everything into memory at once ..." - this is a problem in itself, big data is not for Access. In addition, not the entire database is loaded into memory, but only positions, how many there may be in the enterprise, a hundred different, even for very large ones - this is quite a lot. - Mirdin 1:53 pm
  • In the framework of the current issue I agree. Plus still) - pegoopik
  • @pegoopik, here the question itself violates 1NF - so there will not be a good solution here. - Mirdin 1:58 pm
  • Violation of the 1NF in the case of access is often justified :) In this case, it’s controversial .. Approximately as much as it’s arguable not to install any normal free DBMS. - pegoopik

How to do it quickly? Used database Access.

Perhaps I know terribly the ability of MS Access ... but I assume that it is impossible to do this task using Access databases, because there is a weak potential for analytical functions and SQL query extensions.

In more capable DBMSs in which the SQL language expands with additional analytic functions, there are ways to accomplish such a task, but even they look awful and crooked - for example, in Oracle using the composition of analytical functions in several subqueries. Therefore, the most ideal option for you is what you are already doing - to form a request for positions, by transitioning to posts, to carry out the second request to receive all the full names from this position. Process the resulting lists.

The action you have suggested is:

Suppose there are 1 lines of 2 names from the Personnel per record, we glue them together and write them into the parent record.

  • this is a terrible and gross violation of the data structure and logic (it is necessary to read about normal forms of the database). The conclusion is that you most likely have an incorrect formulation of the problem, or an incorrect decision to carry it out.

PS: I explain .. if you are all the full names belonging to 1 position, you will thrust into the field of this position, then fulfilling a request for this position and outputting information on the client - you will not save RAM in any way, since the mathematical rule "the sum does not change from the change of the places of the terms" operates. It does not matter if you receive these full names and you will store them in different fields, or in one field, the number of bytes of characters will not change, in any case the difference will not be noticeable for the PC. But when you receive a list of known posts, the volumes of information transferred will grow very quickly, which will increase the load on traffic and databases.

It is better to formulate the task set before you so that you can prompt the optimal solution, since in this matter, you ask for help to make a decision that you made yourself - but it is erroneous.

  • "for example in Oracle" you can write your aggregate function. In Access too, but it will be executed on the client side. - 4per