union query?

[Slez via AccessMonster.com]

I need to pull data from 2 queries together for a report, and based on alot
of searching and reading previous posts it sounds like a UNION query is the
route I should go. I haven't gotten it to work yet, and could use some
guidance.

qryNoBidTotal returns the following records (as an example):

[GCName] [NoBidQty]
Customer A 5
Customer C 1
Customer D 2
Customer G 1

qryStatClient returns these records (there are a number of add'l
fields/columns, but no need to post):

[GCName] [Project]
Customer C Proj 1
Customer C Proj 2
Customer E Proj 3
Customer F Proj 4
Customer G Proj 5

I want to combine them to achieve the following:
[GCName] [NoBidQty] [Project]
Customer A 5
Customer C 1
Customer C Proj 1
Customer C Proj 2
Customer D 2
Customer E Proj 3
Customer F Proj 4
Customer G 1
Customer G Proj 5

In qryStatClient, I tried typing the word "UNION" after the SQL and pasting
the SQL from qryNoBidTotal, but when I run the query, I get an error stating
that an operator is required between the end of the first query's SQL and
UNION. Am I going about this the right way? I can post SQL if this is an
achievable goal. Feedback is appreciated! Thanks!
Slez

 

[Guest]

Try a new query like this:

SELECT qryNoBidTotal.GCName, qryNoBidTotal.NoBidQty, qryStatClient.Project
FROM qryNoBidTotal INNER JOIN qryStatClient ON qryNoBidTotal.GCName =
qryStatClient .GCName

 

[John W. Vinson/MVP]

I need to pull data from 2 queries together for a report, and based on alot
of searching and reading previous posts it sounds like a UNION query is
the
route I should go. I haven't gotten it to work yet, and could use some
guidance.

A UNION query must have two complete SELECT... FROM... queries with the SAME
number of fields of the SAME datatypes. You can probably use

SELECT [GCName],[NoBidQty],"" AS Project FROM qryNoBidQty
UNION ALL
SELECT [GCName], Null, [Project] FROM qryStatClient
ORDER BY GCName;

John W. Vinson

 

[Slez via AccessMonster.com]

Thank you both for your reply. I'm not really understanding how this works,
so I have decided to post all of my SQL. If you are able to take a little
time to review it, perhaps you can offer some suggestions that would help me
grasp this. Thanks again!

TRANSFORM Sum(([Quantity]*([UnitPrice]+[QuoteCost]))*[Markup]) AS
LineTotalSellPrice
SELECT tblProjectClient.GCName, Project.ProjectID, Project.ProjectName, Bid.
BidNumber, Bid.BidDate, Bid.BidStatus, tblProjectClient.AwardGC, Bid.
SalesTaxAmount, Bid.UseTaxAmount
FROM (Product INNER JOIN (Bid INNER JOIN ((Project INNER JOIN Item ON Project.
ProjectID = Item.ProjectID) INNER JOIN ItemDetail ON (Item.ItemNumber =
ItemDetail.ItemNumber) AND (Item.RoomNumber = ItemDetail.RoomNumber) AND
(Item.BidNumber = ItemDetail.BidNumber) AND (Item.ProjectID = ItemDetail.
ProjectID) AND (Project.ProjectID = ItemDetail.ProjectID)) ON (Project.
ProjectID = Bid.ProjectID) AND (Bid.BidNumber = Item.BidNumber) AND (Bid.
ProjectID = Item.ProjectID)) ON Product.ProductDescription = ItemDetail.
ProductDescription) INNER JOIN tblProjectClient ON Project.ProjectID =
tblProjectClient.ProjectID
WHERE (((Bid.BidDate)>=#1/1/2007#) AND ((Bid.BidStatus)<>"Void"))
GROUP BY tblProjectClient.GCName, Project.ProjectID, Project.ProjectName, Bid.
BidNumber, Bid.BidDate, Bid.BidStatus, tblProjectClient.AwardGC, Bid.
SalesTaxAmount, Bid.UseTaxAmount
ORDER BY tblProjectClient.GCName, Project.ProjectID, Bid.BidNumber, Bid.
BidType
PIVOT Bid.BidType
UNION
SELECT tblNoBid.GCName, Sum(tblNoBid.NoBidQuantity) AS SumOfNoBidQuantity
FROM tblNoBid
WHERE (((tblNoBid.NoBidEntryDate)>#1/1/2007#))
GROUP BY tblNoBid.GCName;

I need to pull data from 2 queries together for a report, and based on alot
of searching and reading previous posts it sounds like a UNION query is
the
route I should go. I haven't gotten it to work yet, and could use some
guidance.

A UNION query must have two complete SELECT... FROM... queries with the SAME
number of fields of the SAME datatypes. You can probably use

SELECT [GCName],[NoBidQty],"" AS Project FROM qryNoBidQty
UNION ALL
SELECT [GCName], Null, [Project] FROM qryStatClient
ORDER BY GCName;

John W. Vinson