many to one relation

[cporter]

I'm not sure how to begin on this.

I want to check two tables and based on certain criteria generate a
list, PMDUE. I've gotten this far. Then I need to relate the PMDUE list
to a table, WO, in a many to one relation using the WO primary key
which is an autonumber field.


The basic fields are:

PM DUE
MachID, ProNo, Decript, datedue, where the MachID and ProNo combine for
the primary key

WO
MachID, WONo, where WONo is the the primary key and an autonumber field

So all the records on the PMDUE list with the same MachID will be
related to a single record on the WO table.


Can this be done and what method would you suggest?

Carl

 

[Tom Ellison]

Dear CPorter:

From your description, I think this may be what you're asking to have:

SELECT *
FROM WO
INNER JOIN [PM DUE] PM
ON PM.MachID = WO.MachID

An alternative is to have a LEFT JOIN instead of an INNER JOIN. I'll leave
it to you to see the difference and decide which it is you want.

Tom Ellison

 

[cporter]

A join doesn't produce the desired effect. I am trying to take data
from a table and assign numbers from another table based on a field
name, machid, so it fills in a different work order number for each
machid. Something like the info below. Can I use something like

While MachID=MachID, orderno=Last(workord.wono)+1




Table Workord
DAYDOWN DAYUP TIMEDOWN TIMEUP MACHID EMPLOYEE ACTION REQUEST1 COMMENT1 COMMENT2 WONO THD SUBSYS
06-Apr-06 06-Apr-06 15:15:00 19:30:00 ASP-137 JRE PF repair none none 65761 4.25 STR
06-Apr-06 07-Apr-06 23:45:00 5:00:00 ASP-137 CGF CO repair none none 65762 5.25 CHD
07-Apr-06 07-Apr-06 4:15:00 5:50:00 ASC-071 CGF CO repair none none 65763 1.58 CHD
06-Apr-06 12:00:00 ASP-137 SDL PF repair none none 65764 0 CHD
07-Apr-06 07-Apr-06 6:00:00 6:10:00 ASC-071 SDL PF repair none none 65765 0.17 CHD

Table PM Temp
MACHID DESC1 DESC2 FREQ DLP FLAGACT WONO RECORDNO PROCNO PROCFLAG
ASC-130 PM PM 31 10-Mar-06 65700 1554 28 TRUE
ASC-130 PM PM 31 10-Mar-06 65700 1555 29 TRUE
ASC-130 PM PM 7 31-Mar-06 65700 1558 32 TRUE
ASC-130 PM PM 7 31-Mar-06 65700 1559 33 TRUE
ASP-137 PM PM 180 05-Oct-05 65704 1564 43 TRUE
PLE-004 PM PM 7 30-Mar-06 65737 1574 1 TRUE
PLE-004 PM PM 7 30-Mar-06 65737 1575 2 TRUE
PLE-004 PM PM 7 30-Mar-06 65737 1576 3 TRUE
PLE-004 PM PM 7 30-Mar-06 65737 1577 4 TRUE
PLE-004 PM PM 7 30-Mar-06 65737 1578 5 TRUE
PLE-004 PM PM 7 30-Mar-06 65737 1579 6 TRUE
PLE-004 PM PM 7 30-Mar-06 65737 1580 7 TRUE
PLE-004 PM PM 30 08-Mar-06 65737 1581 8 TRUE