Combining two queries in one report.

B

bbig80524

I have two parameter queries that create the output I'm looking for. Both
queries use the same input. I now want to combine these two queries in one
report such that the complete results from the first query is printed first
and the complete results of the second are printed next. Both queries report
columns from tables. I don't need to total anything. It would be nice to
report the input parameter on the top of the page as well but that would be
icing on the cake.

I tried creating a main report and then adding a subreport (to the detail
section) but my output is a mess. I get the first line from the first query
followed by the entire second query and then it repeats only with the second
line from the first query and so on. While I figured adding the subreport to
the detail section would produce the results I got, I don't know what to try
next.

Any help you could provide would be appreciated.
 
D

Duane Hookom

Place the final subreport in the Report Footer section so it only prints
once on the main report.
 
B

bbig80524

Thanks Duane, simple enough.

My main report is a simple listing of select records from a table (with
column headers). The sub report is something similar. How can I get the
report to start listing the second list right after the first list is
finished. The number of records in both reports can vary.

dbs
 
D

Duane Hookom

If the subreport method doesn't work, you might be able to create a union
query to use as the Record Source of the main report and then not use the
subreport.
 
D

Duane Hookom

Assuming you have two similar tables (our queries) like:

tblCustomers
================
CustomerID
ContactFName
ContactLName
ContactTitle
.....

tblSuppliers
==================
SupplierID
SupplierFirstName
SupplierLastName
SupplierTitle
.....

You can create a single list of all customer contacts and suppliers with a
union query like:

SELECT "Customers" as OriginalTable, CustomerID as ID, ContactFName as
FName, ContactLName as LName, ContactTitle as Title
FROM tblCustomers
UNION ALL
SELECT "Suppliers", SupplierID, SupplierFirstName, SupplierLastName,
SupplierTitle
FROM tblSuppliers;

This can be created only in the SQL view of your query design.
 
G

GoBrowns!

Duane;

I am having this exact problem and tried to write the Union query in SQL,
but I keep getting the error that the select statement includes a reserved
work or an argument name that is misspelled or missing. Here are the two
queries I am trying to unite:

Unscheduled_Production
================
Work_Center
Work_Center_Description
Material
Material_Description
Delivered_Quantity

.....

Excess_Production
============
Work_Center
Work_Center_Description
Material
Material_Description
Excess_Production

Here is what I put in SQL:

SELECT "Unscheduled_Production" as OriginalTable, Work_Center as Work
Center, Work_Center_Description as Work Center Description, Material as IDH,
Material_Description as IDH Description, Delivered_Quantity as Produced;
FROM Unscheduled_Production
UNION ALL
SELECT "Excess_Production", Work_Center, Work_Center_Description, Material,
Material_Description, Excess_Production
FROM Excess_Production;

Can you please help me troubleshoot?
Thanks!
 
J

John Spencer

You need square brackets around any field or table name (or alias for them)
that contains spaces, is a reserved word, or contains any characters besides
letters, numbers, and the underscore character.

Also the semi-colon indicates END OF QUERY so you need to remove that from the
first query in the UNION query. Those changes should leave you with a query
that looks like the following:

SELECT "Unscheduled_Production" as OriginalTable
, Work_Center as [Work Center]
, Work_Center_Description as [Work Center Description]
, Material as IDH
, Material_Description as [IDH Description]
, Delivered_Quantity as Produced
FROM Unscheduled_Production
UNION ALL
SELECT "Excess_Production"
, Work_Center
, Work_Center_Description
, Material
, Material_Description
, Excess_Production
FROM Excess_Production;

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
G

GoBrowns!

John -

It worked like a charm! Thanks so much for your help.

Another question: My report has a "grand total" column in the report footer.
The details of the report have several different subtotal sections, and the
length of the report changes daily. However, my "grand total" ALWAYS appears
on its own separate page following the report. There are no page breaks or
anything in there - any advice here?

Thanks so much - Go Browns!

John Spencer said:
You need square brackets around any field or table name (or alias for them)
that contains spaces, is a reserved word, or contains any characters besides
letters, numbers, and the underscore character.

Also the semi-colon indicates END OF QUERY so you need to remove that from the
first query in the UNION query. Those changes should leave you with a query
that looks like the following:

SELECT "Unscheduled_Production" as OriginalTable
, Work_Center as [Work Center]
, Work_Center_Description as [Work Center Description]
, Material as IDH
, Material_Description as [IDH Description]
, Delivered_Quantity as Produced
FROM Unscheduled_Production
UNION ALL
SELECT "Excess_Production"
, Work_Center
, Work_Center_Description
, Material
, Material_Description
, Excess_Production
FROM Excess_Production;

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

GoBrowns! said:
Duane;

I am having this exact problem and tried to write the Union query in SQL,
but I keep getting the error that the select statement includes a reserved
work or an argument name that is misspelled or missing. Here are the two
queries I am trying to unite:

Unscheduled_Production
================
Work_Center
Work_Center_Description
Material
Material_Description
Delivered_Quantity

....

Excess_Production
============
Work_Center
Work_Center_Description
Material
Material_Description
Excess_Production

Here is what I put in SQL:

SELECT "Unscheduled_Production" as OriginalTable, Work_Center as Work
Center, Work_Center_Description as Work Center Description, Material as IDH,
Material_Description as IDH Description, Delivered_Quantity as Produced;
FROM Unscheduled_Production
UNION ALL
SELECT "Excess_Production", Work_Center, Work_Center_Description, Material,
Material_Description, Excess_Production
FROM Excess_Production;

Can you please help me troubleshoot?
Thanks!
Click to expand...
Click to expand...
 
J

John Spencer

Probably the Force New Page property of one of the report sections is set to
After Section or the ForceNewPage propery of the Report footer is set to
before Section.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

GoBrowns! said:
John -

It worked like a charm! Thanks so much for your help.

Another question: My report has a "grand total" column in the report footer.
The details of the report have several different subtotal sections, and the
length of the report changes daily. However, my "grand total" ALWAYS appears
on its own separate page following the report. There are no page breaks or
anything in there - any advice here?

Thanks so much - Go Browns!

John Spencer said:
You need square brackets around any field or table name (or alias for them)
that contains spaces, is a reserved word, or contains any characters besides
letters, numbers, and the underscore character.

Also the semi-colon indicates END OF QUERY so you need to remove that from the
first query in the UNION query. Those changes should leave you with a query
that looks like the following:

SELECT "Unscheduled_Production" as OriginalTable
, Work_Center as [Work Center]
, Work_Center_Description as [Work Center Description]
, Material as IDH
, Material_Description as [IDH Description]
, Delivered_Quantity as Produced
FROM Unscheduled_Production
UNION ALL
SELECT "Excess_Production"
, Work_Center
, Work_Center_Description
, Material
, Material_Description
, Excess_Production
FROM Excess_Production;

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

GoBrowns! said:
Duane;

I am having this exact problem and tried to write the Union query in SQL,
but I keep getting the error that the select statement includes a reserved
work or an argument name that is misspelled or missing. Here are the two
queries I am trying to unite:

Unscheduled_Production
================
Work_Center
Work_Center_Description
Material
Material_Description
Delivered_Quantity

....

Excess_Production
============
Work_Center
Work_Center_Description
Material
Material_Description
Excess_Production

Here is what I put in SQL:

SELECT "Unscheduled_Production" as OriginalTable, Work_Center as Work
Center, Work_Center_Description as Work Center Description, Material as IDH,
Material_Description as IDH Description, Delivered_Quantity as Produced;
FROM Unscheduled_Production
UNION ALL
SELECT "Excess_Production", Work_Center, Work_Center_Description, Material,
Material_Description, Excess_Production
FROM Excess_Production;

Can you please help me troubleshoot?
Thanks!



:

Assuming you have two similar tables (our queries) like:

tblCustomers
================
CustomerID
ContactFName
ContactLName
ContactTitle
.....

tblSuppliers
==================
SupplierID
SupplierFirstName
SupplierLastName
SupplierTitle
.....

You can create a single list of all customer contacts and suppliers with a
union query like:

SELECT "Customers" as OriginalTable, CustomerID as ID, ContactFName as
FName, ContactLName as LName, ContactTitle as Title
FROM tblCustomers
UNION ALL
SELECT "Suppliers", SupplierID, SupplierFirstName, SupplierLastName,
SupplierTitle
FROM tblSuppliers;

This can be created only in the SQL view of your query design.

--
Duane Hookom
MS Access MVP

I've not yet heard of a Union query. Would you explain?

Thanks.

:

If the subreport method doesn't work, you might be able to create a union
query to use as the Record Source of the main report and then not use the
subreport.
--
Duane Hookom
MS Access MVP

Thanks Duane, simple enough.

My main report is a simple listing of select records from a table (with
column headers). The sub report is something similar. How can I get the
report to start listing the second list right after the first list is
finished. The number of records in both reports can vary.

dbs

:

Place the final subreport in the Report Footer section so it only
prints
once on the main report.
--
Duane Hookom
MS Access MVP

I have two parameter queries that create the output I'm looking for.
Both
queries use the same input. I now want to combine these two queries
in
one
report such that the complete results from the first query is
printed
first
and the complete results of the second are printed next. Both
queries
report
columns from tables. I don't need to total anything. It would be
nice
to
report the input parameter on the top of the page as well but that
would
be
icing on the cake.

I tried creating a main report and then adding a subreport (to the
detail
section) but my output is a mess. I get the first line from the
first
query
followed by the entire second query and then it repeats only with
the
second
line from the first query and so on. While I figured adding the
subreport
to
the detail section would produce the results I got, I don't know
what
to
try
next.

Any help you could provide would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
G

GoBrowns!

I looked at this and all of the report sections were set to "None" on Force
New Page property. Any other suggestions?

Thanks!

John Spencer said:
Probably the Force New Page property of one of the report sections is set to
After Section or the ForceNewPage propery of the Report footer is set to
before Section.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

GoBrowns! said:
John -

It worked like a charm! Thanks so much for your help.

Another question: My report has a "grand total" column in the report footer.
The details of the report have several different subtotal sections, and the
length of the report changes daily. However, my "grand total" ALWAYS appears
on its own separate page following the report. There are no page breaks or
anything in there - any advice here?

Thanks so much - Go Browns!

John Spencer said:
You need square brackets around any field or table name (or alias for them)
that contains spaces, is a reserved word, or contains any characters besides
letters, numbers, and the underscore character.

Also the semi-colon indicates END OF QUERY so you need to remove that from the
first query in the UNION query. Those changes should leave you with a query
that looks like the following:

SELECT "Unscheduled_Production" as OriginalTable
, Work_Center as [Work Center]
, Work_Center_Description as [Work Center Description]
, Material as IDH
, Material_Description as [IDH Description]
, Delivered_Quantity as Produced
FROM Unscheduled_Production
UNION ALL
SELECT "Excess_Production"
, Work_Center
, Work_Center_Description
, Material
, Material_Description
, Excess_Production
FROM Excess_Production;

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

GoBrowns! wrote:
Duane;

I am having this exact problem and tried to write the Union query in SQL,
but I keep getting the error that the select statement includes a reserved
work or an argument name that is misspelled or missing. Here are the two
queries I am trying to unite:

Unscheduled_Production
================
Work_Center
Work_Center_Description
Material
Material_Description
Delivered_Quantity

....

Excess_Production
============
Work_Center
Work_Center_Description
Material
Material_Description
Excess_Production

Here is what I put in SQL:

SELECT "Unscheduled_Production" as OriginalTable, Work_Center as Work
Center, Work_Center_Description as Work Center Description, Material as IDH,
Material_Description as IDH Description, Delivered_Quantity as Produced;
FROM Unscheduled_Production
UNION ALL
SELECT "Excess_Production", Work_Center, Work_Center_Description, Material,
Material_Description, Excess_Production
FROM Excess_Production;

Can you please help me troubleshoot?
Thanks!



:

Assuming you have two similar tables (our queries) like:

tblCustomers
================
CustomerID
ContactFName
ContactLName
ContactTitle
.....

tblSuppliers
==================
SupplierID
SupplierFirstName
SupplierLastName
SupplierTitle
.....

You can create a single list of all customer contacts and suppliers with a
union query like:

SELECT "Customers" as OriginalTable, CustomerID as ID, ContactFName as
FName, ContactLName as LName, ContactTitle as Title
FROM tblCustomers
UNION ALL
SELECT "Suppliers", SupplierID, SupplierFirstName, SupplierLastName,
SupplierTitle
FROM tblSuppliers;

This can be created only in the SQL view of your query design.

--
Duane Hookom
MS Access MVP

I've not yet heard of a Union query. Would you explain?

Thanks.

:

If the subreport method doesn't work, you might be able to create a union
query to use as the Record Source of the main report and then not use the
subreport.
--
Duane Hookom
MS Access MVP

Thanks Duane, simple enough.

My main report is a simple listing of select records from a table (with
column headers). The sub report is something similar. How can I get the
report to start listing the second list right after the first list is
finished. The number of records in both reports can vary.

dbs

:

Place the final subreport in the Report Footer section so it only
prints
once on the main report.
--
Duane Hookom
MS Access MVP

I have two parameter queries that create the output I'm looking for.
Both
queries use the same input. I now want to combine these two queries
in
one
report such that the complete results from the first query is
printed
first
and the complete results of the second are printed next. Both
queries
report
columns from tables. I don't need to total anything. It would be
nice
to
report the input parameter on the top of the page as well but that
would
be
icing on the cake.

I tried creating a main report and then adding a subreport (to the
detail
section) but my output is a mess. I get the first line from the
first
query
followed by the entire second query and then it repeats only with
the
second
line from the first query and so on. While I figured adding the
subreport
to
the detail section would produce the results I got, I don't know
what
to
try
next.

Any help you could provide would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Sorry, no other suggestions.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

GoBrowns! said:
I looked at this and all of the report sections were set to "None" on Force
New Page property. Any other suggestions?

Thanks!

John Spencer said:
Probably the Force New Page property of one of the report sections is set to
After Section or the ForceNewPage propery of the Report footer is set to
before Section.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

GoBrowns! said:
John -

It worked like a charm! Thanks so much for your help.

Another question: My report has a "grand total" column in the report footer.
The details of the report have several different subtotal sections, and the
length of the report changes daily. However, my "grand total" ALWAYS appears
on its own separate page following the report. There are no page breaks or
anything in there - any advice here?

Thanks so much - Go Browns!

:

You need square brackets around any field or table name (or alias for them)
that contains spaces, is a reserved word, or contains any characters besides
letters, numbers, and the underscore character.

Also the semi-colon indicates END OF QUERY so you need to remove that from the
first query in the UNION query. Those changes should leave you with a query
that looks like the following:

SELECT "Unscheduled_Production" as OriginalTable
, Work_Center as [Work Center]
, Work_Center_Description as [Work Center Description]
, Material as IDH
, Material_Description as [IDH Description]
, Delivered_Quantity as Produced
FROM Unscheduled_Production
UNION ALL
SELECT "Excess_Production"
, Work_Center
, Work_Center_Description
, Material
, Material_Description
, Excess_Production
FROM Excess_Production;

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

GoBrowns! wrote:
Duane;

I am having this exact problem and tried to write the Union query in SQL,
but I keep getting the error that the select statement includes a reserved
work or an argument name that is misspelled or missing. Here are the two
queries I am trying to unite:

Unscheduled_Production
================
Work_Center
Work_Center_Description
Material
Material_Description
Delivered_Quantity

....

Excess_Production
============
Work_Center
Work_Center_Description
Material
Material_Description
Excess_Production

Here is what I put in SQL:

SELECT "Unscheduled_Production" as OriginalTable, Work_Center as Work
Center, Work_Center_Description as Work Center Description, Material as IDH,
Material_Description as IDH Description, Delivered_Quantity as Produced;
FROM Unscheduled_Production
UNION ALL
SELECT "Excess_Production", Work_Center, Work_Center_Description, Material,
Material_Description, Excess_Production
FROM Excess_Production;

Can you please help me troubleshoot?
Thanks!



:

Assuming you have two similar tables (our queries) like:

tblCustomers
================
CustomerID
ContactFName
ContactLName
ContactTitle
.....

tblSuppliers
==================
SupplierID
SupplierFirstName
SupplierLastName
SupplierTitle
.....

You can create a single list of all customer contacts and suppliers with a
union query like:

SELECT "Customers" as OriginalTable, CustomerID as ID, ContactFName as
FName, ContactLName as LName, ContactTitle as Title
FROM tblCustomers
UNION ALL
SELECT "Suppliers", SupplierID, SupplierFirstName, SupplierLastName,
SupplierTitle
FROM tblSuppliers;

This can be created only in the SQL view of your query design.

--
Duane Hookom
MS Access MVP

I've not yet heard of a Union query. Would you explain?

Thanks.

:

If the subreport method doesn't work, you might be able to create a union
query to use as the Record Source of the main report and then not use the
subreport.
--
Duane Hookom
MS Access MVP

Thanks Duane, simple enough.

My main report is a simple listing of select records from a table (with
column headers). The sub report is something similar. How can I get the
report to start listing the second list right after the first list is
finished. The number of records in both reports can vary.

dbs

:

Place the final subreport in the Report Footer section so it only
prints
once on the main report.
--
Duane Hookom
MS Access MVP

I have two parameter queries that create the output I'm looking for.
Both
queries use the same input. I now want to combine these two queries
in
one
report such that the complete results from the first query is
printed
first
and the complete results of the second are printed next. Both
queries
report
columns from tables. I don't need to total anything. It would be
nice
to
report the input parameter on the top of the page as well but that
would
be
icing on the cake.

I tried creating a main report and then adding a subreport (to the
detail
section) but my output is a mess. I get the first line from the
first
query
followed by the entire second query and then it repeats only with
the
second
line from the first query and so on. While I figured adding the
subreport
to
the detail section would produce the results I got, I don't know
what
to
try
next.

Any help you could provide would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

subreport repeating parameters 8
Merging two reports 4
DB Documentation Report Crashes 0
Office 10 Crosstab query with Parameters 0
Blank Lines Added To Each page Of Report 3
Parameters with mor than one crosstab query in a report. 8
Draw A Line Across Both Columns 2
one report based on multiple queries 2

Top