Fox 2.6 Pulling Data/Remove Duplicates

[jlg13]

Hi Guys, me again, making trouble...

I am trying to create a file (.txt) pulling data from various Fox 2.6 tables. I realize the help you give is only as good as the info I give so I will do my very best...

Ultimately I want to to create a mini program with the proper SQL statements. For now, I am just I am using the Create Queries from the command prompt (Create table using 1 query and then creating a 2nd table using results from first query plus more logic to create a final table with the data I want. Then a COPY TO file once I confirm data is accurate... Yeah, I know, probably not very efficient... (refer back to "making trouble" above)

My data is good but includes DUPLICATES I am trying to clean up.

Tables (I think I need) with field names (I think I need) and if indexed as FYI.
lm_pkage.cert_code (C,8) Indexed
lm_pkage.f_name (C,30)
lm_pkage.l_name (C,30) Indexed

lm_em2cc.cert_code (C,8) Indexed
lm_em2cc.email (C,75) Indexed

lm_stdnt.f_name (C,30)
lm_stdnt.l_name (C,30)
(There is an index on l_name+f_name)
lm_stdnt.email (C,75)

I will give you the 2 SQLs I have created.

In this final table I get results that include the following.

The way the overall data is set up...
lm_stdnt table has both Kimberly and Julianna (Same last name field = L_name) Lets use "Smith"
lm_pkage table has only ONE record with that certificate code (60A87184) and it is assigned to Julianna
lm_em2cc table has both kimberly and Julianna with unique email address

I would like the results to exclude Kimberly.

I have probably 15 situations that create duplicates like this that I would like to clean up.

Contributions appreciated. I also like to learn so feel free to explain.

Thanks in advance.

Joe

 

[Kamran_Ahmad]

(Code to generate the 2 SQL statements is missing, so can't suggest how to achieve the desired result while creating certset2.dbf)

Just after creating certset2.dbf:

SELECT * ;
[indent]FROM certset2 ;[/indent]
[indent]WHERE RTRIM(LTRIM(Cert_Code)) + UPPER(RTRIM(LTRIM(F_Name))) IN ;[/indent]
[indent][indent](SELECT RTRIM(LTRIM(Cert_Code)) + UPPER(RTRIM(LTRIM(F_Name))) ;[/indent][/indent]
[indent][indent]FROM lm_pkage) ;[/indent][/indent]
[indent]TO FILE MyTextFile     && sent output to "MyTextFile.txt"[/indent]

 

[jlg13]

Not sure why it did not display but here it is... without using the code display feature here.
Thanks Kamran


SELECT Lm_pkage.cert_code, Lm_pkage.min_remain, Lm_em2cc.email;
FROM Lm_pkage, Lm_em2cc;
WHERE Lm_em2cc.cert_code = Lm_pkage.cert_code;
AND Lm_pkage.min_remain > 0;
INTO TABLE certset1.dbf


SELECT Lm_stdnt.f_name, Lm_stdnt.l_name, Certset1.min_remain,;
Certset1.cert_code, Lm_stdnt.pk_expire;
FROM Certset1, Lm_stdnt;
WHERE UPPER(Lm_stdnt.email) = UPPER(Certset1.email);
ORDER BY Lm_stdnt.pk_expire;
INTO TABLE certset2.dbf

 

[mjcmkrsr]

Hi,

Please find below a sketch of code how to check for duplicates

*!*	Check for duplicates - Found records are highlighted, bold and italic

PUBLIC go_Form

go_Form = CreateObject ("frmForm")
go_Form.Visible = .T.
go_Form.Show

READ Events
CLOSE ALL
CLEAR ALL

***********************************************************************

DEFINE CLASS frmForm As Form
  Width = 450
  MinWidth = 450
  Height = 360
  MinHeight = 360
  AutoCenter = .T.
  Caption = "Identical Code2Check = Duplicate"
  

	ADD OBJECT grdNames as Grid WITH ;
		RecordSource = "csrAll", ColumnCount = 6, Visible = .t., Top = 12, Left = 12, Width = 426, Height = 294, Anchor = 15

	ADD OBJECT cmdCancel As CommandButton WITH;
		Width = 60, Height = 30, Left = 12, Top = 318, Caption = "Release", Anchor = 6

	PROCEDURE Load()
	
		CREATE CURSOR csrdemo (cDrawNo C(5), cBallNo C(3), cPartNo C(10), cCode C(1))
		INSERT INTO csrdemo VALUES ('8342C','130','61225-350',"N")
		INSERT INTO csrdemo VALUES ('8342D','130','61225-350',"Y")
		INSERT INTO csrdemo VALUES ('8342C','131','834X-131',"Y")
		INSERT INTO csrdemo VALUES ('8342D','131','834X-131',"Y")
		INSERT INTO csrdemo VALUES ('8342C','132','834X-132',"N")
		INSERT INTO csrdemo VALUES ('8342D','132','834X-132',"N")
		INSERT INTO csrdemo VALUES ('8342C','133','61225-362',"Y")
		INSERT INTO csrdemo VALUES ('8342D','133','61225-362',"Y")
		INSERT INTO csrdemo VALUES ('8342C','137','834X-137',"Y")
		INSERT INTO csrdemo VALUES ('8342D','137','834X-137',"Y")
		INSERT INTO csrdemo VALUES ('8342C','141','834X-141',"Y")
		INSERT INTO csrdemo VALUES ('8342D','141','834X-141',"N")
		INSERT INTO csrdemo VALUES ('8342D','141','834X-141',"N")
		INSERT INTO csrdemo VALUES ('8342C','150','67242-107',"N")
		INSERT INTO csrdemo VALUES ('8342D','150','67242-107',"N")
		INSERT INTO csrdemo VALUES ('8342C','152','8332-124',"N")
		INSERT INTO csrdemo VALUES ('8342D','152','8332-124',"Y")
		INSERT INTO csrdemo VALUES ('8342C','153','834X-153',"Y")
		INSERT INTO csrdemo VALUES ('8342D','153','834X-153',"Y")
		INSERT INTO csrdemo VALUES ('8342D','153','834X-153',"N")
		INSERT INTO csrdemo VALUES ('8342D','153','834X-153',"Y")
		INSERT INTO csrdemo VALUES ('8342C','154','834X-154',"Y")
		INSERT INTO csrdemo VALUES ('8342D','154','834X-154',"Y")
		
*!*	BEGIN of code to check for duplicates		

		SELECT csrDemo.*, csrDemo.cDrawNo + csrDemo.cBallNo + csrDemo.cCode as cCode2Check, csrDupes.lDupes ;
			FROM csrDemo ;
				JOIN (SELECT cDrawNo, cBallNo, cCode, .T. as lDupes FROM csrDemo GROUP BY 1, 2, 3 HAVING COUNT(cDrawNo + cBallNo + cCode) >= 2) csrDupes ;
					ON csrDemo.cDrawNo + csrDemo.cBallNo + csrDemo.cCode = csrDupes.cDrawNo + csrDupes.cBallNo + csrDupes.cCode;
			UNION ALL ;
				SELECT csrDemo.*, csrDemo.cDrawNo + csrDemo.cBallNo + csrDemo.cCode as cCode2Check, csrNoDupes.lDupes ;
					FROM csrDemo ;
					JOIN (SELECT cDrawNo, cBallNo, cCode, .F. as lDupes FROM csrDemo GROUP BY 1, 2, 3 HAVING COUNT(cDrawNo + cBallNo + cCode) < 2) csrNoDupes ;
						ON csrDemo.cDrawNo + csrDemo.cBallNo + csrDemo.cCode = csrNoDupes.cDrawNo + csrNoDupes.cBallNo + csrNodupes.cCode ;
			ORDER BY 2, 1 ;
			INTO CURSOR csrAll
			
*!*	END of code to check for duplicates

	ENDPROC
	
	PROCEDURE grdNames.Init()
		WITH ThisForm.grdNames
			.Column1.Header1.Caption = "DrawNo"
			.Column2.Header1.Caption = "BallNo"
			.Column3.Header1.Caption = "PartNo"
			.Column4.Header1.Caption = "in Stock"
			.Column5.Header1.Caption = "Code2Check"
			.Column6.Header1.Caption = "Duples?"
			.SetAll("DynamicFontItalic", "csrAll.lDupes", "Column")	  
			.SetAll("DynamicFontBold", "csrAll.lDupes", "Column")	  
			.SetAll("DynamicBackColor", "ICASE(csrAll.lDupes, RGB(225,0,0), ;
											MOD(ASC(RIGHT(csrAll.cDrawNo, 1)), 2) = 0, RGB(200,200,200), ;
											RGB(254,254,254))", "Column")	  	
		ENDWITH 
	ENDPROC 
	
	PROCEDURE cmdCancel.Click()
		CLEAR Events
		ThisForm.Release

	ENDPROC
  
	PROCEDURE Destroy()
		CLEAR Events
		ThisForm.Release

	ENDPROC
ENDDEFINE

***********************************************************************

hth

marK