updating two fields based on 2 criteria

[werdina]

Hello,

I have an access database where the data was imported from a separate system. The table contains the fields COMPANY, TRANSACTIONTYPE, DATE, PRICE. After importing, I need to add two columns COMPANYCODE and ACCOUNTNO that need to appear on the report. The COMPANYCODE of course depends on the COMPANY field. The ACCOUNTNO depends on both the COMPANY and the TRANSACTIONTYPE. There are 3 TRANSACTIONTYPEs per COMPANY. I was wondering if there is a way of updating the fields without having to create individual update queries since there are over a hundred companies involved.

English is not my first language so excuse me if my description is not clear.

 

[Remou]

You can create a separate company table with these fields:

CompanyCode
Company

This can be filled with a query using select distinct on the company field. Company codes can then be added. When the table is complete an index can be added to the company or the company code field. This table can be joined to the imported table in a query.

Another table can be created in a similar manner for account numbers. The fields would probably be:

Company
TransactionType
AccountNo

There would be no need to update the imported table as suitable queries would show the data with thw two extra columns. The extra tables would be reusable, should you need to import again.

 

[werdina]

hello again,

Thanks for your quick response. However, I cannot seem to create the right sql statement to be able to insert the codes on the same table. Can you give some idea on the construction of the sql statement?

 

[Remou]

Please post an example of your SQL statement.

 

[werdina]

SELECT DISTINCT CREXPORT1.DE_003REF, CREXPORT1.DE_002UTMANAGER, CREXPORT1.DE_004TRANDATE, CREXPORT1.DE_005ASSETFULL, CREXPORT1.DE_006TNOMINAL, CREXPORT1.DE_007UNITPRICE, CREXPORT1.DE_008CONSID, CREXPORT1.DE_009NOMINEE, CREXPORT1.ACCOUNTNUMBER, CREXPORT1.ACCOUNTNAME, CREXPORT1.REGHOLDER, AGENTCODE.AGENTCODE, ACCOUNTNO.ACCOUNTNO
FROM CREXPORT1, AGENTCODE, ACCOUNTNO
WHERE (((CREXPORT1.DE_002UTMANAGER)=[AGENTCODE].[DE_002UTMANAGER])) OR (((CREXPORT1.DE_002UTMANAGER)=[ACCOUNTNO].[MANAGER]) AND ((CREXPORT1.DE_009NOMINEE)=[NOMINEE]));

Every reference number (CREXPORT1.DE_003REF) appears twice.

 

[Remou]

I suggested that you create two additional tables, are AGENTCODE, ACCOUNTNO these tables? If so, you need a Join of some description, lets try an Inner Join, which will include only those records that find a match. This is an example, but I can only guess your field names:

[tt]SELECT DISTINCT CREXPORT1.DE_003REF,
CREXPORT1.DE_002UTMANAGER,
CREXPORT1.DE_004TRANDATE,
CREXPORT1.DE_005ASSETFULL,
CREXPORT1.DE_006TNOMINAL,
CREXPORT1.DE_007UNITPRICE,
CREXPORT1.DE_008CONSID,
CREXPORT1.DE_009NOMINEE,
CREXPORT1.ACCOUNTNUMBER,
CREXPORT1.ACCOUNTNAME,
CREXPORT1.REGHOLDER,
AGENTCODE.AGENTCODE,
ACCOUNTNO.ACCOUNTNO
FROM (CREXPORT1
INNER JOIN AGENTCODE
ON CREXPORT1.DE_002UTMANAGER =[AGENTCODE].[DE_002UTMANAGER])
INNER JOIN ACCOUNTNO
ON (CREXPORT1.DE_002UTMANAGER=ACCOUNTNO.[MANAGER])
AND (CREXPORT1.DE_009NOMINEE)=ACCOUNTNO.[NOMINEE])[/tt]

You may wish to read:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins