Gather multiple tables into one query by row and not column?

[veejay]

I have several tables where I keep different informations, but in all of them I always have the following same: Employee, Reviewer, Date, Application_num and one column that contains a "standard".



I'm trying to organize this information in a query or table but instead of wanting the information displayed as:

Employee, Reviewer, Date, Application_num, Standard 1, Standard 2, Standard 3 ...
Employee 1, Rev, Date, Num, Value 1, Value 2, Value 3

I would like to view the information like this :

Employee, Reviewer, Date, Application_num, Standard, Value
Employee 1, Reviewer, Date, Application_num, Standard 1 name, Standard Value
Employee 1, Reviewer, Date, Application_num, Standard 2 name, Standard Value
Employee 1, Reviewer, Date, Application_num, Standard 3 name, Standard Value
Employee 2, Reviewer, Date, Application_num, Standard 1 name, Standard Value
Employee 2, Reviewer, Date, Application_num, Standard 2 name, Standard Value
Employee 2, Reviewer, Date, Application_num, Standard 3 name, Standard Value

The idea is that I figured that for one of my report, I need to be able to identify a few files this way but can't wrap my head around this.

My intuition is that I will probably need a module to achieve this, but was wondering if there's another possible solution.

Just to be clear, I guess this would be a partial transpose, as I'm already able and already have set up a query similar to the first one, but i would need to be able to transpose the standards as row, while retaining the identification information.

[ranman256]

this is not how queries nor databases show data.

you could create a 'report' table with the field you showed,
then run an append query for each table add to the tReport,
then sum the data in the report and you may get what you ask. (non canon view)

[veejay]

Nevermind I figured that SQL could come to the rescue and ended up running a crosstab query as such:

SELECT DATE_OF_ACTION, EXAMINER, REVIEWER, MANAGER, APPLICATION_NUM, "SUB_STANDARD" As STANDARD, SUB_STANDARD As GRADE  
FROM FilesReviewed
UNION ALL
SELECT DATE_OF_ACTION, EXAMINER, REVIEWER, MANAGER, APPLICATION_NUM, "30_A_STANDARD" As STANDARD, [30_A_STANDARD] As GRADE  
FROM FilesReviewed  
UNION ALL
SELECT DATE_OF_ACTION, EXAMINER, REVIEWER, MANAGER, APPLICATION_NUM, "NICE_STANDARD" As STANDARD, NICE_STANDARD As GRADE  
FROM FilesReviewed
UNION ALL
SELECT DATE_OF_ACTION, EXAMINER, REVIEWER, MANAGER, APPLICATION_NUM, "FORMAL_STANDARD" As STANDARD, FORMAL_STANDARD As GRADE  
FROM FilesReviewed
UNION ALL
SELECT DATE_OF_ACTION, EXAMINER, REVIEWER, MANAGER, APPLICATION_NUM, "CLERICAL_STANDARD" As STANDARD, CLERICAL_STANDARD As GRADE  
FROM FilesReviewed
UNION ALL
SELECT DATE_OF_ACTION, EXAMINER, REVIEWER, MANAGER, APPLICATION_NUM, "FB_STANDARD" As STANDARD, FB_STANDARD As GRADE  
FROM FilesReviewed

[June7]

That's not CROSSTAB, it's a UNION. UNION rearranges data to normalized structure should be in first place.

Is there a unique record ID field? Might want to include it as well.

[veejay]

The APPLICATION_NUM is already unique (no duplicates allowed).

Thanks for clarifying that this is not a crosstab as I have read but rather a UNION.

What do you mean by "should be in first place" ? Was there anything I have done wrong here?

Thanks

[June7]

That you are resorting to a UNION is indication data structure is not normalized. Multiple similar name fields is another clue. UNION query deals with the situation but can perform slowly with large dataset. Normalization would have the Standard fields data as separate records in a related table. Then a CROSSTAB query could be used to present data as you currently store it.

Will there always be only 3 standards? Do all 3 fields always have data?

"Normalize until it hurts, denormalize until works"

[veejay]

We will always have 5 différents standards and they will always have data.

[June7]

Okay, but post 3 seems to show 6 standards.

The first SELECT line sets field names. Don't have to repeat the aliases (but won't hurt).