Union Query - Multi valued Field (Attachment)

[andrewpogi]

Hi,

Im new to access, recently I have created a table of payments (Year, Voucher No, Payee, Voucher_Copy, etc.) with column for Voucher_Copy as attachments - in pdf file. I created a table for each year because the database would be huge if I would combine the years. So the table header for each year are the same.

Then, I created another file to have a query for each table table and created a union query, the problem after I created the union query and run it I stumble an error message saying "multi-valued field (attachment field - with pdf attachment) cannot be used in a union query.

below is the copy of the sql statement, appreciate of any help from the experts.

Many thanks in advance..

SELECT PAYMENTS_2013.['YEAR], PAYMENTS_2013.[BANK], PAYMENTS_2013.[DOC NO], PAYMENTS_2013.[VCHR NO], PAYMENTS_2013.['DATE], PAYMENTS_2013.[STATUS], PAYMENTS_2013.[CUR], PAYMENTS_2013.[AMOUNT], PAYMENTS_2013.[PAYEE], PAYMENTS_2013.[DESCRIPTION], PAYMENTS_2013.[VOUCHER_COPY], PAYMENTS_2013.[VOUCHER_COPY].[FileData], [VOUCHER_COPY].[FileFlags] AS Expr1, PAYMENTS_2013.[VOUCHER_COPY].[FileName], [VOUCHER_COPY].[FileTimeStamp] AS Expr2, PAYMENTS_2013.[VOUCHER_COPY].[FileType], [VOUCHER_COPY].[FileURL] AS Expr3, PAYMENTS_2013.[PRE_EXP], PAYMENTS_2013.[FA], PAYMENTS_2013.[FA_NO]
FROM PAYMENTS_2013
UNION ALL
SELECT PAYMENTS_2014.['YEAR], PAYMENTS_2014.[BANK], PAYMENTS_2014.[DOC NO], PAYMENTS_2014.[VCHR NO], PAYMENTS_2014.['DATE], PAYMENTS_2014.[STATUS], PAYMENTS_2014.[CUR], PAYMENTS_2014.[AMOUNT], PAYMENTS_2014.[PAYEE], PAYMENTS_2014.[DESCRIPTION], PAYMENTS_2014.[VOUCHER_COPY], PAYMENTS_2014.[VOUCHER_COPY].[FileData], [VOUCHER_COPY].[FileFlags] AS Expr1, PAYMENTS_2014.[VOUCHER_COPY].[FileName], [VOUCHER_COPY].[FileTimeStamp] AS Expr2, PAYMENTS_2014.[VOUCHER_COPY].[FileType], [VOUCHER_COPY].[FileURL] AS Expr3, PAYMENTS_2014.[PRE_EXP], PAYMENTS_2014.[FA], PAYMENTS_2014.[FA_NO]
FROM PAYMENTS_2014;

 

[arnelgp]

you do same with your first SELECT statement, pare:


PAYMENTS_2014.[VOUCHER_COPY].[FileData], [VOUCHER_COPY].[FileFlags] AS Expr1, PAYMENTS_2013.[VOUCHER_COPY].[FileName]


**
in fact you only need to show the [FileName] for this attachement field.


PAYMENTS_2013.[FileName]
PAYMENTS_2014.[FileName]

 

[andrewpogi]

Sir Arnel,

Many thanks for the quick help, I tried it, but I cannot view the attachment, I can only see the file name of the attachment in the column. I there a way to see the attach file.

Thanks in advance Sir.

 

[plog]

I created a table for each year because the database would be huge if I would combine the years.

"Huge" is a pretty ambiguous term. Can you put a number and unit on it (e.g. 40 kb, 56 MB, 1.7 GB)? And if you can't, then what issues are you having with a "huge" table?

I think you need to put this all into one table. You didn't mention any issues with all of it in one table, however, here you are with an issue because you are using multiple tables.

 

[arnelgp]

create your union but dont include the Attachment Field, only include the Attachment's FileName.
save this union query.
create another query. add the union query you made above and table PAYMENT_2013 and table PAYMENT_2014.
add all the fields from the Union Query to this new query.

relate union query to the two table using [YEAR] and [DOC NO] and [Attachment.FileName] as the linking fields.
right click on each line to invoke Join Properties.
select (2) from the options.
add the [VOUCHER_COPY] (attachment field) from both year table to this new query.
run your query, but this will show two attachments.

 

[andrewpogi]

1 year data is almost 1.2 GB because of the attachments, and if I will put the 2 years data in 1 table the file would be more than the 2gb limit of access. Any advice.

 

[andrewpogi]

Mr. Arnel, Thanks a lot for the steps provided, the solution works fine with me. many thanks.

 

[Pat Hartman]

I would not store the attachments in the Access database. Keep the attachments in a network folder and only store the document name including the full path. That will dramatically reduce the size of the database.