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.
dkma2010Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
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)
dkma2010Author Commented:
I will give this a try when I return to work.
dkma2010Author Commented:
I have not been to get your suggestion to work. I will keep trying but I keep getting other projects to work on.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

dkma2010Author Commented:
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.
Dale FyeOwner, Developing Solutions LLCCommented:
OK, lets try the query for subform #2

SELECT Top 25 yourTable.EmployeeID, yourTable.Time
FROM yourTable
WHERE yourTable.EmployeeID
NOT IN (SELECT TOP 25 EmployeeID FROM yourTable ORDER BY EmployeeID)
ORDER By yourTable.EmployeeID

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dkma2010Author Commented:
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 FyeOwner, Developing Solutions LLCCommented:
Glad I could help.

Do those subforms populate relatively quickly?
dkma2010Author Commented:
Yes they populate quickly. I am testing them out further to make sure everything works.
Dale FyeOwner, Developing Solutions LLCCommented:
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;
dkma2010Author Commented:
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 FyeOwner, Developing Solutions LLCCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.