Hi Frank,

I want it like result 2

>To get it clear in my head, you have:
>
>

Table1
>Field1, Field2, Field3
>aaaaaa, bbbbbb, cccccc

>
>and
>
>

Table2
>Field1, Field2, Field3
>dddddd, eeeeee, ffffff

>
>and is your result is to look like this:
>
>

Result1
>Field1, field2, Field3, Field4, Field5, Field6
>aaaaaa, bbbbbb, cccccc, dddddd, eeeeee, ffffff

>
>or like this:
>
>

Result2
>Field1, Field2, Field3
>aaaaaa, bbbbbb, cccccc
>dddddd, eeeeee, ffffff

>
>?
>
>
>>Hi Frank
>>
>>I could explain it like this:
>>
>>I have two transaction files that I want to join together for display purposes. They have no relation to each other really.
>>
>>One transaction file records what happens to each job, the other transaction just records when production starts end when production stops for all jobs. I am calulating totals for different processes as totals on the form for a selected job.
>>
>>So I want to join the two together to show all job activity and include production activity. Also when production stops I want that tp be reflected in the job totals.
>>
>>Ayway I've realised that this is a job for a query and a simple append from, so I've ended up with this:
>>
>>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
>>SELECT tmplog
>>APPEND FROM wipdata!productionlog
>>
>>The transactions I pull off from productionlog needs narrowing down by transaction date but you get the point. Also need to index tmplog on transaction date so log is in correct order.
>>
>> Really wanted to do this using 100% SQL but time to move on.
>>
>>>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.