UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Is It Possible To Create A Form That Looks Like A Cross-tab Query?, Access 2016    
 
   
swearmanc
post Nov 19 2019, 10:46 AM
Post#1



Posts: 2
Joined: 19-November 19



Hi all, long time lurker, first time poster.

I've used this site for months to complete my project and it has been extremely helpful. I can't tell you guys enough how many of the posts on here I have found helpful - couldn't have gotten this far without you all!

I need a form to build a working schedule - I'm scheduling drivers in trucks on certain days for 12 hour shifts. I have a driver schedule table (see attached jpeg)
Attached File  table.JPG ( 65.84K )Number of downloads: 8

that is one record containing driverID, date scheduled, default truck, etc. Then I have a crosstab query which displays the data in a nice easy to read way (see the other attached jpeg) that has the driver names and truck numbers as a row heading, the day of the week as a column heading, and displays a "1" if a driver is scheduled in that truck/day. What I am after is to design a form that looks like this crosstab,

Attached File  Crosstab.JPG ( 69.33K )Number of downloads: 8


with Driver Names as the row heading, Days of the week as a column heading, and then a combo-box in the "value" section so that a user can simply pick from the combo box the truck the driver should be scheduled in.

Is this possible? If there is an example of a similar set-up just point me in that direction and I can figure it out. Thanks!!!!

Go to the top of the page
 
GroverParkGeorge
post Nov 19 2019, 11:34 AM
Post#2


UA Admin
Posts: 36,204
Joined: 20-June 02
From: Newcastle, WA


Hi, and "officially" Welcome to UtterAccess.

You can DISPLAY records in a crosstab, yes. You can NOT edit those records in a crosstab, so no, this would not work as an input/update form.

You could probably set it up with a format that shows the crosstab in a subform that updates to DISPLAY your schedule as you do the actual updating in a standard Access form, though. I've done that very thing for a small clinic that had to schedule appointments with their staff showing times when they were booked and not booked. The left side of the form was devoted to the updating, and the right side of the form displayed the schedule as a crosstab in a subform control.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
isladogs
post Nov 19 2019, 12:59 PM
Post#3


UtterAccess VIP
Posts: 1,917
Joined: 4-June 18
From: Somerset, UK


As George stated you can easily create a read only form based on a crosstab query.
If you need it to be editable, you can use the crosstab to populate a 'temp' table and use that as the form record source.
Any changes made then need to be saved back to the original data table(s).
When the form is closed, the temp table is emptied

I've done this for various apps including a student attendance register form covering several dates/periods.
Its a little tricky to set up but works well with no time delay. As far as end users are concerned its just a standard form.
However using it repeatedly will cause file size to increase

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
GroverParkGeorge
post Nov 19 2019, 02:55 PM
Post#4


UA Admin
Posts: 36,204
Joined: 20-June 02
From: Newcastle, WA


That's a good point. A temp table can be created to mimic the functionality of the crosstab.

I often use a "side end" db for such tasks to avoid bloating the "front end".

Here's an example, although it was intended to be part of a different demo. It creates the temp accdb for the temp table

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
swearmanc
post Nov 22 2019, 09:36 AM
Post#5



Posts: 2
Joined: 19-November 19



Thanks for the advice guys... here is what I have so far. Trying to keep it as simple as possible. The user makes changes to the table via the subform on the left, then clicks refresh to see the new master schedule on the right. They will be using the drop-down filters built in to access to filter for day shift/night shift, and to sort the cross-tab on the right by last name or by truck number to see open spots.

Attached File  formsofar.JPG ( 131.83K )Number of downloads: 6


Anyone have any ideas on how I cold improve this? Was wondering if maybe I can change the "1" in the cross-tab to display as "Yes" or maybe even just a capital "X"
Go to the top of the page
 
GroverParkGeorge
post Nov 22 2019, 09:46 AM
Post#6


UA Admin
Posts: 36,204
Joined: 20-June 02
From: Newcastle, WA


IIRC, I used an "X" for this, but I'm also wondering if you could use conditional formatting to change the backcolor of a control .

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
isladogs
post Nov 22 2019, 10:23 AM
Post#7


UtterAccess VIP
Posts: 1,917
Joined: 4-June 18
From: Somerset, UK


If the value is always 1 or null, you could use IIf or Replace to substitute capital P in place of 1and then use Wingdings 2 font. That will be shown as a tick
Or capital O which shows as a cross in that font.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th December 2019 - 04:57 PM