Вопрос:

MS Access Create Table - усеченное поле Memo

access-vba union-all

89 просмотра

1 ответ

740 Репутация автора

Access 2013

У меня есть несколько журналов, которые объединены с помощью запроса UNION ALL, который написан макросом.

Полученный запрос выглядит следующим образом:

SELECT [zData Navy FY15 AFS].[Journal Voucher ID], Count([zData Navy FY15 AFS].[Journal Voucher ID]) AS [Record Count], First([zData Navy FY15 AFS].[Table Source]) AS [Table Source], First([zData Navy FY15 AFS].[dBranch]) AS [dBranch], First([zData Navy FY15 AFS].[DB_ID]) AS [DB_ID], First([zData Navy FY15 AFS].[Source]) AS [Source], First([zData Navy FY15 AFS].[GF/WCF]) AS [GF/WCF], First([zData Navy FY15 AFS].[Line of Accounting]) AS [Line of Accounting], First([zData Navy FY15 AFS].[Accounting Period]) AS [Accounting Period], First([zData Navy FY15 AFS].[Beginning/Ending Indicator]) AS [Beginning/Ending Indicator], First([zData Navy FY15 AFS].[Reason]) AS [Reason], First([zData Navy FY15 AFS].[DoDFMR Journal Voucher Category]) AS [DoDFMR Journal Voucher Category], First([zData Navy FY15 AFS].[Temporary Indicator]) AS [Temporary Indicator], First([zData Navy FY15 AFS].[Creator User ID]) AS [Creator User ID], First([zData Navy FY15 AFS].[Approval User ID]) AS [Approval User ID], First([zData Navy FY15 AFS].[Created Date]) AS [Created Date], First([zData Navy FY15 AFS].[Approval Date]) AS [Approval Date], First([zData Navy FY15 AFS].[Unsupported Indicator]) AS [Unsupported Indicator], First([zData Navy FY15 AFS].[General Ledger Account Code]) AS [General Ledger Account Code], First([zData Navy FY15 AFS].[SUS ID]) AS [SUS ID], First([zData Navy FY15 AFS].[Partner Code]) AS [Partner Code], Sum([zData Navy FY15 AFS].[Debit Amount]) AS [Debit Amount], Sum([zData Navy FY15 AFS].[Credit Amount]) AS [Credit Amount], First([zData Navy FY15 AFS].[Tie Points IND]) AS [Tie Points IND], First([zData Navy FY15 AFS].[Auto/Manual]) AS [Auto/Manual], First([zData Navy FY15 AFS].[Quarter]) AS [Quarter], First([zData Navy FY15 AFS].[FY]) AS [FY], First([zData Navy FY15 AFS].[Record Use]) AS [Record Use], First([zData Navy FY15 AFS].[Eliminations IND]) AS [Eliminations IND], First([zData Navy FY15 AFS].[Fund Number]) AS [Fund Number], Len(First([zData Navy FY15 AFS].[Reason])) AS [Length] FROM [zData Navy FY15 AFS] GROUP BY [zData Navy FY15 AFS].[Journal Voucher ID], [zData Navy FY15 AFS].[Record Use]
UNION ALL
SELECT [zData Navy FY15 BBA GF].[Journal Voucher ID], Count([zData Navy FY15 BBA GF].[Journal Voucher ID]) AS [Record Count], First([zData Navy FY15 BBA GF].[Table Source]) AS [Table Source], First([zData Navy FY15 BBA GF].[dBranch]) AS [dBranch], First([zData Navy FY15 BBA GF].[DB_ID]) AS [DB_ID], First([zData Navy FY15 BBA GF].[Source]) AS [Source], First([zData Navy FY15 BBA GF].[GF/WCF]) AS [GF/WCF], First([zData Navy FY15 BBA GF].[Line of Accounting]) AS [Line of Accounting], First([zData Navy FY15 BBA GF].[Accounting Period]) AS [Accounting Period], First([zData Navy FY15 BBA GF].[Beginning/Ending Indicator]) AS [Beginning/Ending Indicator], First([zData Navy FY15 BBA GF].[Reason]) AS [Reason], First([zData Navy FY15 BBA GF].[DoDFMR Journal Voucher Category]) AS [DoDFMR Journal Voucher Category], First([zData Navy FY15 BBA GF].[Temporary Indicator]) AS [Temporary Indicator], First([zData Navy FY15 BBA GF].[Creator User ID]) AS [Creator User ID], First([zData Navy FY15 BBA GF].[Approval User ID]) AS [Approval User ID], First([zData Navy FY15 BBA GF].[Created Date]) AS [Created Date], First([zData Navy FY15 BBA GF].[Approval Date]) AS [Approval Date], First([zData Navy FY15 BBA GF].[Unsupported Indicator]) AS [Unsupported Indicator], First([zData Navy FY15 BBA GF].[General Ledger Account Code]) AS [General Ledger Account Code], First([zData Navy FY15 BBA GF].[SUS ID]) AS [SUS ID], First([zData Navy FY15 BBA GF].[Partner Code]) AS [Partner Code], Sum([zData Navy FY15 BBA GF].[Debit Amount]) AS [Debit Amount], Sum([zData Navy FY15 BBA GF].[Credit Amount]) AS [Credit Amount], First([zData Navy FY15 BBA GF].[Tie Points IND]) AS [Tie Points IND], First([zData Navy FY15 BBA GF].[Auto/Manual]) AS [Auto/Manual], First([zData Navy FY15 BBA GF].[Quarter]) AS [Quarter], First([zData Navy FY15 BBA GF].[FY]) AS [FY], First([zData Navy FY15 BBA GF].[Record Use]) AS [Record Use], First([zData Navy FY15 BBA GF].[Eliminations IND]) AS [Eliminations IND], First([zData Navy FY15 BBA GF].[Fund Number]) AS [Fund Number], Len(First([zData Navy FY15 BBA GF].[Reason])) AS [Length] FROM [zData Navy FY15 BBA GF] GROUP BY [zData Navy FY15 BBA GF].[Journal Voucher ID], [zData Navy FY15 BBA GF].[Record Use]
UNION ALL
SELECT [zData Navy FY15 BE].[Journal Voucher ID], Count([zData Navy FY15 BE].[Journal Voucher ID]) AS [Record Count], First([zData Navy FY15 BE].[Table Source]) AS [Table Source], First([zData Navy FY15 BE].[dBranch]) AS [dBranch], First([zData Navy FY15 BE].[DB_ID]) AS [DB_ID], First([zData Navy FY15 BE].[Source]) AS [Source], First([zData Navy FY15 BE].[GF/WCF]) AS [GF/WCF], First([zData Navy FY15 BE].[Line of Accounting]) AS [Line of Accounting], First([zData Navy FY15 BE].[Accounting Period]) AS [Accounting Period], First([zData Navy FY15 BE].[Beginning/Ending Indicator]) AS [Beginning/Ending Indicator], First([zData Navy FY15 BE].[Reason]) AS [Reason], First([zData Navy FY15 BE].[DoDFMR Journal Voucher Category]) AS [DoDFMR Journal Voucher Category], First([zData Navy FY15 BE].[Temporary Indicator]) AS [Temporary Indicator], First([zData Navy FY15 BE].[Creator User ID]) AS [Creator User ID], First([zData Navy FY15 BE].[Approval User ID]) AS [Approval User ID], First([zData Navy FY15 BE].[Created Date]) AS [Created Date], First([zData Navy FY15 BE].[Approval Date]) AS [Approval Date], First([zData Navy FY15 BE].[Unsupported Indicator]) AS [Unsupported Indicator], First([zData Navy FY15 BE].[General Ledger Account Code]) AS [General Ledger Account Code], First([zData Navy FY15 BE].[SUS ID]) AS [SUS ID], First([zData Navy FY15 BE].[Partner Code]) AS [Partner Code], Sum([zData Navy FY15 BE].[Debit Amount]) AS [Debit Amount], Sum([zData Navy FY15 BE].[Credit Amount]) AS [Credit Amount], First([zData Navy FY15 BE].[Tie Points IND]) AS [Tie Points IND], First([zData Navy FY15 BE].[Auto/Manual]) AS [Auto/Manual], First([zData Navy FY15 BE].[Quarter]) AS [Quarter], First([zData Navy FY15 BE].[FY]) AS [FY], First([zData Navy FY15 BE].[Record Use]) AS [Record Use], First([zData Navy FY15 BE].[Eliminations IND]) AS [Eliminations IND], First([zData Navy FY15 BE].[Fund Number]) AS [Fund Number], Len(First([zData Navy FY15 BE].[Reason])) AS [Length] FROM [zData Navy FY15 BE] GROUP BY [zData Navy FY15 BE].[Journal Voucher ID], [zData Navy FY15 BE].[Record Use]
UNION ALL
SELECT [zData Navy FY15 WCF BBA].[Journal Voucher ID], Count([zData Navy FY15 WCF BBA].[Journal Voucher ID]) AS [Record Count], First([zData Navy FY15 WCF BBA].[Table Source]) AS [Table Source], First([zData Navy FY15 WCF BBA].[dBranch]) AS [dBranch], First([zData Navy FY15 WCF BBA].[DB_ID]) AS [DB_ID], First([zData Navy FY15 WCF BBA].[Source]) AS [Source], First([zData Navy FY15 WCF BBA].[GF/WCF]) AS [GF/WCF], First([zData Navy FY15 WCF BBA].[Line of Accounting]) AS [Line of Accounting], First([zData Navy FY15 WCF BBA].[Accounting Period]) AS [Accounting Period], First([zData Navy FY15 WCF BBA].[Beginning/Ending Indicator]) AS [Beginning/Ending Indicator], First([zData Navy FY15 WCF BBA].[Reason]) AS [Reason], First([zData Navy FY15 WCF BBA].[DoDFMR Journal Voucher Category]) AS [DoDFMR Journal Voucher Category], First([zData Navy FY15 WCF BBA].[Temporary Indicator]) AS [Temporary Indicator], First([zData Navy FY15 WCF BBA].[Creator User ID]) AS [Creator User ID], First([zData Navy FY15 WCF BBA].[Approval User ID]) AS [Approval User ID], First([zData Navy FY15 WCF BBA].[Created Date]) AS [Created Date], First([zData Navy FY15 WCF BBA].[Approval Date]) AS [Approval Date], First([zData Navy FY15 WCF BBA].[Unsupported Indicator]) AS [Unsupported Indicator], First([zData Navy FY15 WCF BBA].[General Ledger Account Code]) AS [General Ledger Account Code], First([zData Navy FY15 WCF BBA].[SUS ID]) AS [SUS ID], First([zData Navy FY15 WCF BBA].[Partner Code]) AS [Partner Code], Sum([zData Navy FY15 WCF BBA].[Debit Amount]) AS [Debit Amount], Sum([zData Navy FY15 WCF BBA].[Credit Amount]) AS [Credit Amount], First([zData Navy FY15 WCF BBA].[Tie Points IND]) AS [Tie Points IND], First([zData Navy FY15 WCF BBA].[Auto/Manual]) AS [Auto/Manual], First([zData Navy FY15 WCF BBA].[Quarter]) AS [Quarter], First([zData Navy FY15 WCF BBA].[FY]) AS [FY], First([zData Navy FY15 WCF BBA].[Record Use]) AS [Record Use], First([zData Navy FY15 WCF BBA].[Eliminations IND]) AS [Eliminations IND], First([zData Navy FY15 WCF BBA].[Fund Number]) AS [Fund Number], Len(First([zData Navy FY15 WCF BBA].[Reason])) AS [Length] FROM [zData Navy FY15 WCF BBA] GROUP BY [zData Navy FY15 WCF BBA].[Journal Voucher ID], [zData Navy FY15 WCF BBA].[Record Use]
UNION ALL
SELECT [zData USMC FY15 AFS].[Journal Voucher ID], Count([zData USMC FY15 AFS].[Journal Voucher ID]) AS [Record Count], First([zData USMC FY15 AFS].[Table Source]) AS [Table Source], First([zData USMC FY15 AFS].[dBranch]) AS [dBranch], First([zData USMC FY15 AFS].[DB_ID]) AS [DB_ID], First([zData USMC FY15 AFS].[Source]) AS [Source], First([zData USMC FY15 AFS].[GF/WCF]) AS [GF/WCF], First([zData USMC FY15 AFS].[Line of Accounting]) AS [Line of Accounting], First([zData USMC FY15 AFS].[Accounting Period]) AS [Accounting Period], First([zData USMC FY15 AFS].[Beginning/Ending Indicator]) AS [Beginning/Ending Indicator], First([zData USMC FY15 AFS].[Reason]) AS [Reason], First([zData USMC FY15 AFS].[DoDFMR Journal Voucher Category]) AS [DoDFMR Journal Voucher Category], First([zData USMC FY15 AFS].[Temporary Indicator]) AS [Temporary Indicator], First([zData USMC FY15 AFS].[Creator User ID]) AS [Creator User ID], First([zData USMC FY15 AFS].[Approval User ID]) AS [Approval User ID], First([zData USMC FY15 AFS].[Created Date]) AS [Created Date], First([zData USMC FY15 AFS].[Approval Date]) AS [Approval Date], First([zData USMC FY15 AFS].[Unsupported Indicator]) AS [Unsupported Indicator], First([zData USMC FY15 AFS].[General Ledger Account Code]) AS [General Ledger Account Code], First([zData USMC FY15 AFS].[SUS ID]) AS [SUS ID], First([zData USMC FY15 AFS].[Partner Code]) AS [Partner Code], Sum([zData USMC FY15 AFS].[Debit Amount]) AS [Debit Amount], Sum([zData USMC FY15 AFS].[Credit Amount]) AS [Credit Amount], First([zData USMC FY15 AFS].[Tie Points IND]) AS [Tie Points IND], First([zData USMC FY15 AFS].[Auto/Manual]) AS [Auto/Manual], First([zData USMC FY15 AFS].[Quarter]) AS [Quarter], First([zData USMC FY15 AFS].[FY]) AS [FY], First([zData USMC FY15 AFS].[Record Use]) AS [Record Use], First([zData USMC FY15 AFS].[Eliminations IND]) AS [Eliminations IND], First([zData USMC FY15 AFS].[Fund Number]) AS [Fund Number], Len(First([zData USMC FY15 AFS].[Reason])) AS [Length] FROM [zData USMC FY15 AFS] GROUP BY [zData USMC FY15 AFS].[Journal Voucher ID], [zData USMC FY15 AFS].[Record Use]
UNION ALL
SELECT [zData USMC FY15 BE].[Journal Voucher ID], Count([zData USMC FY15 BE].[Journal Voucher ID]) AS [Record Count], First([zData USMC FY15 BE].[Table Source]) AS [Table Source], First([zData USMC FY15 BE].[dBranch]) AS [dBranch], First([zData USMC FY15 BE].[DB_ID]) AS [DB_ID], First([zData USMC FY15 BE].[Source]) AS [Source], First([zData USMC FY15 BE].[GF/WCF]) AS [GF/WCF], First([zData USMC FY15 BE].[Line of Accounting]) AS [Line of Accounting], First([zData USMC FY15 BE].[Accounting Period]) AS [Accounting Period], First([zData USMC FY15 BE].[Beginning/Ending Indicator]) AS [Beginning/Ending Indicator], First([zData USMC FY15 BE].[Reason]) AS [Reason], First([zData USMC FY15 BE].[DoDFMR Journal Voucher Category]) AS [DoDFMR Journal Voucher Category], First([zData USMC FY15 BE].[Temporary Indicator]) AS [Temporary Indicator], First([zData USMC FY15 BE].[Creator User ID]) AS [Creator User ID], First([zData USMC FY15 BE].[Approval User ID]) AS [Approval User ID], First([zData USMC FY15 BE].[Created Date]) AS [Created Date], First([zData USMC FY15 BE].[Approval Date]) AS [Approval Date], First([zData USMC FY15 BE].[Unsupported Indicator]) AS [Unsupported Indicator], First([zData USMC FY15 BE].[General Ledger Account Code]) AS [General Ledger Account Code], First([zData USMC FY15 BE].[SUS ID]) AS [SUS ID], First([zData USMC FY15 BE].[Partner Code]) AS [Partner Code], Sum([zData USMC FY15 BE].[Debit Amount]) AS [Debit Amount], Sum([zData USMC FY15 BE].[Credit Amount]) AS [Credit Amount], First([zData USMC FY15 BE].[Tie Points IND]) AS [Tie Points IND], First([zData USMC FY15 BE].[Auto/Manual]) AS [Auto/Manual], First([zData USMC FY15 BE].[Quarter]) AS [Quarter], First([zData USMC FY15 BE].[FY]) AS [FY], First([zData USMC FY15 BE].[Record Use]) AS [Record Use], First([zData USMC FY15 BE].[Eliminations IND]) AS [Eliminations IND], First([zData USMC FY15 BE].[Fund Number]) AS [Fund Number], Len(First([zData USMC FY15 BE].[Reason])) AS [Length] FROM [zData USMC FY15 BE] GROUP BY [zData USMC FY15 BE].[Journal Voucher ID], [zData USMC FY15 BE].[Record Use]
UNION ALL
SELECT [zData USMC FY15 WCF BBA].[Journal Voucher ID], Count([zData USMC FY15 WCF BBA].[Journal Voucher ID]) AS [Record Count], First([zData USMC FY15 WCF BBA].[Table Source]) AS [Table Source], First([zData USMC FY15 WCF BBA].[dBranch]) AS [dBranch], First([zData USMC FY15 WCF BBA].[DB_ID]) AS [DB_ID], First([zData USMC FY15 WCF BBA].[Source]) AS [Source], First([zData USMC FY15 WCF BBA].[GF/WCF]) AS [GF/WCF], First([zData USMC FY15 WCF BBA].[Line of Accounting]) AS [Line of Accounting], First([zData USMC FY15 WCF BBA].[Accounting Period]) AS [Accounting Period], First([zData USMC FY15 WCF BBA].[Beginning/Ending Indicator]) AS [Beginning/Ending Indicator], First([zData USMC FY15 WCF BBA].[Reason]) AS [Reason], First([zData USMC FY15 WCF BBA].[DoDFMR Journal Voucher Category]) AS [DoDFMR Journal Voucher Category], First([zData USMC FY15 WCF BBA].[Temporary Indicator]) AS [Temporary Indicator], First([zData USMC FY15 WCF BBA].[Creator User ID]) AS [Creator User ID], First([zData USMC FY15 WCF BBA].[Approval User ID]) AS [Approval User ID], First([zData USMC FY15 WCF BBA].[Created Date]) AS [Created Date], First([zData USMC FY15 WCF BBA].[Approval Date]) AS [Approval Date], First([zData USMC FY15 WCF BBA].[Unsupported Indicator]) AS [Unsupported Indicator], First([zData USMC FY15 WCF BBA].[General Ledger Account Code]) AS [General Ledger Account Code], First([zData USMC FY15 WCF BBA].[SUS ID]) AS [SUS ID], First([zData USMC FY15 WCF BBA].[Partner Code]) AS [Partner Code], Sum([zData USMC FY15 WCF BBA].[Debit Amount]) AS [Debit Amount], Sum([zData USMC FY15 WCF BBA].[Credit Amount]) AS [Credit Amount], First([zData USMC FY15 WCF BBA].[Tie Points IND]) AS [Tie Points IND], First([zData USMC FY15 WCF BBA].[Auto/Manual]) AS [Auto/Manual], First([zData USMC FY15 WCF BBA].[Quarter]) AS [Quarter], First([zData USMC FY15 WCF BBA].[FY]) AS [FY], First([zData USMC FY15 WCF BBA].[Record Use]) AS [Record Use], First([zData USMC FY15 WCF BBA].[Eliminations IND]) AS [Eliminations IND], First([zData USMC FY15 WCF BBA].[Fund Number]) AS [Fund Number], Len(First([zData USMC FY15 WCF BBA].[Reason])) AS [Length] FROM [zData USMC FY15 WCF BBA] GROUP BY [zData USMC FY15 WCF BBA].[Journal Voucher ID], [zData USMC FY15 WCF BBA].[Record Use];

Поле «Reason» - это поле Memo.

Оператор SQL сохраняется как запрос: «Экспорт данных», а затем таблица, построенная с использованием:

DoCmd.RunSQL "SELECT * INTO [DATA OUTPUT TABLE] FROM [Data Export];"

Отдельные операторы выбора дают результаты с правильной длиной, а поле [Длина] заполняется длиной исходной строки, но запрос UNION ALL возвращает <= 255 строк символов. Похоже, что для набора данных «Экспорт данных» по умолчанию используется «Короткий текст».

Автор: Schalton Источник Размещён: 22.08.2016 09:07

Ответы (1)


0 плюса

4205 Репутация автора

Решение

Ваше поле REASON является вычисляемым полем, а First([zData Navy FY15 AFS].[Reason]) AS [Reason]не фактическим полем напоминания

Вам нужно помочь вашему запросу UNION ALL, сказав, что это памятное поле

Добавьте пустую строку в качестве первого предложения SELECT и сделайте REASON фактическим мемо-полем

Как насчет его последнего предложения о добавлении пустой строки в начало - скопируйте и вставьте это в верхнюю часть вашего запроса

РЕДАКТИРОВАТЬ - Удалена функция "COUNT ()"

SELECT [zData Navy FY15 AFS].[Journal Voucher ID], [zData Navy FY15 AFS].[Journal Voucher ID] AS [Record Count], [zData Navy FY15 AFS].[Table Source] AS [Table Source], 
[zData Navy FY15 AFS].[dBranch] AS [dBranch], [zData Navy FY15 AFS].[DB_ID] AS [DB_ID], [zData Navy FY15 AFS].[Source] AS [Source], [zData Navy FY15 AFS].[GF/WCF] AS [GF/WCF], 
[zData Navy FY15 AFS].[Line of Accounting] AS [Line of Accounting], [zData Navy FY15 AFS].[Accounting Period] AS [Accounting Period], 
[zData Navy FY15 AFS].[Beginning/Ending Indicator] AS [Beginning/Ending Indicator], [zData Navy FY15 AFS].[Reason] AS [Reason], 
[zData Navy FY15 AFS].[DoDFMR Journal Voucher Category] AS [DoDFMR Journal Voucher Category], [zData Navy FY15 AFS].[Temporary Indicator] AS [Temporary Indicator], 
[zData Navy FY15 AFS].[Creator User ID] AS [Creator User ID], [zData Navy FY15 AFS].[Approval User ID] AS [Approval User ID], 
[zData Navy FY15 AFS].[Created Date] AS [Created Date], [zData Navy FY15 AFS].[Approval Date] AS [Approval Date], 
[zData Navy FY15 AFS].[Unsupported Indicator] AS [Unsupported Indicator], [zData Navy FY15 AFS].[General Ledger Account Code] AS [General Ledger Account Code], 
[zData Navy FY15 AFS].[SUS ID] AS [SUS ID], [zData Navy FY15 AFS].[Partner Code] AS [Partner Code], [zData Navy FY15 AFS].[Debit Amount] AS [Debit Amount], 
[zData Navy FY15 AFS].[Credit Amount] AS [Credit Amount], [zData Navy FY15 AFS].[Tie Points IND] AS [Tie Points IND], 
[zData Navy FY15 AFS].[Auto/Manual] AS [Auto/Manual], [zData Navy FY15 AFS].[Quarter] AS [Quarter], [zData Navy FY15 AFS].[FY] AS [FY], 
[zData Navy FY15 AFS].[Record Use] AS [Record Use], [zData Navy FY15 AFS].[Eliminations IND] AS [Eliminations IND], 
[zData Navy FY15 AFS].[Fund Number] AS [Fund Number], Len([zData Navy FY15 AFS].[Reason])) AS [Length] 
FROM [zData Navy FY15 AFS] Where (False)

UNION ALL
Автор: dbmitch Размещён: 22.08.2016 10:12
Вопросы из категории :
32x32