Crosstab? Select..... argggh

[Dave1975]

Background.

I am coding a simple database that tracks jobs and which vehicle they are allocated to for delivery on a given day.

the team that schedule the jobs need a weekly clickable plan that shows the vehicles as the rows and the days of the week as the columns. the jobs showing need to be clickable to go to that record (job)

I can get 7 lists showing the jobs schedule for each day based of the Monday of that week for reference. with each list - if an entry is clicked it references that record.

I was really hoping to be able to do 1 list that each result will be clickable - so I have been trying to 'merge' the 7 queries I have that currently source the 7 lists.

The fields are simple. Vehicle Reg, Job Number and Del Date. The week commencing date is available on the form used and its used to provide criteria for the 7 queries. If I could somehow merge these lists/queries into one cross tab or similar to give something like this it would be great.

| | Mon | Tue | Wed | Thu | Fri | Sat | Sun |
|Vehicle 1 | --- | JOB1 | JOB4 | --- | JOB5 | --- | --- |
|Vehicle 1 | --- | JOB2 | JOB3 | --- | --- | --- | --- |
|Vehicle 1 | --- | JOB6 | JOB3 | --- | --- | --- | --- |
|Vehicle 2 | --- | JOB7 | --- | --- | --- | --- | --- |
|Vehicle 3 | --- | --- | --- | --- | JOB8 | --- | --- |
|Vehicle 3 | --- | --- | --- | --- | JOB9 | --- | --- |
|Vehicle 4 |JOBa| --- | --- | --- | --- | --- | --- |

really hoping there is a solution.....

 

[Minty]

http://www.access-programmers.co.uk/forums/showthread.php?t=281212&highlight=DesGrid
See this thread for some examples

 

[The_Doc_Man]

The problem you present is that you have multiple vehicles doing multiple jobs on the same day (e.g. Vehicle 1 does 3 jobs on Tuesday but Vehicle 2 does only 1 and Vehicle 3 is idle). That requirement makes the number of necessary rows for a given week almost totally impossible to predict. It also means that a crosstab might not work as expected in displaying the required data. Crosstabs are notoriously difficult for use in populating other tables because you have no guarantee as to what is in the actual row or column headers if there are blanks/nulls/unassigned slots in the table being cross-tabbed.

Then you want it clickable within your grid to jump to the Job info, and that means that an ordinary datasheet won't be enough to get the desired results without some really complex programming based on cursor x-y coordinates in ANY spreadsheet or datasheet.

Further, in the general case, one could imagine that if you added just a couple of vehicles you would run into the problem that you wouldn't be able to display the entire sheet at one time, which only makes it that much harder to use.

From a theoretical viewpoint, there is also the matter that for vehicle 1, the selector for a given slot will be something really arbitrary because neither "Vehicle" nor "Date" is enough to tell you which job is involved - which means that determination of the prime key (of the implied underlying table) is not going to be trivial either.

I don't want to seem like I'm raining on your parade, but I have to say that this is a totally non-trivial problem to solve because of the variable number of lines for a given vehicle and the requirement to be able to click on a slot in that variably-sized grid to follow a link elsewhere.

This is difficult because of what constitutes a "control" in Access. Those rows and columns in any kind of normal Access display would not be controls. They would be parts of a single control (such as e.g. a sub-form).

 

[Dave1975]

Doc Man: All valid points - did wonder if I was gonna achieve it through standard controls etc. I have it working with 7 lists but the software I am trying to replace looks like its been custom written in C+ or similar (very old bit of software).

Our IT doesn't allow much to happen without their say so and Access is my only outlet without them getting involved with their hoop jumping

Minty: Will check the examples to see if anything is in there that may help

Both: This is a new version of some old software and I am trying to emulate everything that is the current setup before changing stuff but if I cant replicate this function - then so be it.