Fairly New to SQL and now trying a bit more complicated coding

[cpascual]

I am using a database software known as GAGEpack, it is syntactically correct according to the SQL standard for Microsoft Access. I just had to start using the IIF command and I am fairly confident in using it on a small scale. In the coding below I am using multiple nested IIFs and I am trying the SWITCH function as well. I am also join all the tables into one so that it will contain all the necessary information.

My question is: Is there an easier way to code this using another function? I have not been able to get my coding to work and I am just not sure if my error is coming from my nested IIFs, SWITCH function, or that whole LEFT JOIN garbage. This software does not give me any error messages so I am unable to pinpoint my mistakes. So is there another function I should be using instead in order for me to select the correct data? I just need some suggestions and then I can try to figure out how to use the function on my own.


SQL1=SELECT Format(EventDate, "Short Date"), IIF(Type=23, 'Calibration', EventType.Name),
SQL2=IIF(EventType=23, IIF(CalibPassFail=-1, 'Pass', 'Fail'), IIF(EventType=0, IIF(CalibPassFail=-1, 'Pass', 'Fail'), 'N/A')),
SQL3=Format(WasLate, "\N\o;\Y\e\s"),
SQL4=SWITCH(EventType=0, CalibDoneBy, EventType=23, VendorName, EventType=3, VendorName, EventType=25, MaintDoneBy),
SQL5=Events.Comment, GageNumber
SQL6=FROM ((((((Gages LEFT JOIN Events ON Gages.GageId=Events.GageId)
SQL7=LEFT JOIN EventType ON (Events.EventType=EventType.Type))
SQL8=LEFT JOIN EventCalib ON (Events.EventID=EventCalib.EventID))
SQL9=LEFT JOIN EventRepair ON (Events.EventID=EventRepair.EventID))
SQL10=LEFT JOIN EventMaint ON (Events.EventID=EventMaint.EventID))
SQL11=LEFT JOIN Vendor ON (EventCalib.CalibVendorID=Vendor.VendorID))
SQL12=LEFT JOIN Vendor ON (EventRepair.RepairVendorID=Vendor.VendorID)
SQL13=WHERE (GageNumber >= '%1' AND GageNumber <= '%2') AND Events.GageId=$1 ORDER BY GageNumber, EventDate

 

[Fear Naught]

Are you doing this in SQL or Access? As your post is in the SQL Server Forum I am assuming it is SQL. T-SQL doesn't support the IIF() function and provides the CASE expression instead.

See: http://msdn.microsoft.com/en-us/library/ms181765.aspx

 

[cpascual]

FROM ((((((Gages LEFT JOIN Events ON Gages.GageId=Events.GageId)
LEFT JOIN EventType ON (Events.EventType=EventType.Type))
LEFT JOIN EventCalib ON (Events.EventID=EventCalib.EventID))
LEFT JOIN EventRepair ON (Events.EventID=EventRepair.EventID))
LEFT JOIN EventMaint ON (Events.EventID=EventMaint.EventID))
LEFT JOIN Vendor ON (EventCalib.CalibVendorID=Vendor.VendorID))
LEFT JOIN Vendor ON (EventRepair.RepairVendorID=Vendor.VendorID)


Will I have an issue with my last two LEFT JOIN statements? I want the vendors to be displayed for two different situations (for Calibrations and Repairs). Will this mess up the final table it spit out? If it will is there another way I should be doing this?