How to control records of NPL1 with condition (1 Viewer)

[btamsgn]

Dear Everyone,
I have two issues as follows:
1. How to control records of NPL1 with condition each 10 records, etc...
(Thanks for your support "arnelgp")

Total	NPL1	NPL2	NPL3
10.66667​	AA	DC	tC
10.66667​	AA	eA	tB
10.66667​	AA	eB	TA
10.66667​	AA	eC	SC
10.66667​	AA	FA	sB
10.66667​	AA	FB	SA
10.66667​	AA	FC	rC
10.66667​	AA	GA	RB
10.66667​	AA	GB	RA
10.66667​	AA	GC	qC
10.66667​	AB	DB	tC
10.66667​	AB	DC	tB
10.66667​	AB	eA	TA
10.66667​	AB	eB	SC
10.66667​	AB	eC	sB
10.66667​	AB	FA	SA
10.66667​	AB	FB	rC
10.66667​	AB	FC	RB
10.66667​	AB	GA	RA
10.66667​	AB	GB	qC
10.66667​	AC	DA	tC
10.66667​	AC	DB	tB
10.66667​	AC	DC	TA
10.66667​	AC	eA	SC
10.66667​	AC	eB	sB
10.66667​	AC	eC	SA
10.66667​	AC	FA	rC
10.66667​	AC	FB	RB
10.66667​	AC	FC	RA
10.66667​	AC	GA	qC

2. How to compare and list missing records from two tables (Table1 and Table2)
Pls find the attachment in your ref.

 

[arnelgp]

1. see modified code of XYZ() sub.
run Form1.
2. since Table1 has more records than Table2, Query2 will
list all records from Table1 Not in Table2.

 

[btamsgn]

Hi arnelgp,
1. How to add recordcount into table1 and table2 in column "countgrp" with group "NPL1"
=> Table2: example: AA: 1-10, AB:1-10, etc...
=>Table1: example : AA: 1-17, AB: 1-15, etc...
2. How to add recordcount into table1 and table2 in column "count all"
=> Table2: example: countall : 1 -> 100
=>Table1: example : countall : 1 -> 1000

Total	NPL1	NPL2	NPL3	countgrp	countall
10.66667​	AA	DC	tC	1​	1​
	AA	eA	tB	2​	2​
	AA	eB	TA	3​	3​
	AA	eC	SC	4​	4​
	AA	FA	sB	5​	5​
	AA	FB	SA	6​	6​
	AA	FC	rC	7​	7​
	AA	GA	RB	8​	8​
	AA	GB	RA	9​	9​
	AA	GC	qC	10​	10​
	AB	DB	tC	1​	11​
	AB	DC	tB	2​	12​
	AB	eA	TA	3​	13​
	AB	eB	SC	4​	14​
	AB	eC	sB	5​	15​
	AB	FA	SA	6​	16​
	AB	FB	rC	7​	17​
	AB	FC	RB	8​	18​
	AB	GA	RA	9​	19​
	AB	GB	qC	10​	20​
	AC	DA	tC	1​	21​
	AC	DB	tB	2​	22​
	AC	DC	TA	3​	23​
	AC	eA	SC	4​	24​
	AC	eB	sB	5​	25​
	AC	eC	SA	6​	26​
	AC	FA	rC	7​	27​
	AC	FB	RB	8​	28​
	AC	FC	RA	9​	29​
	AC	GA	qC	10​	30​
	BA	CC	tC	1​	31​
	BA	DA	tB	2​	32​
	BA	DB	TA	3​	33​
	BA	DC	SC	4​	34​
	BA	eA	sB	5​	35​
	BA	eB	SA	6​	36​
	BA	eC	rC	7​	37​
	BA	FA	RB	8​	38​
	BA	FB	RA	9​	39​
	BB	CB	tC	1​	40​
	BB	CC	tB	2​	41​
	BB	DA	TA	3​	42​
	BB	DB	SC	4​	43​
	BB	DC	sB	5​	44​
	BB	eA	SA	6​	45​
	BB	eB	rC	7​	46​
	BB	eC	RB	8​	47​
	BB	FA	RA	9​	48​
	BB	FB	qC	10​	49​

 

[arnelgp]

i added Autonumber field both to Table1, Table2 and Output table.
then i made queries for the 3 tables (qryTable1, qryTable2, and qryOuput) that has Count.

 

[arnelgp]

ohh, there is additional "Count All"