Transpose coloumn to rows with UNION query (1 Viewer)

elly.khanlar · Oct 23, 2019

Hi everyone,

I'm trying to transpose columns to rows into some existing field, which doesn't turn out to be the result I am looking for. based on my code, the 2019 late rate or 2019 Rebate or 2019 Fee will create a new field instead of layering into the existing field called "Category".

here is my code:

Select Location, Product, Category, " lane rate", [2019 lane rate] AS [2019 Price], "2019 Rebate",[2019 Rebate] AS [2019 Price], "2019 Fee", [2019 Fee] AS [2019 Price],

From [TableABC]

Where [2019 lane rate] is not NULL OR [2019 Rebate] is not NULL OR [2019 Fee] is not NULL

Here is the table I am looking for to get

theDBguy · Oct 23, 2019

Hi. What you posted doesn't look like a complete UNION query.

isladogs · Oct 23, 2019

I suggest you use Excel Power Query to do this.
See https://www.access-programmers.co.uk/forums/showthread.php?t=306580

plog · Oct 23, 2019

I just reviewed your post history (https://www.access-programmers.co.uk/forums/search.php?searchid=13935107) every single thread you have started is because you are trying to overcome a poor table structure. Interestingly, in every single thread someone has advised using Excel.

If you are not going to use a database as intended (normalized tables), then I suggest Excel. Otherwise you are just going to keep starting threads asking for hacks to make what you have work. This house of cards will fall.

June7 · Oct 23, 2019

How is this question any different from https://access-programmers.co.uk/forums/showthread.php?t=307183

Two solutions were provided.

elly.khanlar · Oct 24, 2019

Hi June,

Thanks for your reply. It is different since I want to layer this into the existing field instead of creating a new field.

Here is what I have comeup so far which is working but the "where"clause is not working

SELECT Location, Product, TableABC.Category & " - Lane rate" AS Category,

[2019 lane rate] AS [2019 Price], [2020 lane rate] AS [2020 Price], 1 AS SortCategory
FROM TableABC

Where [2019 Lane rate] is not Null OR [2020 Lane rate]
UNION ALL

SELECT Location, Product, TableABC.Category & " - Rebate",
[2019 Rebate], [2020 Rebate], 2
FROM TableABC

Where [2019 Rebate] is not Null OR [2020 Rebate]

UNION ALL
SELECT Location, Product, TableABC.Category & " - Fee",
[2019 Fee], [2020 Fee],3
FROM TableABC
ORDER BY Location, Product, SortCategory;

Where [2019 Fee] is not Null OR [2020 Fee]

theDBguy · Oct 24, 2019

elly.khanlar said:
...but the "where"clause is not working
...

Where [2019 Lane rate] is not Null OR [2020 Lane rate]
...
Where [2019 Rebate] is not Null OR [2020 Rebate]
...
Where [2019 Fee] is not Null OR [2020 Fee]

What are you trying to filter? Saying "OR [2020...]" is an incomplete condition. So, the first part "[2019...] Is Not Null" is checking for a non-empty field. What does the second part supposed to check?

June7 · Oct 24, 2019

What I suggested to you will produce the output shown in your example.

I do not understand your reason for why you say it is not acceptable. What 'existing' field?

Should have continued with same thread instead of starting a new one with essentially same requirement.

Transpose coloumn to rows with UNION query (1 Viewer)

elly.khanlar

Registered User.

Attachments

theDBguy

I’m here to help

isladogs

MVP / VIP

plog

Banishment Pending

June7

AWF VIP