EXPORT QUERY *.csv format (1 Viewer)

georg0307

Registered User.
Local time
Today, 13:21
Joined
Sep 11, 2014
Messages
91
Dear All,

I am not an expert of Access.
I have the following query:
SELECT [Ordine_Versamento] & ";;" & [Material] & ";" & [Quantità] & ";;;" AS A, [Ordine_Versamento] & ";" & "V" & ";;;;" AS B
FROM TABELLA_IPOS_VERSAMENTO;

Where I obtain the following result:
A B
PREL_LL;;28LR078921;2;;; PREL_LL;V;;;;
PREL_LL;;28LR078921;5;;; PREL_LL;V;;;;
PREL_LL;;28LR117066;1;;; PREL_LL;V;;;;
PREL_LL;;28LR069697;1;;; PREL_LL;V;;;;
PREL_LL;;28LR032329;1;;; PREL_LL;V;;;;
PREL_LL;;28LR078921;10;;; PREL_LL;V;;;;
PREL_LL;;28LR072650;1;;; PREL_LL;V;;;;
PREL_LL;;28YWX101220;1;;; PREL_LL;V;;;;
PREL_LL;;28LR069697;1;;; PREL_LL;V;;;;
PREL_LL;;28LR069697;4;;; PREL_LL;V;;;;
PREL_LL;;28LR002189;1;;; PREL_LL;V;;;;
PREL_LL;;28LR027666;1;;; PREL_LL;V;;;;
PREL_LL;;28LR027664;1;;; PREL_LL;V;;;;
PREL_LL;;28LR078921;2;;; PREL_LL;V;;;;
PREL_LL;;28LR060915;1;;; PREL_LL;V;;;;
PREL_LL;;28MSC000030E;1;;; PREL_LL;V;;;;
PREL_LL;;28LR078921;4;;; PREL_LL;V;;;;
PREL_LL;;28LR023889;1;;; PREL_LL;V;;;;
PREL_LL;;28LR013005;1;;; PREL_LL;V;;;;
PREL_LL;;28LR078545;1;;; PREL_LL;V;;;;
PREL_LL;;28LR078545;1;;; PREL_LL;V;;;;

What I need is:

PREL_LL;V;;;; (first record)
PREL_LL;;28LR078921;2;;;
PREL_LL;;28LR078921;5;;;
PREL_LL;;28LR117066;1;;;
PREL_LL;;28LR069697;1;;;
PREL_LL;;28LR032329;1;;;
PREL_LL;;28LR078921;10;;;
PREL_LL;;28LR072650;1;;;
PREL_LL;;28YWX101220;1;;;
PREL_LL;;28LR069697;1;;;
PREL_LL;;28LR069697;4;;;
PREL_LL;;28LR002189;1;;;
PREL_LL;;28LR027666;1;;;
PREL_LL;;28LR027664;1;;;
PREL_LL;;28LR078921;2;;;
PREL_LL;;28LR060915;1;;;
PREL_LL;;28MSC000030E;1;;;
PREL_LL;;28LR078921;4;;;
PREL_LL;;28LR023889;1;;;
PREL_LL;;28LR013005;1;;;
PREL_LL;;28LR078545;1;;;
PREL_LL;;28LR078545;1;;;

The issue seems simple, but I don't how to proceed.

DB attached: View attachment VERSAMENTO_PRELIEVO_LL.accdb

Thanks.
Best regards,
Georg
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:21
Joined
Sep 21, 2011
Messages
14,038
Use a UNION query to create one field and use your concatenation to populate that field correctly. ?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:21
Joined
Feb 19, 2013
Messages
16,553
If I've understood correctly you need to use a union query

Code:
SELECT [Ordine_Versamento] & ";" & "V" & ";;;;" 
FROM TABELLA_IPOS_VERSAMENTO
UNION SELECT [Ordine_Versamento] & ";;" & [Material] & ";" & [Quantità] & ";;;" 
FROM TABELLA_IPOS_VERSAMENTO;

Note the number of ; in each query is different -they need to be the same for the query to work
 

June7

AWF VIP
Local time
Today, 04:21
Joined
Mar 9, 2014
Messages
5,423
Consider what happens with a UNION query:

SELECT [Ordine_Versamento] & ";;" & [Material] & ";" & [Quantità] & ";;;" AS A FROM TABELLA_IPOS_VERSAMENTO
UNION SELECT [Ordine_Versamento] & ";" & "V" & ";;;;;;" FROM TABELLA_IPOS_VERSAMENTO;

You should see the records are sorted alphabetically. Maintaining the order in your sample would require an identifier field to control that and that means output contains a value you don't want.

SELECT ID, [Ordine_Versamento] & ";;" & [Material] & ";" & [Quantità] & ";;;" AS A FROM TABELLA_IPOS_VERSAMENTO
UNION SELECT 0, [Ordine_Versamento] & ";" & "V" & ";;;;;;" FROM TABELLA_IPOS_VERSAMENTO;

So save that second version of UNION and use it as source for another query to export.

SELECT QUERY_VERSAMENTO.A
FROM QUERY_VERSAMENTO
ORDER BY QUERY_VERSAMENTO.ID;

Alternative is VBA code using text file object read/write commands to create file.
 
Last edited:

georg0307

Registered User.
Local time
Today, 13:21
Joined
Sep 11, 2014
Messages
91
Dear All,

many thanks for help each time I receive a suggestion, I learn something new.

Best regards,

Georg
 

Users who are viewing this thread

Top Bottom