sort crosstab query columns and generate report vba (1 Viewer)

[Hutchy]

This is a query, report and vba question.
I'm using Ms Access 2007.

TABLE 1: projectname, activityname, totalhoursworked, employeename
TABLE 2: employeename, employeelevel
TABLE 3: employeelevel, rate

I created a select query to join the info that I need.

SELECT QUERY 1: projectname, activityname, employeename, totalhoursworked, rate, cost (calculated field (totalhoursworked*rate))

I have 2 crosstab queries.

CROSSTAB QRY 1: ROW (projectname, activityname) COLUMN (employeename) VALUE (totalhoursworked (summed))

CROSSTAB QRY 2: ROW (projectname, activityname) COLUMN (employeename) VALUE (cost (summed))

I then created a 2nd select query with inner joins to join both crosstab queries on similar fields (activity & projectname).

SELECT QUERY 2: projectname, activityname, employeename (totalhoursworked as value), employeename (calculatedcost as value)

It gives me this:

However, I want it like this:

Please keep in mind that those employeename... refers to more employees being added after a period of time. Hence I want to know if I could use vba to generate a report every time a button is pressed on a form? I know how to link the form to the query.

Thanks

 

[CJ_London]

With regards column order, you can do this in select query2 - just drag the columns down in the order you want.

Or you can do it in your report.

A crosstab query will keep adding columns as you add employees and you have a maximum 22" of report width to play with. Allowing for the first two columns taking say 4" that leaves 18" - at two columns per employee at say 1" each per column means a maximum of 9 employees - or 11 if you put the first two columns in a different section of the report. So first question - is that going to be enough? If not, you need to rethink how you present the data.

With adding new employees then you have a problem - you need to go into the report design to add new columns for the new employee. However there is a workaround if you are prepared to use a datasheet view of the data. That workaround is to have a report and simply drag your select query2 onto it as a subform.

With regards the easy bit

I want to know if I could use vba to generate a report every time a button is pressed on a form? I know how to link the form to the query.

yes - use docmd.openreport

 

[Hutchy]

...

With regards column order, you can do this in select query2 - just drag the columns down in the order you want.

Or you can do it in your report.

Wouldn't I have to do that every time a new employee is added?

A crosstab query will keep adding columns as you add employees and you have a maximum 22" of report width to play with. Allowing for the first two columns taking say 4" that leaves 18" - at two columns per employee at say 1" each per column means a maximum of 9 employees - or 11 if you put the first two columns in a different section of the report. So first question - is that going to be enough? If not, you need to rethink how you present the data.

With adding new employees then you have a problem - you need to go into the report design to add new columns for the new employee. However there is a workaround if you are prepared to use a datasheet view of the data. That workaround is to have a report and simply drag your select query2 onto it as a subform.

Sounds like I'm going to have a problem
There's going to be 2 groups of data: planned and actual.
So maybe planned on one page and actual on another, if possible?

With regards the easy bit
yes - use docmd.openreport

Isn't this with the assumption that I already have a report made?

 

[CJ_London]

Wouldn't I have to do that every time a new employee is added?

depends on what you are going to do with it

You could have planned on first row, actual on second (get more employees on report that way)

Or something I have done in the past is merge two columns into one e.g. in query2

Evaluated:[Planned] & "/" & [Actual]

which would be

8/3
10/4

this converts it to a text but you can make it look better along the following lines which will right align each value

Evaluated:space(2-len([Planned])) & [Planned] & "|" & space(5-len([Actual])) & [Actual]

 

[Hutchy]

CAn you help me with something first?

How do I get the two crosstab queries to come together in a select query and displays everything without repeating the projectname and activityname?

This picture is wrong, it should show the projectname and activityname being repeated.

 

[CJ_London]

rather than using SELECT * you need to select the individual columns e.g.

SELECT XTab1.ProjectName, XTab1.activityname, XTab1.employeeName & "/" & XTab2.employeeName AS Employee1, ....

 

[Hutchy]

rather than using SELECT * you need to select the individual columns e.g.

SELECT XTab1.ProjectName, XTab1.activityname, XTab1.employeeName & "/" & XTab2.employeeName AS Employee1, ....

Here's my problem...
I'm aware if selecting by columns - though I don't know what this does & "/" & and the AS is - but after doing the crosstab query my empname field got distributed as brown, john, doe, john, etc...

So, when I tried using TESTPCEQryHrsCost.empname, I got this error

Unless I have to use TESTPCEQryHrsCost.brown, john... which I can't do because more employees will be added.

Here's the query

SELECT PCEQryHrs_Crosstab.Project, PCEQryHrs_Crosstab.Activity, PCEQryHrs_Crosstab.empname & "/" & PCEQryCost_Crosstab.empname AS Campbell, Garrett
FROM PCEQryHrs_Crosstab INNER JOIN PCEQryCost_Crosstab ON (PCEQryHrs_Crosstab.Project = PCEQryCost_Crosstab.Project) AND (PCEQryHrs_Crosstab.Activity = PCEQryCost_Crosstab.Activity);

PCEQryHrs_Crosstab is the crosstab that shows the total hours for each empname.
PCEQryCost_Crosstab is the crosstab that shows the calculated cost for each empname.

PS. Thanks for responding

 

[CJ_London]

The error message speaks for itself - if you don't understand it - google it.

I'm only going by what you stated- employee1, employee2 etc rather than empname.

It's not difficult - when you have dragged both of your crosstabs onto the query grid for what you called select query2 - you can see the field names - use them.

with regards the 'I don't know what this does & "/" ' that is per my previous post as a suggestion which works for me and I thought you might find works for you.

As previously suggested, laying data out horizontally like this where the columns can change provides you with extra work everytime there is a change.

 

[Hutchy]

So what I had asked for seems next to impossible, because reports have a max width, which rendered this useless.
I, however, used a union query to get the employees vertically instead of horizontally with the with the cost and hours next to them.
2 union queries joined with by a select query accomplished my request.