Consolidate 3 table to 1 1

[TheLazyPig]

Hi!

How to consolidate three dbf files into one? The table consist of tin,account_name,lname,fname,mname,tax_base,amt_wth,return_period.
I wanted to SUM tax_base and amt_wth within the three tables. So if the account_name of 3 tables has AAA the output would have a row of AAA with the sum of tax_base and amt_wth from the record in each table.

The only consolidation that I know is to combine all of the records into one table like APPEND FROM table2 to table1...

Thank you

 

[Steve Meyerson]

TLP,

You could probably do that in a single (long) SELECT statement. Another method (example):

USE Table1
APPEND FROM Table2
APPEND FROM Table3
SELECT Tin,Account_name, Lname, Fname, Mname, Return_period, ;
  SUM(Tax_base) AS 'TaxBase', SUM(Amt_wth) AS 'AmtWth' ;
  FROM Table1 ;
  INTO CURSOR AllAccts ;
  GROUP BY Account_name ;
  ORDER BY Account_name

Steve

 

[vernpace]

SQL SELECT has a UNION clause specifically for this purpose. Checkout VFP help fore more info.

 

[TheLazyPig]

SELECT filename as name FROM &pcCurName WHERE filename LIKE '%REMITTANCE%' INTO CURSOR curTemp
SELECT curTemp
	
USE (curTemp.name) IN 0 SHARED
    
    SELECT * FROM ( ;
    SELECT ALPHA_TYPE,FTYPE_CODE,TIN,BR_COD_PAY,ACCOUNT_NA 
		  ,LNAME_PAY,FNAME_PAY,MNAME_PAY,RETRN_PER,ATC_CODE ;	
		  ,TAX_RATE,SUM(TAX_BASE) AS TAX_BASE,SUM(AMT_WTH) AS AMT_WTH,TIN_WA,BR_COD_WA ;
	  FROM REMITTANCE_JAN2022 ;
	 UNION ;
	 SELECT ALPHA_TYPE,FTYPE_CODE,TIN,BR_COD_PAY,ACCOUNT_NA ;
		  ,LNAME_PAY,FNAME_PAY,MNAME_PAY,RETRN_PER,ATC_CODE ;	
		  ,TAX_RATE,SUM(TAX_BASE) AS TAX_BASE,SUM(AMT_WTH) AS AMT_WTH,TIN_WA,BR_COD_WA ;
	  FROM REMITTANCE_FEB2022 ;
	 UNION ;
	 SELECT ALPHA_TYPE,FTYPE_CODE,TIN,BR_COD_PAY,ACCOUNT_NA ;
		  ,LNAME_PAY,FNAME_PAY,MNAME_PAY,RETRN_PER,ATC_CODE ;	
		  ,TAX_RATE,SUM(TAX_BASE) AS TAX_BASE,SUM(AMT_WTH) AS AMT_WTH,TIN_WA,BR_COD_WA ;
	  FROM REMITTANCE_MAR2022 ;
	  ) GROUP BY TIN;
	  INTO CURSOR curDtl READWRITE

I get a syntax error.

curTemp = contains the path and name of the files

 

[Chris Miller]

Union is the wrong operation, it would add columns, so your final table would have all columns 3 times with suffixes automatically added to avoid double names.

You want to join data and sum grouped by all fields, mainly account_name, but all fields that you don't SUM. Join only appends data, but group then makes 1 out of all three records from jan,feb,mar.

Aside of that, having the filenames in data is fine, you only use one of the three tables, though. It's not necessary to use dbfs before SQL, SQL can also open the tables. So all you'll need is CD into the folder of them, unless they are all in different folders. If each month is a folder, then it would make sense to first use them, but then do it with a scan-loop on curTemp:

Select curTemp && btw is unnecessary directly after a query INTO CURSOR curTemp, curTemp is the currently activated workarea.
Scan
    USE (alltrim(curTemp.name)) IN 0 SHARED
Endscan

the query should be something like

SELECT fieldlist, SUM(TAX_BASE) AS TAX_BASE,SUM(AMT_WTH) AS AMT_WTH;
	  FROM REMITTANCE_JAN2022 jan;
          INNER JOIN REMITTANCE_FEB2022 feb ON jan.account_name = feb.account_name;
	  INNER JOIN REMITTANCE_MAR2022 mar ON jan.account_name = mar.account_name;
          GROUP BY fieldlist;
	  INTO CURSOR curDtl READWRITE

I don't know your data, but this might fail on too many fields. If you store say a transaction number, lets call it tn just for sake of exposition to talk about it. If that tn differs for each record, you can't have all of these numbers aggregated into one record that totals jan-mar. This field has to be dropped.

More concretely said if for account_name AAA there is a jan transaction with number 1, a feb transaction with number 3243 and a mar transaction with number 546456 you cant get a list 1,3243,546456 with a GROUP BY aggregation, you could sum them, which gives a nonsense result as that transaction number sum does not exist as transaction number, you could take the lowest of them with MIN(tn) or the highest with MAX(tn), but you'd need to create three columns tn_jan, tn_feb, tn_mar to store them all.

And what is said for a transaction number may be true for many of your fields. This will become a more complicate operation than just one group by query, if you need such lists or multiple fields to store values of jan,feb, and mar separately. The simpler solution is to drop the fields, if you still need to display them all, that's done with a join of the totaled record with the jan,feb, mar detail records that you still have, and that's how that's usually done instead of generating this into new fields.

And besides that "bad news" for all non-grouped fields, the problem becomes worse, if you have multiple records in each month already. Then you could need more fields for the single vaules, in theory even multiple per day. So that's surely not something you'd design, you'll need to drop this info from the total result as it's details that are all still available in the jan.mar tables and can be taken from there, when necessary.

Chriss

 

[Chris Miller]

To look into another detail:

Account_name, Lname, Fname, Mname

Without having a glimpse on your data, I'd say these 4 fields are the same for every record with the same Account_name and are fully determined by the Account_name alone, which will be unique for an account of somebody, no mattr if their name is unique among the accounts.

For this group having Account_name in the result is sufficient to then determine and join Lname, Fname, Mname. And that's how data should be organized in tables. Where a table defines a touple of data values for a unique identifier, it is stored. Anywhere else you only store the identifer as placeholder for all detail data.

If you do this throughout the whole data that's in very short and undetailed normalized data. And if you store all data that way, all consolidations become much simpler, as you keep details to just one origin table you can join but don't need to join.

It's just one more example of a question about how to query data that's not getting a simple answer, because the basis of the data storage isn't in the normalized form.

Chriss

 

[Steve Meyerson]

TLP,

If you are considering my original SELECT statement and you are using VFP9, I forgot to mention you will need to do ONE of the following, depending on the result you want. Otherwise you will get an error msg.

1. Eliminate the non-calculated fields (except account_name) from the SELECT clause.
2. Add the non-calculated field names to the GROUP BY clause.
3. SET ENGINEBEHAVIOR 70

Note: #2 could cause extra records for an account if the values of any of the non-calculated fields are not exactly the same for an account in each of the 3 tables.

Steve

 

[Chris Miller]

Indeed, pardon my brain fart of the day, LazyPig.
Sounds horrible, but I can live with it.

Union is correct.

Just one adjustment, since you said:

if the account_name of 3 tables has AAA the output would have a row of AAA...

The grouping should mainly be by account_name or ACCOUNT_NA as your own query suggests is the name of the field.

If TIN is just another value that is unique for an account, for example if this is the taxpayer identification number, then grouping by it works just the same. The other thing I said still is true, once you have one grouping identifier, there is no reason to have the others in the group by query, you can always join these further values like the three part name (fname,mname,lname) to the end result via the TIN or via the account_na.

That's less criticizing the query, but criticizing what details you store in your REMITTANCE tables that are already stored for each account name or tin once and for all further related data. Data redundancy is rarely a friend.

Chriss