Robin,

is it a matter of you wanting the production data appended to the logfile data for display purposes and both tables have the same structure? Then you could use a UNION ALL like this:

select upper(cname) as name,type;
    ,desc,date;
    ,reason,operator;
    ,startfinish,transid;
    ,JOBNO ;
  from wipdata!logfile ;
    inner join operators on jobno=wipdata.jobno and operator=operators.nempno;
UNION ALL;
  <select the corresponding fields from ProductionLog here>;
  order by 4;
  into cursor tmplog readwrite

But I am not sure I have understood exactly what you are trying to do. Maybe show us some sample data from each table and what your expected result is.


>Hi Al
>
>I think it would be best if I explain in at little more detail what I am trying to do:
>
>LOGFILE.DBF contains a log of what has happened to a specific jobno. The form selects all the transactions for a selected job into a list control.
>
>The factory was working 24X7 but then changed to say 8-6 for each day for example.
>
>Now customer now wants the form to show when production wais running and when production was down in the same listbox.
>
>So as you can see the productionlog records don't have a jobno since they are for all jobs.
>
>Looks like I may well be using APPEND FROM to join the two tables together?
>
>Rob
>
>
>
>>>Hi
>>>
>>>I have two tables that I want to join together into a single, problem is they are unrelated to each other, this is to say that one file is transactions for a specific jobno so my code is:
>>>
>>>select upper(cname) as name,type,desc,date,reason,operator,startfinish,transid,JOBNO from wipdata!logfile,operators into cursor tmplog readwrite where jobno=wipdata.jobno and operator=operators.nempno order by transid
>>>
>>>The other file (PRODUCTIONLOG.DBF) is transactions not specific to a jobno but just to a daterange.
>>>
>>>Both files have the same structure but obviously jobno is empty in the PRODUCTIONLOG.DBF
>>>
>>>I'm struggling to work out what the syntax should be to join both tables together since I have no ON clause.
>>>
>>>Any help extremley appreciated
>>
>>If you don't have foreign key values in ProductionLog.dbf then you're stuck. You need to somehow populate the jobno column (if that's the foreign key) so you can do your JOIN.
>>
>>If you know that all transactions in ProductionLog.dbf for a specific range of datetimes is for JobX, then you can UPDATE the JobNo column for the corresponding rows in ProductionLog.DBF (use a copy of the table, to be safer) with JobX's primary key value. But if the transactions are interleaved between several jobs, you can't do that.
>>
>>It sounds like a bunch of child rows in ProductionLog.dbf got created but the code didn't update the foreign key
>>
>>Ask yourself if a human could relate the tables manually. Maybe one couldn't because the information just isn't there. If so, no SELECT - SQL command can magically make it happen.