Update fields via query

[bigroo]

Hi all,



I have been asked to modify an Access database that someone else created and is no longer with the company. I am adding an import for external csv file for job tickets.

This import with be monthly into Access with job details, among the fields I that will import is Chargeback Code as numbers and another field name Cost Item as a text string. I have another table with 2 fields named Cost Item and ucmc code.
As the import process goes, csv comes into the database to a temp table. I will then have a select query that will drop in the ucmc code if the Cost Item name matches, no problems here.

The table all the data is to be appended too eventually has two fields for the ucmc code, st1 and st2. In the past, the monthly job details were entered manually into a form and the correct field was then selected, if st1 or st2.

I wish this all to be done as an append query, but here is the kicker. If the Chargeback code number begins with 3 then that ucmc code will drop into st1. If the Chargeback code number begins with anything other than 3 that ucmc code will drop into st2.
I know there is someone out there that can offer suggestions to make this work within the query.

Thanks in advance

[Robeen]

or . . .
You could have TWO append queries - one that appends all records that have Chargeback Code Numbers beginning with 3 - with the ucmc code going into st1 - and another where the Chargeback Code Numbers are NOT beginning with 3 - with the ucmc code going into st2.
Would that work for you?

[ssanfu]

You didn't provide table names, so I had to create my own. I tried to use your field names, except I don't use spaces in names.......
So...

INSERT INTO tblUCMC ( st1, st2, Cost_Item )
SELECT IIf(Left(NZ([ucmc_code],0),1)=3,[ucmc_code],0) AS S1, IIf(Left(NZ([ucmc_code],0),1)<>3,[ucmc_code],0) AS S2, NZ([EGU],"") AS C1
FROM tblTemp;

[bigroo]

Thanks Robeen, I was about to excute your 2 append queries, I really did not think of that but meeting I had to attend came up, so never got the chance.

But ssanfu, I decided to go with your solution, worked like a charm after trying to figure out what you had shown me. Here is what I finished up with.

INSERT INTO COPYCENTemp ( COST_CENTER, ST1, ST2, NAME, [DATE], O )

SELECT [Import-CSV SummaryUCMC Chargeback Raw Data].[Chargeback Code], IIf(Left(NZ([Chargeback Code],0),1)=3,[ucmc Stock code],0) AS ST1, IIf(Left(NZ([Chargeback Code],0),1)<>3,[ucmc Stock code],0) AS ST2, [Import-CSV SummaryUCMC Chargeback Raw Data].[Audit Full Name], [Import-CSV SummaryUCMC Chargeback Raw Data].[Closed Date], [Import-CSV SummaryUCMC Chargeback Raw Data].[Produced Consumed Item Quantity]

FROM [Import-CSV SummaryUCMC Chargeback Raw Data] INNER JOIN tblXJT_Cost_Items ON [Import-CSV SummaryUCMC Chargeback Raw Data].[Cost Item Name] = tblXJT_Cost_Items.[Cost Item Name];

My question is can I remove the zero's from the two columns if no value exists or if not I can do an update query for those 2 columns?

Thanks

[ssanfu]

My question is can I remove the zero's from the two columns if no value exists or if not I can do an update query for those 2 columns?

If you mean the fields st1 & st2, what are the data types? If they are numbers, I don't think so. But what does it matter? If you don't like looking at the zeros in the form, set the text box control format to not display zeros.

[bigroo]

Thanks for the input and reply. The St1 and St2 are Text type, so I will let them go, as you say, "What does it matter"
Thanks for the help, this SQL statement has moved me on quite a ways now.

[ssanfu]

Since they are text, you can change the 0 (zero) to "".

If(Left(NZ([Chargeback Code],0),1)=3,[ucmc Stock code],0) AS ST1,
If(Left(NZ([Chargeback Code],0),1)=3,[ucmc Stock code],"") AS ST1,

[bigroo]

Thanks ssanfu, I will give that a try!

[bigroo]

Thanks works like a charm ssanfu, that's the ticket mate!!