Exporting a table field as a separate standalone row (better explained with pictures!) (1 Viewer)

peskywinnets

Registered User.
Local time
Today, 17:09
Joined
Feb 4, 2014
Messages
576
I have a query sourced from several Access tables that gets exported like this...

Now.jpg


the problem is Xero (an accounts app), won't accept the shipping cost on the same row, but needs it to be broken out onto its own dedicated row, like this...

After.jpg


my question is what would be the most efficient approach here.... (the end goal is an export to a csv ...either from a query or table)

Many thanks!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:09
Joined
May 7, 2009
Messages
19,169
just Create a Union Query and export the Query to textfile (csv).
Code:
SELECT yourTable.OrderNo, yourTable.Name, yourTable.SKU, yourTable.Quantity, yourTable.Price
FROM yourTable
UNION
SELECT yourTable.OrderNo, yourTable.Name, "shipping",1, yourTable.Shipping
FROM yourTable
GROUP BY yourTable.OrderNo, yourTable.Name, yourTable.Shipping;
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:09
Joined
May 7, 2009
Messages
19,169
i used group to only show distinct OrderNo, Name and Shipping amount.
 

peskywinnets

Registered User.
Local time
Today, 17:09
Joined
Feb 4, 2014
Messages
576
just Create a Union Query and export the Query to textfile (csv).
Code:
SELECT yourTable.OrderNo, yourTable.Name, yourTable.SKU, yourTable.Quantity, yourTable.Price
FROM yourTable
UNION
SELECT yourTable.OrderNo, yourTable.Name, "shipping",1, yourTable.Shipping
FROM yourTable
GROUP BY yourTable.OrderNo, yourTable.Name, yourTable.Shipping;

That worked a charm (y) ....that said I couldn't get the 'GROUP BY' to work ..Access reported extra characters at the end of the SQL? (my SQL is actually a lot uglier on the eye ....as I'd simplified my opening post just to make it easier to ask the question! Here's my SQL...

Code:
SELECT OrdersFBM__processed_and_shipped.BillingName AS [*ContactName], OrdersFBM__processed_and_shipped.Email AS EmailAddress, OrdersFBM__processed_and_shipped.VATInvoiceNumber AS [*InvoiceNumber], OrdersFBM__processed_and_shipped.OrderDate AS [*InvoiceDate], OrdersFBM__processed_and_shipped.PaymentReference AS Reference, [OrdersFBM__processed_and_shipped]![OrderDateTime] AS [*DueDate], OrdersFBM__processed_and_shipped.ItemPrice AS [*UnitAmount], OrdersFBM__processed_and_shipped.SKU AS InventoryItemCode, OrdersFBM__processed_and_shipped.SKU AS [*Description], OrdersFBM__processed_and_shipped.[Qty Ordered] AS [*Quantity], "200" AS [*AccountCode], IIf([OrdersFBM__processed_and_shipped]![VATCode]="GBZ","Zero Rated Income",IIf([OrdersFBM__processed_and_shipped]![VATCode]="GBE","Zero Rated EC Goods Income","20% (VAT on Income)")) AS [*TaxType], OrdersFBM__processed_and_shipped.CurrencyCode, OrdersFBM__processed_and_shipped.GrandTotal AS Total, OrdersFBM__processed_and_shipped.Country, OrdersFBM__processed_and_shipped.Source, IIf([Invoice]![Source]="amazon",[Invoice]![SubSource],[OrdersFBM__processed_and_shipped]![PaymentMethod] & " " & [OrdersFBM__processed_and_shipped]![CurrencyCode]) AS TrackingName1, OrdersFBM__processed_and_shipped.EbayUserID AS TrackingName2, OrdersFBM__processed_and_shipped.OrderDateTime
FROM OrdersFBM__processed_and_shipped
WHERE (((OrdersFBM__processed_and_shipped.VATInvoiceNumber) Not Like "AM-*") AND ((OrdersFBM__processed_and_shipped.OrderDate)>#9/30/2020#) AND ((OrdersFBM__processed_and_shipped.Country)="United Kingdom") AND ((OrdersFBM__processed_and_shipped.Source)<>"vc"))
ORDER BY OrdersFBM__processed_and_shipped.OrderDateTime;

UNION SELECT DISTINCT OrdersFBM__processed_and_shipped.BillingName AS [*ContactName], OrdersFBM__processed_and_shipped.Email AS EmailAddress, OrdersFBM__processed_and_shipped.VATInvoiceNumber AS [*InvoiceNumber], OrdersFBM__processed_and_shipped.OrderDate AS [*InvoiceDate], OrdersFBM__processed_and_shipped.PaymentReference AS Reference, [OrdersFBM__processed_and_shipped]![OrderDateTime] AS [*DueDate], [OrdersFBM__processed_and_shipped]![ShippingCharge]-[OrdersFBM__processed_and_shipped]![ShippingDiscount] AS [*UnitAmount], "Shipping" AS InventoryItemCode, "Shipping" AS [*Description], 1 AS [*Quantity], "200" AS [*AccountCode], IIf([OrdersFBM__processed_and_shipped]![VATCode]="GBZ","Zero Rated Income",IIf([OrdersFBM__processed_and_shipped]![VATCode]="GBE","Zero Rated EC Goods Income","20% (VAT on Income)")) AS [*TaxType], OrdersFBM__processed_and_shipped.CurrencyCode, OrdersFBM__processed_and_shipped.GrandTotal AS Total, OrdersFBM__processed_and_shipped.Country, OrdersFBM__processed_and_shipped.Source, IIf([Invoice]![Source]="amazon",[Invoice]![SubSource],[OrdersFBM__processed_and_shipped]![PaymentMethod] & " " & [OrdersFBM__processed_and_shipped]![CurrencyCode]) AS TrackingName1, OrdersFBM__processed_and_shipped.EbayUserID AS TrackingName2, OrdersFBM__processed_and_shipped.OrderDateTime
FROM OrdersFBM__processed_and_shipped
WHERE (((OrdersFBM__processed_and_shipped.VATInvoiceNumber) Not Like "AM-*") AND ((OrdersFBM__processed_and_shipped.OrderDate)>#9/30/2020#) AND ((OrdersFBM__processed_and_shipped.Country)="United Kingdom") AND ((OrdersFBM__processed_and_shipped.Source)<>"vc"))
ORDER BY OrdersFBM__processed_and_shipped.OrderDateTime;

Ideally, if there was NO Shipping Charge ( a value of 0.00 in the Shipping field) I'd prefer the row for the Shipping not to even show ...but I guess that's complicating things a lot more?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:09
Joined
Feb 19, 2013
Messages
16,553
Access reported extra characters at the end of the SQL?
remove the ; after the first query and before the UNION
if there was NO Shipping Charge ( a value of 0.00 in the Shipping field) I'd prefer the row for the Shipping not to even show
include a criteria in the second part of the union query

WHERE Shipping<>0

or

WHERE Shipping is not Null

depending whether you have a 0 or null when there is no charge
 

peskywinnets

Registered User.
Local time
Today, 17:09
Joined
Feb 4, 2014
Messages
576
include a criteria in the second part of the union query

WHERE Shipping<>0

or

WHERE Shipping is not Null

depending whether you have a 0 or null when there is no charge

thank you :)(y)

I have one issue...if I drop each 'chunk' of SQL into a standalone query (not union)...each individual query runs fine.

When I do the union query & run it I get popup...

Error.jpg


...when I click on OK, everythingthen runs fine

Any ideas?

(to be clear *InvoiceNumber is the field name mandated by the target App I'm exporting this data to....an accounts app called Xero)
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:09
Joined
Feb 19, 2013
Messages
16,553
to be clear *InvoiceNumber is the field name mandated by the target App
So why does your sql have

OrdersFBM__processed_and_shipped.OrderDate AS [*InvoiceDate]
 

peskywinnets

Registered User.
Local time
Today, 17:09
Joined
Feb 4, 2014
Messages
576
So why does your sql have

OrdersFBM__processed_and_shipped.OrderDate AS [*InvoiceDate]

All the data for my union query is sourced from another query (a query called OrdersFBM__processed_and_shipped).

The Order date info in that source query is called OrderDate - the target app that I'm exporting to requires the csv field to be named *InvoiceDate hence that line you've asked about

(or have I missed the cut & thrust of your question?)
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:09
Joined
Feb 19, 2013
Messages
16,553
I presume XeroUnion is the name of your union query and this error is in another query which references it?

As you should know by now, using non alpha numeric characters in field names causes problems.

Are you sure you need the initial * - I've just looked on Xero and it was not helpful as I don't have an account with them - but you should be able to download the template. Looking at tutorials online of how to import does not show an initial *. See this example https://tv.xero.com/detail/video/5127747322001/importing-sales-and-purchases-in-xero

Assuming you do need the * then suggest add it in to the last step before exporting
 

peskywinnets

Registered User.
Local time
Today, 17:09
Joined
Feb 4, 2014
Messages
576
I presume XeroUnion is the name of your union query and this error is in another query which references it?

As you should know by now, using non alpha numeric characters in field names causes problems.

Are you sure you need the initial * - I've just looked on Xero and it was not helpful as I don't have an account with them - but you should be able to download the template. Looking at tutorials online of how to import does not show an initial *. See this example https://tv.xero.com/detail/video/5127747322001/importing-sales-and-purchases-in-xero

Assuming you do need the * then suggest add it in to the last step before exporting

Thanks ...you went above & beyond there....the csv template file I downloaded had the * in...but I've just removed them as a trial & the import to Xero still works (so they aren't necessary), however Access still protests (albeit with a different error message now the * are removed)...

Untitled-1.jpg


I'll experiment some more
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:09
Joined
Feb 19, 2013
Messages
16,553
perhaps your query has a filter or sort in it (open query in design view and look at properties)

this is a different parameter to last time which was XeroUnion, this one is XeroInvoice_IncVAT

so still says to me you are doing this from another query. In the union query you change OrderDateTime to DueDate although you do bring it through for a second time for some reason

Suggest remove the order by part of each part of the union - it is almost certainly ineffective anyway and should be applied to the union query as a whole and not each element.
 

June7

AWF VIP
Local time
Today, 09:09
Joined
Mar 9, 2014
Messages
5,423
A query with GROUP BY that does not include all fields or has no aggregate functions in SELECT or does not otherwise use other listed fields should error.

DISTINCT is not needed because UNION will not allow duplicate records (UNION ALL will). It is doubtful records are duplicated because Price and Quantity will be different on each record.

An ORDER BY clause is only recognized in the last SELECT statement of a UNION. However, ORDER BY can be eliminated if fields are listed in SELECT in desired sort hierarchy.

First SELECT dictates data types and field names, it is not necessary to repeat alias names in subsequent SELECT statements. Can shorten statement by not including table/query name in front of each field in SELECT.

I had no problem using * character in alias name within [ ]. Query opens just fine. I do agree should not use if not needed.
 
Last edited:

peskywinnets

Registered User.
Local time
Today, 17:09
Joined
Feb 4, 2014
Messages
576
I appreciate your input - I'm a little out my depth here (new to UNION queries).

here's what I did...

I constructed 1 query (which had all the items sold)...it has filters

I duplicated that query )s that it would be a replica of query 1) & then modified just for the shipping cost...I had to use distinct else there would have been several entries if the order contained more than one product ....this query 2 also had filters (to remove those orders that had a shipping charge of 0.00)

I then dropped both SQL into the union query...

It's a little ugly to read in SQL (& I don't expct you all to try), but here's the SQL...
Code:
SELECT OrdersFBM__processed_and_shipped.BillingName AS ContactName, OrdersFBM__processed_and_shipped.Email AS EmailAddress, OrdersFBM__processed_and_shipped.VATInvoiceNumber AS InvoiceNumber, OrdersFBM__processed_and_shipped.PaymentReference AS Reference, OrdersFBM__processed_and_shipped.OrderDate AS InvoiceDate, [OrdersFBM__processed_and_shipped]![OrderDateTime] AS DueDate, OrdersFBM__processed_and_shipped.ItemPrice AS UnitAmount, OrdersFBM__processed_and_shipped.SKU AS InventoryItemCode, "ENGINEER " & StrConv([OrdersFBM__processed_and_shipped]![SKU],1) AS Description, OrdersFBM__processed_and_shipped.[Qty Ordered] AS Quantity, "200" AS AccountCode, IIf([OrdersFBM__processed_and_shipped]![VATCode]="GBZ","Zero Rated Income",IIf([OrdersFBM__processed_and_shipped]![VATCode]="GBE","Zero Rated EC Goods Income","20% (VAT on Income)")) AS TaxType, OrdersFBM__processed_and_shipped.CurrencyCode AS CurrencyCode, OrdersFBM__processed_and_shipped.XeroGrandTotal AS Total, OrdersFBM__processed_and_shipped.Country, OrdersFBM__processed_and_shipped.Source, IIf([Invoice]![Source]="amazon",[Invoice]![SubSource],[OrdersFBM__processed_and_shipped]![PaymentMethod] & " " & [OrdersFBM__processed_and_shipped]![CurrencyCode]) AS TrackingName1, OrdersFBM__processed_and_shipped.EbayUserID AS TrackingName2, OrdersFBM__processed_and_shipped.OrderDateTime, [OrdersFBM__processed_and_shipped]![SKU] AS SortOrder
FROM OrdersFBM__processed_and_shipped
WHERE (((OrdersFBM__processed_and_shipped.VATInvoiceNumber) Not Like "AM-*") AND ((OrdersFBM__processed_and_shipped.OrderDate)>#9/30/2020#) AND ((OrdersFBM__processed_and_shipped.Source)<>"vc"));


UNION SELECT OrdersFBM__processed_and_shipped.BillingName AS ContactName, OrdersFBM__processed_and_shipped.Email AS EmailAddress, OrdersFBM__processed_and_shipped.VATInvoiceNumber AS InvoiceNumber, OrdersFBM__processed_and_shipped.PaymentReference AS Reference, OrdersFBM__processed_and_shipped.OrderDate AS InvoiceDate, [OrdersFBM__processed_and_shipped]![OrderDateTime] AS DueDate, [ShippingCharge]+[ShippingDiscount] AS UnitAmount, "shipping" AS InventoryItemCode, "shipping charge" AS Description, 1 AS Quantity, "200" AS AccountCode, IIf([OrdersFBM__processed_and_shipped]![VATCode]="GBZ","Zero Rated Income",IIf([OrdersFBM__processed_and_shipped]![VATCode]="GBE","Zero Rated EC Goods Income","20% (VAT on Income)")) AS TaxType, OrdersFBM__processed_and_shipped.CurrencyCode AS CurrencyCode, OrdersFBM__processed_and_shipped.XeroGrandTotal AS Total, OrdersFBM__processed_and_shipped.Country, OrdersFBM__processed_and_shipped.Source, IIf([Invoice]![Source]="amazon",[Invoice]![SubSource],[OrdersFBM__processed_and_shipped]![PaymentMethod] & " " & [OrdersFBM__processed_and_shipped]![CurrencyCode]) AS TrackingName1, OrdersFBM__processed_and_shipped.EbayUserID AS TrackingName2, OrdersFBM__processed_and_shipped.OrderDateTime, [OrdersFBM__processed_and_shipped]![SKU] AS SortOrder
FROM OrdersFBM__processed_and_shipped
WHERE (((OrdersFBM__processed_and_shipped.VATInvoiceNumber) Not Like "AM-*") AND ((OrdersFBM__processed_and_shipped.OrderDate)>#9/30/2020#) AND ((OrdersFBM__processed_and_shipped.Source)<>"vc"));
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:09
Joined
Feb 19, 2013
Messages
16,553
that still will not work - you have a semi colon before the UNION - yo were told about this in post #7

Now very confused - the errors you are telling us about are not coming from this query but might be coming from a query that references it
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:09
Joined
Sep 21, 2011
Messages
14,044
I presume XeroUnion is the name of your union query and this error is in another query which references it?

As you should know by now, using non alpha numeric characters in field names causes problems.

Are you sure you need the initial * - I've just looked on Xero and it was not helpful as I don't have an account with them - but you should be able to download the template. Looking at tutorials online of how to import does not show an initial *. See this example https://tv.xero.com/detail/video/5127747322001/importing-sales-and-purchases-in-xero

Assuming you do need the * then suggest add it in to the last step before exporting
This the the Xero Template for Invoices. Just a standard csv file? I had to zip it as csv files are not permitted.
 

Attachments

  • SalesInvoiceTemplate.zip
    351 bytes · Views: 285
Last edited:

peskywinnets

Registered User.
Local time
Today, 17:09
Joined
Feb 4, 2014
Messages
576
that still will not work - you have a semi colon before the UNION - yo were told about this in post #7

Now very confused - the errors you are telling us about are not coming from this query but might be coming from a query that references it
It's because I created a new test query to have a dabble/ play with (without messing up my original working query)...when I copied in some SQL, the semi-colon came in with it

To be clear the Union query work (with or without the semi colon)...it's just a bit annoying to have the popup showing everytime i run it! Clearly something isn't right...oddly when I run each separate query (top SQL part & lower SQL part) as standalone queries...no errors.

Anyway, it's more of an irritation that a real big problem...rather than take up your time, I'll park this up & live with it for now.

Many thanks for all your help & input
This the the Xero Template for Invoices. Just a standard csv file? I had to zip it as csv files are not permitted.

Yes...that's the one I'm using (it has the Asterisk in the csv column headers...but they aren't needed when you come to reupload back to Xero.
 

Users who are viewing this thread

Top Bottom