corresponding data query

  • Thread starter Thread starter pat67
  • Start date Start date
P

pat67

Ok. I have a major problem that I am not able to figure out. here are
the details:

My compnay receives parts in on a PO and line item. that is one
movement. then after it is inspected, it is moved to stock. So each PO
and line item has a receipt movement and a corresponding inspection
movement. That works great provided there is only one of each
movement. The issue I am having is for some parts they are received
one at a time meaning an order for 10 pieces would have 10 movements.
The problem then is not being able to line up the receipt with its
corresponding inspection. what it looks like is this

PO Line Move Date
123 1 Rec 1/1/2010
123 1 Rec 1/1/2010
123 1 Rec 1/2/2010
123 1 Rec 1/3/2010
123 1 Rec 1/3/2010
123 1 Rec 1/4/2010
123 1 Ins 1/2/2010
123 1 Ins 1/2/2010
123 1 Ins 1/3/2010
123 1 Ins 1/5/2010
123 1 Ins 1/6/2010
123 1 Ins 1/6/2010



The way the query works is to put each PO and line item on one line.
The problem here would be that because there are 4 different rec.
dates, it puts each different ins. date down for each one because it
doesn't know which one should be. Is there any way in Access to remedy
this?
 
Indeed, there is no sure way to make a match. Assuming you have a MoveID
column (or at least, I will use one for illustration)

MoveID PO Line Move Date
1 123 1 Rec 1/1/2010
2 123 1 Rec 1/1/2010
3 123 1 Rec 1/2/2010
4 123 1 Rec 1/3/2010
5 123 1 Rec 1/3/2010
6 123 1 Rec 1/4/2010
7 123 1 Ins 1/2/2010
8 123 1 Ins 1/2/2010
9 123 1 Ins 1/3/2010
10 123 1 Ins 1/5/2010
11 123 1 Ins 1/6/2010
12 123 1 Ins 1/6/2010

Logically, in time, Inspection MoveID=7, may well be about Reception
MoveID=2, ModeID = 8 about MoveID=3, and so on, up to MoveID = 12 applied to
Reception MoveID=1 and if found defective, who ever is responsible would be
those implied by MoveID = 1, not those by moveID=6, for example. Other
scenario are possible too, so, as you said, there is no sure way to make a
single match.


If any match can do the job, then you rank as here , (ORDER BY PO_Line, Move
DESC ), and join such that Rank Of Inspection - Rank of Reception =
Count of Reception. Here, since the COUNT of reception (by PO_Line) is 6,
MoveID 7 will be matched with MoveID = 1 (since 7-1 = 6), and so will be
8-2, 9-3 .... 12 and 6.


If some more elaborate match should be performed, you would have to specify
it, and that should be based on the data available to you, which is, as it
is now, ambiguous for a strict match.



Vanderghast, Access MVP
 
Indeed, there is no sure way to make a match. Assuming you have a MoveID
column (or at least, I will use one for illustration)

MoveID PO Line Move Date
1            123 1 Rec 1/1/2010
2            123 1 Rec 1/1/2010
3            123 1 Rec 1/2/2010
4            123 1 Rec 1/3/2010
5            123 1 Rec 1/3/2010
6            123 1 Rec 1/4/2010
7            123 1 Ins 1/2/2010
8            123 1 Ins 1/2/2010
9            123 1 Ins 1/3/2010
10          123 1 Ins 1/5/2010
11          123 1 Ins 1/6/2010
12          123 1 Ins 1/6/2010

Logically, in time, Inspection MoveID=7, may well be about Reception
MoveID=2, ModeID = 8 about MoveID=3, and so on, up to MoveID = 12applied to
Reception MoveID=1 and if found defective, who ever is responsible would be
those implied by MoveID = 1, not those by moveID=6, for example. Other
scenario are possible too, so, as you said, there is no sure way to make a
single match.

If any match can do the job, then you rank as here , (ORDER BY PO_Line, Move
DESC ), and join such that  Rank Of Inspection  -  Rank of Reception  =
Count of Reception. Here, since the COUNT of reception (by PO_Line)  is6,
MoveID 7  will be matched with MoveID = 1  (since  7-1 = 6), and so will be
8-2, 9-3 ....  12 and 6.

If some more elaborate match should be performed, you would have to specify
it, and that should be based on the data available to you, which is, as it
is now, ambiguous for a strict match.

Vanderghast, Access MVP









- Show quoted text -
Click to expand...

Sorry, but I am confused. What would the sql be? I am not sure where
to insert the ORDER BY. Thanks
 
Sorry, but I am confused. What would the sql be? I am not sure where
to insert the ORDER BY. Thanks- Hide quoted text -

- Show quoted text -
Click to expand...

Here is my sql. where and how would I add the join and rank?

SELECT [2009Rec_Data].PO, [2009Rec_Data].Item, [2009Rec_Data].Type,
[2009Rec_Data].[Material Number], [2009Rec_Data].[Material
description], [2009Rec_Data].MvT, [2009Rec_Data].[Pstg date]
FROM 2009Rec_Data
GROUP BY [2009Rec_Data].PO, [2009Rec_Data].Item, [2009Rec_Data].Type,
[2009Rec_Data].[Material Number], [2009Rec_Data].[Material
description], [2009Rec_Data].MvT, [2009Rec_Data].[Pstg date]
ORDER BY [2009Rec_Data].PO, [2009Rec_Data].Item, [2009Rec_Data].MvT;
 
That assumes that 'any' match will do.

And to rank, you need to have some primary key, or something like that, that
will allow you to compute the rank (to differentiate the records that could
be otherwise totally identical). Computing the rank will be a query all by
itself, like:

--------------------------(untested)

SELECT PO, MvT, [Pstg date] As mDate,
(SELECT COUNT(*)
FROM 2009Rec_data AS b
WHERE b.PO = a.PO
AND b.Mvt IN( "Rec", "Ins")
AND (a.MvT < b.MvT
OR (a.MvT = b.MvT
AND (a.[Pstg date] > b.[Pstg date]
OR (a.[Pstg date] = b.[Pstg date]
AND a.pk >= b.pk
)
)
)
)
) AS rank


FROM 2009Rec_data AS a

WHERE MvT IN("Rec", "Ins")

--------------------------------


That should give a rank value, for all records, such that by PO, the records
with "Rec" are before any records with "Ins" and that for a given Mvt value,
the rank increases as the date increase (the primary key breaks any tie
left). The complex AND OR AND OR AND simply describe the exact order we
want, which ends up with a comparison on the primary key, the field pk
(change the name for that primary key field as appropriate in your case). As
computed here, the rank restarts at 1 for each PO.

That query may take a lot of time to execute. Better to plan a test on a
very small amount of data (less than 2000 records) for debugging purposes.

Once you got the rank by the previous query, say you call the query qRank,
you will write another query:

----------------------------------
SELECT PO, COUNT(*) AS length
FROM 2009Rec_data
WHERE MvT = "Rec"
GROUP BY PO
-----------------------------------

saved as qStat, which will give us all the required statistic to built the
final query:

------------------------------------
SELECT a.PO, a.MvT, a.mDate, b.MvT, b.mDate

FROM ((SELECT * FROM qRank WHERE MvT= "Rec" ) AS a
LEFT JOIN qStat AS c ON a.PO = c.PO)
LEFT JOIN (SELECT * FROM qRank AS WHERE MvT="Ins" ) As b
ON a.PO=b.PO
AND a.rank+c.length = b.rank
--------------------------------------

which matches a "Rec" Mvt to an "Ins" Mvt, as first in first out match. If
there is no values under b.mDate, that is because there is no "Ins" left to
match the "Rec" Mvt.



Vanderghast, Access MVP


Sorry, but I am confused. What would the sql be? I am not sure where
to insert the ORDER BY. Thanks- Hide quoted text -

- Show quoted text -
Click to expand...

Here is my sql. where and how would I add the join and rank?

SELECT [2009Rec_Data].PO, [2009Rec_Data].Item, [2009Rec_Data].Type,
[2009Rec_Data].[Material Number], [2009Rec_Data].[Material
description], [2009Rec_Data].MvT, [2009Rec_Data].[Pstg date]
FROM 2009Rec_Data
GROUP BY [2009Rec_Data].PO, [2009Rec_Data].Item, [2009Rec_Data].Type,
[2009Rec_Data].[Material Number], [2009Rec_Data].[Material
description], [2009Rec_Data].MvT, [2009Rec_Data].[Pstg date]
ORDER BY [2009Rec_Data].PO, [2009Rec_Data].Item, [2009Rec_Data].MvT;
 
That assumes that 'any' match will do.

And to rank, you need to have some primary key, or something like that, that
will allow you to compute the rank (to differentiate the records that could
be otherwise totally identical). Computing the rank will be a query all by
itself, like:

--------------------------(untested)

SELECT PO, MvT, [Pstg date] As mDate,
    (SELECT COUNT(*)
    FROM 2009Rec_data AS b
    WHERE b.PO = a.PO
        AND b.Mvt IN( "Rec", "Ins")
        AND  (a.MvT <  b.MvT
                OR (a.MvT = b.MvT
                    AND  (a.[Pstg date]  > b.[Pstg date]
                            OR (a.[Pstg date]= b.[Pstg date]
                                    AND a.pk >= b.pk
                                  )
                             )
                        )
                    )
    ) AS rank

FROM 2009Rec_data AS a

WHERE MvT IN("Rec", "Ins")

--------------------------------

That should give a rank value, for all records, such that by PO, the records
with "Rec" are before any records with "Ins" and that for a given Mvt value,
the rank increases as the date increase (the primary key breaks any tie
left). The complex AND OR AND OR AND  simply describe the exact order we
want, which ends up with a comparison on the primary key, the field pk
(change the name for that primary key field as appropriate in your case).As
computed here, the rank restarts at 1 for each PO.

That query may take a lot of time to execute. Better to plan a test on a
very small amount of data (less than 2000 records) for debugging purposes..

Once you got the rank by the previous query, say you call the query qRank,
you will write another query:

----------------------------------
SELECT PO,  COUNT(*) AS length
FROM 2009Rec_data
WHERE MvT = "Rec"
GROUP BY PO
-----------------------------------

saved as qStat, which will give us  all the required statistic to builtthe
final query:

------------------------------------
SELECT a.PO, a.MvT, a.mDate, b.MvT, b.mDate

FROM ((SELECT * FROM qRank  WHERE MvT= "Rec" ) AS a
    LEFT JOIN qStat AS c ON a.PO = c.PO)
    LEFT JOIN (SELECT * FROM qRank AS  WHERE MvT="Ins" ) As b
        ON a.PO=b.PO
        AND a.rank+c.length = b.rank
--------------------------------------

which matches a "Rec" Mvt  to an "Ins" Mvt, as first in first out match.. If
there is no values under b.mDate, that is because there is no "Ins" left to
match the "Rec" Mvt.

Vanderghast, Access MVP


Sorry, but I am confused. What would the sql be? I am not sure where
to insert the ORDER BY. Thanks- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

Here is my sql. where and how would I add the join and rank?

SELECT [2009Rec_Data].PO, [2009Rec_Data].Item, [2009Rec_Data].Type,
[2009Rec_Data].[Material Number], [2009Rec_Data].[Material
description], [2009Rec_Data].MvT, [2009Rec_Data].[Pstg date]
FROM 2009Rec_Data
GROUP BY [2009Rec_Data].PO, [2009Rec_Data].Item, [2009Rec_Data].Type,
[2009Rec_Data].[Material Number], [2009Rec_Data].[Material
description], [2009Rec_Data].MvT, [2009Rec_Data].[Pstg date]
ORDER BY [2009Rec_Data].PO, [2009Rec_Data].Item, [2009Rec_Data].MvT;- Hide quoted text -

- Show quoted text -
Click to expand...

what is a.pk and b.pk? When i run the first query it asks for
parameter values for those.

I have an ID for each transaction. the movements are numbered either
101 for receipt or 321 for inspection.
 
pk was the name I used for the primary key. If your field ID is your primary
key, use that field. A primary key must not have duplicated values.


Vanderghast, Access MVP
 
pk was the name I used for the primary key. If your field ID is your primary
key, use that field. A primary key must not have duplicated values.

Vanderghast, Access MVP
Click to expand...

When i get to the last query it says join expression not supported
 
Indeed, it seems we need to break it into two queries:


SELECT a.PO, a.mDate, a.Mvt, a.rank+c.length AS rPlusLength
FROM (SELECT * FROM qRank WHERE MvT= "Rec" ) AS a
INNER JOIN qStat AS c
ON a.PO=c.PO


saved as qInterm , and


SELECT a.PO, a.MvT, a.mDate, b.MvT, b.mDate
FROM qInterm LEFT JOIN
( SELECT * FROM qRank WHERE MvT="Ins") AS b
ON a.rankPlusLength = b.rank



Vanderghast, Access MVP

pk was the name I used for the primary key. If your field ID is your
primary
key, use that field. A primary key must not have duplicated values.

Vanderghast, Access MVP
Click to expand...

When i get to the last query it says join expression not supported
 
Back
Top