Hello,
I need to clean up data from text file which is huge.
I wonder if Access Expression Builder can mirroring "IF function" from Excel
Here is what i am trying to do
The data consist of multiple customer and multiple date. But the layout only specified customer ID once eg.
CustID : aaaa
Date
01012013
02012013
03012013
CustID : bbbb
01012013
02012013
When exported the file I used Fixed Width command to separate Date Column and Cust ID column.
Below is the result that I am looking for
CustID : aaaa : aaaa
Date : aaaa
01012013 : aaaa
02012013 : aaaa
03012013 : aaaa
CustID : bbbb : bbbb
01012013 : bbbb
02012013 : bbbb
1. Column A is Date
2. Column B is Customer ID
because Customer ID in column B only appear once, I need to create another column to populate that Customer ID whenever the transaction related with that Customer
3. Column C is the column where i tried to populate Customer ID to each date related to that Customer.
If I worked in excel the formula will be --> IF(AND(C5="",B6=""),"",IF(AND(C5="",B6<>""),B6,IF(B6<>"",B6,C5)))but in access i am stucked.
Please help and Thanks in advance!!
I need to clean up data from text file which is huge.
I wonder if Access Expression Builder can mirroring "IF function" from Excel
Here is what i am trying to do
The data consist of multiple customer and multiple date. But the layout only specified customer ID once eg.
CustID : aaaa
Date
01012013
02012013
03012013
CustID : bbbb
01012013
02012013
When exported the file I used Fixed Width command to separate Date Column and Cust ID column.
Below is the result that I am looking for
CustID : aaaa : aaaa
Date : aaaa
01012013 : aaaa
02012013 : aaaa
03012013 : aaaa
CustID : bbbb : bbbb
01012013 : bbbb
02012013 : bbbb
1. Column A is Date
2. Column B is Customer ID
because Customer ID in column B only appear once, I need to create another column to populate that Customer ID whenever the transaction related with that Customer
3. Column C is the column where i tried to populate Customer ID to each date related to that Customer.
If I worked in excel the formula will be --> IF(AND(C5="",B6=""),"",IF(AND(C5="",B6<>""),B6,IF(B6<>"",B6,C5)))but in access i am stucked.
Please help and Thanks in advance!!