Excel - HELP

[TXHUNT3R]

You can give the Array a name in the name manager if you like. Highlight the lookup data then go to Formulas>Name Manager and give it a name... let's just call it Jared.

=vlookup(G2,Jared,2,false)

I see what you mean but I pull in different data sets.

I might run a report but it doesn't have a project name on 20K lines, I have to go pull in the project name based on a client number.

Vehicle report has classification code but no description... I have a sheet for that as well..... And so on.

[TXHUNT3R]

Yes its a great program. I couldn't live with out it. Then I stacked learning to code on top of it and now I can walk a line that most ppl cant between the IT world and the business world

Shoot, I can write Macros, but not code, you are ahead of me there.

A guy tried to show me once and I was like yeah ok....

[duckmanep]

Yes, once I go to select my range (ctrl + shift - arrow over - arrow down) my $ signs auto populate into the formula. I don't have to add them or hit F4.

My mind is blown. What version of Excel?

[duckmanep]

I have recently started learning some basic SQL. Running select queries and stuff now with simple joins. Beyond that I'm lost.

[Reaper33]

Shoot, I can write Macros, but not code, you are ahead of me there.

A guy tried to show me once and I was like yeah ok....

Like everything else it(SQL) just takes practice. That and you have to actually have a database you can query against. Then that syntax starts to translate into VB and SaaS

[Reaper33]

I have recently started learning some basic SQL. Running select queries and stuff now with simple joins. Beyond that I'm lost.

Google is your friend. I more or less learned how to code by looking it up on google or stealing other ppls stuff and looking It over and re-creating it.

[TXHUNT3R]

I used to be pretty skilled in Access but its been years now and I have lost a good deal of it, that was before the new windows as well, so I'd be screwed now..

[Reaper33]

This was from yesterday. Once you break it down into pieces its really pretty basic

IF OBJECT_ID ('TEMPDB..#Treat_Code') IS NOT NULL DROP TABLE #Treat_Code
IF OBJECT_ID ('TEMPDB..#Accounts') IS NOT NULL DROP TABLE #Accounts
IF OBJECT_ID ('TEMPDB..#Account_Holder') IS NOT NULL DROP TABLE #Account_Holder
IF OBJECT_ID ('TEMPDB..#Citizenship') IS NOT NULL DROP TABLE #Citizenship
IF OBJECT_ID ('TEMPDB..#W8BEN') IS NOT NULL DROP TABLE #W8BEN
IF OBJECT_ID ('TEMPDB..#Treaty_Accounts') IS NOT NULL DROP TABLE #Treaty_Accounts
IF OBJECT_ID ('TEMPDB..#Non_Treaty_Accounts') IS NOT NULL DROP TABLE #Non_Treaty_Accounts
IF OBJECT_ID ('TEMPDB..#Tax_id') IS NOT NULL DROP TABLE #Tax_id
IF OBJECT_ID ('TEMPDB..#Agreements') IS NOT NULL DROP TABLE #Agreements


/************************************************** ************************************************** ***********************
Assumptions
*Only looking at the tax holder for the account
*The treaty list is all inclusive
*Only accounts provided will be considered
*The expiration date for ALL W8's will be 7-1-16
*The expiration date for ALL Tax Status' will be 7-1-16
*All FATCA restrictions will be removed and then the FATCA program will re-add them as necissary
*The original agreement date will carry over to the new row

************************************************** ************************************************** ***********************/

set nocount on

Create Table #W8BEN
(
acc_id varchar (9),
cat_id varchar (3),
cat_seq_nbr varchar (1),
acc_tex_tex_tef_sts varchar (3),
hld_id varchar (11),
acc_hld_ctz varchar (2),
tax_id varchar (11),
acc_agr_d_rec datetime
)



--Get all of the country codes that we have a Treaty with
select def_cou_cod
INTO #Treat_Code
from def_cou
where ts = 0
and def_cou_cod in
('as','au','bg','bb','be','bu','ca','ch','cy','ez' ,'da','eg','en','fi','fr','gm','gr','hu','ic','in' ,'id',
'ei','is','it','jm','ja','kz','lg','lh','lu','mt', 'mx','mo','nl','nz','no','pk','rp','pl','po','ro', 'rs',
'lo','si','sf','ks','sp','ce','sw','sz','th','td', 'ts','tu','up','uk','us','ve')


--Get all of the accounts provided by Camille
select a.acc_id, a.cat_id, a.cat_seq_nbr, a.acc_tex_tex_tef_sts
INTO #Accounts
from acc_tex a with (nolock)
join acc_lku b with (nolock)
on a.acc_id = b.acc_id
join def_tex c with (nolock)
on a.acc_tex_tex_tef_sts = c.def_tex_cod
where a.ts = 0
and b.ts = 0
and c.ts = 0
and b.acc_lku_lku_cod = 'pcn'
and b.acc_lku_lku like 'FSW%'
and a.cat_id in ('fdt','fdi','for','foi')
and def_tex_tex_gov_sts <> 'for'


--Get account holder id
select acc.acc_id, acc.cat_id, acc.cat_seq_nbr, hld_id
INTO #Account_Holder
from #Accounts acc
JOIN acc_alh alh
on acc.acc_id = alh.acc_id and
acc.cat_id = alh.cat_id and
acc.cat_seq_nbr = alh.cat_seq_nbr and
alh.ts = 0 and
acc_alh_aff = 'tax'

--Get Citizenship for account holder
Select acc_id, cat_id, cat_seq_nbr, hld.hld_id, acc_hld_ctz
INTO #Citizenship
from #Account_Holder acc
JOIN acc_hld hld on acc.hld_id = hld.hld_id and hld.ts = 0


--Get tax-id for the holder
Select acc_id, cat_id, cat_seq_nbr, hlu.hld_id, acc_hlu_hlu_lku
INTO #Tax_id
from acc_hlu hlu
JOIN #Citizenship C on hlu.hld_id = c.hld_id and hlu.ts = 0 and acc_hlu_hlu_lku_cod = 'tax'


--Get the agreement recieved dates for all the accounts
select agr.acc_id, agr.cat_id, agr.cat_seq_nbr, acc_agr_d_rec
INTO #Agreements
from acc_agr agr
JOIN #Accounts A on a.acc_id = agr.acc_id and agr.cat_id = a.cat_id and agr.ts = 0
where doc_cod in ('fw1','fw2','w81','w82')



--Build Main Table of required information
Insert into #W8BEN
Select
a.acc_id,
a.cat_id,
a.cat_seq_nbr,
a.acc_tex_tex_tef_sts,
c.hld_id,
c.acc_hld_ctz,
t.acc_hlu_hlu_lku,
agr.acc_agr_d_rec
From
#Accounts a
JOIN
#Citizenship c on a.acc_id = c.acc_id and a.cat_id = c.cat_id
JOIN
#Tax_id T on a.acc_id = T.acc_id and a.cat_id = T.cat_id
LEFT JOIN
#Agreements agr on agr.acc_id = a.acc_id



--Identify the Accounts that we have a treaty with
select *
INTO #Treaty_Accounts
from #W8BEN W
JOIN #Treat_Code T on W.acc_hld_ctz = T.def_cou_cod



select *
INTO #Non_Treaty_Accounts
from #W8BEN
where acc_hld_ctz not in
(select def_cou_cod from #Treat_Code)





----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------


set nocount off



/************************************************** *******************
Begin doing work and update statements
************************************************** ********************/







DECLARE @GMTActTS DATETIME = GETUTCDATE()
DECLARE @TS NUMERIC(18, 6)
EXEC sws_convertactts2ts @GMTActTS, @TS OUTPUT





/************************************************** ************************************************** ***********************
Non zero the old agreement row and Insert new agreement row for the treaty and non treaty accounts.
This will be done in four sections. One section for the treaty accounts with a TIN, one section for the treaty accounts
without a TIN,One section for the non-treaty accounts with a TIN, one section for the non-treaty accounts without a TIN
************************************************** ************************************************** ***********************/



/*********************************
Treaty Accounts
*********************************/


--non zero the W8 agreement to insert a new row on the treaty accounts
update acc_agr
set ts = @ts
--select agr.*
from acc_agr agr
JOIN #Treaty_Accounts T on agr.acc_id = t.acc_id and agr.ts = 0
where doc_cod in ('fw1','fw2','w81','w82')
--298



--This insert is for accounts w/ a treaty and w/ a TIN
Insert into acc_agr
Select
NULL,
acc_agr_d_rec,
'7-1-16',
NULL,
NULL,
NULL,
acc_id,
cat_id,
cat_seq_nbr,
0.000000,
getutcdate(),
NULL,
acc_agr_d_rec,
NULL,
'fw1',
NULL,
hld_id
FROM
#Treaty_Accounts
Where
tax_id not in ('000-00-0000','111-11-1111')
--12



--This insert is for accounts w/ a treaty and w/out a TIN
Insert into acc_agr
Select
NULL,
acc_agr_d_rec,
'7-1-16',
NULL,
NULL,
NULL,
acc_id,
cat_id,
cat_seq_nbr,
0.000000,
getutcdate(),
NULL,
acc_agr_d_rec,
NULL,
'fw2',
NULL,
hld_id
FROM
#Treaty_Accounts
Where
tax_id in ('000-00-0000','111-11-1111')
--289







/*********************************
Non Treaty Accounts
*********************************/



--non zero the W8 agreement to insert a new row on the non treaty accounts
update acc_agr
set ts = @ts
--select agr.*
from acc_agr agr
JOIN #Non_Treaty_Accounts T on agr.acc_id = t.acc_id and agr.ts = 0
where doc_cod in ('fw1','fw2','w81','w82')
--32


--This insert is for accounts w/out a treaty and w/ a TIN
Insert into acc_agr
Select
NULL,
acc_agr_d_rec,
'7-1-16',
NULL,
NULL,
NULL,
acc_id,
cat_id,
cat_seq_nbr,
0.000000,
getutcdate(),
NULL,
acc_agr_d_rec,
NULL,
'w81',
NULL,
hld_id
FROM
#Non_Treaty_Accounts
Where
tax_id not in ('000-00-0000','111-11-1111')
--1


--This insert is for accounts w/out a treaty and w/out a TIN
Insert into acc_agr
Select
NULL,
acc_agr_d_rec,
'7-1-16',
NULL,
NULL,
NULL,
acc_id,
cat_id,
cat_seq_nbr,
0.000000,
getutcdate(),
NULL,
acc_agr_d_rec,
NULL,
'w82',
NULL,
hld_id
FROM
#Non_Treaty_Accounts
Where
tax_id in ('000-00-0000','111-11-1111')
--31


/************************************************** ************************************************** ***********************
Non zero the old tax status row and Insert new tax status row for the treaty and non treaty accounts.
This will be done in four sections. One section for the treaty accounts with a TIN, one section for the treaty accounts
without a TIN,One section for the non-treaty accounts with a TIN, one section for the non-treaty accounts without a TIN
************************************************** ************************************************** ***********************/


update acc_tex
set ts = @TS
--select tex.*
from acc_tex tex
JOIN #Accounts a on a.acc_id = tex.acc_id
where tex.ts = 0
--333



/*********************************
Treaty Accounts
*********************************/


--This insert is for accounts w/ a treaty and w/ a TIN
Insert into acc_tex
Select
0.000000,
0.000000,
0.000000,
'7-1-16',
GETUTCDATE(),
0.000000,
NULL,
NULL,
NULL,
acc_id,
cat_id,
cat_seq_nbr,
'fw1',
hld_id,
NULL
FROM #Treaty_Accounts
Where
tax_id not in ('000-00-0000','111-11-1111')
--12


--This insert is for accounts w/ a treaty and w/out a TIN
Insert into acc_tex
Select
0.000000,
0.000000,
0.000000,
'7-1-16',
GETUTCDATE(),
0.000000,
NULL,
NULL,
NULL,
acc_id,
cat_id,
cat_seq_nbr,
'fw2',
hld_id,
NULL
FROM #Treaty_Accounts
Where
tax_id in ('000-00-0000','111-11-1111')
--289



/*********************************
Non Treaty Accounts
*********************************/



--This insert is for accounts w/out a treaty and w/ a TIN
Insert into acc_tex
Select
0.000000,
0.000000,
0.000000,
'7-1-16',
GETUTCDATE(),
0.000000,
NULL,
NULL,
NULL,
acc_id,
cat_id,
cat_seq_nbr,
'w81',
hld_id,
NULL
FROM #Non_Treaty_Accounts
Where
tax_id not in ('000-00-0000','111-11-1111')
--1


--This insert is for accounts w/out a treaty and w/out a TIN
Insert into acc_tex
Select
0.000000,
0.000000,
0.000000,
'7-1-16',
GETUTCDATE(),
0.000000,
NULL,
NULL,
NULL,
acc_id,
cat_id,
cat_seq_nbr,
'w82',
hld_id,
NULL
FROM #Non_Treaty_Accounts
Where
tax_id in ('000-00-0000','111-11-1111')
--31



/************************************************** ************************************************** ***********************
This will non-zero all of the FATCA restrictions for the provided account list
************************************************** ************************************************** ***********************/

update acc_res
set ts = @TS
--Select *
from acc_res res
JOIN #Accounts a on res.acc_id = a.acc_id and res.ts = 0
where acc_res_res_cod in ('fa1','fa2','fa3','fa4','fa5','fa6','fa7','aol')
--827












Declare @UpdateLogAud varchar(2000)
Declare @LogNetDBServer varchar(200)

Set @LogNetDBServer = (
Select
sws_prm_def_param_value
from
sws_prm_def_glo with (nolock)
where
sws_prm_def_param_name = 'db_logging_srv' and
sws_prm_def_app_name = 'global' and
ts = 0
)

Set @UpdateLogAud = @LogNetDBServer + '..sws_utl_ins_log_aud_SP ' +
'''Query Analyzer'', ''Query Analyzer'', ''Query Analyzer'', ''100.100.100.100'', ''Query Analyzer'', ' +
'''100.100.100.100'', ''NonZero/Insert'', ''' + convert(char(23),@GMTActTS,121) + ''', ' +
'''ACC_TEX, ACC_AGR, ACC_RES'', ''' + 'Update tax status, agreements and rescritions on FSW foreign Accounts'''

Execute (@UpdateLogAud)

[Reaper33]

I used to be pretty skilled in Access but its been years now and I have lost a good deal of it, that was before the new windows as well, so I'd be screwed now..

Ya Access is a good learning tool for SQL bc it will generate the syntax for you. So you do it in Access and can see whats happening then you look at the syntax and can really see how it works

[TexasHoney]

Ya Access is a good learning tool for SQL bc it will generate the syntax for you. So you do it in Access and can see whats happening then you look at the syntax and can really see how it works

I'm an IT person and all I have to say is Access is the devil. [emoji49] it's great to use when there is nothing else but if I can help it I avoid it at all cost.