Union Query
Aug 3, 2006
Okay, people. I am getting married in a few days and my mind is on other things. Consequently, I can't get my head around this one.
I have two queries, Query1 and Query2. Both have a different number of fields but they have the field "IA Code" in common.
I want to create a new query containing all rows from Query1 and all rows from Query2. Where the [IA Code] matches, I'd like the information to be displayed in one row.
I think I need to use Union somewhere along the lines, but I can't work it out.
Any pointers/syntax greatly appreciated.
View Replies
ADVERTISEMENT
Oct 24, 2013
I'm having a problem with a UNION / UNION ALL query.It seems there is a application crash fault when running the query that MS are aware of and have issued a hot fix. Unfortunately it will take my IT dept some time to check and install the hot fix If they agree to do it at all.
Problem signature:
Problem Event Name: APPCRASH
Application Name: MSACCESS.EXE
Application Version: 12.0.6606.1000
[code]...
View 1 Replies
View Related
Oct 28, 2005
Hello All,
I'm trying to run a UNION query that joins five queries through a MS WorkSpace into a DAO.recordset in VB. I'm pulling the data from a SQL Server Database through VB in Access. I'm attempting to open a recordset with a query passed to it as a string. The query is below. For some reason, I'm receiving a message: "MS Jet database engine cannot find the input table or query. Runtime Error 3078".
Here's what's puzzling. When I run a single query without any UNION statement, the code finds the table and runs fine without error, but anytime I join two or more queries with a UNION statement in the VB, it gives me the error.
I've executed the same UNION query in both Access Query Builder and SQL Server's Query Analyzer and they work fine in both environments. It's only when I call the query from a DAO.Recodset with VB that it causes this problem. The following is a sample of the UNION query joining two of the five queries. Does anyone have any idea what could be the problem? The following query executes in about 5 seconds so I don't think there's a "time-out" issue. I'm thinking that the UNION statement may be the culprit. Maybe there's another way to approach joining these separate queries? Any help would be most appreciated. Thanks.
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((CategoryID) = 'HCPROD') AND ((BrandID)<>'CSS')) AND (((BrandID)<>'1356')) AND (((BrandID)<>'1400')) AND (((BrandID)<>'1551')) AND (((BrandID)<>'555')) AND (((BrandID)<>'66'))
AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
UNION
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((ProductID) = '0029800')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
Set wrkJet = CreateWorkspace("", "pw", "", dbUseJet)
Set db = wrkJet.OpenDatabase("DW", _
dbDriverNoPrompt, True, _
"ODBC;DATABASE=DW;DSN=DW2")
'Set rs1 = db.OpenRecordset(strSQL)
View 9 Replies
View Related
Aug 18, 2005
I have created a report that provides me with employee expenses for temps per week. The types of expenses have been defined as Ad_hoc amounts.
An SQL union query I have used to combine fields
Adhoc_Code_1 - 3
Adhoc_description_1 - 3
Adhoc_Pay_Amount_1 - 3
(details of full sql query below)
I have tried to run for a particular week which should have 3 expense entries but only 2 have been picked up.
I think this is because both Adhoc_ Pay_Amount_2 and 3 have a value of 6 and the UNION operation will not return duplicate records. I have amended to UNION ALL but all entries are duplicated. Can anyone help?
Thanks
SELECT dbo_Valid_Timesheets.Employer_Ref, dbo_Valid_Timesheets.Personnel_Ref, dbo_Valid_Timesheets.Department, dbo_Valid_Timesheets.Tax_Yr_Proc_By_Payroll, dbo_Valid_Timesheets.Period_Proc_By_Payroll, dbo_Valid_Timesheets.Session_Proc_By_Payroll, dbo_Valid_Timesheets.Adhoc_Code_1, dbo_Valid_Timesheets.Adhoc_Description_1, dbo_Valid_Timesheets.Adhoc_Pay_Amount_1, dbo_Valid_Timesheets.Timesheet_Number
FROM dbo_Valid_Timesheets
WHERE (((dbo_Valid_Timesheets.Adhoc_Pay_Amount_1)<>0));
UNION ALL
SELECT dbo_Valid_Timesheets.Employer_Ref, dbo_Valid_Timesheets.Personnel_Ref, dbo_Valid_Timesheets.Department, dbo_Valid_Timesheets.Tax_Yr_Proc_By_Payroll, dbo_Valid_Timesheets.Period_Proc_By_Payroll, dbo_Valid_Timesheets.Session_Proc_By_Payroll, dbo_Valid_Timesheets.Adhoc_Code_2, dbo_Valid_Timesheets.Adhoc_Description_2, dbo_Valid_Timesheets.Adhoc_Pay_Amount_2, dbo_Valid_Timesheets.Timesheet_Number
FROM dbo_Valid_Timesheets
WHERE (((dbo_Valid_Timesheets.Adhoc_Pay_Amount_2)<>0));
UNION ALL
SELECT dbo_Valid_Timesheets.Employer_Ref, dbo_Valid_Timesheets.Personnel_Ref, dbo_Valid_Timesheets.Department, dbo_Valid_Timesheets.Tax_Yr_Proc_By_Payroll, dbo_Valid_Timesheets.Period_Proc_By_Payroll, dbo_Valid_Timesheets.Session_Proc_By_Payroll, dbo_Valid_Timesheets.Adhoc_Code_3, dbo_Valid_Timesheets.Adhoc_Description_3, dbo_Valid_Timesheets.Adhoc_Pay_Amount_3, dbo_Valid_Timesheets.Timesheet_Number
FROM dbo_Valid_Timesheets
WHERE (((dbo_Valid_Timesheets.Adhoc_Pay_Amount_3)<>0));
UNION ALL SELECT dbo_EE_Payment_History.Employer_Ref, dbo_EE_Payment_History.Personnel_Ref, dbo_Payslip_Static_Data.Department, dbo_EE_Payment_History.Tax_Year, dbo_EE_Payment_History.Tax_Period, dbo_EE_Payment_History.Tax_Session, dbo_EE_Payment_History.Payment_Ref, dbo_EE_Payment_History.Type, Val([Payment_Value]) AS [Value], "" AS Timesheet
FROM dbo_EE_Payment_History INNER JOIN dbo_Payslip_Static_Data ON (dbo_EE_Payment_History.Tax_Session = dbo_Payslip_Static_Data.Session_Number) AND (dbo_EE_Payment_History.Tax_Period = dbo_Payslip_Static_Data.Period_Number) AND (dbo_EE_Payment_History.Tax_Year = dbo_Payslip_Static_Data.Tax_Year) AND (dbo_EE_Payment_History.Personnel_Ref = dbo_Payslip_Static_Data.Personnel_Ref) AND (dbo_EE_Payment_History.Employer_Ref = dbo_Payslip_Static_Data.Employer_Ref)
WHERE (((dbo_EE_Payment_History.Tax_Year)=[Forms]![Misc]![year]) AND ((dbo_EE_Payment_History.Tax_Period)=[Forms]![Misc]![period]) AND ((dbo_EE_Payment_History.Tax_Session)=[Forms]![Misc]![session]) AND ((dbo_EE_Payment_History.Payment_Ref)=777));
UNION ALL SELECT dbo_EE_Payments.Employer_Ref, dbo_EE_Payments.Personnel_Ref, dbo_Payslip_Static_Data.Department, [Forms]![Misc]![year] AS Tax_Year, [Forms]![Misc]![period] AS Tax_Period, [Forms]![Misc]![session] AS Tax_Session, dbo_EE_Payments.Payment_Ref, dbo_EE_Payments.X_Type, Val([Calculated_Value]) AS [Value], "" AS Timesheet
FROM dbo_EE_Payments INNER JOIN dbo_Payslip_Static_Data ON (dbo_EE_Payments.Personnel_Ref = dbo_Payslip_Static_Data.Personnel_Ref) AND (dbo_EE_Payments.Employer_Ref = dbo_Payslip_Static_Data.Employer_Ref)
WHERE (((dbo_EE_Payments.Payment_Ref)=777));
View 10 Replies
View Related
May 19, 2005
OK, so I am UNIONing two tables using UNION ALL. It works fine. The resultant table has 192 records (63 + 129).
If a use just UNION or UNION DISTINCT I get 184 records. I'm pretty sure that is telling me that 8 records (192 - 184) exist in both tables.
How do I query to find out what those 8 records are?? I'm trying to use an INTERSECT in MS Access, but it doesn't want to work. Here is my original query:
select * from qryMOE_Active_All
UNION ALL select * from qryMOE_Closed_All;
Thanks,
Brian
View 3 Replies
View Related
Sep 26, 2005
I am a basic access user so please forgive my ignorance. I have created a union query of three tables. There is one field from the third table that I would like to have in the final table but this field does not exist in the first table. If I put "none" in the first SELECT line, then it queries correctly but the field name on the table is "Expr1006". If I put "Field 2" or [Field 2] in that same space of the first SELECT line, then it asks me for a parameter value and whatever I enter it fills in all the cells of that field with that value. I just don't know that language very well or even if you can add a new field into the first SELECT table. Thank you for any thoughts
Ex: SELECT [Field 1], [Field 2], "none", [Field 3]
FROm [Table 1]
UNION
SELECT [Field 1], "none", [Field 2], [Field 3]
FROM [Table 2]
View 3 Replies
View Related
Mar 16, 2006
Hello All,
I need help with an Union All Query. It is ignoring the second select statement. Can anyone see what I am doing wrong?
SELECT
CEPM_PLGR.COST_ELEM_CTGY AS [COST_ELEM_CTGY]
, CEPM_PLGR.PROJ_ELEM_ID AS [PROJ_ELEM_ID]
, CEPM_PLGR.COST_ELEM AS [COST_ELEM]
, CEPM_PLGR.SUM_UNIT AS [SUM_UNIT]
, CEPM_PLGR.PRD AS [PRD]
, CEPM_PLGR.CUR_BUD AS [CUR_BUD]
, CEPM_PLGR.PRD_BUD AS [PRD_BUD]
, CEPM_PLGR.CURRENT_ACT AS [CURRENT_ACT]
, CEPM_PLGR.PERIOD_ACT AS [PERIOD_ACT]
, CSIOWNER_PELM.PROJ_ELEM_DESC AS [PROJ_ELEM_DESC]
, CSIOWNER_PELM.PROJ_ELEM_MGR AS [PROJ_ELEM_MGR]
, CSIOWNER_PELM.PROJ_ELEM_TYPE AS [PROJ_ELEM_TYPE]
, NULL AS [COMMIT GA]
, CSIOWNER_PELM.COST_STAT AS [COST_STAT]
, CSIOWNER_PELM.PROJ_NBR AS [PROJ_NBR]
,NULL AS [COMMIT $]
FROM
CEPM_PLGR INNER JOIN CSIOWNER_PELM ON CEPM_PLGR.PROJ_ELEM_ID = CSIOWNER_PELM.PROJ_ELEM_ID
WHERE
((CSIOWNER_PELM.PROJ_TYPE)<>"T&M" And (CSIOWNER_PELM.PROJ_TYPE)<>"T7M") AND ((CSIOWNER_PELM.COST_MODE)="D")
AND ((CEPM_PLGR.PROJ_ELEM_ID)<>" ")
UNION ALL SELECT
NULL AS [COST_ELEM_CTGY]
, NULL AS [PROJ_ELEM_ID]
, NULL AS [COST_ELEM]
, NULL AS [SUM_UNIT]
, NULL AS [PRD]
, NULL AS [CUR_BUD]
, NULL AS [PRD_BUD]
, NULL AS [CURRENT_ACT]
, NULL AS [PERIOD_ACT]
, NULL AS [PROJ_ELEM_DESC]
, NULL AS [PROJ_ELEM_MGR]
, NULL AS [PROJ_ELEM_TYPE]
, (IIf( [PROJECT ID] Like "N0160*" And "N8100*" And "N9004*"
,(NZ([COMMIT $],0))
,(NZ([COMMIT GA $],0)))) AS [COMMIT GA]
, NULL AS [COST_STAT]
, NULL AS [PROJ_NBR]
, [PURCHASE_COMMITMENTS_FINAL].[COMMIT $] AS [COMMIT $]
FROM
[PURCHASE_COMMITMENTS_FINAL]
WHERE [PURCHASE_COMMITMENTS_FINAL].[PROJECT ID]
IN
(SELECT CEPM_PLGR.PROJ_ELEM_ID AS [PROJ_ELEM_ID]
FROM
CEPM_PLGR INNER JOIN CSIOWNER_PELM ON CEPM_PLGR.PROJ_ELEM_ID = CSIOWNER_PELM.PROJ_ELEM_ID
WHERE
((CSIOWNER_PELM.PROJ_TYPE)<>"T&M" And (CSIOWNER_PELM.PROJ_TYPE)<>"T7M") AND ((CSIOWNER_PELM.COST_MODE)="D")
AND ((CEPM_PLGR.PROJ_ELEM_ID)<>" ")) AND (([PURCHASE_COMMITMENTS_FINAL].[PROJECT ID]) <> " ");
I think it is my where clause IN function. But I do not know how to fix it. any help would be great!!!!
Thanks in advanced, Kerrie
View 2 Replies
View Related
Jan 25, 2007
I have 2 tables with employee details in it. There is no natural link between the 2. I wish to write a query that will sum up the total pay for both tables.
I've had a look through the forum and it seems that a Union query is probably best.
I haven't worked with Unions before, so I did 2 sub queries to total the pay in both table and then union the 2 queries. I did this because there are some date parameters that I query each table by so I do this in the sub queries.
This works but I get 2 outputs in the result, the total pay from each table. I wanted to have just one output, which is the sum of all pay.
Can anyone help please.
SQL:
SELECT QrySub_TotalPay1.TotalPay 'sub query that sums up table 1
FROM QrySub_TotalPay1
UNION SELECT QrySub_TotalPay2.TotalPay 'sub query that sums up table 2
FROM QrySub_TotalPay2;
TIA.
View 3 Replies
View Related
Apr 24, 2007
I have 2 tables and I m trying to get sum of qty for each product
I tried union qurey following way but doesn't work.
Product_Master table has primary key set up on productcode. I need to pick up OpeningBal along with ProductName and ProductCode from this table.
T_PurInvFoot table contains multiple records of the similar ProductCode. I want to make sum of these PurQty data and add it with OpeningBal data from Product_master table and present it in one line.
The avalable quanities are in 2 diff. tables are here.
Product_Master.OpeningBal=5
(1 Record)
T_PurInvFoot.PurQty =4
T_PurInvFoot.PurQty =6
(2 Records)
So total it should be 15 when it produce records. But it show only 10 records from below query.
SELECT Product_Master.ProductCode, Product_Master.ProductName,Sum(Product_Master.Open ingBal) as Stock
From Product_Master INNER JOIN T_PurInvFoot ON T_PurInvFoot.ProductCode=Product_Master.ProductCod e
Group By Product_Master.ProductCode,Product_Master.ProductN ame
UNION Select T_PurInvFoot.ProductCode, T_PurInvFoot.ProductName, sum(T_PurInvFoot.PurQty) as Stock
From T_PurInvFoot INNER JOIN Product_Master ON T_PurInvFoot.ProductCode=Product_Master.ProductCod e
Group By T_PurInvFoot.ProductCode,T_PurInvFoot.ProductName
ORDER BY Product_Master.ProductCode;
Can somebody advice me how to do it.
I need to add 3rd table here in future to get sum of the qty of the similar ProductCode so how to the query would be?
One more thing, can we do it in VBA and set the RecordSource to a form or report ?
With kind regards,
Ashfaque
View 1 Replies
View Related
Oct 11, 2007
Hello All,
What I am trying to do is if the field (CAR) or (PAR) or (Incident) is checked in this table (Inventory Worksheet) then show it. I not sure how to use the union query to accomplish this.
Any Ideas?
Thanks,
View 5 Replies
View Related
Oct 12, 2007
I have two tables with data and I want to join the data together for a report.
Example:
SELECT tbl1.a tbl1.b tbl1.c tbl1.d
FROM tbl1
UNION SELECT tbl2.a tbl2.b tbl2.c
FROM tbl2;
Currently I can't join them b/c the column counts aren't the same.
If table1 has more columns than table2 can I join them by indicating some kind of phantom column 'd' for table2 to be joined to column 'd' of table1?
View 3 Replies
View Related
Dec 13, 2007
Will you help me built a union query ? I have 2 queries, qryinput and qryoutput.Out of these query i have a third query called qryDiff substracting these
queries. However qryDiff does not show all the goods sold, only those goods that are substracted.Therefore i want to build an union query but somehow i cannot do it. Will you help ?
My first query, qryinput is :
SELECT [order details].ProductID, Sum([order details].Quantity) AS Sum1, orders.orderid
FROM (orders INNER JOIN [order details] ON orders.orderid = [order details].OrderID) INNER JOIN products ON [order details].ProductID = products.Productid
GROUP BY [order details].ProductID, orders.orderid;
My second query, qryoutput is :
SELECT [order details].ProductID, orders.orderid, [order details].Quantity AS Sum2
FROM ((orders INNER JOIN [order details] ON orders.orderid = [order details].OrderID) INNER JOIN Customers ON orders.customerid = Customers.Customerid) INNER JOIN products ON [order details].ProductID = products.Productid;
My thirs query, qryDiff is :
SELECT qryProducts.ProductID, Sum(qryInput.Sum1) AS imported, Sum(qryOutput.Sum2) AS exported
FROM (qryProducts LEFT JOIN qryInput ON qryProducts.ProductID = qryInput.ProductID) LEFT JOIN qryOutput ON qryProducts.ProductID = qryOutput.ProductID
GROUP BY qryProducts.ProductID;
Now i want to convert the query qryDiff into an union query, but i cannot do it.I somehow managed to build a simple query showing all the products:
SELECT ProductID
FROM qryInput
UNION SELECT ProductID
FROM qryOutput;
This query shows the productsid indeed, but i cannot add the other items form the qryinput and qryoutput as the sum etc.
I also i cannot convert my query qryDiff into an uinion query, with fields for the imported and the exported sums and also for the product names.
Where is my error and now could i achieve my aim? My aim is that in the query qryDiff to show all the products, and not only the products sold.
I will be very grateful for any comments
View 6 Replies
View Related
Dec 30, 2007
I have 2 tables, Employees and Customers. Each table has a column called active.
How can I make a union query that will return all active employees and customers?
View 1 Replies
View Related
Mar 23, 2008
Hello friends
How are you
Please I need help in Union Queries
what does this code mean
"SELECT [Query_buy] ,Type.[Query_buy].Sum0 ,no AS InQuin , "Return" as Description From [Query_buy]"
I need to make combined report shows the reteurnd items.
I hope that my words are enough clear
Thank you all
View 1 Replies
View Related
Sep 23, 2004
I have two tables - one of staff and one of activities. I want to assign each new activity to a member of staff based on a unique ref and then want the other related fields to be filled in automatically. i.e. I select the staff ID from a lookup using a combo box and a forename and surname field in the activity table is then immediately populated with the right details from the staff table. I think the animal I need for this is a union query but apparently I need to code some SQL to do this and let's just say my SQL leaves a bit to be desired!
View 2 Replies
View Related
Dec 13, 2007
Will you help me built a union query ? I have 2 queries, qryinput and qryoutput.Out of these query i have a third query called qryDiff substracting these
queries. However qryDiff does not show all the goods sold, only those goods that are substracted.Therefore i want to build an union query but somehow i cannot do it. Will you help ?
My first query, qryinput is :
SELECT [order details].ProductID, Sum([order details].Quantity) AS Sum1, orders.orderid
FROM (orders INNER JOIN [order details] ON orders.orderid = [order details].OrderID) INNER JOIN products ON [order details].ProductID = products.Productid
GROUP BY [order details].ProductID, orders.orderid;
My second query, qryoutput is :
SELECT [order details].ProductID, orders.orderid, [order details].Quantity AS Sum2
FROM ((orders INNER JOIN [order details] ON orders.orderid = [order details].OrderID) INNER JOIN Customers ON orders.customerid = Customers.Customerid ) INNER JOIN products ON [order details].ProductID = products.Productid;
My thirs query, qryDiff is :
SELECT qryProducts.ProductID, Sum(qryInput.Sum1) AS imported, Sum(qryOutput.Sum2) AS exported
FROM (qryProducts LEFT JOIN qryInput ON qryProducts.ProductID = qryInput.ProductID) LEFT JOIN qryOutput ON qryProducts.ProductID = qryOutput.ProductID
GROUP BY qryProducts.ProductID;
Now i want to convert the query qryDiff into an union query, but i cannot do it.I somehow managed to build a simple query showing all the products:
SELECT ProductID
FROM qryInput
UNION SELECT ProductID
FROM qryOutput;
This query shows the productsid indeed, but i cannot add the other items form the qryinput and qryoutput as the sum etc.
I also i cannot convert my query qryDiff into an uinion query, with fields for the imported and the exported sums and also for the product names.
Where is my error and now could i achieve my aim? My aim is that in the query qryDiff to show all the products, and not only the products sold.
I will be very grateful for any comments
View 3 Replies
View Related
Aug 22, 2005
I need help here.
i have a multiselect listbox on my form and i want to hard-code its rowsources depending on certain selection criteria.Iam finding a problem. Actually i dont get any results when i use the query below. Any idea what iam missing out.
Me.OrgTypesLB.RowSource = "SELECT DISTINCT ORG_TYPE FROM tblOrganTypes UNION SELECT" & "All" & " from tblOrganTypes"
Kea
View 2 Replies
View Related
Sep 27, 2005
Hi,
In my database im using several union queries as they turn out to be very useful.
However, whenever I have a calculation that involves one of the union queries, things happen at a very slow pace. Is there a way to speed it up a bit?
Thank you
Stacey
View 6 Replies
View Related
Oct 26, 2006
Hi All,
Could someone please look at the attached database and help me understand why my Union Query is not matching up correctly with the Program ID?
See attached
Thank you for all your help
View 1 Replies
View Related
Nov 20, 2006
I have a union query that will run from the SQL design window but if I try and run it from the database window it crashes Access (2003). Any ideas?
SELECT [TestLat]-0.5 AS Lat, tblTestData.TestLong AS [Long], tblTestData.TestTemp
FROM tblTestData
UNION SELECT [TestLat]+0.5 AS Lat, tblTestData.TestLong AS [Long], tblTestData.TestTemp
FROM tblTestData
UNION SELECT tblTestData.TestLat AS Lat, [TestLong]-0.5 AS [Long], tblTestData.TestTemp
FROM tblTestData
UNION SELECT tblTestData.TestLat AS Lat, [TestLong]+0.5 AS [Long], tblTestData.TestTemp
FROM tblTestData;
View 4 Replies
View Related
Jan 12, 2007
Hi all,
i have this query which utlises a vb function allowing you to filter a table or query by the value of a form drop down- works great!
what i now need to do is do the same on a query which combines the same data with some user added data (all fields are the same) from another table- so two tables with identicle fields being combined by a union query- then query that using the criteria from my vb function
well in theory that should work but it doesn't- doesn't matter what i try i always get no results-
any ideas why this fails? what is it about union queries that stops you from doing simple things like this even though the fields are the same?
dubs
View 1 Replies
View Related
Jun 22, 2007
I created a union query, that shows all the Highs and Mediums. My question is if a table does not have a field that is in all the other table how do you create field and set it to High. This Union query runs, but ask me to set the value for [Criticality of Risk] on the Open AF Issues Query. So I have to type “High” I would like this to be automated.
What I did:
SELECT A.[ Open Issue Tbl.ID] AS [IDCount],
A.[Criticality of Risk] AS [Criticality of Risk],
A.[Database] As [DataBase]
FROM [Open S Issues] A
UNION ALL SELECT B.[ID] AS [IDCount],
B.[Criticality of Risk] AS [Criticality of Risk],
B.[Database] As [DataBase]
FROM [Open Tangents] B
UNION ALL SELECT C.[AF Number] AS IDCount,
[Criticality of Risk],
C.[Database] As [DataBase]
FROM [Open AF Issues] C;
Thanks…
View 2 Replies
View Related
Jul 2, 2007
To try to get over some of my previous problems, I have tried using a Union Query - I understand this might help me get the results I need. Below is my SQL, which is meant to combine the results of the 10 individual queries. Have I misunderstood something, or not done something right? I am getting error messages.
The data is drawn from 2 tables - PROJECTS and PERSONNEL.
SELECT Personnel.[C/Q Number], Personnel.[1st Fix Men Plan], Personnel.[1st Fix Men ACT], Projects.Date
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
WHERE (((Projects.Fix)="1"))
GROUP BY Personnel.[C/Q Number], Personnel.[1st Fix Men Plan], Personnel.[1st Fix Men ACT], Projects.Date
HAVING (((Personnel.[1st Fix Men Plan])=True) AND ((Personnel.[1st Fix Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]))
UNION
SELECT Personnel.[C/Q Number], Personnel.[2nd Fix Men Plan], Personnel.[2nd Fix Men ACT], Projects.Date
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
WHERE (((Projects.Fix)="2"))
GROUP BY Personnel.[C/Q Number], Personnel.[2nd Fix Men Plan], Personnel.[2nd Fix Men ACT], Projects.Date
HAVING (((Personnel.[2nd Fix Men Plan])=True) AND ((Personnel.[2nd Fix Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]))
UNION
SELECT Personnel.[C/Q Number], Personnel.[3rd Fix Men Plan], Personnel.[3rd Fix Men ACT], Projects.Date
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
WHERE (((Projects.Fix)="3"))
GROUP BY Personnel.[C/Q Number], Personnel.[3rd Fix Men Plan], Personnel.[3rd Fix Men ACT], Projects.Date
HAVING (((Personnel.[3rd Fix Men Plan])=True) AND ((Personnel.[3rd Fix Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]))
UNION
SELECT Personnel.[C/Q Number], Personnel.[4th Fix Men Plan], Personnel.[4th Fix Men ACT], Projects.Date, Projects.Fix
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
GROUP BY Personnel.[C/Q Number], Personnel.[4th Fix Men Plan], Personnel.[4th Fix Men ACT], Projects.Date, Projects.Fix
HAVING (((Personnel.[4th Fix Men Plan])=True) AND ((Personnel.[4th Fix Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]) AND ((Projects.Fix)="4"))
UNION
SELECT Personnel.[C/Q Number], Personnel.[5th Fix Men Plan], Personnel.[5th Fix Men ACT], Projects.Date, Projects.Fix
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
GROUP BY Personnel.[C/Q Number], Personnel.[5th Fix Men Plan], Personnel.[5th Fix Men ACT], Projects.Date, Projects.Fix
HAVING (((Personnel.[5th Fix Men Plan])=True) AND ((Personnel.[5th Fix Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]) AND ((Projects.Fix)="5"))
UNION
SELECT Personnel.[C/Q Number], Personnel.[Snag 1 Men ACT], Projects.Date, Projects.Fix
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
GROUP BY Personnel.[C/Q Number], Personnel.[Snag 1 Men ACT], Projects.Date, Projects.Fix
HAVING (((Personnel.[Snag 1 Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]) AND ((Projects.Fix)="S#"))
UNION
SELECT Personnel.[C/Q Number], Personnel.[Snag 2 Men ACT], Projects.Date, Projects.Fix
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
GROUP BY Personnel.[C/Q Number], Personnel.[Snag 2 Men ACT], Projects.Date, Projects.Fix
HAVING (((Personnel.[Snag 2 Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]) AND ((Projects.Fix)="S#"))
UNION
SELECT Personnel.[C/Q Number], Personnel.[Snag 3 Men ACT], Projects.Date, Projects.Fix
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
GROUP BY Personnel.[C/Q Number], Personnel.[Snag 3 Men ACT], Projects.Date, Projects.Fix
HAVING (((Personnel.[Snag 3 Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]) AND ((Projects.Fix)="S#"))
UNION
SELECT Personnel.[C/Q Number], Personnel.[Snag 4 Men ACT], Projects.Date, Projects.Fix
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
GROUP BY Personnel.[C/Q Number], Personnel.[Snag 4 Men ACT], Projects.Date, Projects.Fix
HAVING (((Personnel.[Snag 4 Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]) AND ((Projects.Fix)="S#"))
UNION SELECT Personnel.[C/Q Number], Personnel.[Snag 5 Men ACT], Projects.Date, Projects.Fix
FROM Personnel INNER JOIN Projects ON Personnel.[C/Q Number] = Projects.[C/Q Number]
GROUP BY Personnel.[C/Q Number], Personnel.[Snag 5 Men ACT], Projects.Date, Projects.Fix
HAVING (((Personnel.[Snag 5 Men ACT])=True) AND ((Projects.Date) Between [forms]![frmDateEntry]![Start Date] And [forms]![frmDateEntry]![End Date]) AND ((Projects.Fix)="S#"))
ORDER BY Projects.Date
View 4 Replies
View Related
Oct 2, 2007
Hello again...
Is it possible to use a UNION query for comparison between to tables or queries?
If so, please advise how to
example:
one query consists of a selection of books, grouped by category for a student
the second query consists of all books, grouped by category for a student
The purpose of the new union query is to check if the student has all the applicable books in his booklist; if the student has forgotten to pick a book for his list, this new query will show the one(s) the student forgot.
Kind of cryptical this example, hope i could make it clear enough...
View 4 Replies
View Related
May 12, 2005
Hello,
I have set up a form which will be used for filtering reports. I would like to be able to generate a single report and in order to do that I will have to filter three fields.
I am using three combo boxes to achieve this. The three fields I am filtering are as follows: Tenant Name, Building Name, and Unit
The Tenant Name and Building Name work perfectly but I can't get the rowsource for Unit to display any values.
Here is what I have so far:
For the tenant name:
rowsource:
SELECT Tenants.[Tenant Name] FROM Tenants ORDER BY [Tenant Name];
The above displays all the tenants that currently have leases.
Once the Tenant Name is selected the Building combobox is populated with all the buildings leased to the selected tenant (Tenants can lease more than one building). This is achieved by the following:
rowsource:
SELECT distinct Leases.LeaseID, Leases.[buildingName] FROM Leases WHERE (((Leases.Tenant)=[forms]![Lease Offer]![tenantCombo])) UNION select distinct null, null FROM Leases ORDER BY Leases.[buildingName];
This also works perfectly. Only the buildings that are occupied by the tenant are displayed.
Now for my problem. Because, not only can the tenant be holding a lease in more than one building, but they can also have more then one lease in the specific building (ie for a specific Unit number). So what I am trying to do is to have the Unit combobox display only the unit numbers that the selected tenant is leasing in the selected building.
I tried to simply modify the rowsource of building name but it is not working (the combo box is always null).
rowsource:
SELECT distinct Leases.LeaseID, Leases.[Unit] FROM Leases WHERE (((Leases.Tenant)=[forms]![Lease Offer]![tenantCombo]) AND ((Leases.buildingName)=[forms]![Lease Offer]![buildingCombo])) UNION select distinct null, null FROM Leases ORDER BY Leases.[Unit];
So this is where I need help. This is very important to my project because, it is the only way I see being able to filter my report to a single specific lease.
Also, not sure if it will be useful, but I had trouble to get just the first two comboboxes working but I eventually succeeded, so if anyone is interested on how to achieve these to update here is the code:
It is only basically three functions:
'this is the "On Current" even of the form
Private Sub Form_Current()
buildingCombo.Requery
unitCombo.Requery
End Sub
'this is "On Change" event in the the tenant combo box. the building part 'works but the unit combo doesn't. However, I'm pretty sure it is a problem 'with the actual query not the code below
Private Sub tenantCombo_Change()
buildingCombo.Value = Null
unitCombo.Value = Null
buildingCombo.Requery
unitCombo.Requery
End Sub
'this is "On Change" event in the building Combobox, however it is not doing 'anything for me now.
Private Sub buildingCombo_Change()
unitCombo.Value = Null
unitCombo.Requery
End Sub
Thanks in advance for any input you might have.
Eric
View 1 Replies
View Related
Jul 21, 2005
This is my first attempt at using a Union Query - Any help would be greatly appreciated!
I have 5 fields I'm combining from two tables:
SELECT [AcctNumber],[ReqNumber],[ReqDate],[Description1],[SupplierName]
FROM [tblRequisitions]
UNION SELECT [AcctNumber],[ReqNumber],[ReqDate],[Description1],[SupplierName]
FROM [tblJournalTransfers];
I have two issues to begin with:
1) In my tblJournalTransfers field ReqNumber (stored as text) I input mask: "JT "000;;_ so that I can differentiate a Journal Transfer number from a Requisition number. tblRequisitions is straight 3 digit number but stored as text also. The above Union Query is returning nice results, however it is ignoring the "JT" prefix in the ReqNumber field on the records pulled from tblJournalTransfers???
2) Can this Union Select query be used within another query? I need to add more fields from both tables to get the results needed for a report. When I tried to base another query on both the Union Query and the two tables (tblJournalTransfers and tblRequisitions) the resulting records were multiples (numerous sets of each record).
Thank you for your assistance!!!
Diana
View 2 Replies
View Related
