Calender Type

[MayaMana]

I am not sure if this should be under this or under queries.
However I am trying to make a report that shows the person name down the side and across the top has the dates of the month. Next to the persons name under the date it would have why they are off that day.
I have attached two pictures. One is what I am trying to achieve, the other is the table the information is coming from.

I tired using a cross query but I was unsuccessful. In getting the results I wanted.
Any advice is greatly appreciated and welcomed.

Thanks.

 

[Beetle]

What do you mean by you were "unsuccessful"?

A simple crosstab query like this;

TRANSFORM First(YourTable.[Reason]) AS FirstOfReason
SELECT YourTable.[Person]
FROM YourTable
GROUP BY YourTable.[Person]
PIVOT Format([MyDate],"Short Date");

Returns these results;

Is that what you want?

 

[MayaMana]

Yeah that is what I am looking for. I will have to try what you suggested when I get back on Monday I am about to leave for the weekend.

It sort of worked but not really. This is what I ended up with.

TRANSFORM Count(ProductionVacationTimes.Reason) AS CountOfReason
SELECT ProductionVacationTimes.Person
FROM ProductionVacationTimes
GROUP BY ProductionVacationTimes.Person
PIVOT Format([Date],"Short Date");

I have never used the crossqueries before so there is a good chance I just did something wrong.

 

[MayaMana]

Thank you, that helped a lot. I am still kind of stumped as to how to write a report to show this according to month without having to make each one.

Is there a way that I could add the day under the date?

 

[Beetle]

Can you provide a few more details about what you want to see in the report?

 

[MayaMana]

I uploaded a picture of how I would like the report to show up. Is there a way for this report to be filtered for each month without having to create a new report for each month? I'm not sure if that makes sense.

 

[Beetle]

As far as being able to choose the month for the results, you could create a small form where the user would (for example) select the desired month from a combo box. This form would be used as a parameter for the crosstab query. You may need to create another query that returns the Month (or the Month number) from the Date field in the underlying table, along with the other fields you need, then base your crosstab query on that query.

In regards to the report (at least the way you want it to function based on your example), that sort of thing is difficult to achieve with an Access report. The reason is because you don't know ahead of time how many columns are going to be returned by the query, so you have essentially two choices;

1) Create a report with enough Controls (text boxes) across the header to satisfy the the highest number of possibilities (in this case 31), then use VBA code during the creation of the report to hide the ones that end up not being used (along with resizing any borders, etc. that are on the report).

2) Create the entire report on the fly using VBA code based on how many columns are returned.

Both of these options are quite code intensive and I don't recommend either unless you are very experienced with VBA code.

Another option here would be to export the results of the crosstab to Excel. This is still going to require some coding if (as I assume) you want the spreadsheet to be nicely formatted when it's created, but it would not be as involved as the Access report method.

Post back if you have questions about any of the above.

 

[MayaMana]

If you can let me know how to hide textboxes on a report while in report view I would be very greatful. On another report I have have a code for it but it only hides the label when you print the report and still shows it while it is in view.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.labelname.Visible = Nz(Me.Field, 0)
End Sub

Yes it is a label, however the textboxes have done the same thing, and it is a yes or no field.

I will see if I can figure out what you mean setting up a parameter for the cross query. (Queries have not been a big focus of mine since most that I use have already been set up and all I've needed is just minor parameters on them). However I am still a little unsure of how I would set up the report to know which fields to use.
- I will let you know how this works out for me.

The people who will be filling it out agreed to fill it out in access instead of excel to make it a little bit easier on me.

 

[Beetle]

If you want to do some research on creating a dynamic report based on crosstab query you can find some info/examples here and here.