How can I merge two pairs of fields into 1 pair of fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have four fields which I want to merge into 2 fields:

Run_From
Run_To

Run_From_Postcode
Run_To_Postcode

I want to merge the above two sets of data from each pair into two new fields:

Run_Title
Run_Postcode

I tried doing it with a Union query, but I think that only works on 2
tables?. I have just the one table (which I can't split, as it is critical to
the rest of the database.)

this is my SQL for the query

SELECT tbl_Runs.Run_No, tbl_Runs.Run_From, tbl_Runs.Run_From_Postcode,
tbl_Runs.Run_To, tbl_Runs.Run_To_Postcode
FROM tbl_Runs;
 
I have four fields which I want to merge into 2 fields:

Run_From
Run_To

Run_From_Postcode
Run_To_Postcode

I want to merge the above two sets of data from each pair into two new fields:

Run_Title
Run_Postcode

I tried doing it with a Union query, but I think that only works on 2
tables?. I have just the one table (which I can't split, as it is critical to
the rest of the database.)

this is my SQL for the query

SELECT tbl_Runs.Run_No, tbl_Runs.Run_From, tbl_Runs.Run_From_Postcode,
tbl_Runs.Run_To, tbl_Runs.Run_To_Postcode
FROM tbl_Runs;
Click to expand...

Sure you can. You can union two datasets from the same table.

SELECT RunFrom AS Run_Title
WHERE ...
FROM tbl_Runs
UNION ALL
SELECT RunTo AS Run_Title
FROM tbl_Runs
WHERE ...

so I guess RunTitle would be the result if an IIF statement...
 
Piet,

Thanks for your reply. I tried this

SELECT Run_From FROM tbl_Runs AS Run_Title WHERE Run_No <=320
ORDER BY Run_No
UNION ALL
SELECT Run_To FROM tbl_Runs AS Run_Title WHERE Run_No <=320


Which gave me all the Run_From's and Run-To's in one column, but didn't
create the new field Run-Title. Also, Is there any way of having each pair
retain it's original Run_No?

For example:

Run_No Run_From Run_From_Postcode Run_To Run_To_Postcode
1 Easy St N4 Main St N1
2 South St N1 North St WC1
....

Becomes:

Run No Run Title Run Postcode
1 Easy St N4
1 Main St N1
2 South St N1
2 North St WC1
....

RunTitle would not be the result if an IIF statement, I want the Query to
generate it along with another new field 'RunPostcode' which will contain the
results of the 2 pairs of field merge, respectively.
 
Piet,

I have managed to get my Columns just how I need them with this SQL:

SELECT Run_To AS Run_Title, Run_No, Run_To_Postcode AS Postcode,
Run_From_Postcode AS Unused FROM tbl_Runs WHERE Run_No <=320
ORDER BY Run_No
UNION ALL SELECT Run_From AS Run_Title, Run_No,Run_From_Postcode ,
Run_To_Postcode FROM tbl_Runs WHERE Run_No <=320
ORDER BY Run_No;

Which gives me:

Run No Run Title Postcode Unused
1 Easy St N4 N1
1 Main St N1 N4
2 South St N1 WC1
2 North St WC1 N1


to put the icing on the cake, it would be nice if I could dispense with the
'Unused' column from the results grid, I suppose the SQL needs to use the
'Run_From_Postcode' field to complete the process, but is there some kind of
'Don't Show' command with SQL which will use the field, but not show its
result. I know I can simply hide the column in the results, but it would be
cleaner (for later use) if it wasn't there in the first place.

Thanks for your help on this, much appreciated.
 
Piet,

I have managed to get my Columns just how I need them with this SQL:

SELECT Run_To AS Run_Title, Run_No, Run_To_Postcode AS Postcode,
Run_From_Postcode AS Unused FROM tbl_Runs WHERE Run_No <=320
ORDER BY Run_No
UNION ALL SELECT Run_From AS Run_Title, Run_No,Run_From_Postcode ,
Run_To_Postcode FROM tbl_Runs WHERE Run_No <=320
ORDER BY Run_No;

Which gives me:

Run No Run Title Postcode Unused
1 Easy St N4 N1
1 Main St N1 N4
2 South St N1 WC1
2 North St WC1 N1

to put the icing on the cake, it would be nice if I could dispense with the
'Unused' column from the results grid, I suppose the SQL needs to use the
'Run_From_Postcode' field to complete the process, but is there some kind of
'Don't Show' command with SQL which will use the field, but not show its
result. I know I can simply hide the column in the results, but it would be
cleaner (for later use) if it wasn't there in the first place.

Thanks for your help on this, much appreciated.
Click to expand...

uncheck the show button... (under the field name in the QBE grid).
 
Back
Top