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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.

"pietlinden@xxxxxxxxxxx" wrote:

On Oct 27, 4:25 pm, efandango <efanda...@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
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;

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...


.

Relevant Pages

  • Re: recordset.clone
    ... Denver wrote: ... Dim SQL as String ... If you change that to a UNION query you can add an option for 'All': ... Note that the initial sql assigned to SQL will be the same sql that the subform is currently based on. ...
    (microsoft.public.access.formscoding)
  • Re: Simulating A Full Outer Join in Access from 3 Tables
    ... task unless you have advanced knowledge of VBA and or Jset SQL DDL. ... you can use the union query you have to load the dates. ... and using the date feild as the basis to align ... What I'm seeking to do is consolidate the files into one so ...
    (microsoft.public.access.queries)
  • Re: Submit Form Elements to Insert Multiple Records into a Database
    ... > response...wouldn't your solution just append each set of values to ... > each other causing the insert statement to feed in one long string of ... The derived_table is any SQL statement that returns a dataset that matches ... UNION query portion of the INSERT statement into Query Analyser. ...
    (microsoft.public.inetserver.asp.general)
  • Re: Union query
    ... You seem to imply that you had a union query that was working ... But the sql you show us only has those two fields being selected ... ... qryNFIRandomisedReport and qryFIRandomisedReport. ...
    (microsoft.public.access.queries)
  • Re: Union fields in the order they appear in the table
    ... with SQL, and do not know where I am going wrong. ... "John W. Vinson" wrote: ... populate the table based on the UNION query; be sure that there is a nonunique ...
    (microsoft.public.access.queries)