Help with Access/SQL

[Mekrel]

So I'm a complete and utter novice when it comes to Access, and I'm having trouble trying to setup a crude table correctly in order to get the fields in a PivotTable friendly layout.

I need the layout as I'm going to drive a PivotTable from the access database and currently, each fact is a separate field which means I can't utilize a slicer to switch between viewing different facts.

What I want to try and do is change the layout from how it is in the top, to how is should look at the bottom:


upload images

Any ideas? There's some SQL using Union All that I've Googled but it just errors out on me.

 

[tom_e]

From what I can make out on my phone screen you're in effect looking to pivot the data in your SQL already?

It's a pain in the backside to get your head around initially but the pivot command should do what you need.

 

[gpuerrilla]

Been there and realised that its far superior to just design the DB well then using a Power Query in Excel to do tons of things very quickly that take ages to hack and waste time in Access.

 

[gpuerrilla]

Any ideas? There's some SQL using Union All that I've Googled but it just errors out on me.

This one is a ball ache but I have managed it in the past. Something I recall (which is degrading rapidly these days as I get older) is you get the query broken down into two SELECT statements. Split the statements with a UNION , e.g.

SELECT blah blah
UNION
SELECT more detail
ORDER BY blah;

then end with your conditions. To fastrack past the errors check each SELECT works on its own before trying to join them.