link data with specific conditon in query

[Nicolasdg]

Hi Everybody,
I'm working for a distribution company as packaging manager. One of my tasks is to provide clear overview to customers for container packaging in rent.
Actually it's a real mess, our ERP system does not allow to create such reports and therefore I need to put it all in an excel file which takes me one week to add and check +- 12000 cells.

One of my extractions is the movement of rental containers . This data is composed of outgoing lines with packaging batch and posting date and incoming lines also with packaging batch and posting date.

What I have :
:
Customer
Material
Batch
Posting Date
out
282724
1001
UB920204

3/07/2012​

in
282724
1001
UB920204

27/07/2012​

out
282724
1001
UB920204

13/03/2013​

in
282724
1001
UB920204

18/06/2013​

out
282724
1001
UB920204

20/09/2013​

What I would like to obtain:
Customer
Material
Batch
Posting Date out
Posting Date in
282724
1001
UB920204

3/07/2012​

27/07/2012​

282724
1001
UB920204

13/03/2013​

18/06/2013​

282724
1001
UB920204

20/09/2013​

0



What I obtain from access
Customer
Material
Batch
Posting Date out
Posting Date in
282724
1001
UB920204

3/07/2012​

27/07/2012​

282724
1001
UB920204

13/03/2013​

27/07/2012​

282724
1001
UB920204

20/09/2013​

27/07/2012




So is it possible to make access “understand” that he may only link data where posting date in is greater than posting date out but less than next posting date out???

 

[namliam]

Though I appreciate the effort you put into tryin to format your data, its almost impssible to comprihend :banghead:

You want to either post a (small) excell sheet/database containing your information or post it thusly:

Customer		Material	Batch	Posting Date	
282724		1001	UB920204	3/7/2012		out
282724		1001	UB920204	27/07/2012	in

I thiink what you are trying to do is possible by using a cartesian product, but getting information from previous/following records without auto / proper keys is a resource/time consuming business...

Unless your Batch/Material or something is a Natural key ... resorting to the cartesian is the only way.

Assuming your batch is your natural key something like:
Select *
From YourTable InTable
join YourTable OutTable on InTable.Batch = OutTable.Batch
where InTable.[Posting Date] < OutTable.[Posting Date]

If however your keys dont work you want to use some way of picking "Rank 1" which isnt possible in Access, unless you use some code and a "proper" database key and a "foreign key" to self join the table to itself without issues.
Or again use a (part) cartesian product and MIN(AutoKey) to create the foreign key on the fly which again is very resource requiring and potentially time consuming when you are talking about large number of records.
The coding way in this case seems feasable since the "out date" will not change over the course of time, once it has gone out.

FYI strongly suggest not using any spaces in column or table names. Its possible to do but can cause some issues.

 

[Nicolasdg]

Hello namliam,
i totally agree and apologize because reading me again i came to the same conclusion as you :banghead:
attached file:
sample 1: extraction from our ERP system (SAP)
mvt type : 621 = OUT and 622 = IN
batch = container reference
posting date = date when container left or came back at our plant.

sample 2: how I would like Access to query my data
posting date from mvt type 622 is linked to mvt type 621
fusion of both line into 1

in both sample you will see that one batch UB908899 has 3 'out mvt' and 2 'in mvt'. You may not have a posting date IN bigger than more than 1 posting date out.

well I hope this is better to understand

thanks again

 

[namliam]

So like I said or guessed, your batch is somewhat of a natural key but isnt really because the Batch UB908899 is not quite unique even in combination with Material Document or anything else...

So you will have to either find some natural key that will identify each shipment uniquely or resort to the costly cartesian product that I mentioned.

Also we need to consider if it is even remotely possible that there can be two rows with the same Batch and Date information.... anyways...
A simple query of

SELECT TableIn.Batch
     , TableIn.[Posting Date OUT] AS PostingIn
     , TableOut.[Posting Date OUT] AS PostingOut
FROM       YourTable AS TableIn 
INNER JOIN YourTable AS TableOut ON TableIn.Batch = TableOut.Batch
WHERE TableOut.[Posting Date OUT]>[tableIn].[posting date out]
  AND TableIn.[Movement Type]=621
  AND TableOut.[Movement Type]=622

Then create a second query on top of that which gets the min(PostingOut) date from the above query:

SELECT Batch
     , PostingIn
     , Min(PostingOut) AS MinPostingOut
FROM AboveQuery
GROUP BY Batch, PostingIn

Unfortunately you have to do this in two steps because Access doesnt work well with Where and Group by together at all times.... Not exactly sure why, but it does cause problems and this should just work. The second query in the MinPostingOut column should contain the send dates for the Batch/PostingIn dates... The batches that have not been send yet are not represented in this query... So "Left join" your original table to this one and you should have your answer your looking for.

Keep in mind though that this (in part) is creating a cartesian product which will start causing performance issues if you have large datasets