Crosstab data In forms

[Dan2kx]

Hello, i have a crosstab that generates a date range (curtosy of "Jim Doherty")and i would like to display that info on a form, the headers change by date depending upon your selection, so how can i make the form display these dynamic dates from the crosstab?

would i have to recreate the form each time? or the row source of the list box?

any suggestions?

Thank guys

[Jim Doherty]

Hello, i have a crosstab that generates a date range (curtosy of "Jim Doherty")and i would like to display that info on a form, the headers change by date depending upon your selection, so how can i make the form display these dynamic dates from the crosstab?

would i have to recreate the form each time? or the row source of the list box?

any suggestions?

Thank guys

Hi Dan,

I take it tested it out got that working from your previous thread then?. Remember, in one sense it is limiting because of the dynamic nature of the column headers in the crosstab. To set a conventional continuous form based on the changeable column names these being the 'date' is impractical in this scenario.

Probably the easiest and simplest way to merely display the data on a form would be to place a subform on a main form. On the main form you could place two unbound textboxes (date criteria) that control the data supplied to the subform. The subform would base itself on a 'query' as its sourceobject.

I have attached a zip file that demonstrates what I am talking about if you notice it will create a query for the subform the minute the main form opens. This query merely acts as a kind of proxy datasource for the subform if you like, until you click the button on screen that returns dynamic column dates based on those you supply.

Its all very workaroundish I think overall and remains so because of the limitations.

Regards

Jim :)

[Dan2kx]

Hello again thanks for that works a treat, i modified it a little bit so that there are buttons that display the dates by month

but i have another problem... im trying to get the crosstab to show the names (of the staff) as well but im not having any luck, access says that my query contains too many ambiguos outer joins or something.. any ideas?

i also want to sum the columns can that be done?

Thanks again

[Jim Doherty]

Hello again thanks for that works a treat, i modified it a little bit so that there are buttons that display the dates by month

but i have another problem... im trying to get the crosstab to show the names (of the staff) as well but im not having any luck, access says that my query contains too many ambiguos outer joins or something.. any ideas?

i also want to sum the columns can that be done?

Thanks again

Dan,

This going to be limited to what you can ultimately get with this. Its a query that you are seeing on screen via a subform and you can really only push the boat out so far with it.

However... I have revised the attached zip file module code and included additional functionality based around what you are asking. It has a sample staff table in there and also the holiday table. The SQL is revised to include the staff in the dataset so you might want to read that and all the amended code in the module to see how it fits into your environment.

I have basically revised the code to read the crosstab headers on the fly, build another SQL statement on the fly by manipulating sum aggregate headers based around the dynamism of the crosstab and then recreating an additional query which is then UNIONED with the crosstab to give the SUM totals. Beyond that..... there is not much more you can do with this workaround!

Regards

Jim :)

[Dan2kx]

Thanks again for all your help, i will have to wait till tonight to test it out so ill let ya know how it goes.

again thanks for the help.

[Dan2kx]

Hi, ive tested it out and it works great, but it doesnt keep the "placeholder" to extrapolate the null dates, i cant figure out why it isnt still doing it, the crosstab looks the samec(apart from the new columns) and its not just on the second one it doesnt show on the 1st either...
i dont want to trouble you too much more cos you have helped me quite alot...

i do have another question you might be able to help with...
my boss wants it to show in half days (that what his current thing does (in mumps if you have ever heard of that lol)) i have the holiday table set up like this

EntryID- PK
StaffID- FK
RequestID- links to staff table to denote the most current request (adds one to each booking)
Date
AM
PM
Comments

so i currently have one entry for each date using a mod function to check wot the day is (out of 14 possible (because he needs a two week shift pattern)) in the AM/PM boxes shows the ammount in minutes that they are taking off, relating to the shift they normally do on whichever day 1-14.

do you think that if i seperate the entry of AM/PM (and add the date twice for each date) and then run the "tblMyDates" query twice it would populate the crosstab with 2 dates for each? am and pm?

thanks again lots
hope you can advise

[Jim Doherty]

Hi, ive tested it out and it works great, but it doesnt keep the "placeholder" to extrapolate the null dates, i cant figure out why it isnt still doing it, the crosstab looks the samec(apart from the new columns) and its not just on the second one it doesnt show on the 1st either...
i dont want to trouble you too much more cos you have helped me quite alot...

i do have another question you might be able to help with...
my boss wants it to show in half days (that what his current thing does (in mumps if you have ever heard of that lol)) i have the holiday table set up like this

EntryID- PK
StaffID- FK
RequestID- links to staff table to denote the most current request (adds one to each booking)
Date
AM
PM
Comments

so i currently have one entry for each date using a mod function to check wot the day is (out of 14 possible (because he needs a two week shift pattern)) in the AM/PM boxes shows the ammount in minutes that they are taking off, relating to the shift they normally do on whichever day 1-14.

do you think that if i seperate the entry of AM/PM (and add the date twice for each date) and then run the "tblMyDates" query twice it would populate the crosstab with 2 dates for each? am and pm?

thanks again lots
hope you can advise

If you want to bring back the placeholder in the display then remove this line in the transform sql section in the code module

 
& " WHERE (((tbl_Staff.FirstName) Is Not Null) AND ((tbl_Staff.Surname) Is Not Null))" _
 
 

As for the remainder well splitting into AMPM is going to create a whole lot of problems on that current program flow because it was built around your initial request. To build it now around your later requirement would require a complete recode and revisit to the whole of it. So in short in needs doing all over again thats why it necessary to define from the outset what is actually required.

I'm sure Dan that given the existing code you can get your fingers around the method if nothing else and have a stab at it..

Regards

Jim :)

[Dan2kx]

Thanks for the fix, that is great, and i wasn't suggesting you redo it for me, your help has more than surpassed my expectations so far and for that i am extremely grateful, just wish my boss knew what he wanted, everytime i show him "new" stuff he says but it doesnt do this... ya just cant get the staff lol

regarding the am/pm thing.. is it possible to seperate this as it stands now in a crosstab? or would i need to redesign my appplication also?

thanks again

[Jim Doherty]

Thanks for the fix, that is great, and i wasn't suggesting you redo it for me, your help has more than surpassed my expectations so far and for that i am extremely grateful, just wish my boss knew what he wanted, everytime i show him "new" stuff he says but it doesnt do this... ya just cant get the staff lol

regarding the am/pm thing.. is it possible to seperate this as it stands now in a crosstab? or would i need to redesign my appplication also?

thanks again

I'm sure you wasn't Dan :) problem is I don't see overall what you have or have there from a complete system sense, nor do I have the ear of your boss to make him understand that programming is not an overnight thing to be revisited and changed overnight at a whim ( well he can of course, if he has a big enough wallet LOL) Believe me... been there, seen that, got the T shirt.

Regarding this bit:

(regarding the am/pm thing.. is it possible to seperate this as it stands now in a crosstab?)

Conventionally no... on existing design the crosstab is transforming on date so thats how the columns are representing themselves... as a single date per column. If you bring the AMPM into the equation as two separate fields then the display would have three elements to deal with and thus have to transform 'three' columns to keep visually within the output you require, in other words the date column and then two columns to the right of each date column to reflect a sum of the minutes per AM and an additonal column to represent a sum of the minutes PM.

Its not that a display of this type cannot be done, but not with the conventional transform SQL in Access. You would need to create and populate a physical table on the fly and even then, you are rapidly in danger of running out of columns to give you what you want.

Remember Access has a column limitation of 255, so three columns per date (and it has to be that way because you are wanting to sum aggregate for each column value in a matrix) is gong to restrict the range you can request overall. One months worth of data will require 90 columns at least. Not the best proposition, unless of course you never need more than 14 days then maybe its feasible (subject to testing).

This is a bit like driving down a road and navigating as you go. You know where you want to get to and you eventually get there, but in the process you pick up a dozen nails in your tyres, use three tanks full of gas and at the end of it realise the train/bus only cost 5 dollars (or 2.5 UK pounds).....ahahahah

Regards

Jim :)

[Dan2kx]

i have had an idea... which doesnt require a lot of thought/work...
i could duplicate the data in the holidays table so that the count would return
1=AM only
2=PM only
3=AM/PM
(i can just do sum more "If''s" in my existing code)
and that will be problem solved (a bit sloppy in the conventional database sense but hey.. it would work)

i have one final question (and then i promise to leave you alone) is it possible to write an expression ammonst the crosstab so that if the count is
1 then it shows "AM/__"
2 "__/PM"
3 "AM/PM" (this in place of the numeric values displayed in the crosstab)
or something similar (and i could exclude any other counts at an earlier stage in the program)
i hope that makes sense...
and thanks again

[Jim Doherty]

i have had an idea... which doesnt require a lot of thought/work...
i could duplicate the data in the holidays table so that the count would return
1=AM only
2=PM only
3=AM/PM
(i can just do sum more "If''s" in my existing code)
and that will be problem solved (a bit sloppy in the conventional database sense but hey.. it would work)

i have one final question (and then i promise to leave you alone) is it possible to write an expression ammonst the crosstab so that if the count is
1 then it shows "AM/__"
2 "__/PM"
3 "AM/PM" (this in place of the numeric values displayed in the crosstab)
or something similar (and i could exclude any other counts at an earlier stage in the program)
i hope that makes sense...
and thanks again

I'm finding it difficult to visualise what you have in mind Dan from the perspective of the little example app I wrote and how it fits in actually with your db. What does the underscores represent? Theres nothing to stop you dumping the contents of a crosstab into yet another table buult on the fly the datatypes of which could be text to handle the string concatenation side of things

Jim :)

[Dan2kx]

Well i can duplicate the records going into the table so that if they just want the morning off then it enters the record once, just pm can enter it twice and for both enter it a third time, this will of course return either 1/2/3 in the crosstab counts,

i just wondered weither or not it was possible to return rather than the number (1/2/3) instead AM/PM/Both (or something similar)

[Dan2kx]

hello again,
i have created a function that if inserted on a copy of the crosstab would return the values of the count as strngs:

Function DateCount(s As String) As String
Dim strReturn As String
strReturn = s
If s = "0" Then
s = "__/__"
End If
If s = "1" Then
s = "AM/__"
End If
If s = "2" Then
s = "__/PM"
End If
If s = "3" Then
s = "AM/PM"
End If
strReturn = s
DateCount = strReturn
End Fucntion

How could i implement that into the "On h fly" nature of the queries?

thanks
Dan

[Dan2kx]

Jim i've cracked it, i created a select query from the crosstab that generates the dates in the same way you did, it sums from the crosstab and then the union is made between the sum and the new query that outputs my function...

i was goin to upload the finished file (the one that you started (havent put it into mine yet thats gna take a while), so that you could see it but it looks like i cant)

but i basically used your method and just altered some of the sql

so for the final time (for now anyway) i thank you, couldnt have done it without you

Dan

[Jim Doherty]

Jim i've cracked it, i created a select query from the crosstab that generates the dates in the same way you did, it sums from the crosstab and then the union is made between the sum and the new query that outputs my function...

i was goin to upload the finished file (the one that you started (havent put it into mine yet thats gna take a while), so that you could see it but it looks like i cant)

but i basically used your method and just altered some of the sql

so for the final time (for now anyway) i thank you, couldnt have done it without you

Dan

Hey Dan you're welcome I'm pleased you sorted it and thanks for posting back

Regards

Jim :)

[Dan2kx]

(for now anyway)

Ok.... I have had another thought.....
what i have done since i last posted is display the union query in a list box, and have buttons to generate the start/end dates (in the txtboxes) but what i am trying to do now is to show the complete list of staff, ATM it shows only those who are on holiday, and i have tried "nz"ing the fields with no luck, and changing the row heading (staffid) to take from the staff table (rather than the holidays) ive tried to build a query to substite the two tables and list staff id next to the dates, and so on but with no luck... what am i doing wrong??
the idea is to list all staff in this query, and limit the list by department, can it be done?

[Jim Doherty]

Ok.... I have had another thought.....
what i have done since i last posted is display the union query in a list box, and have buttons to generate the start/end dates (in the txtboxes) but what i am trying to do now is to show the complete list of staff, ATM it shows only those who are on holiday, and i have tried "nz"ing the fields with no luck, and changing the row heading (staffid) to take from the staff table (rather than the holidays) ive tried to build a query to substite the two tables and list staff id next to the dates, and so on but with no luck... what am i doing wrong??
the idea is to list all staff in this query, and limit the list by department, can it be done?

Hi Dan,

I suspect it most probably can but my guess is by now you are probably far away from a coding sense from what contribution I made to this.

Rather than me working it out all over if you send me an 'empty' copy so I can see for myself what you have. PM me wth your emaiI and I will mail you

Jim :)