Summary query - Is there a better way

[Sean]

I have 10 query's and each one summarizes a different group of data, and each
query has 2 output fields. The output of one query would be Boxes 10,
another query's output would be Ladders 20. Each of these queries is pulling
from a different data source. Then I have a summary query that pulls in the
results of each one of these query's. However, if one of the query's has no
results to display, the entire summary query does not produce any results
even if their subquery's have data to present. Is there a better way to set
this up? The actual query is below. Thanks,

SELECT NegativeATP.NegATP, ShipComplete.Ship_Complete,
TotalLines.TotalLines, ShippableLines.Shippable,
DeliveryGroup_Shippable.DeliveryGroup, TJ_UnShippable_Percentage.Percent AS
TJ_Percentage, GHO_UnShippable_Percentage.Percent AS GHO_Percentage,
BB_UnShippable_Percentage.Percent AS BB_Percentage,
BP_UnShippable_Percentage.Percent AS BP_Percentage
FROM NegativeATP, ShipComplete, TotalLines, ShippableLines,
DeliveryGroup_Shippable, TJ_UnShippable_Percentage,
GHO_UnShippable_Percentage, BB_UnShippable_Percentage,
BP_UnShippable_Percentage;

 

[Jeff Boyce]

Sean

It all starts with the data ... and you haven't described the data enough to
understand why you are using "10 queries [to] summarize ... group of
data".

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Sean]

I am not sure how to answer the question, but let me start here and we can
go from there. The output is this ( I will seperate it into 2 lines for
visibility here but it all comes out in one query):

NegATP Ship_Complete TotalLines Shippable DeliveryGroup
430 51 746 20 248

The above output are all from summary query's that are counting records.

TJ_Percentage GHO_Percentage BB_Percentage BP_Percentage
73.26% 6.51% 17.67% 0.47%

The above percentage fields are dividing each groups total (groups are TJ,
GHO, BB, BP) by a number field in their query's. I was just unable to get
the summarized data above in one query that is why each group above is its
own seperate query and the summary query just pulls them all together in on
report if you will.

Sean

It all starts with the data ... and you haven't described the data enough to
understand why you are using "10 queries [to] summarize ... group of
data".

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have 10 query's and each one summarizes a different group of data, and
each
query has 2 output fields. The output of one query would be Boxes 10,
another query's output would be Ladders 20. Each of these queries is
pulling
from a different data source. Then I have a summary query that pulls in
the
results of each one of these query's. However, if one of the query's has
no
results to display, the entire summary query does not produce any results
even if their subquery's have data to present. Is there a better way to
set
this up? The actual query is below. Thanks,

SELECT NegativeATP.NegATP, ShipComplete.Ship_Complete,
TotalLines.TotalLines, ShippableLines.Shippable,
DeliveryGroup_Shippable.DeliveryGroup, TJ_UnShippable_Percentage.Percent
AS
TJ_Percentage, GHO_UnShippable_Percentage.Percent AS GHO_Percentage,
BB_UnShippable_Percentage.Percent AS BB_Percentage,
BP_UnShippable_Percentage.Percent AS BP_Percentage
FROM NegativeATP, ShipComplete, TotalLines, ShippableLines,
DeliveryGroup_Shippable, TJ_UnShippable_Percentage,
GHO_UnShippable_Percentage, BB_UnShippable_Percentage,
BP_UnShippable_Percentage;

 

[Jeff Boyce]

Let me try again...

What data?

You gave us the results of queries. Those queries are based on some
(underlying) data.

If we don't know where you're starting, how can we tell you how to 'get
there'?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am not sure how to answer the question, but let me start here and we can
go from there. The output is this ( I will seperate it into 2 lines for
visibility here but it all comes out in one query):

NegATP Ship_Complete TotalLines Shippable DeliveryGroup
430 51 746 20 248

The above output are all from summary query's that are counting records.

TJ_Percentage GHO_Percentage BB_Percentage BP_Percentage
73.26% 6.51% 17.67% 0.47%

The above percentage fields are dividing each groups total (groups are TJ,
GHO, BB, BP) by a number field in their query's. I was just unable to get
the summarized data above in one query that is why each group above is its
own seperate query and the summary query just pulls them all together in
on
report if you will.

Sean

It all starts with the data ... and you haven't described the data enough
to
understand why you are using "10 queries [to] summarize ... group of
data".

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have 10 query's and each one summarizes a different group of data, and
each
query has 2 output fields. The output of one query would be Boxes 10,
another query's output would be Ladders 20. Each of these queries is
pulling
from a different data source. Then I have a summary query that pulls
in
the
results of each one of these query's. However, if one of the query's
has
no
results to display, the entire summary query does not produce any
results
even if their subquery's have data to present. Is there a better way
to
set
this up? The actual query is below. Thanks,

SELECT NegativeATP.NegATP, ShipComplete.Ship_Complete,
TotalLines.TotalLines, ShippableLines.Shippable,
DeliveryGroup_Shippable.DeliveryGroup,
TJ_UnShippable_Percentage.Percent
AS
TJ_Percentage, GHO_UnShippable_Percentage.Percent AS GHO_Percentage,
BB_UnShippable_Percentage.Percent AS BB_Percentage,
BP_UnShippable_Percentage.Percent AS BP_Percentage
FROM NegativeATP, ShipComplete, TotalLines, ShippableLines,
DeliveryGroup_Shippable, TJ_UnShippable_Percentage,
GHO_UnShippable_Percentage, BB_UnShippable_Percentage,
BP_UnShippable_Percentage;

 

[Sean]

The "Data" is a table in access which is populated from a spread sheet.
There are text fields, number fields, and date fields. All of the 10 query's
are summarizing this data based on different criteria in each query.

Let's say one of the queries is summarizing the number of products shipped
from phoenix, and there was no shipments from phoenix that day, the query
does not present any results. And if this happens, using this query within
the summary query (the 10 querys) causes all of the other query's that did
have results on their own not to show the results in the summary query. I am
not sure else how to explain it other than putting it into a database and
emailing it to you. Thanks again,

Let me try again...

What data?

You gave us the results of queries. Those queries are based on some
(underlying) data.

If we don't know where you're starting, how can we tell you how to 'get
there'?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am not sure how to answer the question, but let me start here and we can
go from there. The output is this ( I will seperate it into 2 lines for
visibility here but it all comes out in one query):

NegATP Ship_Complete TotalLines Shippable DeliveryGroup
430 51 746 20 248

The above output are all from summary query's that are counting records.

TJ_Percentage GHO_Percentage BB_Percentage BP_Percentage
73.26% 6.51% 17.67% 0.47%

The above percentage fields are dividing each groups total (groups are TJ,
GHO, BB, BP) by a number field in their query's. I was just unable to get
the summarized data above in one query that is why each group above is its
own seperate query and the summary query just pulls them all together in
on
report if you will.

Sean

It all starts with the data ... and you haven't described the data enough
to
understand why you are using "10 queries [to] summarize ... group of
data".

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have 10 query's and each one summarizes a different group of data, and
each
query has 2 output fields. The output of one query would be Boxes 10,
another query's output would be Ladders 20. Each of these queries is
pulling
from a different data source. Then I have a summary query that pulls
in
the
results of each one of these query's. However, if one of the query's
has
no
results to display, the entire summary query does not produce any
results
even if their subquery's have data to present. Is there a better way
to
set
this up? The actual query is below. Thanks,

SELECT NegativeATP.NegATP, ShipComplete.Ship_Complete,
TotalLines.TotalLines, ShippableLines.Shippable,
DeliveryGroup_Shippable.DeliveryGroup,
TJ_UnShippable_Percentage.Percent
AS
TJ_Percentage, GHO_UnShippable_Percentage.Percent AS GHO_Percentage,
BB_UnShippable_Percentage.Percent AS BB_Percentage,
BP_UnShippable_Percentage.Percent AS BP_Percentage
FROM NegativeATP, ShipComplete, TotalLines, ShippableLines,
DeliveryGroup_Shippable, TJ_UnShippable_Percentage,
GHO_UnShippable_Percentage, BB_UnShippable_Percentage,
BP_UnShippable_Percentage;

 

[Jeff Boyce]

Sean

Let's try this ...

If I were to describe a (overly-simplified) enrollment database, I'd use:

tblStudent
StudentID
LName
FName
DateOfBirth

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
StudentID
ClassID
EnrollmentDate

Now, you have underlying data that you are querying. What does that data
look like? Are there ten different tables for your 10 queries, or one
table? What's in those/that table(s) - see above

Regards

Jeff Boyce
Microsoft Office/Access MVP

The "Data" is a table in access which is populated from a spread sheet.
There are text fields, number fields, and date fields. All of the 10
query's
are summarizing this data based on different criteria in each query.

Let's say one of the queries is summarizing the number of products shipped
from phoenix, and there was no shipments from phoenix that day, the query
does not present any results. And if this happens, using this query
within
the summary query (the 10 querys) causes all of the other query's that did
have results on their own not to show the results in the summary query. I
am
not sure else how to explain it other than putting it into a database and
emailing it to you. Thanks again,

Let me try again...

What data?

You gave us the results of queries. Those queries are based on some
(underlying) data.

If we don't know where you're starting, how can we tell you how to 'get
there'?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am not sure how to answer the question, but let me start here and we
can
go from there. The output is this ( I will seperate it into 2 lines
for
visibility here but it all comes out in one query):

NegATP Ship_Complete TotalLines Shippable DeliveryGroup
430 51 746 20 248

The above output are all from summary query's that are counting
records.

TJ_Percentage GHO_Percentage BB_Percentage BP_Percentage
73.26% 6.51% 17.67%
0.47%

The above percentage fields are dividing each groups total (groups are
TJ,
GHO, BB, BP) by a number field in their query's. I was just unable to
get
the summarized data above in one query that is why each group above is
its
own seperate query and the summary query just pulls them all together
in
on
report if you will.



:

Sean

It all starts with the data ... and you haven't described the data
enough
to
understand why you are using "10 queries [to] summarize ... group
of
data".

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have 10 query's and each one summarizes a different group of data,
and
each
query has 2 output fields. The output of one query would be Boxes
10,
another query's output would be Ladders 20. Each of these queries
is
pulling
from a different data source. Then I have a summary query that
pulls
in
the
results of each one of these query's. However, if one of the
query's
has
no
results to display, the entire summary query does not produce any
results
even if their subquery's have data to present. Is there a better
way
to
set
this up? The actual query is below. Thanks,

SELECT NegativeATP.NegATP, ShipComplete.Ship_Complete,
TotalLines.TotalLines, ShippableLines.Shippable,
DeliveryGroup_Shippable.DeliveryGroup,
TJ_UnShippable_Percentage.Percent
AS
TJ_Percentage, GHO_UnShippable_Percentage.Percent AS GHO_Percentage,
BB_UnShippable_Percentage.Percent AS BB_Percentage,
BP_UnShippable_Percentage.Percent AS BP_Percentage
FROM NegativeATP, ShipComplete, TotalLines, ShippableLines,
DeliveryGroup_Shippable, TJ_UnShippable_Percentage,
GHO_UnShippable_Percentage, BB_UnShippable_Percentage,
BP_UnShippable_Percentage;