Prev: Link Unicode File
Next: 2003: Table Field Type For Beeeeg Decimal Numbers?
From: The Frog on 12 Aug 2010 11:04
Hi Everyone,

I am hoping someone can shed some light on what I think is abnormal
behavior. I am in the process of wrapping up some reports and letting
a user run the report from a command button. Seems pretty simple -
you'd think.

The data used for the reports is based on a single table. One of the
fields is a date field capturing the date the row was introduced to
the table. To produce the 'right' data for the reports I use two
queries: 1 query for the most recent data where the WHERE clause has
the date column as MAX(date_column), and this seems to work quite
nicely. Checked it by hand and all seems well.

Here is the SQL:
SELECT data.category, data.subcategory, data.segment, data.VS,
data.ORGANISATION, data.IST, data.SOLL
FROM data
GROUP BY data.category, data.subcategory, data.segment, data.VS,
data.ORGANISATION, data.IST, data.SOLL, data.report_date
HAVING (((data.report_date)=(SELECT Max([report_date]) FROM
qryReportDates) Or (data.report_date) Is Null));


I have a second query that grabs data from a specified date that the
user selects through a form (called Switchboard - not my choice of
name FWIW). The specific control that provides the date is called
cmbHistory (combo box). Works nicely and provides a list of dates for
the user to choose from not including the most recent (MAX) date
available. This date value from the combo box is used as the criteria
in the SQL for the second query:

Here is the SQL:
SELECT data.category, data.subcategory, data.segment, data.VS,
data.ORGANISATION, data.IST
FROM data
WHERE (((data.report_date) Like "*" & [Forms]![Switchboard]!
[cmbHistory] & "*"))
GROUP BY data.category, data.subcategory, data.segment, data.VS,
data.ORGANISATION, data.IST;

(I would like to do this without the like statement but I cannot seem
to ge that to function - but this is a minor thing).

I have a 'master' query that returns the results from both of these
into a single 'view' of the data, labelling the 'fact' columns
differently for both time periods (IST and OLD_IST). This seems to
work nicely too.
Here is the SQL:
SELECT qryCrosstab_BASE_RECENT.VS,
qryCrosstab_BASE_RECENT.ORGANISATION,
qryCrosstab_BASE_RECENT.category, qryCrosstab_BASE_RECENT.subcategory,
qryCrosstab_BASE_RECENT.segment, qryCrosstab_BASE_RECENT.IST,
qryCrosstab_BASE_RECENT.SOLL, qryCrosstab_BASE_HISTORICAL.IST AS
OLD_IST
FROM qryCrosstab_BASE_RECENT LEFT JOIN qryCrosstab_BASE_HISTORICAL ON
(qryCrosstab_BASE_RECENT.category =
qryCrosstab_BASE_HISTORICAL.category) AND
(qryCrosstab_BASE_RECENT.subcategory =
qryCrosstab_BASE_HISTORICAL.subcategory) AND
(qryCrosstab_BASE_RECENT.segment =
qryCrosstab_BASE_HISTORICAL.segment) AND (qryCrosstab_BASE_RECENT.VS =
qryCrosstab_BASE_HISTORICAL.VS) AND
(qryCrosstab_BASE_RECENT.ORGANISATION =
qryCrosstab_BASE_HISTORICAL.ORGANISATION);

The returned results seem to make sense and provide the right data for
reporting.

On top of this 'master' query I run a series of queries to get the
report data I want. This is simply a means to get the filtered data
that is desired for the report. An example is to filter the 'master'
query with the following:

SELECT qryCrosstab_MASTER.VS, qryCrosstab_MASTER.ORGANISATION,
Sum([IST])/Sum([SOLL]) AS Achieved, (Sum([IST])/Sum([SOLL]))-
(Sum([OLD_IST])/Sum([SOLL])) AS Variance
FROM qryCrosstab_MASTER
GROUP BY qryCrosstab_MASTER.VS, qryCrosstab_MASTER.ORGANISATION;

giving me a reduced subset of the data and only the columns I want.

I then base a crosstab on this to 'present' the data the way the
report needs, like so:

PARAMETERS [Forms]![Switchboard]![cmbHistory] DateTime;
TRANSFORM Max(IIf([FieldName]="Achieved",[Achieved],[Variance])) AS
TheValue
SELECT qryREPORT_2_Data.ORGANISATION
FROM xHeaders, qryREPORT_2_Data
GROUP BY qryREPORT_2_Data.ORGANISATION
PIVOT [VS] & " " & [FieldName];

This crosstab gives me a nice layout and summary of the data, and the
xHeaders table is a nice little trick used to get multiple values into
the output columns (one for achieved and one for variance). I use the
PARAMETERS section to feed the form controls (cmbHistory) value
through to the underlying queries that feed the crosstab (without it
the crosstab wont function as it states it doesnt recognise the
control) So far this all seems to work.

Now the weird stuff begins:

I base a report on our example crosstab above, and I have on that
report a label that is for placing the dates of the underlying report.
Mind you I am not feeding any date data per se to the report itself,
yet the label still produces a result! The caption property of the
label is set to the following:
Zeitraum: Stand Max(Date) vs. Stand Min(Date) -sorry
its in German

The Max(Date) and Min(Date) seem to ***somehow*** get date data from
the data table that all these rqueries are originally based on. The
reports data source is set to the crosstab query that feeds it, and
there is no date data in the crosstab. I have no idea where it is
getting the data from. If I change the Min(Date) to [Forms]!
[Switchboard]![cmbHistory] it does not effect the result at all and I
still get the min date value shown. Thats weird bit number one. I need
to somehow get the actual chosen date shown there.

The second weird bit: If I place a command button on the Switchboard
form, and run a macro from it to produce this report (by itself) it
runs and I see a result. If I use the report as a subreport (basically
just placing them in series one after the other to produce a 'master'
report) I get a complete blank sheet for any report that uses the
combobox! I have no idea why.

Has anyone got any ideas on handling this? At this point I can also
say that if I swap the cmbHistory on the underlying queries out for a
Min statement similar to the Max one used to get the most recent data,
it works fine. I am completely out of my depth trying to find my way
through this one.

Any help appreciated.

The Frog
From: Rich P on 12 Aug 2010 11:45
Greetings, I have just a few ideas you could try.

1st: instead of having a WHERE clause inside of a HAVING... statement
-- try putting your WHERE clause in the main body of the query and then
group by on that result.

2nd: this idea is just for testing purposes -- instead of having a
bunch of queries that are based on other queries try this: save each
query result to a temp table and then query that resulting dataset until
you are at the final query. The purpose of this is so that you can
visually see what data is being captured.

As for the subreports, if you base your recordsources on a permanent
table (not a query) - store the results of the final query in a
permanent table - you may have better luck using the subreports.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
From: Salad on 12 Aug 2010 13:13
The Frog wrote:
> Hi Everyone,
>
> WHERE (((data.report_date) Like "*" & [Forms]![Switchboard]!
> [cmbHistory] & "*"))


> (I would like to do this without the like statement but I cannot seem
> to ge that to function - but this is a minor thing).

Since a combo's values are basically a string, wouldn't a Cdate()
convert it correctly? Or Format (intnational date formats)?

>
> I base a report on our example crosstab above, and I have on that
> report a label that is for placing the dates of the underlying report.
> Mind you I am not feeding any date data per se to the report itself,
> yet the label still produces a result! The caption property of the
> label is set to the following:
> Zeitraum: Stand Max(Date) vs. Stand Min(Date) -sorry
> its in German

Are you saying the label prints out data and not the caption? If so,
Access has lost it's mind by that point. Your query has brought it to
its knees.


> The Max(Date) and Min(Date) seem to ***somehow*** get date data from
> the data table that all these rqueries are originally based on. The
> reports data source is set to the crosstab query that feeds it, and
> there is no date data in the crosstab. I have no idea where it is
> getting the data from. If I change the Min(Date) to [Forms]!
> [Switchboard]![cmbHistory] it does not effect the result at all and I
> still get the min date value shown. Thats weird bit number one. I need
> to somehow get the actual chosen date shown there.

Is it possible to roll the data up with perhaps less filters and let the
report apply the filter to present the data you need?
>
> The second weird bit: If I place a command button on the Switchboard
> form, and run a macro from it to produce this report (by itself) it
> runs and I see a result. If I use the report as a subreport (basically
> just placing them in series one after the other to produce a 'master'
> report) I get a complete blank sheet for any report that uses the
> combobox! I have no idea why.

Perhaps the queries are so complex that Access, like an idiot, doesn't
know it is complex and goes on its merry way.

What happens if you were not to use parameters but hard coded dates (as
a test). If that worked, you could open up the qdf and change/put in
the SQL with all the variables in place prior to running.
From: David W. Fenton on 12 Aug 2010 19:53
The Frog <mr.frog.to.you(a)googlemail.com> wrote in
news:e36cf220-c658-4d48-98d6-a3e9392e05ef(a)z28g2000yqh.googlegroups.co
m:

> Any help appreciated.

Complicated.

I'm surprised you put the parameter definition in the top-level
query. I'd put it in each query where it's used. I was going to
suggest that as the cause of the problem with the LIKE comparison --
if you defined the parameter in that query, it might let you get
away without that.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/
From: The Frog on 13 Aug 2010 04:14
Hi Guys, thanks for the replies.

I will deal with the second wierdness first. I tried originally to
have the parameters (ie/ the users chosen date from a combo box) in
the underlying queries, and if you run the query (use a command button
for example to see the results), it works fine. As soon as the
crosstab is placed as the query to execute, even though it is based on
the underlying queries where the parameter is set, the crosstab
complains that it has no clue what the control is / parameter is. As
soon as I set the parameter and its type in the crosstab (as shown in
the previous post of mine) the crosstab runs (and seems to return the
right result). If I use this crosstab now (with the parameter listed)
as the query for a report, the report runs nicely (except for
wierdness #1 - but I can deal with that in other ways). As soon as the
report is used as a subreport, all I get is a blank page, not even the
headers. I am going to have to keep experimenting with this. I tried
to do the crosstab in a single step based directly on the data table
but couldnt seem to ever get it worked out, hence the queries behind
it.

As for wierdness #1, the label behaving strangely, I will simply kill
the label and replace it with some text boxes and set the values by
code. Bloody strange though. I have absolutely no idea where it is
getting its data from. It was left there during the design phase
before I changed the structure of how it was built, and I forgot about
it. Somehow it is still running.........I checked if there was any
code for it too, but nothing references the label that I can tell,
certainly not on the controlling form or on the report itself..... You
may be right Salad, Access could have lost its mind on this one.

I will attempt the following to correct the situation:
1/ Build the crosstab directly from the data table (Thats going to be
one hell of a query.....)
2/ Import all objects and data into a new clean, blank, unspooked MDB
and see if the spookiness follows.
3/ Do some more research to see why the reports work fine as stand
alones, but not as sub-reports.
4/ Try to remove the LIKE operator from the queries if I cant do the
crosstab directly.

Feedback to follow........

Thanks for the pointers guys. This is a strange one for me to be sure.
I really appreciate your insights.

The Frog
 |  Next  |  Last
Pages: 1 2
Prev: Link Unicode File
Next: 2003: Table Field Type For Beeeeg Decimal Numbers?