ник: Explorer
много сломано копий вокруг такой задачи как указание фэйкового (Fake) порядкового номера записи для номерации строки запроса. Во всех специализированных форумах гуру разработки приложений БД постоянно подтрунивают над новичками.
<skiped>
в общем количество аргументов говорящих не в пользу таких решений весьма велико, тем не менее в ряде случаев такая нумерация просто необходима и весьма удобна.
предположим (может излишне смелое предположение, но для меня регулярная ежедневная задача) у вас есть файл EXCEL содержащий 2-7-10'000 записей о событиях - на листе по порядку перечислены:
DATE | PLACE | RATE
----------+-----------+----------
12/09/06 | 1 | 1.00
12/09/06 | 0 | 1.07
12/09/06 | 3 | 1.88
17/09/06 | 0 | 1.00
17/09/06 | 2 | 1.13
18/09/06 | 1 | 1.43
19/09/06 | 4 | 1.99
19/09/06 | 1 | 1.75
19/09/06 | 0 | 1.08
19/09/06 | 2 | 1.03
19/09/06 | 3 | 1.56
20/09/06 | 1 | 1.98
20/09/06 | 0 | 1.77
|
для документа Excel таая структура вполне нормальна - последовательность события определяется порядком записи. Имея такую таблицу легко расчитать, например сумму с накопленным итогом или среднее отклонение коэффициента на дату или накопленный результирующий коэффициент на дату или на каждое следующее событие (сумма нарастающим итогом например)
при чем в Excel это решается легко и непринужденно - там к предыдущей ячеке можно обратиться явно, чего не скажешь про решение в Access - сто вопросов в форумах - как посчитать значение "Текущая запись" + "Предыдущая запись"? а если нарушен порядок сортировки? а если в таблице есть пропущенные значения?
Кроме того, и об этом новичкам говорят постоянно - принцип хранения и сортировки данных в Access и в Excel (в DataBase и SpreadSheet) совершенно различен - и то, что кажется само собой разумеющимся в одном случае не будет работать или даст неверный результат в другом - будет нарушена последовательность счетчика при пересортировке, например, или Агрегатные функции вообще не будут работать...
в некоторых случаях можно сначала экспортировать даные из Excel в таблицы Access одновременно присваивая им идентификаторы или уже после экспорта данных модифицировать таблицу Access добавив поле счетчика (это совершенно разные подходы) записи получат уникальный идетификатор и с ними можно будет работать точно так как с любыми другими массивами данных в Acces обращаясь к полям записи в запросе.
но это может оказаться слишком хлопотно и громоздко - какой смысл экспортировать данные, если нужно всего три секунды на то, чтобы их обработать, агрегировать, извлечь информацию и выкинуть. (даже не упоминая о потоянно пухнущей при этих операциях базе - которую придется постоянно сжимать и сбрасывать счетчики Autoincrement).
А если в исходной таблице 65536 записей? например данные за 10 лет всего по 10 событий на каждый день, займут 36500 строк в таблице Excel
но можно и не импортивать данные а просто прилинковать таблицы Excel и нумеровать сроки уже в запросе основанном на такой прилинкованной таблице - при этом порядок сортировки (последовательность записей) будет всегда остваться той, что задана в файле Excel.
на примере приведенных выше данных - допустим вам нужно присвоить порядковый номер событиям перечисленным в таблице Excel. Последовательно +1 в пределах каждого одного дня, со сбросом счетчика каждый новый день
COUNTER | DATE | PLACE | RATE
---------+----------+-----------+----------
1 | 12/09/06 | 1 | 1.00
2 | 12/09/06 | 0 | 1.07
3 | 12/09/06 | 3 | 1.88
1 | 17/09/06 | 0 | 1.00
2 | 17/09/06 | 2 | 1.13
1 | 18/09/06 | 1 | 1.43
1 | 19/09/06 | 4 | 1.99
2 | 19/09/06 | 1 | 1.75
3 | 19/09/06 | 0 | 1.08
4 | 19/09/06 | 2 | 1.03
5 | 19/09/06 | 3 | 1.56
1 | 20/09/06 | 1 | 1.98
2 | 20/09/06 | 0 | 1.77
|
если просто экспортировать данные в Access, то при пересортировке таблицы может нарушиться последовательность событий и, например, расчеты основанные на данных предыдущих приодов окажутся выполненными некорректно.
если предварительно подготавливать данные в таблице Excel - можно легко ошибиться - неверно указать номер, пропустить дату и не сбросить счетчик номера в "1"
Но если просто прилинковать таблицу Excel и используя ее в качестве источника строк, выполнить нехитрый запрос, вида (пример):
SELECT
LINKED.DATE, numerate([DATE]) AS COUNTER, LINKED.PLACE, LINLKED.RATE
FROM LINKED
WHERE (((LINKED.DATE) IS NOT NULL) AND ((firstnum())=TRUE))
ORDER BY LINKED.DATE, COUNTER;
|
обращающийся к PUBLIC функциям нумерации, вынесенными в отдельный модуль basNumerate
в декларациях модуля должны быть объявлены переменные:
Option Compare Database
Option Explicit
Private curNum As Long - текущее значение счетчика
Private curDate As Date - текущее значение даты
|
функция numerate (получает в качестве аргумента значение даты текущей записи из запроса):
Public Function numerate (DATE As Date) As Long
If curDate <> DATE Then 'проверяем равна ли дата предыдущей
curNum = 1 ' если нет начинаем считать COUNTER с "1" - т.е. новый или первый день
Else
curNum = curNum + 1 ' если дата равна предшествующей (тот же день) увеличиваем счетчик на "1"
End If
COUNTER = curNum - записываем значение счетчика
curDate = ActionDate - запоминаем значение даты
End Function
|
функция firstnum (вспомогательная функция в условии WHERE нужна для того, чтобы зайти в каждую запись - не возвращает данных в запрос)
Public Function firstnum() As Boolean
firstnum = True
End Function
|
пронумеровав таким образом записи в запросе для расчетов,
можно будет применять все инструменты Access, в том числе агрегатные функции (SUM, AVG) и агрегатные функции домена (DLOOKUP, DMIN), обращаясь к записи по ее идентификатору (COUNTER & DATE дадут уникальный идентификатор записи)
если не использовать проверку на дату и не переустанавливать счетчик в "1" то такого рода запрос пронумерует все записи подряд - в той последовательности как они идут в таблице Excel.
работает достаточно быстро