How many records as known total of them(3 CODi)? (1 Viewer)

btamsgn · Mar 27, 2022

Dear Everyone,
I want to create a sub or function as follows:
- List all materials fields "Manpl" from table "Tnpl" and insert/add into table1.T1,table1.T2,,table1.T3, with condition the same [table1].[tong]
for example: Table1.tong=16.6666666666678(=5.3333333333336+4.6666666666669+6.666666666667 or = 0.6666666666667+ 1.3333333333334+14.6666666666674) from fields table [Tnpl].[CODi]
results found : Table1(T1,T2,T3)= CA CD BE, CA BA RD, BE AH ZA, etc.

-Please find the attachment for your ref., thanks.

MajP · Mar 27, 2022

Code:

SELECT table1.t1,
       table1.t2,
       table1.t3,
       [A].[codi] + [b].[codi] + [C].[codi] AS CalcValue
FROM   ((table1
         INNER JOIN tnpl AS A
                 ON table1.t1 = A.manpl)
        INNER JOIN tnpl AS B
                ON table1.t2 = B.manpl)
       INNER JOIN tnpl AS C
               ON table1.t3 = C.manpl;

Query2 Query2

T1	T2	T3	CalcValue
AE	BA	RD	14.6666666666674
AE	BK	DS	20.6666666666677
BK	CD	RD	16.6666666666675
CA	CD	BE	16.6666666666675

btamsgn · Mar 27, 2022

MajP said:
Code:

SELECT table1.t1, table1.t2, table1.t3, [A].[codi] + [b].[codi] + [C].[codi] AS CalcValue FROM ((table1 INNER JOIN tnpl AS A ON table1.t1 = A.manpl) INNER JOIN tnpl AS B ON table1.t2 = B.manpl) INNER JOIN tnpl AS C ON table1.t3 = C.manpl;

Query2 Query2

T1 T2 T3 CalcValue
AE BA RD
14.6666666666674
AE BK DS
20.6666666666677
BK CD RD
16.6666666666675
CA CD BE
16.6666666666675

Dear MajP,
with total: 16.6666666666678 => results found 8 records, however query from you only 1 record.

Total	T1	T2	T3	Dem
16.66666667	CA	CD	BE	1
16.66666667	CA	BA	RD	2
16.66666667	AE	GD	BK	3
16.66666667	BK	CD	RD	4
16.66666667	BK	RC	ZA	5
16.66666667	BE	AH	ZA	6
16.66666667	AH	RC	FG	7
16.66666667	AH	WS	DS	8

MajP · Mar 27, 2022

Are you asking for potential 204 results? First 50 of those.
Query3 Query3

Total	T1	T2	T3
16.6666666666675	AE	AS	BE
16.6666666666675	AE	BA	HH
16.6666666666675	AE	BE	AS
16.6666666666675	AE	GD	BK
16.6666666666675	AE	HH	BA
16.6666666666675	AE	QA	RC
16.6666666666675	AE	ZA	WS
16.6666666666675	AH	AS	RD
16.6666666666675	AH	BA	QA
16.6666666666675	AH	BE	ZA
16.6666666666675	AH	BK	FC
16.6666666666675	AH	CD	HH
16.6666666666675	AH	DS	WS
16.6666666666675	AH	FC	BK
16.6666666666675	AH	FG	RC
16.6666666666675	AH	HH	CD
16.6666666666675	AH	QA	BA
16.6666666666675	AH	RC	FG
16.6666666666675	AH	RD	AS
16.6666666666675	AH	WS	DS
16.6666666666675	AH	ZA	BE
16.6666666666675	AS	AE	BE
16.6666666666675	AS	AH	RD
16.6666666666675	AS	BA	BK
16.6666666666675	AS	BE	AE
16.6666666666675	AS	BK	BA
16.6666666666675	AS	CA	RC
16.6666666666675	AS	CB	WS
16.6666666666675	AS	RC	CA
16.6666666666675	AS	RD	AH
16.6666666666675	AS	WS	CB
16.6666666666675	BA	AE	HH
16.6666666666675	BA	AH	QA
16.6666666666675	BA	AS	BK

btamsgn · Mar 27, 2022

MajP said:
Are you asking for potential 204 results? First 50 of those.
Query3 Query3

Total T1 T2 T3
16.6666666666675
AE AS BE
16.6666666666675
AE BA HH
16.6666666666675
AE BE AS
16.6666666666675
AE GD BK
16.6666666666675
AE HH BA
16.6666666666675
AE QA RC
16.6666666666675
AE ZA WS
16.6666666666675
AH AS RD
16.6666666666675
AH BA QA
16.6666666666675
AH BE ZA
16.6666666666675
AH BK FC
16.6666666666675
AH CD HH
16.6666666666675
AH DS WS
16.6666666666675
AH FC BK
16.6666666666675
AH FG RC
16.6666666666675
AH HH CD
16.6666666666675
AH QA BA
16.6666666666675
AH RC FG
16.6666666666675
AH RD AS
16.6666666666675
AH WS DS
16.6666666666675
AH ZA BE
16.6666666666675
AS AE BE
16.6666666666675
AS AH RD
16.6666666666675
AS BA BK
16.6666666666675
AS BE AE
16.6666666666675
AS BK BA
16.6666666666675
AS CA RC
16.6666666666675
AS CB WS
16.6666666666675
AS RC CA
16.6666666666675
AS RD AH
16.6666666666675
AS WS CB
16.6666666666675
BA AE HH
16.6666666666675
BA AH QA
16.6666666666675
BA AS BK

Dear MajP,
How to calculate with query 3 ?

MajP · Mar 27, 2022

Sorry the Attachment did not go through. I did a cartesian join of all possible combinations. So this does every combination, but allows repetition. AE, AS, BE
but also includes
AE, BE, AS
AS, AE, BE
AS, BE,AE
BE,AS, AE
....

If you only want one of those then the query is a little more complex.

btamsgn · Mar 27, 2022

MajP said:
Sorry the Attachment did not go through. I did a cartesian join of all possible combinations. So this does every combination, but allows repetition. AE, AS, BE
but also includes
AE, BE, AS
AS, AE, BE
AS, BE,AE
BE,AS, AE
....

If you only want one of those then the query is a little more complex.

My opinion, no repetition

MajP · Mar 27, 2022

Actually if you do not want repitition change the <> to > in qryAll

Code:

SELECT A.MaNPL AS T1, B.MaNPL AS T2, c.MaNPL AS T3
FROM Tnpl AS A, Tnpl AS B, Tnpl AS c
WHERE (((B.MaNPL)>[a].[manpl]) AND ((c.MaNPL)>[b].[manpl] And (c.MaNPL)>[a].[manpl]))
ORDER BY A.MaNPL, B.MaNPL, c.MaNPL;

btamsgn · Mar 28, 2022

Hi All,
Pls write a sub/function with results about 500 record if found because query making pc stable and stading.

Looking forwarding to receiving from you, thanks.

MajP · Mar 28, 2022

That query runs instantaneous without any problems on my computer. There is something wrong with your data base if this simple query does not run. Unless you add tens of thousands more records to the source table. Please provide the real database for review.

arnelgp · Mar 28, 2022

using function to generate result (into table Output).
see form1.

btamsgn · Mar 28, 2022

arnelgp said:
using function to generate result (into table Output).
see form1.

Dear arnelgp,
Currently, I want to open 6 columns in output instead of 3 columns
Pls update and advise, many thanks.

arnelgp · Mar 28, 2022

when i tried using 6 column, the vba goes on "forever".
when it is done, i have exceeded 2g records in Output table.

Gasman · Mar 28, 2022

btamsgn said:
Dear arnelgp,
Pls convert vba to vba access as follows:
Sub XYZ()
Dim sArr(), res(), sRow&, i&, i2&, i3&, T#, T2#, T3#, Tong#, k&
Const e# = 10 ^ (-12) 'Sai so cho phep
Const n& = 50 ' results rows found

i = Range("B999999").End(xlUp).Row
res = Range("B3:C" & i).Value
sArr = Range("B3:C" & i).Value
Range("B3:C" & i).Value = res
ReDim res(1 To n, 1 To 3)
Tong = Range("F3").Value 'Tong can tìm
sRow = UBound(sArr)

For i = 1 To sRow - 2
T = sArr(i, 2)
If T >= Tong Then Exit For
For i2 = i + 1 To sRow - 1
T2 = T + sArr(i2, 2)
If T2 >= Tong Then Exit For
For i3 = i2 + 1 To sRow
T3 = T2 + sArr(i3, 2)
If T3 > Tong + e Then
Exit For
ElseIf T3 >= Tong - e Then
k = k + 1
res(k, 1) = sArr(i, 1)
res(k, 2) = sArr(i2, 1)
res(k, 3) = sArr(i3, 1)
If k = n Then GoTo Ketqua
End If
Next i3
Next i2
Next i
Ketqua:
Range("G3").Resize(n, 3) = res
End Sub
'note: B3:C is from table "Tnpl"
'Tong is from table output
Looking forwarding to receiving from you.

How about doing some of the work yourself?

This site (and others) is here to help people, not just write code for those who come here and demand it.

arnelgp · Mar 28, 2022

here is for you 6 numbers.
it is slow since there are 6 numbers to permute.

btamsgn · Mar 29, 2022

arnelgp said:
here is for you 6 numbers.
it is slow since there are 6 numbers to permute.

Dear arnelgp,
I don't know what happened, after click "generate". My pc's stable and standing and I try to wait 10minutes Finally I close the microsoft access.

mercystone · Mar 29, 2022

Gasman said:
How about doing some of the work yourself?
This site (and others) is here to help people, not just write code for those who come here and demand it.

It is so disheartening to go on everywhere discouraging people who want to be assisted as if you know everything. You should learn to shut your mouth. @arnelgp is very experienced and he has helped so many people. Learning is a process for some people especially who are novices to ms access databases. If you don't want to assist people then ignore their posts. I want to thank these people especially @arnelgp and like-minded who devote their time to assist people even and including downloading databases and updating before uploading. God will bless them.

arnelgp · Mar 29, 2022

i think it is just like that, creating combination of 6 takes time and computing the total of the 6 combination and comparing
it to the first table also takes some time.

How many records as known total of them(3 CODi)? (1 Viewer)

Member

You've got your good things, and you've got mine.

Member

You've got your good things, and you've got mine.

Member

You've got your good things, and you've got mine.

Attachments

Member

You've got your good things, and you've got mine.

Member

You've got your good things, and you've got mine.

..forever waiting... waiting for jellybean!

Attachments

Member

..forever waiting... waiting for jellybean!

Enthusiastic Amateur

..forever waiting... waiting for jellybean!

Attachments

Member

Attachments

Member

..forever waiting... waiting for jellybean!

Attachments

Similar threads

Users who are viewing this thread