Using Access – Create a report/Form each week with variable Column Names

[AirDavidADP]

I have a query that runs each week that shows data from the current week out 26 weeks. So this week it would have weekend dates from 1/13 to 7/21. Next week the columns will start at 1/20 – 7/28. I’m using a crosstab query. I need to have a form or report that can show all the data each week. My problem is if create a form or report the column heading change each week, and I don’t want to go in and updated the form/report manually each week. Is there a way to create a form or report that show my 26 columns no matter that the column name is. Thank you for your help.

[PhilOfWalton]

David

The principal is to create 2 crosstab queries, one for the header information (dates & weeks) but with an additional field called SortOrder, Value 0 and the other crosstab for the data for the corresponding periods and an additional field called SortOrder, Value 1

Then create a Union Query of the 2 subqueries

Here is the SQL for the query below

SELECT QXSubsTitle.*,JoiningFee
FROM QXSubsTitle 
UNION SELECT QXSubsDisc.*,"" FROM QXSubsDisc
UNION SELECT QXSubsBody.*, Format(JoiningFee,"Currency")
 FROM QXSubsBody
ORDER BY RptPos, SortOrder;
 

Phil

[AirDavidADP]

Thank you for your replay. I have attached an example of what my problem is. I hope it helps. Thank you for your time.

[PhilOfWalton]

Have you read and understood my reply?

Have you created the Crosstab to show just the 26 header dates starting at the same week as your qry_Allocation_Part2_Crosstab_Test?

Phil

[AirDavidADP]

Yes, The Crosstab query starts with the current Weekend date and go out 26 weeks. A column for each week for 26 weeks. So each week the query is run, it starts with that current weekending date. This week 1/27 is the first column. Next week 2/3 will be the first column. So each of the 26 columns in the query are different each week. So it's hard to create a report or form to use because the column names are different each week. I hope this helps.

[twinnyfo]

AirDavidADP and Phil,

I don't mean to step in, but I think what David is looking for is the ability to put the results of the query into a Form/Report, which won't work if the field names are actual dates.

Rather than have a Date as the Field Name in your query, create it in such a way that the fields are named Date01, Date02, etc. Then, in your form/report, you have standardized field names that never change, even though the dates will.

Make sense?

It would be helpful to see your actual Query, but I can only presume that you are calculating the dates, using the values 1 through 26, with some type of "beginning date" to determine dates? If so, then the above mentioned modification becomes easier to implement.

Hope this hepps!

[PhilOfWalton]

Hi David & twinnyfo

I think you are both missing the point. The example I gave above has the top line with the dates as a calculated value.

The query that you see is a Union Query with the top line based on the query
SELECT QXSubsTitle.*,JoiningFee FROM QXSubsTitle
Which gives the variable month & year

The second line on the Query
UNION SELECT QXSubsDisc.*,"" FROM QXSubsDisc
Which gives the variable discounts for new member joining on different months.

and the rest based on the query
UNION SELECT QXSubsBody.*, Format(JoiningFee,"Currency") FROM QXSubsBody
Which shows the discounted fees.

All 3 are crosstab queries.

I am reasonable sure that you can do a fixed column, non updateable form based on this, and certain that you can do a fixed column report based on the Union Query. Not only that, the examples below are both the same report, but on the second one the only things changed are the discount structure (in a table) and the year start date from 1st September to 1st June. As you will notice, the number of columns changes automatically to suit the discount structure

and

Note that in the report, I have not discount row which appears in the query.

Phil

[twinnyfo]

Phil,

So, if I understand your example correctly, your Forms/Reports are in Datasheet view? That is a very interesting approach, which I had not thought about--but also, perhaps, why I misunderstood the nature of your query.

Did you somehow apply conditional formatting to the header row, to make them bold?

I have used my approach for several different applications, but not yours, so now I have another approach to similar problems.

Does this solve the OP's original question?

[PhilOfWalton]

Twinnyfo

The examples above are actually subreports on a membership application form.

Surprisingly, the report in design view looks like this:-

As you can imagine there is a lot of formatting to do to get the end result. There are basically 20 fields in the header and detail, and the width is altered to accommodate the information, or set to zero width if there is no information.

It is pretty complicated, but I have another form that sets all the details of fonts, wording in titles, fixed column widths etc. So the (Sub)Report reads the information from the tables associated with the Formater form and formats the report accordingly.

Have attached a Word file to show the formater form, and as you can see, particularly on the second example, I have a lot of control over how the reports are formatted. The first example is for the subreport under discussion. (The images on this website aren't of sufficiently good quality to show fine detail)

Phil

[twinnyfo]

Very impressive work, Phil!

It might work for the purposes of the OP, but I think it's even beyond what I am willing to put into a Report of this type. Don't get me wrong, this looks awesome--in fact, I would recommend you write a detailed article for this forum. I know others would benefit from this!

[PhilOfWalton]

I suspect that if the OP did what I suggested and created a 26 column,fixed width report using the Union Query I suggested, it would be very simple.

As for detailed articles, I have a number of useful bits such as pretty status bars

A pretty message box that you can copy from

and my major project at the moment which is a Library database to translate any database into up to 100 different languages. I feel it should have commercial value, but have no idea how to market it

Phil