Background

The jnet Portal provides an Access extract facility for flexible reporting. This is a powerful reporting tool where the user can construct their own query in order to produce their own custom reports on any aspect of company data.

The extract is available under the Integration menu. Either a full or partial extract can be generated, where a partial extract is limited to the last 12 months' worth of data. It is recommended to always use the partial extract unless specifically required otherwise, as generating full extracts places a significant burden on system resources. Partial extract is selected by default. Click Extract to generate, and once it says "extract finished" click the Access icon to download.

Once downloaded, you may have to right-click on the file, select Properties and click Unblock on Windows systems. The file should then be able to be opened in Access. Open the file, and to start writing your SQL query, click Queries (under Objects) then select "Create a query in Design view". Dismiss the Show Table dialogue box that appears, then switch to SQL view by selecting SQL from the drop down menu that appears in the top left corner of Access, under "File".

Running queries

Some example queries are shown below, ranging from simple to more complex. To run a query, type it into the SQL window and click the red exclamation mark on the toolbar, marked Run. The results will be returned in a Datasheet View. To return to the SQL window, click the drop down, top left hand corner again.

Example queries

To return all of the results from the Sale table:

SELECT * FROM sale

Or to return just the Sale Number, Tax Point Date, Net Total and Gross Total from the Sale table:

SELECT salenumber, taxpointdate, netofvat, grossofvat FROM sale

When data is located on different tables, it is necessary to join them together in the query:

SELECT s.salenumber, t.day_total, t.hour_total FROM sale s LEFT JOIN sum_timesheetentry t ON s.timesheetref = t.timesheetref

The above example will return the number of hours or days attached to each sale. The query looks looks complicated, but the key part is the join, which always takes the form

SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.referencekey=t2.referencekey

In this case t1 and t2 are shorthand ways of writing out the full table names. You can use whatever you like instead of t1 and t2. Writing it out longhand would be:

SELECT * FROM table1 JOIN table2 ON table1.referencekey=table2.referencekey

...which would give the same results. The "ON" part tells Access which is the common key between the two tables. You can only join tables together that have a unique key in common.

To join to the Contract table to give hour/day rates and contract length:

SELECT s.salenumber, c.hourrate, c.dayrate, DateDiff("d", [c.startdate], [c.enddate]) AS contractlength FROM sale s LEFT JOIN contract c ON s.contractref = c.contractref

The above example does a join similarly to the timesheet example, but also uses an Access function to work out the length of the contract given the start and end dates. The first DateDiff parameter "d" tells Access that the output should be in days. By adding "AS contractlength" to the end, we are telling Access to display this in a column called "contractlength".

If a report is required on data contained in three or more tables, two or more joins will be required. This is achieved by nesting the join statements in brackets.

SELECT s.salenumber, t.hour_total, c.hourrate FROM
((sale s LEFT JOIN sum_timesheetentry t ON s.timesheetref = t.timesheetref)
LEFT JOIN contract c ON s.contractref = c.contractref)

The above query joins the sale table to the timesheet table and then joins both of those to the contract table. The first join is put within brackets, then the second join is added to the end. In the example above, the second join has also been enclosed within brackets to demonstrate that the second join then encompasses the whole of the first join. If a third join was needed, it can be seen that this should just be added to the end of the query.

To add a restriction to the data, simply add a WHERE clause on the end.

SELECT * FROM sale WHERE taxpointdate>"2011-07-01"

The above query will display sale records since July 1st 2011 only.