Union Query

[phenley]

I am very new to access, and I am trying to move column headings into row values, and read online doing a union query would work for that. Below is the SQL code I mimicked from another person's code I had seen on a different forum. Company, py, and qtr are three already existing column headings, and Projection Compression is the select query I am trying to manipulate. I am getting the error that says: "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'." I have zero experience in SQL, so any help would be awesome.

SELECT Company, py, qtr, "SumOfCicos" AS Reserve, SumOfCicos AS Amount
FROM Projection Compression
UNION ALL
SELECT Company, py, qtr, "SumOfCsdr" AS Reserve, SumOfCsdr AS Amount
FROM Projection Compression
UNION ALL
SELECT Company, py, qtr, "SumOfCsdi" AS Reserve, SumOfCsdi AS Amount
FROM Projection Compression
UNION ALL
SELECT Company, py, qtr, "SumOfCibnra" AS Reserve, SumOfCibnra AS Amount
FROM Projection Compression
UNION ALL
SELECT Company, py, qtr, "SumOfCsalr" AS Reserve, SumOfCgdr AS Amount
FROM Projection Compression
UNION ALL
SELECT Company, py, qtr, "SumOfCgdi" AS Reserve, SumOfCgdi AS Amount
FROM Projection Compression
UNION ALL
SELECT Company, py, qtr, "SumOfCgalr" AS Reserve, SumOfCgalr AS Amount
FROM Projection Compression
UNION ALL
SELECT Company, py, qtr, "SumOfCdac" AS Reserve, SumOfCdac AS Amount
FROM Projection Compression
UNION ALL
SELECT Company, py, qtr, "SumOfCtdr" AS Reserve, SumOfCtdr AS Amount
FROM Projection Compression
UNION ALL
SELECT Company, py, qtr, "SumOfCtdi" AS Reserve, SumOfCtdi AS Amount
FROM Projection Compression
UNION ALL
SELECT Company, py, qtr, "SumOfCtalr" AS Reserve, SumOfCtalr AS Amount
FROM Projection Compression
UNION ALL;

 

[Minty]

Remove the last Union All; it's expecting another select statement after it.

Not convinced your data is very well stored if you have all those as field names, but you could be using a cross tab query?

 

[phenley]

That worked perfect, thanks! I also had to put brackets around the query name since it contains a space in it.

I ran a crosstab query after this union query, since I wanted all of those field names as values, but if I just started with a crosstab query, it would only allow one field value at a time. I have it sorted the way I envisioned it, but would take suggestions if you have anything else in mind.

 

[plog]

You need to normalize your data. Just by the name of your fields I see a few things wrong:

SumOf...: Is this a calculated value that you are storing? Do you have another query that runs to populate this value? If so, that's the wrong way to do things. Instead, you create a query to calculate the Sum and then reference that query when you need it.

...Cicos: This seems like a value that should be stored in a field, not in a field name. All those values after SumOf should be values you store with the record, not in field names.

I fear this is post is one of many to come because you have an improper structure that you are just putting band-aids on.

 

[phenley]

I have a query that calculates the sum to get all the SumOf... values. In the whole data table, I have the field names Cicos, Csdr, .... with numbers occupying those values. I also have qtr as a field name which contains dates in it.

I then have one query that pulls dates past a certain point and sums up the Cicos, Csdr,... values. Following that, I wanted to create a calculated value using information from the last 15 quarters of data, so that's why I rearranged the data so the dates were on top and the SumOf values as fields. Does that make sense? Is there a better way to rearrange the database? Also, what is normalizing the data?

I really do appreciate the help, I'm just trying to learn a bit on how to work through Access, and you all are an excellent resource.

 

[plog]

Database normalization (https://en.wikipedia.org/wiki/Database_normalization) is the 1st and most important step of building a database. It's the foundation, you need to set it up correctly, or you'll be faced with more and more hacks to get what you want out of your database.

Read up on it, give it a shot on your data, then post back a screenshot of your relationships view and we can help you get it done correctly if you want.