A Query in Ms-access

Question

Hi,

This query displays partnumbers which have eventtype is not equal to "PNremoved FROM Wrapper". This query is displaying the right data. Part number may be removed from this tracking number, but it can be entered using another tracking number. In that case it is not working.

I want the query that works when we enter the same part number with another tracking number.

Part number should not display with the tracking number with which we have used eventtype= "pn Removed from wrapper". But it should display with the another tracking number that has the same part number.

SELECT
    tblRevRelLog_Detail.RevRelTrackingNumber,
    tblRevRelLog_Detail.PartNumber,
    tblRevRelLog_Detail.ChangeLevel,
    tblRevRelLog_Detail.Version,
    tblRevRelLog_Detail.JobPnType,
    tblRevRelLog_Detail.EdsName,
    tblRevRelLog_Detail.DetailerNamePerPartNumber,
    tblRevRelLog_Detail.DetailerCompanyPerPartNumber
FROM
    tblRevRelLog_Detail LEFT JOIN tblEventLog
    ON tblRevRelLog_Detail.PartNumber = tblEventLog.PartNumber
WHERE
    tblEventLog.PartNumber Not In (
        SELECT tblEventLog.PartNumber
        FROM tblEventLog
        WHERE tblEventLog.EventTypeSelected = 'pn REMOVED From Wrapper')
ORDER BY
    tblRevRelLog_Detail.PartNumber;

I am showing this by using an example.

Reviewrelease_Form(main form) linked to reviewreleasetable:

this table contains basic data and wrapper number

                wrapper number:  Testing

RevRel_Form(subform) linked to tblRevRelLog_Detail

This form contains data about the part number

part no   chnglvl  jobpntype engineername company version
8765        1         XXXX      XXXXX       XXXX    xxxx
9898        0         xxxx       xxxxx      xxxx     xxxx
7889        2          xxxx      xxxxx       xxxx    xxxx

eventhistory(subform) linked to tblEventLog

This form contains events happended with part numbers

eventdate   partnum    eventtype       errortype     errorsubtype  comment

xxxxx        8765       1-receive new   xxxx           xxxx        xxxxx
xxxx         9898       1-recieve new   xxxx           xxxx        xxxxx
xxxx         7889       1-receive new   xxxx           xxxx          xxxx
xxxx         8765       2-assign        xxxx            xxx          xxx
xxx          9898       3 errors        xxxx            xxxx         xxxx

If i want to remove a part number from the wrapper, I will remove and the eventhistory form looks as shown below

eventdate   partnum    eventtype       errortype     errorsubtype  comment

xxxxx        8765       1-receive new   xxxx           xxxx        xxxxx
xxxx         9898       1-recieve new   xxxx           xxxx        xxxxx
xxxx         7889       1-receive new   xxxx           xxxx          xxxx
xxxx         8765       2-assign        xxxx            xxx          xxx
xxx          9898       3 errors        xxxx            xxxx         xxxx
xxx          9898       'pn REMOVED....' xxx             xxx        xxxx

Then the RevRel_Form should not display this partnumber data as follows:

part no   chnglvl  jobpntype engineername company version
8765        1         XXXX      XXXXX       XXXX    xxxx
7889        2          xxxx      xxxxx       xxxx    xxxx

This is working fine with the query that I have written in the Data source of the RevRel_form (The query that i have written above).

But the problem is, removed part number will come again with another new wrapper number in the future. with the query I am using right now, It would not display the partnumber data with the new wrapper number aswell.

So, I want the query that displays partnumber data in the RevRel_Form with new wrapper number. But not with the wrapper number in which we removed that part number.

If you cannot see records in the right format, check them out in the edit view.

Answer 1

You need to add a reference to the wrapper, for example:

WHERE
    tblEventLog.wrapper = 'wrapper id'
    AND tblEventLog.EventTypeSelected <> 'pn REMOVED From Wrapper'

If the event log does not contain a wrapper id, I think you have a design problem, because these events clearly apply to a particular wrapper.