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.