swap rows and columns in a query

[109bow]

I have a query, AccumaltiveKmnow, that is based on a linked table AccumaltiveKM. The query gives me the kilometres [km] on todays date for units numbered 02,04 06 up to 94.
Accumaltive KMnowdaynow0204060804/05/2017760622714839.598851542891303
see attachment for the query.

Is it possible to swap the row and columns so that each unit number is listed in a column and the kilometres listed in the next column. Like;
02 763999
04 679000
06 983477
08 456798
for todays date.
I can then link this query to an existing query to give the results I require.
Many thanks for your time!

 

[Minty]

Can you explain how the data is stored - the fieldnames look like a dump straight from a spreadsheet, and therefore looks like very un-normalised data?

What does 24 x 23 as a field name actually represent ?

 

[109bow]

The data is stored in a linked spreadsheet, so cannot be altered. in the spreadsheet the columns are titled 01x02, 03x04 up to 93x94. In the query they are named as 02:01x02, 04:03x04, etc. to give a field named 02, 04, 06 etc. each field name represents a different numerical value. The first column in the AccumlativeKM table are dates, the query gives results for todays date.
I have attached a greatly stripped down database in the hope it explains a little better!

 

[almahmood]

Hi there,

You can use UNION ALL to do this kind of job. Here is the query:

SELECT [00/01/1900] as [Date], '02' as UnitNumber, [02 x 01] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '04' as UnitNumber, [04 x 03] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '06' as UnitNumber, [06 x 05] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '08' as UnitNumber, [08 x 07] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '10' as UnitNumber, [10 x 09] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '12' as UnitNumber, [12 x 11] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '14' as UnitNumber, [14 x 13] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '16' as UnitNumber, [16 x 15] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '18' as UnitNumber, [18 x 17] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '20' as UnitNumber, [20 x 19] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '22' as UnitNumber, [22 x 21] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '24' as UnitNumber, [24 x 23] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '26' as UnitNumber, [26 x 25] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '28' as UnitNumber, [28 x 27] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '30' as UnitNumber, [30 x 29] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '32' as UnitNumber, [32 x 31] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '34' as UnitNumber, [34 x 33] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '36' as UnitNumber, [36 x 35] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '38' as UnitNumber, [38 x 37] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '40' as UnitNumber, [40 x 39] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '42' as UnitNumber, [42 x 41] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '44' as UnitNumber, [44 x 43] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '46' as UnitNumber, [46 x 45] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '48' as UnitNumber, [48 x 47] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '50' as UnitNumber, [50 x 49] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '52' as UnitNumber, [52 x 51] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '54' as UnitNumber, [54 x 53] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '56' as UnitNumber, [56 x 55] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '58' as UnitNumber, [58 x 57] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '60' as UnitNumber, [60 x 59] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '62' as UnitNumber, [62 x 61] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '64' as UnitNumber, [64 x 63] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '66' as UnitNumber, [66 x 65] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '68' as UnitNumber, [68 x 67] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '70' as UnitNumber, [70 x 69] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '72' as UnitNumber, [72 x 71] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '74' as UnitNumber, [74 x 73] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '76' as UnitNumber, [76 x 75] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '78' as UnitNumber, [78 x 77] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '80' as UnitNumber, [80 x 79] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '82' as UnitNumber, [82 x 81] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '84' as UnitNumber, [84 x 83] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '86' as UnitNumber, [86 x 85] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '88' as UnitNumber, [88 x 87] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '90' as UnitNumber, [90 x 89] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '92' as UnitNumber, [92 x 91] as [KM] FROM [Accumaltive KM] UNION ALL
SELECT [00/01/1900] as [Date], '94' as UnitNumber, [94 x 93] as [KM] FROM [Accumaltive KM];

Save this query and apply your criteria. Enjoy!

 

[Minty]

Holy Horrible Query Batman!!!

 

[isladogs]

That must have been tedious to type!

Will it work?
Isn't there a limit to the number of UNIONS allowed in one query?

 

[109bow]

almahmood, thanks for taking the time to produce the code, it has given the results I was after.

 

[almahmood]

Query Batman! Nice Minty

Colin, yes, there is a limitation for UNION in Access. I think we can use 50 times. Of coaurse it's tedious to type but I used Excel to create the line items. It was easy and quick job.