Remove Duplicate for multiple record

[NurulScare]

Hi, help me. im Newbie in Access.

I Have 2 tables.
1. T_Kanban
field: Part No, Date, Qty

2. T_DO
Field: Part No and Qty

Issue: I want to get variance Qty Which mean, [T_Kanban.Qty]-[T_DO.Qty].
However, Qty for T_DO appear as multiple record. actually Qty=50 should only appear once.
the result should be:

Part No	Kdate	Kqty	SDO	Variance
PJ 970	1/1/2021​	100​	50​	50​
PJ 970	2/1/2021​	200​	0​	200​
PJ 970	4/1/2021​	300​	0​	300​

 

[arnelgp]

create first this query and name it, QryPNMinDateSDO.

SELECT T_Kanban.[Part No], Min(T_Kanban.KDate) AS MinOfKDate, T_Kanban.SDO
FROM T_Kanban
GROUP BY T_Kanban.[Part No], T_Kanban.SDO;

create the final query using T_Kanban and the query qryPNMinDateSDO:

SELECT T_Kanban.[Part No], T_Kanban.KDate, T_Kanban.KQty,
Nz([QryPNMinDateSDO].[SDO],0) AS SDO,
[KQty]-Nz([QryPNMinDateSDO].[SDO],0) AS Variance 
FROM T_Kanban LEFT JOIN QryPNMinDateSDO
ON (T_Kanban.KDate = QryPNMinDateSDO.MinOfKDate)
AND (T_Kanban.[Part No] = QryPNMinDateSDO.[Part No]);

 

[NurulScare]

Hi Arnelgp. Thank you for your reply. However I have question. SDO is comes from another table. not T_Kanban table. Here, I attached my access file. Please help me.

 

[arnelgp]

oh,
use this for qryPNMinDateSDO:

SELECT T_Kanban.[Part No],
Min(T_Kanban.KDate) AS MinOfKDate,
First(T_DO.Qty) AS FirstOfQty
FROM T_Kanban INNER JOIN T_DO ON T_Kanban.[Part No] = T_DO.[Part No]
GROUP BY T_Kanban.[Part No];

for the Final Query:

SELECT T_Kanban.[Part No],
    T_Kanban.KDate,
    T_Kanban.KQty, Nz([FirstOfQty],0) AS SDO,
    [KQty]-Nz([FirstOfQty],0) AS Variance
FROM T_Kanban LEFT JOIN QryPNMinDateSDO
    ON (T_Kanban.KDate = QryPNMinDateSDO.MinOfKDate) AND
    (T_Kanban.[Part No] = QryPNMinDateSDO.[Part No]);

 

[NurulScare]

omaigadddd u just saved my life!! Thankss, It worksss.. xoxo

 

[arnelgp]

goodluck

\\Edit: just now did i noticed your uploaded db.

 

[NurulScare]

oh,
use this for qryPNMinDateSDO:

SELECT T_Kanban.[Part No],
Min(T_Kanban.KDate) AS MinOfKDate,
First(T_DO.Qty) AS FirstOfQty
FROM T_Kanban INNER JOIN T_DO ON T_Kanban.[Part No] = T_DO.[Part No]
GROUP BY T_Kanban.[Part No];

for the Final Query:

SELECT T_Kanban.[Part No],
    T_Kanban.KDate,
    T_Kanban.KQty, Nz([FirstOfQty],0) AS SDO,
    [KQty]-Nz([FirstOfQty],0) AS Variance
FROM T_Kanban LEFT JOIN QryPNMinDateSDO
    ON (T_Kanban.KDate = QryPNMinDateSDO.MinOfKDate) AND
    (T_Kanban.[Part No] = QryPNMinDateSDO.[Part No]);

errr stil have question. What if after final query, KQty -SDOQty =-ve. How to bring balance to next date?

ex: 1/1/2021
KQty = 50
SDOQty = 100
Balance = -50.

Result Balance -50 Should bring to 2/1/2021. Become:
KQty = 100
SDOQty = 50 (from balance 1/1/2021)
Balance = 50

urghh this one is like running sum function Hopefully you can help me on this.

 

[arnelgp]

what i did is create a Function (see Module1).
and call the function in Query3.
note that Query3 is Sorted on PartNo and kDate Ascending.

 

[NurulScare]

what i did is create a Function (see Module1).
and call the function in Query3.
note that Query3 is Sorted on PartNo and kDate Ascending.

Hi this error appear. why?

 

[arnelgp]

change the line to:

dblSDO = Nz(dbSDO, 0)
fncMyBalance = dblSDO

 

[NurulScare]

change the line to:

dblSDO = Nz(dbSDO, 0)
fncMyBalance = dblSDO

I'd tried. but the result not as expected after I converted into my real data.

Actually the scenario is like these. I have two Tables.
1. Customer order (Tbl_Kanban) by date
2. Delivery Order (T02_DO) by date

Result I want it to be like this:

PartNo	KanbanDate	KanbanQty	DODate	DOQty	OutstandingKanban	Remarks
PART A	1-Jan	200	-	0	200	Outstanding Kanban for 1/1 is 200 because No delivery
PART A	2-Jan	300	2-Jan	50	450	On 2/1 Outstanding become 450 because Outstanding 1/1 (200) + Kanban on 2/1 (300) minus DO (50)
PART A	4-Jan	0	4-Jan	100	350	On 4/1, No kanban but have delivery. So minus DO 100.

Can you help me on this. I'm really stuck. I attached you my db. Pleaseeee

 

[arnelgp]

see qryFinal.
also see qryUNION.

 

[NurulScare]

see qryFinal.
also see qryUNION.

Wuuu thank You. IT WORKS!!

It just my database performance become very slow. can you advise why?

 

[arnelgp]

you can add Index on PartNo on both Tbl_Kanban and T02_DO.
also add index on the Date field on both table.

 

[NurulScare]

you can add Index on PartNo on both Tbl_Kanban and T02_DO.
also add index on the Date field on both table.

Thankss. I will try it now.

 

[NurulScare]

see qryFinal.
also see qryUNION.

Hi, From this I have another question. what if, the Part no transaction. how to bring forward the Qty to the next day?
ex:

Date	PART NO	KanbanQty	DODate	OutstandingKanban
1/8/2021​	PART A	100​	-	100​
2/8/2021​	PART A	-	-	100​

 

[arnelgp]

from qryFinal, i created another query, qryPartMaxDate.
and from qryPartMaxDate, again i create another query, qryPartLastBalance.

on you form, you need to:

DLookup("Balance","qruPartLastBalance", "PartNo = 'Part A'")

 

[NurulScare]

from qryFinal, i created another query, qryPartMaxDate.
and from qryPartMaxDate, again i create another query, qryPartLastBalance.

on you form, you need to:

DLookup("Balance","qruPartLastBalance", "PartNo = 'Part A'")

Sorry, I dont understand on Form. why should I put it in form?

 

[arnelgp]

you read up forms first.
you can't always use naked tables/queries.
it is very hard to add macro code the way
you want it to work.

 

[NurulScare]

you read up forms first.
you can't always use naked tables/queries.
it is very hard to add macro code the way
you want it to work.

uwaa sorry I cannot understand. huhu im not so advance in ms access. i attached my DB.
Actually i want to create WIP Qty (Qry_WIPQty) based on Production output minus FG go to logistic (FGRS). (The relationship between Table P01_ and P03_FGRS).
1. I already Follow ur step as per qryUnion and QryFinal
2. However I figure some error, the qty not bring forward to the next day.
3. Ex as below:

ex1: part 3211-BZ020A, 10-Aug have output, and part Out (FGRS), So the WIP qty become 180. So 11-Aug bring forward qty become 180
ex2: part 48622-BZ190-H, 10-Aug have output 300, no FGRS. then 11-Aug have Output qty in 200pcs. output become 500. no FGRS. so the qty bring forward to 12-Aug 500.

I want my query to look like this. huhu Thank you for your help ya.

Another question, is it possible to make my queries look like this table? like column heading. if not, i need to copy to excel and do pivot table to get this kind of table.

THANK YOUUU..