Query loses data due to joins

[Rob Parker]

Hello all, especially the MVPs ;-)

First, a reason for the crosspost, for what I think is really a query
problem:

The query I'm having a serious problem with is the recordsource for a
report, which uses several of the fields in Sum textboxes in group and
report footers in the report, so I think I need to get all my data into a
single query, rather than using subreports. But if there's a solution to my
problem which uses sub-reports, please suggest it.

Now for the nitty-gritty (and apologies in advance for the long post), with
a general description first, and then a (simplified for this post) listing
of my current query:

My source data is financial data for a project. The project has Control
Accounts and Work Packages (CA and WP in the query shown below); each WP has
a budget (actually initial and revised budgets, but simplified here via a
query which produces only the original budget). The SQL for this query
(qryBudget) is:
SELECT WP, Budget
FROM tblBudget
WHERE tblBudget.IsOriginal=True;

The database contains data for orders against each WP in the project, with
Commit and Spend data for each order. This data is stored in tblOrders,
which contains a Dataset field (a long integer), which represents the
month/year of the project. Order data is "rolled over" each month via an
append query on this table, which copies the previous dataset's data to the
new dataset; all user input is done via forms which operate on the current
dataset's records. Essentially, this give multiple records in tblOrders for
every order for each month, so that the financial history can be tracked,
using Dataset to separate individual month's data.. Part of this tracking
is a Financial Summary for any month - the report which is based on my
troublesome query - which shows total Commit and Spend for each Work
Package, together with the original Budget figure. The report show data for
every WP, with grouping and summing by CA, and also summing by whole
project.

The monthly commit and spend totals for each Work Package are obtained from
a totals query (qryWP_Totals), with the following SQL:
SELECT tblOrdersWP, tblOrders.DataSet, Sum(tblOrders.Commit) AS
[Commit], Sum(tblOrders.Spend) AS Spend
FROM tblOrders
GROUP BY tblOrders.WP, tblOrders.DataSet;

So far, so good - nothing at all difficult yet, and all seems fine.

The query which I have as the recordsource of my report
(qryFinancialSummary) is:
SELECT tblWP.CA,
tblWP.WP,
tblWP.FullName AS [WP Name],
qryBudget.Budget,
qryWP_Totals.Commit,
qryWP_Totals.Spend,
qryWP_Totals.DataSet
FROM (tblWP LEFT JOIN qryWP_Totals ON tblWP.WP = qryWP_Totals.WP)
LEFT JOIN qryBudget ON tblWP.WP = qryBudget.WP;

The report is opened with the following WHERE condition:
WHERE (Dataset Is Null OR Dataset = Me.txtDataset)
(ie. the dataset for the report (month/year) is selected in the txtDataset
control in the form from which the report is opened.)

The problem I have is that, if a WP has any entries in tblOrders, then this
query drops the budget data (which does not have a Dataset associated with
it) from the output of the query for every dataset other than those for
which data exists in tblOrders (in practice, for all preceding datasets
before the first dataset in which data is present in tblOrders). If there
are no entries for the WP in tblOrders, the Budget figure appears in the
query output (with a null value in the Dataset field).

So, if a Financial Summary is report is generated for a month before that in
which the first order for a WP appears, the budget record for that WP
disappears from the query's output, and the CA and project budget totals in
the report are incorrect.

How can I fix this? General approach answers will be sufficient - I don't
need detailed SQL for the final query SQL.

TIA,

Rob

PS. For the curious: this database application has been in use for over
four years, without the problem being noticed. That's because usually the
Financial Summary report is normally generated for the preceding month
immediately after rolling over the order data to the current month, before
any changes to orders in the new month; however, on this occasion there was
about a two-week period before the reconciliation/financial summary process
occurred, during which the first order was added for a WP - and BAM!!! -
suddenly a WP had disappeared from the Financial Summary report for the
preceding month.

 

[Guest]

No MVP though....

Sounds like the difference between INNER and OUTER joins.
You might try the outer join

Hello all, especially the MVPs ;-)

First, a reason for the crosspost, for what I think is really a query
problem:

The query I'm having a serious problem with is the recordsource for a
report, which uses several of the fields in Sum textboxes in group and
report footers in the report, so I think I need to get all my data into a
single query, rather than using subreports. But if there's a solution to my
problem which uses sub-reports, please suggest it.

Now for the nitty-gritty (and apologies in advance for the long post), with
a general description first, and then a (simplified for this post) listing
of my current query:

My source data is financial data for a project. The project has Control
Accounts and Work Packages (CA and WP in the query shown below); each WP has
a budget (actually initial and revised budgets, but simplified here via a
query which produces only the original budget). The SQL for this query
(qryBudget) is:
SELECT WP, Budget
FROM tblBudget
WHERE tblBudget.IsOriginal=True;

The database contains data for orders against each WP in the project, with
Commit and Spend data for each order. This data is stored in tblOrders,
which contains a Dataset field (a long integer), which represents the
month/year of the project. Order data is "rolled over" each month via an
append query on this table, which copies the previous dataset's data to the
new dataset; all user input is done via forms which operate on the current
dataset's records. Essentially, this give multiple records in tblOrders for
every order for each month, so that the financial history can be tracked,
using Dataset to separate individual month's data.. Part of this tracking
is a Financial Summary for any month - the report which is based on my
troublesome query - which shows total Commit and Spend for each Work
Package, together with the original Budget figure. The report show data for
every WP, with grouping and summing by CA, and also summing by whole
project.

The monthly commit and spend totals for each Work Package are obtained from
a totals query (qryWP_Totals), with the following SQL:
SELECT tblOrdersWP, tblOrders.DataSet, Sum(tblOrders.Commit) AS
[Commit], Sum(tblOrders.Spend) AS Spend
FROM tblOrders
GROUP BY tblOrders.WP, tblOrders.DataSet;

So far, so good - nothing at all difficult yet, and all seems fine.

The query which I have as the recordsource of my report
(qryFinancialSummary) is:
SELECT tblWP.CA,
tblWP.WP,
tblWP.FullName AS [WP Name],
qryBudget.Budget,
qryWP_Totals.Commit,
qryWP_Totals.Spend,
qryWP_Totals.DataSet
FROM (tblWP LEFT JOIN qryWP_Totals ON tblWP.WP = qryWP_Totals.WP)
LEFT JOIN qryBudget ON tblWP.WP = qryBudget.WP;

The report is opened with the following WHERE condition:
WHERE (Dataset Is Null OR Dataset = Me.txtDataset)
(ie. the dataset for the report (month/year) is selected in the txtDataset
control in the form from which the report is opened.)

The problem I have is that, if a WP has any entries in tblOrders, then this
query drops the budget data (which does not have a Dataset associated with
it) from the output of the query for every dataset other than those for
which data exists in tblOrders (in practice, for all preceding datasets
before the first dataset in which data is present in tblOrders). If there
are no entries for the WP in tblOrders, the Budget figure appears in the
query output (with a null value in the Dataset field).

So, if a Financial Summary is report is generated for a month before that in
which the first order for a WP appears, the budget record for that WP
disappears from the query's output, and the CA and project budget totals in
the report are incorrect.

How can I fix this? General approach answers will be sufficient - I don't
need detailed SQL for the final query SQL.

TIA,

Rob

PS. For the curious: this database application has been in use for over
four years, without the problem being noticed. That's because usually the
Financial Summary report is normally generated for the preceding month
immediately after rolling over the order data to the current month, before
any changes to orders in the new month; however, on this occasion there was
about a two-week period before the reconciliation/financial summary process
occurred, during which the first order was added for a WP - and BAM!!! -
suddenly a WP had disappeared from the Financial Summary report for the
preceding month.

 

[Guest]

Hi Rob

If I understand, if a WP has no totals for, say Aug 2007, then when you run
the Aug 2007 report that WP doesn't appear (if it has totals for other
datasets).

This is because the join finds the WP in totals (for other datasets) so it
isn't null so it doesn't satisfy the is null part of the filter.
Then you say of those found only keep this particular dataset - which isn't
there so they're all dropped.

To make this report work you should apply the dataset filter to qryWP_Totals.
If this query is used elsewhere you'll need to make a copy.

I would rewrite qryWP_Totals as...

SELECT tblOrdersWP, tblOrders.DataSet, Sum(tblOrders.Commit) AS
[Commit], Sum(tblOrders.Spend) AS Spend
FROM tblOrders
WHERE DataSet = Forms!MyForm!txtDataSet
GROUP BY tblOrders.WP, tblOrders.DataSet;

(obviously replace MyForm with the real name of the form)
Then, don't filter the final query at all.

hth

Andy Hull

(Another approach, which may be required in other situations, is to have a
datasets table containing all datasets. Then create a query (qryWPDataSet)
like...
select tblWP.CA, tblWP.WP, tblWP.FullName, tblDataSets.DataSet from tblWP,
tblDataSets
Note there is deliberately no join. Then your final query will join
qryWPDataSet to qryWP_Totals on 2 fields, WP and DataSet (as a left join) and
will join qryBudget as you already have. This will work with your existing
filter but it would probably be better to apply the filter to the field
qryWPDataSet.DataSet).

Hello all, especially the MVPs ;-)

First, a reason for the crosspost, for what I think is really a query
problem:

The query I'm having a serious problem with is the recordsource for a
report, which uses several of the fields in Sum textboxes in group and
report footers in the report, so I think I need to get all my data into a
single query, rather than using subreports. But if there's a solution to my
problem which uses sub-reports, please suggest it.

Now for the nitty-gritty (and apologies in advance for the long post), with
a general description first, and then a (simplified for this post) listing
of my current query:

My source data is financial data for a project. The project has Control
Accounts and Work Packages (CA and WP in the query shown below); each WP has
a budget (actually initial and revised budgets, but simplified here via a
query which produces only the original budget). The SQL for this query
(qryBudget) is:
SELECT WP, Budget
FROM tblBudget
WHERE tblBudget.IsOriginal=True;

The database contains data for orders against each WP in the project, with
Commit and Spend data for each order. This data is stored in tblOrders,
which contains a Dataset field (a long integer), which represents the
month/year of the project. Order data is "rolled over" each month via an
append query on this table, which copies the previous dataset's data to the
new dataset; all user input is done via forms which operate on the current
dataset's records. Essentially, this give multiple records in tblOrders for
every order for each month, so that the financial history can be tracked,
using Dataset to separate individual month's data.. Part of this tracking
is a Financial Summary for any month - the report which is based on my
troublesome query - which shows total Commit and Spend for each Work
Package, together with the original Budget figure. The report show data for
every WP, with grouping and summing by CA, and also summing by whole
project.

The monthly commit and spend totals for each Work Package are obtained from
a totals query (qryWP_Totals), with the following SQL:
SELECT tblOrdersWP, tblOrders.DataSet, Sum(tblOrders.Commit) AS
[Commit], Sum(tblOrders.Spend) AS Spend
FROM tblOrders
GROUP BY tblOrders.WP, tblOrders.DataSet;

So far, so good - nothing at all difficult yet, and all seems fine.

The query which I have as the recordsource of my report
(qryFinancialSummary) is:
SELECT tblWP.CA,
tblWP.WP,
tblWP.FullName AS [WP Name],
qryBudget.Budget,
qryWP_Totals.Commit,
qryWP_Totals.Spend,
qryWP_Totals.DataSet
FROM (tblWP LEFT JOIN qryWP_Totals ON tblWP.WP = qryWP_Totals.WP)
LEFT JOIN qryBudget ON tblWP.WP = qryBudget.WP;

The report is opened with the following WHERE condition:
WHERE (Dataset Is Null OR Dataset = Me.txtDataset)
(ie. the dataset for the report (month/year) is selected in the txtDataset
control in the form from which the report is opened.)

The problem I have is that, if a WP has any entries in tblOrders, then this
query drops the budget data (which does not have a Dataset associated with
it) from the output of the query for every dataset other than those for
which data exists in tblOrders (in practice, for all preceding datasets
before the first dataset in which data is present in tblOrders). If there
are no entries for the WP in tblOrders, the Budget figure appears in the
query output (with a null value in the Dataset field).

So, if a Financial Summary is report is generated for a month before that in
which the first order for a WP appears, the budget record for that WP
disappears from the query's output, and the CA and project budget totals in
the report are incorrect.

How can I fix this? General approach answers will be sufficient - I don't
need detailed SQL for the final query SQL.

TIA,

Rob

PS. For the curious: this database application has been in use for over
four years, without the problem being noticed. That's because usually the
Financial Summary report is normally generated for the preceding month
immediately after rolling over the order data to the current month, before
any changes to orders in the new month; however, on this occasion there was
about a two-week period before the reconciliation/financial summary process
occurred, during which the first order was added for a WP - and BAM!!! -
suddenly a WP had disappeared from the Financial Summary report for the
preceding month.

 

[Rob Parker]

Hi Andy,

Thanks for that.

Actually, having slept on it overnight, this morning I realized just how
simple the solution is. Rather than trying to force the lost data into the
query, simply remove the unwanted data (which is causing the loss) from the
query - and the simplest way to do that is to change qryWP_Totals. Rather
than include the form control reference (as you show), I'll actually
re-write the querydef from code before opening the report; I'll do it that
way because the report is actually available from two different forms - and
I'm using that technique in several places in this appplication. [Note:
when I do this, I include a comment in the query's Description property that
the query is re-written by code; it's very helpful when I'm trying to fix
something and everything seems fine when checking directly through the
database window.]

Thanks again,

Rob

Hi Rob

If I understand, if a WP has no totals for, say Aug 2007, then when you
run
the Aug 2007 report that WP doesn't appear (if it has totals for other
datasets).

This is because the join finds the WP in totals (for other datasets) so it
isn't null so it doesn't satisfy the is null part of the filter.
Then you say of those found only keep this particular dataset - which
isn't
there so they're all dropped.

To make this report work you should apply the dataset filter to
qryWP_Totals.
If this query is used elsewhere you'll need to make a copy.

I would rewrite qryWP_Totals as...

SELECT tblOrdersWP, tblOrders.DataSet, Sum(tblOrders.Commit) AS
[Commit], Sum(tblOrders.Spend) AS Spend
FROM tblOrders
WHERE DataSet = Forms!MyForm!txtDataSet
GROUP BY tblOrders.WP, tblOrders.DataSet;

(obviously replace MyForm with the real name of the form)
Then, don't filter the final query at all.

hth

Andy Hull

(Another approach, which may be required in other situations, is to have a
datasets table containing all datasets. Then create a query (qryWPDataSet)
like...
select tblWP.CA, tblWP.WP, tblWP.FullName, tblDataSets.DataSet from tblWP,
tblDataSets
Note there is deliberately no join. Then your final query will join
qryWPDataSet to qryWP_Totals on 2 fields, WP and DataSet (as a left join)
and
will join qryBudget as you already have. This will work with your existing
filter but it would probably be better to apply the filter to the field
qryWPDataSet.DataSet).

Hello all, especially the MVPs ;-)

First, a reason for the crosspost, for what I think is really a query
problem:

The query I'm having a serious problem with is the recordsource for a
report, which uses several of the fields in Sum textboxes in group and
report footers in the report, so I think I need to get all my data into a
single query, rather than using subreports. But if there's a solution to
my
problem which uses sub-reports, please suggest it.

Now for the nitty-gritty (and apologies in advance for the long post),
with
a general description first, and then a (simplified for this post)
listing
of my current query:

My source data is financial data for a project. The project has Control
Accounts and Work Packages (CA and WP in the query shown below); each WP
has
a budget (actually initial and revised budgets, but simplified here via a
query which produces only the original budget). The SQL for this query
(qryBudget) is:
SELECT WP, Budget
FROM tblBudget
WHERE tblBudget.IsOriginal=True;

The database contains data for orders against each WP in the project,
with
Commit and Spend data for each order. This data is stored in tblOrders,
which contains a Dataset field (a long integer), which represents the
month/year of the project. Order data is "rolled over" each month via an
append query on this table, which copies the previous dataset's data to
the
new dataset; all user input is done via forms which operate on the
current
dataset's records. Essentially, this give multiple records in tblOrders
for
every order for each month, so that the financial history can be tracked,
using Dataset to separate individual month's data.. Part of this
tracking
is a Financial Summary for any month - the report which is based on my
troublesome query - which shows total Commit and Spend for each Work
Package, together with the original Budget figure. The report show data
for
every WP, with grouping and summing by CA, and also summing by whole
project.

The monthly commit and spend totals for each Work Package are obtained
from
a totals query (qryWP_Totals), with the following SQL:
SELECT tblOrdersWP, tblOrders.DataSet, Sum(tblOrders.Commit) AS
[Commit], Sum(tblOrders.Spend) AS Spend
FROM tblOrders
GROUP BY tblOrders.WP, tblOrders.DataSet;

So far, so good - nothing at all difficult yet, and all seems fine.

The query which I have as the recordsource of my report
(qryFinancialSummary) is:
SELECT tblWP.CA,
tblWP.WP,
tblWP.FullName AS [WP Name],
qryBudget.Budget,
qryWP_Totals.Commit,
qryWP_Totals.Spend,
qryWP_Totals.DataSet
FROM (tblWP LEFT JOIN qryWP_Totals ON tblWP.WP = qryWP_Totals.WP)
LEFT JOIN qryBudget ON tblWP.WP = qryBudget.WP;

The report is opened with the following WHERE condition:
WHERE (Dataset Is Null OR Dataset = Me.txtDataset)
(ie. the dataset for the report (month/year) is selected in the
txtDataset
control in the form from which the report is opened.)

The problem I have is that, if a WP has any entries in tblOrders, then
this
query drops the budget data (which does not have a Dataset associated
with
it) from the output of the query for every dataset other than those for
which data exists in tblOrders (in practice, for all preceding datasets
before the first dataset in which data is present in tblOrders). If
there
are no entries for the WP in tblOrders, the Budget figure appears in the
query output (with a null value in the Dataset field).

So, if a Financial Summary is report is generated for a month before that
in
which the first order for a WP appears, the budget record for that WP
disappears from the query's output, and the CA and project budget totals
in
the report are incorrect.

How can I fix this? General approach answers will be sufficient - I
don't
need detailed SQL for the final query SQL.

TIA,

Rob

PS. For the curious: this database application has been in use for over
four years, without the problem being noticed. That's because usually
the
Financial Summary report is normally generated for the preceding month
immediately after rolling over the order data to the current month,
before
any changes to orders in the new month; however, on this occasion there
was
about a two-week period before the reconciliation/financial summary
process
occurred, during which the first order was added for a WP - and BAM!!! -
suddenly a WP had disappeared from the Financial Summary report for the
preceding month.