Union query and two Totals queries

  • Thread starter Bill Fischer via AccessMonster.com
  • Start date
B

Bill Fischer via AccessMonster.com

1st maybe I'm trying to do something that isn't possible. I have two Totals
queries I need to join in a Union query. Each Total query has a count field
and three sum fields. When I write the fields in the SQL statement I get
prompted for the values when trying to run the query instead of the values
from the Total queries. Is a Union query possible with Total queries or am I
missing something easy. Do I need to add a Count and Sum prefix to these
fields, which I have tried but may not be correctly writing the statement.

Here is what my SQL looks like for the Union:

SELECT [BusinessInformationID],[Business Name],[Account],[PayCodeID],
[InvoiceNumber],[LocationID],[CodingID],[RMTPay] FROM
[billqtolWhsleContactTotals] UNION ALL SELECT [BusinessInformationID],
[Business Name],[AccountNumber_ID],[PayCodeID],[InvoiceNumber],[LocationID],
[CodingID],[RMTPay] FROM [billqselYesBusinessW/OContact];

The above statement only has the Count field in place, which is
[InvoiceNumber] (trying to figure out one field first then apply it to the
others.

Here is the two Total query statements:

SELECT billqselWhsleContactSell.[BUSINESS NAME], billqselWhsleContactSell.
Account, tblBusinessInformation.PayCodeID, Count(billqselWhsleContactSell.
InvoiceNumber) AS CountOfInvoiceNumber, Sum(billqselWhsleContactSell.
InvoiceSale) AS SumOfInvoiceSale, Sum(billqselWhsleContactSell.InvoiceCost)
AS SumOfInvoiceCost, Sum(billqselWhsleContactSell.[Gross Prft]) AS
[SumOfGross Prft], Sum([Gross Prft])/Sum([InvoiceSale]) AS [Gross Prft %],
tblBusinessInformation.BusinessInformationID, billqselWhsleContactSell.
LocationID, billqselWhsleContactSell.CodingID, tblBusinessInformation.RMTPay
FROM tblBusinessInformation INNER JOIN billqselWhsleContactSell ON
tblBusinessInformation.BusinessInformationID = billqselWhsleContactSell.
BusinessInformationID
GROUP BY billqselWhsleContactSell.[BUSINESS NAME], billqselWhsleContactSell.
Account, tblBusinessInformation.PayCodeID, tblBusinessInformation.
BusinessInformationID, billqselWhsleContactSell.LocationID,
billqselWhsleContactSell.CodingID, tblBusinessInformation.RMTPay;

and

SELECT tblBusinessInformation.[BUSINESS NAME], tblBusinessInformation.
AccountNumber_ID, tblBusinessInformation.PayCodeID, Count
(qryselInvoiceCosting.InvoiceNumber) AS CountOfInvoiceNumber, Sum
(qryselInvoiceCosting.InvoiceSale) AS SumOfInvoiceSale, Sum
(qryselInvoiceCosting.InvoiceCost) AS SumOfInvoiceCost, Sum
(qryselInvoiceCosting.[Gross Prft]) AS [SumOfGross Prft], Sum([Gross Prft])
/Sum([InvoiceSale]) AS [Gross Prft %], tblBusinessInformation.
BusinessInformationID, tblBusinessInformation.LocationID,
tblBusinessInformation.CodingID, tblBusinessInformation.RMTPay
FROM (tblBusinessInformation INNER JOIN qryselInvoiceCosting ON
tblBusinessInformation.BusinessInformationID = qryselInvoiceCosting.
BusinessInformationID) LEFT JOIN tblContacts ON tblBusinessInformation.
BusinessInformationID = tblContacts.BusinessInformationID
GROUP BY tblBusinessInformation.[BUSINESS NAME], tblBusinessInformation.
AccountNumber_ID, tblBusinessInformation.PayCodeID, tblBusinessInformation.
BusinessInformationID, tblBusinessInformation.LocationID,
tblBusinessInformation.CodingID, tblBusinessInformation.RMTPay, tblContacts.
BusinessInformationID
HAVING (((tblBusinessInformation.PayCodeID)=1) AND ((tblContacts.
BusinessInformationID) Is Null));

Thanks in advance,

Bill Fischer
 
B

Bill Fischer via AccessMonster.com

Duh, figured it out, not using the aliases. Like I said missing something
easy. Need an easy button like the commericials! Sometimes you gotta laugh
at yourself!

Bill said:
1st maybe I'm trying to do something that isn't possible. I have two Totals
queries I need to join in a Union query. Each Total query has a count field
and three sum fields. When I write the fields in the SQL statement I get
prompted for the values when trying to run the query instead of the values
from the Total queries. Is a Union query possible with Total queries or am I
missing something easy. Do I need to add a Count and Sum prefix to these
fields, which I have tried but may not be correctly writing the statement.

Here is what my SQL looks like for the Union:

SELECT [BusinessInformationID],[Business Name],[Account],[PayCodeID],
[InvoiceNumber],[LocationID],[CodingID],[RMTPay] FROM
[billqtolWhsleContactTotals] UNION ALL SELECT [BusinessInformationID],
[Business Name],[AccountNumber_ID],[PayCodeID],[InvoiceNumber],[LocationID],
[CodingID],[RMTPay] FROM [billqselYesBusinessW/OContact];

The above statement only has the Count field in place, which is
[InvoiceNumber] (trying to figure out one field first then apply it to the
others.

Here is the two Total query statements:

SELECT billqselWhsleContactSell.[BUSINESS NAME], billqselWhsleContactSell.
Account, tblBusinessInformation.PayCodeID, Count(billqselWhsleContactSell.
InvoiceNumber) AS CountOfInvoiceNumber, Sum(billqselWhsleContactSell.
InvoiceSale) AS SumOfInvoiceSale, Sum(billqselWhsleContactSell.InvoiceCost)
AS SumOfInvoiceCost, Sum(billqselWhsleContactSell.[Gross Prft]) AS
[SumOfGross Prft], Sum([Gross Prft])/Sum([InvoiceSale]) AS [Gross Prft %],
tblBusinessInformation.BusinessInformationID, billqselWhsleContactSell.
LocationID, billqselWhsleContactSell.CodingID, tblBusinessInformation.RMTPay
FROM tblBusinessInformation INNER JOIN billqselWhsleContactSell ON
tblBusinessInformation.BusinessInformationID = billqselWhsleContactSell.
BusinessInformationID
GROUP BY billqselWhsleContactSell.[BUSINESS NAME], billqselWhsleContactSell.
Account, tblBusinessInformation.PayCodeID, tblBusinessInformation.
BusinessInformationID, billqselWhsleContactSell.LocationID,
billqselWhsleContactSell.CodingID, tblBusinessInformation.RMTPay;

and

SELECT tblBusinessInformation.[BUSINESS NAME], tblBusinessInformation.
AccountNumber_ID, tblBusinessInformation.PayCodeID, Count
(qryselInvoiceCosting.InvoiceNumber) AS CountOfInvoiceNumber, Sum
(qryselInvoiceCosting.InvoiceSale) AS SumOfInvoiceSale, Sum
(qryselInvoiceCosting.InvoiceCost) AS SumOfInvoiceCost, Sum
(qryselInvoiceCosting.[Gross Prft]) AS [SumOfGross Prft], Sum([Gross Prft])
/Sum([InvoiceSale]) AS [Gross Prft %], tblBusinessInformation.
BusinessInformationID, tblBusinessInformation.LocationID,
tblBusinessInformation.CodingID, tblBusinessInformation.RMTPay
FROM (tblBusinessInformation INNER JOIN qryselInvoiceCosting ON
tblBusinessInformation.BusinessInformationID = qryselInvoiceCosting.
BusinessInformationID) LEFT JOIN tblContacts ON tblBusinessInformation.
BusinessInformationID = tblContacts.BusinessInformationID
GROUP BY tblBusinessInformation.[BUSINESS NAME], tblBusinessInformation.
AccountNumber_ID, tblBusinessInformation.PayCodeID, tblBusinessInformation.
BusinessInformationID, tblBusinessInformation.LocationID,
tblBusinessInformation.CodingID, tblBusinessInformation.RMTPay, tblContacts.
BusinessInformationID
HAVING (((tblBusinessInformation.PayCodeID)=1) AND ((tblContacts.
BusinessInformationID) Is Null));

Thanks in advance,

Bill Fischer
Click to expand...
 
Top