SQL Query - Understanding the logic.

[BayEnder]

Hi Guys,



First time poster so forgive me if this is in the wrong place.

Somebody within my company has basically left and I'm having to take over a task they do however I basically have no experience with SQL. There's a query ran within a database I need to edit because the raw data we input has changed.

Now I know the query is based on a table of data within the database that I've managed to amend to reflect the new raw data. The database then outputs the data based on this query into an Excel Spreadsheet.

However there's some parts of the query I'm unfamiliar with, anyway the SQL in the query is as below:

"SELECT [Table 1].[Incident Open], [Table 1].[Inc no], [Table 1].Severity, [Table 1].[Tower Start], [Table 1].Domain, [Table 1].[Brief Description], IIf(Len([Table 1].[Actual Tower Time_(dd:hh:mm:ss)])=11,Round(Left([Table 1].[Actual Tower Time_(dd:hh:mm:ss)],2)*24+Mid([Table 1].[Actual Tower Time_(dd:hh:mm:ss)],4,2)+Mid([Table 1].[Actual Tower Time_(dd:hh:mm:ss)],7,2)/60+Mid([Table 1].[Actual Tower Time_(dd:hh:mm:ss)],10,2)/3600,2),Round(Left([Table 1].[Actual Tower Time_(dd:hh:mm:ss)],3)*24+Mid([Table 1].[Actual Tower Time_(dd:hh:mm:ss)],5,2)+Mid([Table 1].[Actual Tower Time_(dd:hh:mm:ss)],8,2)/60+Mid([Table 1].[Actual Tower Time_(dd:hh:mm:ss)],11,2)/3600,2)) AS hours, IIf(Len([Table 1].[INFONAF BUS TIME(dd:hh:mm:ss)])=11,Round(Left([Table 1].[INFONAF BUS TIME(dd:hh:mm:ss)],2)*24+Mid([Table 1].[INFONAF BUS TIME(dd:hh:mm:ss)],4,2)+Mid([Table 1].[INFONAF BUS TIME(dd:hh:mm:ss)],7,2)/60+Mid([Table 1].[INFONAF BUS TIME(dd:hh:mm:ss)],10,2)/3600,2),Round(Left([Table 1].[INFONAF BUS TIME(dd:hh:mm:ss)],3)*24+Mid([Table 1].[INFONAF BUS TIME(dd:hh:mm:ss)],5,2)+Mid([Table 1].[INFONAF BUS TIME(dd:hh:mm:ss)],8,2)/60+Mid([Table 1].[INFONAF BUS TIME(dd:hh:mm:ss)],11,2)/3600,2)) AS infonaf_hours, infonaf_hours/24 AS [Working Days]
FROM [Table 1]
WHERE ((([Table 1].Tower)="Company"));"

The main parts I'm having trouble with are the "SELECT", LEFT, MID functions, e.g what do they do to the data? I assume the select is obviously just selecting the relevant fields from the table in the database.

A good example of my confusion is in this part of the query:

Mid([Table 1].[Actual Tower Time_(dd:hh:mm:ss)],7,2)/60

E.G what does the Mid mean? Is it similar to Excel? where the 7,2 after the statement determine which parts of the data we are picking.

I know this is pretty vague but any help at all would be greatly appreciated.

Thanks,
Brian.

[alcapps]

Mid([Table 1].[Actual Tower Time_(dd:hh:mm:ss)],7,2)/60

mid is a string function.. in the future you can right click on that and select definition. and it will explain it to you and give you an example.

Mid(string, startchar, endchar)

string str = "1234567"

mid(str,3,2)
= "34"
mid(str,3) and no ending
="34567"

so in your example
mid would return
the seventh char for 2 and divide by 60.

[orange]

Brian,

For reference there are many ms Access functions with complete syntax and examples at
http://www.techonthenet.com/access/

Good luck with your project.