Запрос на выборку строки с максимальной датой

@Dmitry_Morozov · Регистрация: 03.01.2017

Author24 — интернет-сервис помощи студентам

Коллеги, привет!

Буду рад если сможете помочь!

Необходимо запросом выбирать данные из таблицы TEST_TABLE в TARGET_TEST_TABLE.
Структура таблиц одинаковая - три столбца - ID, Param, Date, Autonumber

В таблицу TEST_TABLE сваливаются активности по ID, соответсвенно ID могут повторяться много раз. Date - значение по умолчанию Now(), двух операций в секунду с одним ID быть не может, если даже вдруг может, подключу MAX по автосчетчику.

Запрос должен вставлять в таблицу TARGET_TEST_TABLE ID c максимальной датой из таблицы TEST_TABLE, другими словами для каждого ID вставить строку с последней сделанной актиностью.

Запрос на вставку абсолютно всех значений выглядит так

Visual Basic

1
2
3

DB.Execute "INSERT INTO [TARGET_TEST_TABLE] ([ID], [Date], [Param])" & _
                      "SELECT [TEST_TABLE].[ID], [TEST_TABLE].[Date], [TEST_TABLE].[Param]" & _
                "FROM [TARGET_TEST_TABLE] right JOIN [TEST_TABLE] ON [TARGET_TEST_TABLE].[ID] = [TEST_TABLE].[ID]"

Не могу понять как подключить сюда функцию SQL MAX (если возможно через WHERE), всегда ругается на синтаксис

Заранее спасибо!

mobile · 06.04.2017, 21:21

Предположительно так (???)

Visual Basic

DB.Execute "INSERT INTO [TARGET_TEST_TABLE] ([ID], [Date], [Param])" & _
               "SELECT [TEST_TABLE].[ID], [TEST_TABLE].[Date], [TEST_TABLE].[Param]" & _
               "FROM [TARGET_TEST_TABLE] right JOIN [TEST_TABLE] ON [TARGET_TEST_TABLE].[ID] = [TEST_TABLE].[ID] " & _
               "Where [TEST_TABLE].Autonumber in (select top 1 Autonumber from TEST_TABLE t " & _
               "Where t.ID=TEST_TABLE.ID " & _
               "Order by t.[Date] Desc)"

Вы говорите, что в каждой таблице 3 поля, но перечислили и 4-е - счетчик, Autonumber. Поэтому я им воспользовался.

@Capi · 06.04.2017, 21:51

Visual Basic

DB.Execute "INSERT INTO [TARGET_TEST_TABLE] ([ID], [Date], [Param]) " & _
           "SELECT [TEST_TABLE].[ID], [TEST_TABLE].[Date], [TEST_TABLE].[Param] " & _
           "FROM TEST_TABLE INNER JOIN " & _
           "(SELECT ID, Max([Date]) AS MaxDate FROM TEST_TABLE GROUP BY ID) AS Q " & _
           "ON (TEST_TABLE.ID = Q.ID) AND (TEST_TABLE.[Date] = Q.MaxDate)"

@Dmitry_Morozov · 07.04.2017, 11:18 **[ТС]**

Mobile, Capi, огромное спасибо! Разобрался! Работает!

Есть еще один вопрос. Как в запросе на выборку через конструктор организовать пустое поле, которое потом заполняется другим запросом? Если ввести просто заголовок, access предлагает присвоить занчение ячейкам через inputbox

Спасибо!

@Dmitry_Morozov · 08.04.2017, 16:22 **[ТС]**

Разобрался, в названии поля надо написать Название поля:Null

@Capi · 08.04.2017, 16:28

Сообщение от Dmitry_Morozov

Разобрался, в названии поля надо написать Название поля:Null

Это не название поля, а его значение.

@Dmitry_Morozov · 08.04.2017, 16:42 **[ТС]**

Fieldname:null - строка в названии поля в конструкторе - это имел ввиду

@Capi · 08.04.2017, 16:58

Dmitry_Morozov,

Нет. Название может быть любым другим,
кроме служебных слов, к которым, кстати, относится и Null.

SQL

1	SELECT A, B, ...., NULL AS [Пустое поле] FROM.....

@Dmitry_Morozov · 08.04.2017, 17:00 **[ТС]**

Естественно любым другим может быть

@Capi · 08.04.2017, 17:02

Сообщение от Dmitry_Morozov

Естественно любым другим может быть

Так Вы же в качестве "найденного решения" приводите:

Сообщение от Dmitry_Morozov

Разобрался, в названии поля надо написать Название поля:Null

, а это, простите, глупость.
Не различаете имя поля и значение поля.

Покажите текст запроса и результат запроса.

@Dmitry_Morozov · 08.04.2017, 17:08 **[ТС]**

как глупость, то

Создание запроса в Access, режим конструктора.
Ниже таблица для полей запроса.
Первая строка по всем полям - Field, если в нем написать "Нужное название поля":null, то при выполнении запроса это будет пустой столбец с названием "Нужное название поля".

@Capi · 08.04.2017, 17:15

Ну так Вы и задаете таким образом значение поля.
Ладно, со временем, может, поймете...

@Dmitry_Morozov · 08.04.2017, 20:00 **[ТС]**

Capi, я понимаю о чем Вы, просто мы не можем друг друга понять.

Вы говорите про то, что Fieldname - название самого столбца (поля) - может быть любым абсоляютно.
Null - значение записей в поле.

А я говорю немного с другой стороны - что если необходимо получить пустой столбей, нужно в конструкторе в поле Field прописать Fieldname:null - по факту это поле - название столбца, в котором так же задается и его значение.

Добавлено через 1 час 52 минуты
Коллеги, пытаюсь использовать подобную конструкцию в запросе UPDATE, ругется на нехватку параметров, нехватает одного, не могу понять какого

Спасибо!

Visual Basic

1
2
3

 DB.Execute "UPDATE [TARGET_TEST_TABLE] INNER JOIN (SELECT ID, Max([Date]) AS MaxDate FROM TEST_TABLE GROUP BY ID) AS Q " & _
            "ON (TARGET_TEST_TABLE.ID = Q.ID) AND (TARGET_TEST_TABLE.[Date] = Q.MaxDate)" & _
                      "SET [TARGET_TEST_TABLE].[Date]=Q.[Date]"

Заранее спасибо!

Добавлено через 24 минуты
PS Подозреваю, что вместо Q.[Date] Вернее было бы написать Q.MaxDate , в таком случае появляется ошибка

3073 - запрос должен быть обновляемым...

mobile · 08.04.2017, 20:12

Ошибка понятно какая - в подзапросе Q нет поля Date, есть поле MaxDate. И последняя строка должна быть

Visual Basic

1	"SET [TARGET_TEST_TABLE].[Date]=Q.[MaxDate]"

Но этим ошибки не исчерпаются. Джетовский Update на дух не переносит необновляемых подзапросов и немедленно сообщит Вам об этом. Но предположу, что вместо подзапроса можно использовать функцию DMax

Visual Basic

Dim s
s="Update [TARGET_TEST_TABLE] " & _
"Set [TARGET_TEST_TABLE].[Date]=Dmax('[Date]', 'TEST_TABLE'," & _
"'ID=' & TEST_TABLE.ID & ' and [Date]=' & Format([TARGET_TEST_TABLE].[Date], '\#mm\/dd\/yyyy\#'))"
Debug.print s
DB.Execute s

Переменная s и Debug.print s введены для возможной последующей отладки распечатанного текста в конструкторе запросов

Добавлено через 1 минуту
С MaxDate и обновляемыми подзапросами опоздал. Подзадержался с апострофами

@Dmitry_Morozov · 08.04.2017, 20:20 **[ТС]**

Mobile, приветствую, спасибо,

Ругается, пишет мало параметров, нехватает 1 (это вариант с дмакс)

mobile · 08.04.2017, 20:30

Сообщение от Dmitry_Morozov

Ругается, пишет мало параметров, нехватает 1 (это вариант с дмакс)

Я же специально написал

Сообщение от mobile

Переменная s и Debug.print s введены для возможной последующей отладки распечатанного текста в конструкторе запросов

Посмотрите в Immediate (область отладки в редактора ВБА) текст запроса. Скопируйте его, откройте конструктор запросов в режиме SQL и вставьте туда скопированный текст. Попробуйте выполнить. Если будет ошибка, конструктор укажет на нее.

@Dmitry_Morozov · 08.04.2017, 20:47 **[ТС]**

В Immediate выводит код ниже и конструктор ругаетя на синтаксис 'ID=1 and [Date]='

SQL

UPDATE [TARGET_TEST_TABLE]
 SET [TARGET_TEST_TABLE].[DATE]=Dmax('[Date]',
     'TEST_TABLE',
     'ID=' & TEST_TABLE.ID & ' and [Date]=' & Format([TARGET_TEST_TABLE].[DATE], '\#mm\/dd\/yyyy\#'))

mm и dd местами менял

mobile · 08.04.2017, 21:14

Значит поле [Date] в [TARGET_TEST_TABLE] пустое - NULL. Возможно, что Вы хотели обновить именно записи с пустыми полями даты? Тогда так

Visual Basic

Dim s
s="Update [TARGET_TEST_TABLE] " & _
"Set [TARGET_TEST_TABLE].[Date]=Dmax('[Date]', 'TEST_TABLE'," & _
"'ID=' & TEST_TABLE.ID) " & _
" Where [Date] Is Null"
Debug.print s
DB.Execute s

Если же обновить надо все записи, то убрать в последней строке запроса все, что касается даты.

Если же только совпадающие по дате записи и не пустые в [TARGET_TEST_TABLE], то так

Visual Basic

1 2	"'ID=' & TEST_TABLE.ID & ' and [Date]=' & Format([TARGET_TEST_TABLE].[Date], '\#mm\/dd\/yyyy\#')) " & _ "Where Not [TARGET_TEST_TABLE].[Date] Is Null"

@Dmitry_Morozov · 10.04.2017, 16:28 **[ТС]**

Коллеги, привет, чего то я ничерта не понимаю=)

из immediate беру запрос, гоняю его через конструктор, Access оворт будет обновлено n записей, далее окно с предупреждением что эти n записей не будут обновлены в связи с type violation - в обеих таблицах одинаковый тип данных всех полей (таблицы просто совпадают за исключением надора данных).

В чем проблема не пойму

Добавлено через 49 минут
+ запрос ругался на отсутствие аргумента, как оказалось, не хватало связи

Visual Basic

Dim s
s="Update [TARGET_TEST_TABLE] INNER JOIN [TEST_TABLE] ON [TARGET_TEST_TABLE].[VIN]=[TEST_TABLE].[VIN]" & _
"Set [TARGET_TEST_TABLE].[Date]=Dmax('[Date]', 'TEST_TABLE'," & _
"'ID=' & TEST_TABLE.ID) " & _
" Where [Date] Is Null"
Debug.print s
DB.Execute s

Но ошибка по несовпадению осталась...

Добавлено через 48 секунд
+ запрос ругался на отсутствие аргумента, как оказалось, не хватало связи

Visual Basic

Dim s
s="Update [TARGET_TEST_TABLE] INNER JOIN [TEST_TABLE] ON [TARGET_TEST_TABLE].[VIN]=[TEST_TABLE].[VIN]" & _
"Set [TARGET_TEST_TABLE].[Date]=Dmax('[Date]', 'TEST_TABLE'," & _
"'ID=' & TEST_TABLE.ID) " & _
" Where [Date] Is Null"
Debug.print s
DB.Execute s

Но ошибка по несовпадению осталась...

Добавлено через 20 минут
Думаю, что решу проблему в два шага

1) Ваборка инсертом во временную таблицу, т.к. апдейт нельзя сделать по селекту
2) Селект из временной таблицы, где хранятся уже исключительно нужные (максимальные) значения
3) очистка временной таблицы

Думаю, это будет оптимально

Всем спасибо за помощь!

@Dmitry_Morozov · 11.04.2017, 18:47 **[ТС]**

Коллеги, привет!

Подскажите, пожалуйста

Примерно таким же запросом как запрос ниже вставляю данные из одной таблицы в другую.
Но не пойму как по синтаксису правильно встроить WHERE, т.к. надо чтобы в таблицу 1 копировались только те строчки из таблицы 2, ID которых есть в таблице 3

Visual Basic

DB.Execute "INSERT INTO [TARGET_TEST_TABLE] ([ID], [Date], [Param]) " & _
           "SELECT [TEST_TABLE].[ID], [TEST_TABLE].[Date], [TEST_TABLE].[Param] " & _
           "FROM TEST_TABLE INNER JOIN " & _
           "(SELECT ID, Max([Date]) AS MaxDate FROM TEST_TABLE GROUP BY ID) AS Q " & _
           "ON (TEST_TABLE.ID = Q.ID) AND (TEST_TABLE.[Date] = Q.MaxDate)"

Спасибо!

@Dmitry_Morozov 21 / 4 / 0 Регистрация: 03.01.2017 Сообщений: 127
	07.04.2017, 11:18 [ТС]	4
	Mobile, Capi, огромное спасибо! Разобрался! Работает! Есть еще один вопрос. Как в запросе на выборку через конструктор организовать пустое поле, которое потом заполняется другим запросом? Если ввести просто заголовок, access предлагает присвоить занчение ячейкам через inputbox Спасибо! 0

@Dmitry_Morozov 21 / 4 / 0 Регистрация: 03.01.2017 Сообщений: 127
	08.04.2017, 16:22 [ТС]	5
	Разобрался, в названии поля надо написать Название поля:Null 0

@Capi Модератор 6231 / 2909 / 707 Регистрация: 12.06.2016 Сообщений: 7,839
	08.04.2017, 16:28	6
	Сообщение от Dmitry_Morozov Разобрался, в названии поля надо написать Название поля:Null Это не название поля, а его значение. 0

@Dmitry_Morozov 21 / 4 / 0 Регистрация: 03.01.2017 Сообщений: 127
	08.04.2017, 16:42 [ТС]	7
	Fieldname:null - строка в названии поля в конструкторе - это имел ввиду 0

@Dmitry_Morozov 21 / 4 / 0 Регистрация: 03.01.2017 Сообщений: 127
	08.04.2017, 17:00 [ТС]	9
	Естественно любым другим может быть 0

@Capi Модератор 6231 / 2909 / 707 Регистрация: 12.06.2016 Сообщений: 7,839
	08.04.2017, 17:02	10
	Сообщение от Dmitry_Morozov Естественно любым другим может быть Так Вы же в качестве "найденного решения" приводите: Сообщение от Dmitry_Morozov Разобрался, в названии поля надо написать Название поля:Null , а это, простите, глупость. Не различаете имя поля и значение поля. Покажите текст запроса и результат запроса. 0

@Dmitry_Morozov 21 / 4 / 0 Регистрация: 03.01.2017 Сообщений: 127
	08.04.2017, 17:08 [ТС]	11
	как глупость, то Создание запроса в Access, режим конструктора. Ниже таблица для полей запроса. Первая строка по всем полям - Field, если в нем написать "Нужное название поля":null, то при выполнении запроса это будет пустой столбец с названием "Нужное название поля". 0

@Capi Модератор 6231 / 2909 / 707 Регистрация: 12.06.2016 Сообщений: 7,839
	08.04.2017, 17:15	12
	Ну так Вы и задаете таким образом значение поля. Ладно, со временем, может, поймете... 0

@Dmitry_Morozov 21 / 4 / 0 Регистрация: 03.01.2017 Сообщений: 127
	08.04.2017, 20:20 [ТС]	15
	Mobile, приветствую, спасибо, Ругается, пишет мало параметров, нехватает 1 (это вариант с дмакс) 0

mobile 26815 / 14494 / 3192 Регистрация: 28.04.2012 Сообщений: 15,782
	08.04.2017, 20:30	16
	Сообщение от Dmitry_Morozov Ругается, пишет мало параметров, нехватает 1 (это вариант с дмакс) Я же специально написал Сообщение от mobile Переменная s и Debug.print s введены для возможной последующей отладки распечатанного текста в конструкторе запросов Посмотрите в Immediate (область отладки в редактора ВБА) текст запроса. Скопируйте его, откройте конструктор запросов в режиме SQL и вставьте туда скопированный текст. Попробуйте выполнить. Если будет ошибка, конструктор укажет на нее. 0