asked on

Display of records to screen from a query in columns

I have a query which queries a table of employees clock in/out information. What we are wanting to do is display to the screen who is clock in for the day and number of hours worked up until the query has ran.
The query we have works just fine but I need to display it properly.
Sample:
Employee1 Time1 Employee24 Time24 Employee50 Time50
Employee2 Time2 Employee25 Time25 etc
Employee3 Time3 etc
etc until the bottom
of the screen/form then
start a new column on the
screen
I know you can do a report but what we want to do is display this information on a monitor and update the
display every 15 minutes. Can anyone guide me if Access can do this? I know you can do a single column list with a form but can you do multiple columns on form of the same fields? I hope that I have explained this well enough.

Dale Fye

You could create a two column form (probably either continuous or datasheet) which contains the EmployeeID and the time. You would need three copies of this form, and you would place these side-by-side as subforms on a main form.

The query for the left most version of this form would look something like (qry_Left)
SELECT Top 25 EmployeeID, Time FROM yourTable
ORDER By EmployeeID

The query for the 2nd version of this subform would look like(qry_Mid):
SELECT Top 25 EmployeeID, Time FROM yourTable
WHERE EmployeeID NOT IN (SELECT EmployeeID from qry_Left)

And the query for the 3rd version of this subform
SELECT Top 25 EmployeeID, Time FROM yourTable
WHERE EmployeeID NOT IN (SELECT EmployeeID from qry_Left)
WHERE EmployeeID NOT IN (SELECT EmployeeID from qry_Mid)

dkma2010

ASKER

I will give this a try when I return to work.

dkma2010

ASKER

I have not been to get your suggestion to work. I will keep trying but I keep getting other projects to work on.

dkma2010

ASKER

I have created the Main form and placed three subforms into the Main form. Each subform is based on a its own query. The first query returns the top 25 records but when I tried to do the second query based on the first query I didn't get any records. So I changed the queries to show the top 25 so that I know at least the forms and queries are working correctly and they do show the records but of course I want the first form to show the top 25 records and the second to show the next 25 records and the third to show the next 25 records. I'm having a problem of understanding how to get the second and third subform to display as suggested above.
I can attach the file if you want to take a look at it.

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dkma2010

ASKER

OK I finally got all three to work correctly now I had to use the wizard a little bit to get correct syntax. So I'll post the final syntax of all three. Not much different from your syntax but a few things did throw me off.

gry_Left
SELECT TOP 25 PR1.EMPNO, PR1.EMPNAME, PR1.HOURS
FROM PR1
ORDER BY PR1.EMPNO;

qry_Mid
SELECT TOP 25 PR1.EMPNO, PR1.EMPNAME, PR1.HOURS
FROM PR1
WHERE PR1.EMPNO NOT IN (SELECT EMPNO FROM qry_Left);

qry_Right
SELECT TOP 25 PR1.EMPNO, PR1.EMPNAME, PR1.HOURS
FROM PR1
WHERE (((PR1.EMPNO) Not In (SELECT EMPNO FROM qry_Mid))) and (((PR1.EMPNO) Not In (SELECT EMPNO FROM qry_Left)));

Thanks for your help

Dale Fye

Glad I could help.

Do those subforms populate relatively quickly?

dkma2010

ASKER

Yes they populate quickly. I am testing them out further to make sure everything works.

Dale Fye

The reason I ask is that the IN operator in a WHERE clause can be slow.
Another way to do this would be

SELECT PR1.EMPNO, PR1.EMPNAME, PR1.HOURS
FROM PR1
LEFT JOIN PR1 as Temp ON PR1.EmpNo >= Temp.EmpNo
GROUP BY PR1.EmpNo, PR1.EmpName, PR1.Hours
Having Count(Temp.EmpNo) <= 25
ORDER BY PR1.EMPNO;

This technique uses a non-equi join (notice the >= in the ON clause) which you cannot create in the query design grid. This method takes PR1 and joins it to itself, but with this non-equi join, you would get records that look like:

PR1.EmpNo Temp.EmpNo
1 1
2 1
2 2
3 1
3 2
3 3

By Grouping on the PR1 fields and counting the Temp.EmpNo fields, you can identify how many EmpNo's are less than the current number. This will be reflected in the Count(Temp.EmpNo) value that is present in the Having clause. For the left most query, you would use:
HAVING Count(Temp.EmpNo) <= 25. For the middle and right queries, you would adjust the HAVING clause to include the records you want.

SELECT PR1.EMPNO, PR1.EMPNAME, PR1.HOURS
FROM PR1
LEFT JOIN PR1 as Temp
ON PR1.EmpNo >= Temp.EmpNo
GROUP BY PR1.EmpNo, PR1.EmpName, PR1.Hours
Having Count(Temp.EmpNo) BETWEEN 26 and 50
ORDER BY PR1.EMPNO;

dkma2010

ASKER

Ok, thanks for the information I will give that a try. I just found a reason why my first attempts at this failed. I named my original queries with spaces in them qry 1, qry 2, qry 3 so I then named them just like your example qry_Left, qry_Mid, qry_Right and it worked so I went back and double checked my orginal because I knew I had followed your example sure enough I had to rename my original queries to qry_1, qry_2, qry_3 for it to work with the select statement.

Dale Fye

It is good practice to NOT put spaces in object (table, query, form, report, macro) or field names. Many relational database do not even allow this. Generally, I use underscores, many developers prefer to use CamelCase, where they don't put any gaps in but capitalize separate words. Either method works, makes it easy to read, and is acceptable in other RDBMs.

If you want to display a field name in a query, use the regular field name, and then use the Caption property of the field to insert spaces of change what the user sees when they view the query.