Material Qty Still Needed
1001 10
1005 5
Material Qty Still Needed Due Date
1001 10 1/1/2001
1001 10 2/1/2010
1001 10 3/1/2012
1001 10 7/1/2013
Material Qty Still Needed Due Date
1001 10 7/1/2013
Material Qty Still Needed Due Date
1001 10 7/1/2013
1005 5
Left outer join.
Ok first I am not sure what I was thinking. It is an Access Database
Left outer join works to a point. If I delete the part were I am checking to make sure the PO is closed. Then it will list all part numbers even if there is no PO for the items
IF I add
{PO_Detail.Status} <> "closed"
In the Selection Expert in either Record or Group.
Then it will only list the ones with Open PO if there is no PO it will not display the part number
IIRC (about 6-7 years ago) they had the best on-hold music I've ever heard. They even let you select the type of music you wanted rock, jazz, classical etc...Anyone here know Crystal Reports well?
So, how do you determine which POs are associated with the Job for which Materials have been ordered?
Possibly, depending on the structure add an
Or is null
After your <> 'closed'
.
Possibly, depending on the structure add an
Or is null
After your <> 'closed'
When you use <> 'closed' you could have possibly just got rid of your outer join, by forcing data to be in the right side, which would defeat the purpose of the outer join.
NULL is neither "equal to" nor "not equal to" anything... it's either "IS NULL" or "IS NOT NULL".... So ya gotta ask for them explicitly if you want them.
Seems like I remember Access handling joins/nulls in a weird way, too. Sounds like Ed might have the most viable solution, unless you want to throw the whole thing on MySQL where the join/null stuff is more predictable.
Yeah, anytime you have one table that may not have all the matching records in another table in the query, I've always ended up having to two two separate queries, and then left join the two queries. Especially when you throw a WHERE OR or IsNull in there. 16 years of slogging around in nothing but Access, I'm used to it.
Yeah, anytime you have one table that may not have all the matching records in another table in the query, I've always ended up having to two two separate queries, and then left join the two queries. Especially when you throw a WHERE OR or IsNull in there. 16 years of slogging around in nothing but Access, I'm used to it.
One of our customers uses an Access database to track (prioritization, pick status, shipment status) their small parcel shipments. It works great until the transportation department decides to change the format of the Excel spreadsheet that the database imports. Then it becomes 12 straight ours of "WTF?!" and "How the hell does this even work at all!?" to get it to deal with the new data layout.
I am convinced that MS Access runs on the "Magic" database engine.
One of our customers uses an Access database to track (prioritization, pick status, shipment status) their small parcel shipments. It works great until the transportation department decides to change the format of the Excel spreadsheet that the database imports. Then it becomes 12 straight ours of "WTF?!" and "How the hell does this even work at all!?" to get it to deal with the new data layout.
I am convinced that MS Access runs on the "Magic" database engine.
sql = "SELECT DISTINCT Material_Req.Material_Req AS nMaterial_Req,"_
& "Material_Req.Job AS sJob,"_
& "Material_Req.Material AS sMaterial,"_
& "Material_Req.Description AS sDescription, " _
& "Material_Req.Drawing_ID As sDrawing, " _
& "Material_Req.Est_Qty AS nEst_Qty,"_
& "Material_Req.Act_Qty AS nAct_Qty,"_
& "Material_Req.Pick_Buy_Indicator, " _
& " '' AS sVendor, " _
& "Job.Customer_PO AS sPO, " _
& "Material_Req.Due_Date AS dDue_Date, " _
& "Q1.nOn_Hand_Qty, " _
& oCmn.sSQLConvertToString("1", sDBType) & " as sHeader " _
& "FROM (Job INNER JOIN Material_Req ON Job.Job = Material_Req.Job) " _
& "LEFT JOIN " _
& "(SELECT Material_Location.Material, " _
& "Sum(Material_Location.On_Hand_Qty) AS nOn_Hand_Qty " _
& "FROM Material_Location " _
& "GROUP BY Material_Location.Material " _
& ")AS Q1 ON Material_Req.Material = Q1.Material " _
& sWhere