Vlookup and IIF Functions, etc

  • Thread starter Thread starter vivi
  • Start date Start date
V

vivi

Hi there I am having some problems when trying to extract the data I want.

Basically i have these two tables set up:

1. GSTT Raw Data - it contains a list of Asset IDs, its cost center and
other financial infomation.
2. Master Project List - Contains Asset IDs and their Cost Centers

My task is to link the two tables together using the Asset IDs and obtain
the cost centers

The Master Project List is the master list which I need to use for all the
cost centers as the raw data table might not have the correct information.
However, not all the asset ids are in there (some of them are not used
therefore cannot update the master list with the raw data first), therefore
it returns to Error/Blank.

If it is in excel, I will write it as such:

=if(Master Project List! Cost Center = "", 'GSTT Raw Data'!Cost Center,
'Master Project List'!Cost Center)

This will allow me to have one column only in the query.

I have tried to write this in Access and as such:

Cost Center: IIf([TBL_Master_Project_List]![Project Cost
Centre]="",[TBL_Upload_GSTT]![Project Cost
centre],[TBL_Master_Project_List]![Project Cost Centre])

Nevertheless, it will give me the cost center in project master list, but
will not give the raw data's Cost Center if the master list is blank.

Therefore I was wishing to use the vlookup function, but not sure if it
would help and more, i don't know how to do vlookup in Access.

What I want to say is:

=if(iserror(vlookup(GSTT Raw Data!Asset ID,master project
list$A;$B,2,0),GSTT Raw Data!Cost Center,Vlookup(GSTT Raw Data! Asset ID,
master project list$A:$B,2,0)

I hope I make sense as I cannot express this in plain english!!!

Please someone help me...I can't figure it out at all...!! :(

Thanks a lot in advance

Vivi
 
ok if i am reading right you want all cost center information
regardless if it is in the master project table.

as a first note dont use ! use . you use ! if you are referencing a
form.

use this query

SELECT TBL_Upload_GSTT.*,TBL_Master_Project_List.*
FROM TBL_Upload_GSTT LEFT JOIN TBL_Master_Project_List
ON TBL_Upload_GSTT.assetid = TBL_Master_Project_List.assetid;

to use this query make a new query click cancel on the pop up window
then click view in the menu bar and then click sql view then past the
above in there. then run the query or switch back to design view and
run it it is your choice and it should get all the information from
the gst table and the master project table linking them via assetid
and also getting all information from the gst table even if it isnt in
the master join table

Hope this helps

Regards
Kelvan
 
Thanks Kelvan

I am a newby in Access and am not sure if I follow what you said here...I
have tried to adapt your SQL statements in my query but it kept sending me
error message.

I think it's because there are other tables as oppose to just these two -
the current month table and the rate tables, also I wish to able to select
the columns with the information i wanted not everything in GSTT table. Is
this possible or am I making it too complicated?

My SQL is as follow:

SELECT TBL_Current_Month.[Reporting Month], TBL_Upload_GSTT.From AS
[Reporting Period From], TBL_Upload_GSTT.To AS [Reporting Period To],
TBL_Upload_GSTT.[PMO ID], TBL_Master_Project_List.[Project Type],
TBL_Master_Project_List.[Project Name], TBL_Master_Project_List.[Project
Status], TBL_Master_Project_List.[Project Stage],
TBL_Master_Project_List.[Project Coordinator],
TBL_Master_Project_List.[Project Manager], TBL_Upload_GSTT.[Project Type] AS
[GSTT Project Type], TBL_Upload_GSTT.[Project Name] AS [GSTT Project Name],
TBL_Upload_GSTT.[Task name], TBL_Upload_GSTT.[Staff ID],
TBL_Upload_GSTT.Resource, TBL_Rates_GSTT.Rate, TBL_Rates_GSTT.[Emp type],
TBL_Rates_GSTT.[Staff Grade], TBL_Rates_GSTT.[Primary Role],
TBL_Upload_GSTT.[Project Role], TBL_Upload_GSTT.[Contracted Working Hours],
TBL_Upload_GSTT.[Hours booked], TBL_Upload_GSTT.[Type of hours],
Sum(TBL_Upload_GSTT![Hours booked]/TBL_Upload_GSTT![Contracted Working
Hours]) AS [Total Days Worked], Sum((TBL_Upload_GSTT![Hours
booked]/TBL_Upload_GSTT![Contracted Working Hours])*TBL_Rates_GSTT!Rate) AS
[Total Recharge], Sum((TBL_Upload_GSTT![Hours
booked]/TBL_Upload_GSTT![Contracted Working Hours])*TBL_Rates_GSTT!Overhead)
AS [Total Overhead], "GSTT" AS Functions,
IIf([TBL_Master_Project_List]![Project Cost
Centre]="",[TBL_Upload_GSTT]![Project Cost
centre],[TBL_Master_Project_List]![Project Cost Centre]) AS [Cost Center]
FROM ((TBL_Upload_GSTT RIGHT JOIN TBL_Current_Month ON
TBL_Upload_GSTT.[Reporting Month] = TBL_Current_Month.[Reporting Month]) LEFT
JOIN TBL_Master_Project_List ON TBL_Upload_GSTT.[PMO ID] =
TBL_Master_Project_List.[PMO Number]) LEFT JOIN TBL_Rates_GSTT ON
TBL_Upload_GSTT.Resource = TBL_Rates_GSTT.Resource
GROUP BY TBL_Current_Month.[Reporting Month], TBL_Upload_GSTT.From,
TBL_Upload_GSTT.To, TBL_Upload_GSTT.[PMO ID],
TBL_Master_Project_List.[Project Type], TBL_Master_Project_List.[Project
Name], TBL_Master_Project_List.[Project Status],
TBL_Master_Project_List.[Project Stage], TBL_Master_Project_List.[Project
Coordinator], TBL_Master_Project_List.[Project Manager],
TBL_Upload_GSTT.[Project Type], TBL_Upload_GSTT.[Project Name],
TBL_Upload_GSTT.[Task name], TBL_Upload_GSTT.[Staff ID],
TBL_Upload_GSTT.Resource, TBL_Rates_GSTT.Rate, TBL_Rates_GSTT.[Emp type],
TBL_Rates_GSTT.[Staff Grade], TBL_Rates_GSTT.[Primary Role],
TBL_Upload_GSTT.[Project Role], TBL_Upload_GSTT.[Contracted Working Hours],
TBL_Upload_GSTT.[Hours booked], TBL_Upload_GSTT.[Type of hours], "GSTT",
IIf([TBL_Master_Project_List]![Project Cost
Centre]="",[TBL_Upload_GSTT]![Project Cost
centre],[TBL_Master_Project_List]![Project Cost Centre]);


I am sorry if I make this so confusing !!

I really hope you can help me...Thanks a lot

Vivi
 
Back
Top