Excel/msquery+sql+vba

[vanillavera]

Уважаемые Гуру и все кто сможет помочь...

Не уверена что выбрала правильный раздел..

Расскажу суть проблемы с самого начала.
В организации 1С ТиС7.7. Много пользователей, работает вяло, минимальный объем данных выгружается достаточно долго.
Но при этом большинство пользователей извлекает по большей части сходные данные, просто по-разному организованные.
Возникла идея ограничить доступ пользователям к 1С, обеспечив их при этом необходимыми данными в виде готовых динамических отчетов в Excel(в форме сводных таблиц, чтобы каждый смотрел как ему удобно). Для этого выгружать некие срезы БД (за день, неделю, месяц),  делать из них выбор необходимой информации посредством SQL запроса в готовые отчеты, где потом посредством макросов рассчитывать необходимые аналитики.
Данные выгружаются в DBF файлы.
Ввиду моего скудного знания SQL(и написания запросов SQL на VBA) запросы делаю посредством MS Query.
И собственно всё неплохо, если требуется простое соединение таблиц MS Query справляется нормально, все работает.
Проблемы возникают, если нужно сделать полное внешнее соединение таблиц. MS Query умирает.
Да собственно и если соединять более двух таблиц уже та же история.
Что вообще лучше сделать?
Если запрос SQL будет написан текстом непосредственно в макросе, то больше шансов что он выполнится, чем с использованием MS Query?
Если не использовать выгрузку в DBF файлы, а подключаться к тем же данным в SQL формате на сервере, поможет ли это? ускорит процесс? есть шанс, что  MS Query сможет с этим справится?

А может есть какие более изящные решения, подскажите пожалуйста!

[Dimka]

если нужно сделать полное внешнее соединение таблиц. MS Query умирает.
Да собственно и если соединять более двух таблиц уже та же история.

Не очень понятно, что именно ты под этим подразумеваешь. Можешь построить запрос в MS Query, не выполняя его, и потом скопировать сюда SQL код твоего запроса?

У меня есть подозрение, что умирает всё из-за попытки получить полное декартово произведение двух и более таблиц.

[vanillavera]

У меня есть подозрение, что умирает всё из-за попытки получить полное декартово произведение двух и более таблиц.

Не думаю...
SELECT access.ZONA, access.KANAL, Sum(access.SUMPROD) AS 'Продажи', Sum(access.SUMDOG) AS 'Продажи_в_ДогЦен'
FROM {oj access access LEFT OUTER JOIN `Y:\Червакова\Выгрузки\Неделя1`\dz.DBF dz ON access.KODCLI = dz.KODCLI}
GROUP BY access.ZONA, access.KANAL
ORDER BY access.ZONA, access.KANAL
UNION
SELECT access.ZONA, access.KANAL, Sum(access.SUMPROD) AS 'Продажи', Sum(access.SUMDOG) AS 'Продажи_в_ДогЦен'
FROM access access, `Y:\Червакова\Выгрузки\Неделя1`\dz.DBF dz
WHERE access.KODCLI <> dz.KODCLI
GROUP BY access.ZONA, access.KANAL
ORDER BY access.ZONA, access.KANAL

[Dimka]

FROM access access, `Y:\Червакова\Выгрузки\Неделя1`\dz.DBF dz
WHERE access.KODCLI <> dz.KODCLI

Ты это называешь "полным внешним соединением"?

Это как раз и есть полное декартово произведение за вычетом внутреннего соединения. Результат будет размером от M*N-max(N,M) до M*N.

Сколько записей в таблицах? Например, 1000 на 1000 в таком запросе даст результат в 999000 записей.

Ты лучше объясни цель второго запроса (после UNION) - что ты хочешь получить? Тогда можно будет подобрать менее тяжёлое для машины решение.

[vanillavera]

FROM access access, `Y:\Червакова\Выгрузки\Неделя1`\dz.DBF dz
WHERE access.KODCLI <> dz.KODCLI

Ты это называешь "полным внешним соединением"?

Это как раз и есть полное декартово произведение за вычетом внутреннего соединения. Результат будет размером от M*N-max(N,M) до M*N.

Сколько записей в таблицах? Например, 1000 на 1000 в таком запросе даст результат в 999000 записей.

Ты лучше объясни цель второго запроса (после UNION) - что ты хочешь получить? Тогда можно будет подобрать менее тяжёлое для машины решение.

а как правильно?
хочу получить все данные из таблиц access и dz в одной таблице, при этом поля ZONA,KANAL должны быть общими и содержать как значения для непустых access.SUMPROD access.SUMDOG, так и для непустых DZ из DZ...
как-то так

[Dimka]

vanillavera, вопрос не понял.

В тобою приведённых запросах таблица dz не фигурирует ни в результатах, ни в группировках, ни в условии отбора записей из таблицы access. В таком виде она не нужна.

Лучше на словах (без MS Query) опиши структуру таблиц access и dz и объясни:
1) По каким полям связываются или не связываются таблицы.
2) Какие именно поля ты хочешь получить в результате.
3) Какие группировки (и группировочные функции - суммы, количества, средние, минимумы, максимумы и т.п.), какие условия и какие сортировки тебе нужны.

При описании таблиц не просто перечисли названия полей, а опиши словами смысл данных в каждом поле: канал, зона, сумма продаж, сумма договоров, код клиента и т.п. Заодно поясни, что такое "канал", "зона". Это всё называется предметной областью и формулировкой проблемы.

Пример:

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

Выявить продавцов, чьи продажи с начала года превышают средние более, чем на 10%.

Формулировка соответствующего проблеме запроса к базе данных:

Информация о продавцах хранится в таблице Salesman. Внутри этой таблицы: информация о накопленной сумме продаж с начала года для каждого продавца хранится в поле YearToDateSales. Имя продавца хранится в поле Name.

В результате должна получиться таблица с одним полем EffectSalesman, в которой перечислены интересующие имена продавцов.

Для определения средних продаж на каждого продавца нужно вычислить среднее значение поля Salesman.YearToDateSales по всей таблице Salesman. Полученное значение нужно умножить на коэффициент 1.1 и затем найти в таблице Salesman такие записи, значения поля YearToDateSales которых больше вычисленного значения. В найденных записях поле Name включить в результат.

Вот примерно такого рода описание хочется увидеть.


Если двигаться дальше, на языке SQL согласно описанию получается следующий запрос:

Код: (SQL)

SELECT Salesman.Name AS EffectSalesman
FROM
  Salesman,
  (
    SELECT AVG(Salesman.YearToDateSales) AS VALUE
    FROM Salesman
  ) AS AverageSales
WHERE Salesman.YearToDateSales > AverageSales.VALUE * 1.1

Здесь используется подзапрос, который из таблицы Salesman получает как бы "временную" таблицу AverageSales, состоящую из 1 поля и 1 записи. Эта "временная" таблица соединяется с таблицей Salesman как полное декартово произведение, так что в результате к таблице Salesman будто бы прибавляют дополнительное поле AverageSales.Value, в котором хранится средняя сумма продаж с начала года в расчёте на одного продавца. Затем по условию отбираются нужные записи. И, наконец, из полученных записей оставляется лишь поле Salesman.Name которое переименовывается в нужное EffectSalesman.