Horizontal

[crazymarc]

Hello,
I am quite new to access, and am not sure how I can do what i need in my report. I will explain the problem, and any help at all would be greatly appreciated!

In the database, I have Names of people and events. The different events are variable (more can be added, and they can be deleted), as well as the names. In the database, each instance of an event (no matter what type of event) is stored along with who is a part of the event, and how long they are spending doing it.

I have a query set up that does a group by name, and will count how many times each person has done a certain event, and a total of how long they have spent doing the event.

I am trying to set up my table (report) like this:

       | Name 1 | Name 2 | ... | Name N |
       | # |Time| # |Time| ... | # |Time|
       ----------------------------------
EVENT 1| x |x:xx| x |x:xx| ... | # |x:xx|
EVENT 2| x |x:xx| x |x:xx| ... | # |x:xx|
.......| x |x:xx| x |x:xx| ... | # |x:xx|
EVENT N| x |x:xx| x |x:xx| ... | # |x:xx|

Again, any help at all would be very appreciated! Thanks very much!

 

[khawar]

Created sample query for you named "Crosstabmain"

You can can make a report based on this

 

[crazymarc]

Thank you very much for that, it seems to be working well
Now, I need some way that my report can expand horizontally without having to manually add the new people to the report? I'll explain in a bit more detail:

When designing a report, you design how one record will be presented and then it automatically expands downwards for all the records that will be displayed. Mine needs to expand both Vertically AND Horizontally, since new people are able to be added, and I don't want to have to manually add those new people to the report every time someone new is added (See diagram I made, with the Name 2 ... Name N).

Expanding Vertically are the new events that can be added/removed, and across are the names which can be added/removed.

Is there any way that this can be done?

Thanks,
Marc

 

[khawar]

You have to write some vba for that have a look at my sample in sample databases http://www.access-programmers.co.uk/forums/showthread.php?t=153367

It adjusts number of columns and column names according to data in the report

 

[crazymarc]

Thank you for the help, but after taking a look at that example, I don't think that I will be able to adapt mine to work with that. I did, however, come up with an idea on how I might do it -- But as always, I am having another problem.

I figured that I would write a report generation script in VBA, that would take my crosstab query (Based off the crosstab khawar had originally sampled for me), and for each column in it I would generate another on the report, and then enter all the field data. I know this may not exactly be the BEST way to go, but I'm somewhat at a loss for ideas.

When trying to open the recordset in my VBA, I am running into problems:

Const strTitle = "Title"
    Const strQuery = "FullQuery"
 
    'Open Database
    Dim objDB As Database
    Dim objRS As Recordset
    Dim objField As Field
    'Set the database
    Set objDB = CurrentDb
 
    Set objRS = objDB.OpenRecordset("FullQuery")

The last line gives me an error that it is Expecting 1 parameter. After a bit of research, I learned that this can mean there is a problem with the SQL in the crosstab I am trying to pull the Recordset from. Here are my Crosstab Query and Crosstab SubQuery, if anyone might be able to help me out!

Crosstab SubQuery (FullSubQuery):

PARAMETERS [Month] Short;
Select Appointments.[Service Code], [Providers]![First Name] & " " & [Providers]![Last Name] & " Count" AS Description, IIf(Count(*)=0,0,Count(*)) AS Data
FROM Appointments, Providers
WHERE (((Appointments.Deleted)<>True) AND (Providers.Deleted <> TRUE) AND ((Month([Appointments]![Start Date]))=[Month]) AND ((Appointments.Code) Like "#" & [Providers].code))
GROUP BY Appointments.[Service Code], [Providers]![First Name] & " " & [Providers]![Last Name] & " Count"
UNION ALL SELECT Appointments.[Service Code], [Providers]![First Name] & " " & [Providers]![Last Name] & " Hours" AS Expr1,Format(Sum(Hour([Appointments]![Length]))+(Sum(Minute([Appointments]![Length]))/60),"Fixed") AS CountOfHours
FROM Appointments, Providers
WHERE (((Appointments.Deleted)<>True) AND ((Month([Appointments]![Start Date]))=[Month]) AND ((Appointments.Code) Like "#" & [Providers].code))
GROUP BY Appointments.[Service Code], [Providers]![First Name] & " " & [Providers]![Last Name] & " Hours";

Crosstab Query (FullQuery):

TRANSFORM IIf(Sum(FullSubQuery.Data)=0,0,Sum(FullSubQuery.Data)) AS SumOfData
SELECT [ServiceCodes Query].Description
FROM FullSubQuery, [ServiceCodes Query]
WHERE (((FullSubQuery.[Service Code])=[ServiceCodes Query].Code))
GROUP BY FullSubQuery.[Service Code], [ServiceCodes Query].Description
PIVOT FullSubQuery.Description;

Thanks for the help!