Prev: Passing value to Query Criteria from ListBox
Next: How to add Percentages to the detail section of a report
From: GoBrowns! on 15 Jul 2008 16:31
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!



"Duane Hookom" wrote:

> 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
>
> "bbig80524" <bbig80524(a)discussions.microsoft.com> wrote in message
> news:FF72F13B-DBC0-4605-A40E-A23705CDB0D2(a)microsoft.com...
> > I've not yet heard of a Union query. Would you explain?
> >
> > Thanks.
> >
> > "Duane Hookom" wrote:
> >
> >> 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
> >>
> >> "bbig80524" <bbig80524(a)discussions.microsoft.com> wrote in message
> >> news:878CD239-E854-4269-BDB2-01558944BE63(a)microsoft.com...
> >> > 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
> >> >
> >> > "Duane Hookom" wrote:
> >> >
> >> >> Place the final subreport in the Report Footer section so it only
> >> >> prints
> >> >> once on the main report.
> >> >> --
> >> >> Duane Hookom
> >> >> MS Access MVP
> >> >>
> >> >> "bbig80524" <bbig80524(a)discussions.microsoft.com> wrote in message
> >> >> news:E71C0B54-293E-4619-AA32-66986963863F(a)microsoft.com...
> >> >> >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.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
From: John Spencer on 16 Jul 2008 08:00
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!
>
>
>
> "Duane Hookom" wrote:
>
>> 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
>>
>> "bbig80524" <bbig80524(a)discussions.microsoft.com> wrote in message
>> news:FF72F13B-DBC0-4605-A40E-A23705CDB0D2(a)microsoft.com...
>>> I've not yet heard of a Union query. Would you explain?
>>>
>>> Thanks.
>>>
>>> "Duane Hookom" wrote:
>>>
>>>> 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
>>>>
>>>> "bbig80524" <bbig80524(a)discussions.microsoft.com> wrote in message
>>>> news:878CD239-E854-4269-BDB2-01558944BE63(a)microsoft.com...
>>>>> 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
>>>>>
>>>>> "Duane Hookom" wrote:
>>>>>
>>>>>> Place the final subreport in the Report Footer section so it only
>>>>>> prints
>>>>>> once on the main report.
>>>>>> --
>>>>>> Duane Hookom
>>>>>> MS Access MVP
>>>>>>
>>>>>> "bbig80524" <bbig80524(a)discussions.microsoft.com> wrote in message
>>>>>> news:E71C0B54-293E-4619-AA32-66986963863F(a)microsoft.com...
>>>>>>> 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.
>>>>>>
>>>>>>
>>>>
>>>>
>>
>>
From: GoBrowns! on 16 Jul 2008 11:18
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" wrote:

> 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!
> >
> >
> >
> > "Duane Hookom" wrote:
> >
> >> 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
> >>
> >> "bbig80524" <bbig80524(a)discussions.microsoft.com> wrote in message
> >> news:FF72F13B-DBC0-4605-A40E-A23705CDB0D2(a)microsoft.com...
> >>> I've not yet heard of a Union query. Would you explain?
> >>>
> >>> Thanks.
> >>>
> >>> "Duane Hookom" wrote:
> >>>
> >>>> 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
> >>>>
> >>>> "bbig80524" <bbig80524(a)discussions.microsoft.com> wrote in message
> >>>> news:878CD239-E854-4269-BDB2-01558944BE63(a)microsoft.com...
> >>>>> 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
> >>>>>
> >>>>> "Duane Hookom" wrote:
> >>>>>
> >>>>>> Place the final subreport in the Report Footer section so it only
> >>>>>> prints
> >>>>>> once on the main report.
> >>>>>> --
> >>>>>> Duane Hookom
> >>>>>> MS Access MVP
> >>>>>>
> >>>>>> "bbig80524" <bbig80524(a)discussions.microsoft.com> wrote in message
> >>>>>> news:E71C0B54-293E-4619-AA32-66986963863F(a)microsoft.com...
> >>>>>>> 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.
> >>>>>>
> >>>>>>
> >>>>
> >>>>
> >>
> >>
>
From: John Spencer on 16 Jul 2008 11:58
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! wrote:
> 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" wrote:
>
>> 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!
>>>
>>>
>>>
>>> "Duane Hookom" wrote:
>>>
>>>> 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
>>>>
>>>> "bbig80524" <bbig80524(a)discussions.microsoft.com> wrote in message
>>>> news:FF72F13B-DBC0-4605-A40E-A23705CDB0D2(a)microsoft.com...
>>>>> I've not yet heard of a Union query. Would you explain?
>>>>>
>>>>> Thanks.
>>>>>
>>>>> "Duane Hookom" wrote:
>>>>>
>>>>>> 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
>>>>>>
>>>>>> "bbig80524" <bbig80524(a)discussions.microsoft.com> wrote in message
>>>>>> news:878CD239-E854-4269-BDB2-01558944BE63(a)microsoft.com...
>>>>>>> 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
>>>>>>>
>>>>>>> "Duane Hookom" wrote:
>>>>>>>
>>>>>>>> Place the final subreport in the Report Footer section so it only
>>>>>>>> prints
>>>>>>>> once on the main report.
>>>>>>>> --
>>>>>>>> Duane Hookom
>>>>>>>> MS Access MVP
>>>>>>>>
>>>>>>>> "bbig80524" <bbig80524(a)discussions.microsoft.com> wrote in message
>>>>>>>> news:E71C0B54-293E-4619-AA32-66986963863F(a)microsoft.com...
>>>>>>>>> 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.
>>>>>>>>
>>>>>>
>>>>
From: GoBrowns! on 16 Jul 2008 13:16
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" wrote:

> 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! wrote:
> > 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" wrote:
> >
> >> 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!
> >>>
> >>>
> >>>
> >>> "Duane Hookom" wrote:
> >>>
> >>>> 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
> >>>>
> >>>> "bbig80524" <bbig80524(a)discussions.microsoft.com> wrote in message
> >>>> news:FF72F13B-DBC0-4605-A40E-A23705CDB0D2(a)microsoft.com...
> >>>>> I've not yet heard of a Union query. Would you explain?
> >>>>>
> >>>>> Thanks.
> >>>>>
> >>>>> "Duane Hookom" wrote:
> >>>>>
> >>>>>> 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
> >>>>>>
> >>>>>> "bbig80524" <bbig80524(a)discussions.microsoft.com> wrote in message
> >>>>>> news:878CD239-E854-4269-BDB2-01558944BE63(a)microsoft.com...
> >>>>>>> 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
> >>>>>>>
> >>>>>>> "Duane Hookom" wrote:
> >>>>>>>
> >>>>>>>> Place the final subreport in the Report Footer section so it only
> >>>>>>>> prints
> >>>>>>>> once on the main report.
> >>>>>>>> --
> >>>>>>>> Duane Hookom
> >>>>>>>> MS Access MVP
> >>>>>>>>
> >>>>>>>> "bbig80524" <bbig80524(a)discussions.microsoft.com> wrote in message
> >>>>>>>> news:E71C0B54-293E-4619-AA32-66986963863F(a)microsoft.com...
> >>>>>>>>> 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.
> >>>>>>>>
> >>>>>>
> >>>>
>
 |  Next  |  Last
Pages: 1 2
Prev: Passing value to Query Criteria from ListBox
Next: How to add Percentages to the detail section of a report