Is it possible to update a report, based on a crosstabquery?

[Nemo]

Hi All,

I try to explain my problem. I have a database, which records the excursions
on a lake, by motorboats. The number of the sailors is about 15 (people).

I have the need to filter how many times a sailor had journeys with a
particular customer.

The xtabqry is as follows:

Customer Sailor Excursion
TotalEscursion
RowHeading ColumnHeading Value (count) RowHeading

The output in the report is so described :

Customer Total Sailor1 Sailor2 Sailor3
------------------------------------------------
Customer1 2 1 1
Customer2 1 1
Customer3 1 1


Recording a new trip, in the database, the *new customers* are automatically
updated, vertically in the row, **but NOT the new sailors**!!
Is there a solution about that????

THANKS A LOT for any kind of answer!
Nemo

 

[vanderghast]

Edit your crosstab query. Spot the PIVOT clause, there is probably an
IN(... ) list, remove it. Alternatively, you can delete the Column Headings
properties of the crosstab.

SURE, instead of deleting it from your initial query, do it on a COPY of
your initial query.

Having no IN list for the PIVOT, or no ColumnHeadings, can create problems
to chart, report or form expecting those columns to be present. So, in the
end, you may have to manually update the PIVOT IN list, or the Column
Headings property value, to keep track of new sailors.


Vanderghast, Access MVP

 

[Nemo]

Thank you for your answer Vanderghast,
I'll do some trial, even though, I would like the query/report will
automatically update, as I am not the final user.
A sailor is a friend of mine and I have built the database for them.
Thanks again.
Nemo

 

[vanderghast]

It is still doable, but you will have to write the SQL statement as a
string, with the IN list made, dynamically, with a list of all sailors:

pseudo-code:
strSQL="TRANSFORM ... PIVOT ... IN("
Dim strInList AS variant
strInList=nothing
for each (sailor )
strInList = (strInList + ", ") & sailor.name
next
strInList = strInList & ")"
strSQL=strSQL & strInList
' use strSQL as record source of the report



Vanderghast, Access MVP

 

[Nemo]

Thank you for the HOPE, Vanderghast!
During the weekend, I'll do some trials and I hope to succeed.
Reading your answer, I learnt a new word *doable*....thanks for your help!
Cordially,
Nemo

 

[Steve]

Hello Nemo,

If you are unsuccessful, I can build the crosstab report and make it
automatic for you. I provide help with Access applications for a reasonable
fee. My fee to create the automatic crosstab report would be very modest.
Contact me if you are interested.

Steve
(e-mail address removed)

 

[Keith Wilby]

Contact me if you are interested.

But be sure to read this first:

http://home.tiscali.nl/arracom/whoissteve.html