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

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

On Oct 28, 5:13 am, efandango <efanda...@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
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.

"pietlin...@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...

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

.

Relevant Pages

  • Re: Please display the table faster in the datagrid
    ... I doubt I have the sql command correct but I think you will see what I'm talking about. ... Additional complication added if user has ability to sort grid by different columns. ... you should know total number of records and somehow "cheat" the scrollbar of your control. ... One of modifications of this solution can be the following: you populate data on demand but all populated data remains in place even if it is currently invisible - this way you don't need to retrieve data once populated. ...
    (microsoft.public.vb.database)
  • bug? cannot get value of cell on invisible column
    ... This works nicely and one does not see the id field when the grid is displayed nor is it editable. ... Then I made the column visible but the field readonly and that caused the sql "update" command to fail as the update command skipped over that "id" field and put "authString" where id was supposed to go causing an exception on the sqlserver. ...
    (microsoft.public.dotnet.framework.aspnet.datagridcontrol)
  • Re: How to requery grid based on query
    ... you will get the REQUERY() orportunity if you use a view instead of a query. ... CREATE SQL VIEW Command ... Form runs, grid> displays data. ... a grid does not have a requery> method. ...
    (microsoft.public.fox.programmer.exchange)
  • Re: Select - SQL vs. SUM
    ... SQL SELECT doesn't read the buffered data in the Grid, ... > I have a grid whose recordsource is a table named _payments. ... > first time and hit Enter key, the value in txtUnused is updated and the ...
    (microsoft.public.fox.programmer.exchange)
  • Re: VB ADO.NET Concurrency, Access, date fields and parameters
    ... Hi Bill, thank you very much for your help, it is much apreciated. ... >> I then edit a date in the grid. ... > Not sure what your update command looks like but that's the likely source. ... > There's no profiler that I konw of for Access, but if you ever use SQL ...
    (microsoft.public.dotnet.framework.adonet)