Join question 1

[shannanl]

I have two tables: Maint_history_T and Parts_T

I want to display all the parts in the parts table and a field called next_maint in Maint_history_T only if a field called status = 'Pending' in the Maint_history_T otherwise I want to leave the next_maint field blank in the recordset.

I tried something like this:

SELECT [mainthistory].[partid], [mainthistory].[maintdate], [mainthistory].[initials], [mainthistory].[status], [mainthistory].[notes], [mainthistory].[Maint_id], [partstable].[partkey], [partstable].[manukey], [partstable].[enditemkey], [partstable].[description], [partstable].[enditem], [partstable].[partnumber], [partstable].[serialnumber], [partstable].[stocknumber], [partstable].[location], [partstable].[quantity], [partstable].[datepurchased], [partstable].[manupart], [partstable].[manuserial], [partstable].[manustock], [partstable].[notes], [partstable].[maintinterval], [partstable].[btu], [partstable].[warranty], [partstable].[warranty_expires], [partstable].[loaned], [partstable].[loaned_to], [partstable].[loaned_date], [partstable].[return_date], [partstable].[part_id]
FROM mainthistory RIGHT JOIN partstable ON [mainthistory].[partid]=[partstable].[part_id];

This works fine except for one thing:

It shows a record for each date regardless of if the next_maint is pending or completed. So it actually shows two records, or more if there is multiple entries in the maint_history_T for a part. I only want one displayed, that being the next_maint_date ONLY if the status = 'Pending' otherwise just return null, etc.

Any help would be greatly appreciated. I hope my long winded explanation did not confuse anyone.

Thanks,

Shannan

 

[lupins46]

SELECT m.[partid], m.[maintdate], m.[initials], m.[status], m.[notes], m.[Maint_id], [partstable].[partkey], [partstable].[manukey],.....etc

from
Partstable
inner join
(Select * from mainthistory where Status = 'pending') as M
on
partstable.part_id = m.partid

 

[shannanl]

This gives me a syntax error in the join operation. I cant figure out why.

Shannan

 

[lupins46]

Can you post your sql.

 

[shannanl]

SELECT mainthistory.partid, mainthistory.maintdate, mainthistory.initials, mainthistory.status, mainthistory.notes, mainthistory.Maint_id, partstable.partkey, partstable.manukey, partstable.enditemkey, partstable.description, partstable.enditem, partstable.partnumber, partstable.serialnumber, partstable.stocknumber, partstable.location, partstable.quantity, partstable.datepurchased, partstable.manupart, partstable.manuserial, partstable.manustock, partstable.notes, partstable.maintinterval, partstable.btu, partstable.warranty, partstable.warranty_expires, partstable.loaned, partstable.loaned_to, partstable.loaned_date, partstable.return_date, partstable.part_id FROM partstable LEFT JOIN mainthistory ON partstable.part_id = mainthistory.partid ORDER BY description

Thanks,

Shannan

 

[shannanl]

This might be easier to understand:

I have two tables. A tabled called "partstable" which contains a bunch of parts. A table called "mainthistory" which is keyed to a part_id field in the partstable. Basically when a maintenance date is set for a part, an entry in the mainthistory table is written with a status of 'Pending' and the maintdate of the actual date the maintenance is scheduled for. I want to return a recordset of all the parts. One of the fields I want in the recordset is the maintdate (from mainthistory table). I want it to be the maintdate ONLY if the status of the matching record in mainthistory is 'Pending' otherwise just return a null value for the date. As it is now it returns all the completed dates and pending dates so I get 2-3 records for each part. I only want one for each part. Basically ignore the 'completed' status entries. I am using something like this but it returns the multiple entries for each part.

SELECT mainthistory.maintdate, mainthistory.status, *
FROM partstable LEFT JOIN mainthistory ON partstable.part_id = mainthistory.partid
WHERE (((mainthistory.status) Is Null)) OR mainthistory.status = 'Pending'


Something like this is what I am looking for:

Maintdate Part Number Part Name
10/20/2004 19823958885 Widget
12948928498 Widget Collar
2/25/2004 35395893858 Widget keeper

* The second entry does not have a 'Pending' status in the mainthistory table, it only has a 'Completed' status and the return is null not a date as 1 and 3 have because they are 'Pending'.

Thanks for the help,

Shannan

 

[lupins46]

The first thing you need to do is create a query based just on the mainthistory table which returns the records you want to use in the main query.
This appears to be:
Select * from Mainthistory where (status is null) or (status = 'Pending').
Save this as query1.

Now create a new query using the Parts table and query1 and join them on the partid fields.
You should now be able to convert the join to select all parts and select whatever fields you want.

 

[shannanl]

I tried what lupins had suggested. The first time I only recieved a syntax error. I tried it again last night and it worked perfectly. I must have got fat fingered the first time around.

Thanks for the help guys.

Shannan