Ms Access Help with Union & transposing data

[ekariuki]

Hi Guys,

I am a newbee to Bytes currently working on an MS Access database for one of my clients:

With respect, I'll get right to it:

I have 2 tables:
Table 1. TblTrip_Facts
Route Name , FactTypeId ,CustomerName ,RouteType
AISX1-0103 , 0 , TTMA , DEDICATED
AISX1-0104 , 0 , TTMA , DEDICATED


Table 2: TblLabel
Labels,
RouteType,
CustomerName,
Route Name,


I am looking to export a CSV file that looks like the following:

FINAL OUTPUT: My wish list. (TABLE B)

TripReference, FactTypeID, Label, Value
AISX1-0103, 0, RouteType , DEDICATED
AISX1-0103, 0, CustomerName, TTMA Tetsu Group
AISX1-0103, 0, Route Name, AISX1-0103
AISX1-0104, 0, RouteType , DEDICATED
AISX1-0104, 0, CustomerName, TTMA Tetsu Group
AISX1-0104, 0, Route Name, AISX1-0104

Please let me know if you have any ideas on how I can convert my tables to one table (TABLE B) and include a new field Value.

Thanks for the help in advance.

[NeoPa]

The information you give is quite clear as far as it goes, but you haven't given any linking information. What relationship do the tables bear to each other?

Another problem is that you seem to want the fields renamed for use in your new table, yet you haven't explained this. Most of us are intelligent enough to work it out for ourselves eventually, but few of us appreciate having to ;)

[ekariuki]

Neopa: thank you for the response.

Part 1 of your question:

This is how the tables are associated.

The Row headings (RouteFacts, Route Name, CustomerName) in Table 1tbl_TripFacts are values in Table 2 TblLabel under the Label Feild

Basically: Table1's field names have been transposed to form Table 2's data under the field name "Labels".

Part 2:

Good obsavation. My mistake. The field Routes will be renamed to TripReference (I can handle that renaming)

In Detail: For every TripReference There is a RouteType, CustomerName and a Route Name. I want these to show up under the field name "Value" reading from top to bottom with TripReference being repeated as opposed to from left to right.

[NeoPa]

I'm trying to work out what you mean, but you've introduced more new problems with your explanation than you explain.

Could you try this again, but be very careful to get all the details correct. Where something differs from what you've said before then you need to point this out otherwise I have no way of knowing which version you made the mistake on. I cannot work with this as it stands, as it is too unclear. Far too much would be guesswork.

You've clearly put effort into getting this right, but it seems you still need a little guidance to see the most important parts.

The importance is in the detail. If you learn nothing more about database work than this point you will still have made much progress.

[ekariuki]

Let me try this one more time:

Table 1. contains the Trip facts

Table 2. Contains the specific trip details

My Questions:

1. How I can combine data from two fields and stack it up in a single collumn
2. How can I use one tables field headings as data in another table
In Ms excel it would be a cut and paste then transpose.

[NeoPa]

I'm sorry.

I cannot for the life of me see why posting this question should be as complicated as it seems to be for you. I thought I was being clear in my explanation of why I cannot work with this. Perhaps I wasn't clear enough. I really cannot think what I can say more than I have already. I certainly haven't got the time to weave through all the various posts trying to put something together that may make sense. This is your responsibility of course. So far all we have (as far as I can see) is a few posts with bits of the puzzle in them. Unfortunately, as some of the say different things from the others, it's not even as simple as just putting them together. I still need to know which information is reliable.

I think we'd better just call it a day to be honest. Such a shame, as I thought your first post indicated such promise.

[ekariuki]

Thanks NeoPa:

This worked incase anyone runs into the same issue.

SELECT TripReference, FactTypeID, 'RouteType' AS Label, RouteType AS [Value] FROM [CM Trips]
UNION
SELECT TripReference, FactTypeID, 'CustomerName' AS Label, CustomerName AS [Value] FROM [CM Trips]
UNION
SELECT TripReference, FactTypeID, 'RouteName' AS Label, [Route Name] AS [Value] FROM [CM Trips];

[NeoPa]

Ironically, it now seems clear what you were trying to ask for.

Just a couple of tips I can add :

1. In a UNION query, the names of the fields are only important in the first line. I sometimes use them anyway, for clarity, but it's worth noting. In fact I would recommend you do, but only if you can always rely on yourself getting them correct 100%. Otherwise it can be misleading.
2. In case it helps, you can sort the results of a UNION query, although not within each query.

My recommendation then, would be :

SELECT   TripReference,
         FactTypeID,
         'RouteType' AS [Label],
         RouteType AS [Value]
FROM     [CM Trips]
UNION
SELECT   TripReference,
         FactTypeID,
         'CustomerName' AS [Label],
         CustomerName AS [Value]
FROM     [CM Trips]
UNION
SELECT   TripReference,
         FactTypeID,
         'RouteName' AS [Label],
         [Route Name] AS [Value]
FROM     [CM Trips]
ORDER BY TripReference,
         FactTypeID,
         [Label]

You may choose a different order, but you can see what I mean.