Union Select Query (What Am I Doing Wrong)
May 28, 2006
Please see attached database
If you open the form called people you will see a command button
Called add charge once pressed will open a form called charge
On the Bill To combo Box I have the following Union Select Query
SELECT PrimaryInsurance, PersonID FROM tblpeople UNION Select SecondaryInsurance, PersonID FROM tblPeople
What I want the bill to combo box to display is only the Insurances that are related to the
Patient that the charge is for and not to display other patients insurances
Any Ideas
View Replies
ADVERTISEMENT
Jun 28, 2006
I have Access2003 and a problem creating a simple method of constructing a union query.
I am using a commercial paradox databse that has a habit of creating a new table every time a certain action is performed. There is currently a set of 160 tables with identical fields. The database allows me to view data in these tables individually. I would like to pull the information together, from between 20 and 30 specific tables at any one time, and view the data in one table.
I have linked the tables in a an access database and would go about viewing the data by creating a union query containing each of the tables of interest. Then appending the info in these tables to a new table.
Unfortunately manually adjusting the SQL expression is a bit cumbersome to say the least I am wondering if it possible to select the tables of interest from a List Box on a simple form and have those selected inserted into the union query ?
Alternatively, the table names are actually a field within a different table. Another field within this table actually determines whether they will be of interest. So ideally I could query that table to tell me which tables need to appear in the union query. Again I would like the union query generated automatically as a result of that query.
I am confident, perhaps without good reason , that this must be possible ....
any help much appreciated.
View 14 Replies
View Related
Oct 9, 2013
I am trying to get a report that shows both column totals and row totals.
I have a crosstab query that gives me the Row totals along with the crosstab data.
I have read on a previous post [URL] ....
That I can union the crosstab to a select query which should get me what I want, but to be honest I am lost.
View 3 Replies
View Related
Jun 23, 2006
(This is a modified repost - which hopefully makes sense) I am using Access2003. I am trying to set up a fast method of creating a union query. I have a jobs table that stores info about jobs with a separate table for each job that pulls together info from elsewhere when a review is conducted. The tables are as follows (and are linked from a paradox DB) :-
Table Name: jobs
JobID (J000001, J000002, etc)
Status (Live, Filled, etc)
Review Tables
Table Name: J000001 / J000002 etc
Consultant: (Joe, Terry etc)
ObjectID: (RoberI, SmithJ etc)
Status: (H, P, D, R etc)
The jobs table contains information about jobs, including a unique code (JobID) that identifies the job. There is also a status filed that tells us whether the job is Live or closed etc.
The first time a job is reviewed a new table, a review table is generated, and the name of the table is the same as the JobID for that job. So Job J000001 has a review table with table name J000001 etc. The review tables may contain information with the same ObjectID (as they are unique fields from a third table – the candidates table)
I would like to generate a union query for all jobs in table jobs with a status of live. I can do this manually, if I review a list of live jobs, with the following sql expression;
SELECT ObjectID, Consultant, Status, "J000001"
as [JobNo] FROM J000001
UNION SELECT ObjectID, Consultant, Status, "J000002"
as [JobNo] FROM J000002
UNION SELECT ObjectID, Consultant, Status, "J000003"
as [JobNo] FROM J000003;
I can then append the info into a new table. However this query is run at least twice a day and things change.
I would like to know is there a means of automatically generating sql for the union query based on results of a query of the jobs table ?
Any help greatly appreciated.
View 2 Replies
View Related
Sep 14, 2007
I have the following query:
SELECT UnionTable.groupby, UnionTable.SeqID, UnionTable.Actual
FROM (
SELECT VAP1.groupby, VAP1.SeqID, VAP1.SomVanbedrag as Actual
FROM qryVoorplaatActualPillar_Forecast AS VAP1
UNION
SELECT VAP2.groupby, VAP2.SeqID, VAP2.SomVanbedrag as Actual
FROM qryVoorplaatActualPillarIST_Forecast AS VAP2) AS UnionTablewhich is two select queries called UnionTable and a wrapper.
Access handles this very well. Until you look at the SQL statement. If you don't pay attention Access stores the next query
SELECT UnionTable.groupby, UnionTable.SeqID, UnionTable.Actual
FROM [
SELECT VAP1.groupby, VAP1.SeqID, VAP1.SomVanbedrag as Actual
FROM qryVoorplaatActualPillar_Forecast AS VAP1
UNION
SELECT VAP2.groupby, VAP2.SeqID, VAP2.SomVanbedrag as Actual
FROM qryVoorplaatActualPillarIST_Forecast AS VAP2]. AS UnionTable
Now when you want to execute the query you get an errormessage.
Has anyone seen this before? Is this a known bug or is it a feature? Is there a workaround?
View 1 Replies
View Related
Sep 15, 2005
I have a query that requires a selection from my list box "[Forms]![ViewReports]![ListFilterName]"
I have a list box with a UNION so that I can query all contacts. The bound column is one.
SELECT 0,"<<ALL","TEAM MEMBERS>>" from Contacts UNION (SELECT Contacts.ID, Contacts.[First Name], Contacts.[Last Name] FROM Contacts ORDER BY [Last Name]);
Problems
The list is not ordering by last name.
The query does not assume all contacts when "<<ALL","TEAM MEMBERS>>" is selected.
Where did I go wrong? What's missing?
View 2 Replies
View Related
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
Jan 23, 2007
I am having difficulty getting a query to work, there's probably something really simple I'm missing. . . I can get the Union function to work but I can't get it to create a table for me. The basic query looks like this:
INSERT INTO test
SELECT * FROM [Design] WHERE (Design.CID Not Like "*-*")
UNION
SELECT * FROM [Release] WHERE (Release.CID Not Like "*-*")
It works fine and shows me the table I want without the first line, but I would like it to input into a new or existing table. Any help would be greatly appreciated!
View 1 Replies
View Related
Mar 29, 2005
I have a UNION of two SELECT queries. It works very well but I need the end-user to be able to modify the data so I am using INSERT INTO tablename. This will work for one query at a time but if I try to use the UNION it reports “Syntax error in FROM clause”. Can you use UNION in this case or can anyone see my error?
The full statement is rather complex, I have added a few spaces and linebreaks to make it readable as follows:
INSERT INTO TableCompleteForMailing
SELECT [Address List].[FamilySurname] AS Surname,
[Address List].[DEARFirstnames] AS FirstName,
[Address List].[Mailing] AS Mailing,
[Address List].[Christmas Mailing] AS OtherMailing, [Address List].[Address 1] AS Address1,
[Address List].[Postcode] AS Postcode
FROM [Address List]
UNION
SELECT [Names].[LastName] AS Surname,
[Names].[FirstName] AS FirstName,
[Names].[MailingList] AS Mailing,
[Names].[Selected] AS OtherMailing,
[Address List].[Address 1] AS Address1,
[Address List].[Postcode] AS Postcode
FROM [Names],[Address List]
WHERE [Names].[AddressListID]=[Address List].[AddressListID]
ORDER BY Surname, FirstName;
View 3 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
Jan 15, 2007
SELECT s.weekth, s.contracttype, sum(case when s.[contract status]='advance complete' then price end)/sum([purchaing price])
from submissionandpd as s
THX
View 5 Replies
View Related
Jan 22, 2007
t2 is a subset of t1, I want t1-t2.
but following code does not seem to work.
SELECT *
FROM t1
except
select *
from t2
How to fix this problem? Thanks.
View 1 Replies
View Related
Mar 14, 2007
SELECT Orders.DelAdd1 AS Street, Orders.DelAdd2 AS [Street 2], Orders.DelTown AS Town, Orders.DelCounty AS County, Orders.DelPostcode AS Postcode, Sandwich.SandwichName AS Sandwich, sum(Orderline.Quantity) AS Quantity
FROM Orders, Orderline, Sandwich
WHERE Orderline.Sandwichno=Orders.SandwichNo and Orders.DeliveryDate=[Please enter Today's Date]
GROUP BY Orders.DelPostcode;
It says I've not specified street as an aggregate function, but I don't want to!
View 1 Replies
View Related
May 15, 2007
Hi Guys,
what could be the probelm with my query? Here:
******************
SELECT tblPartnerProfile.PartnerType AS [Org Type], tblPartnerProfile.PartnerName, tblPartnerProfile.PartnerLoc1, tblPartnerProfile.PartnerStatus AS Status, Count(PartnerType) AS TotalType,
FROM tblPartnerProfile
WHERE PartnerLoc1 = 1 OR PartnerLoc1 = 2
******************************
I would like to have it give a count of how many [Org Type] 1 and 2 for Loc1 1 and Loc2 2.
Thanks in advance,
Samia
View 4 Replies
View Related
Jul 24, 2007
Hello,
I have a Query that I wrote as follows:
SELECT [TableA].[FieldA], [TableA].[FieldB], TableB.FieldC
FROM [TableA],TableB
RIGHT JOIN [TableA] ON [TableA].[FieldB] = TableB.[FieldD]
GROUP BY [TableA].[FieldA], [TableA].[FieldB], TableB.FieldC;
What happened was that TableA has Fields A and B, and Table B has a field C and D. Tables B and D have the same content (Not necessary unique) and I would like to see a mapping from Field A -> Field C by joining on Field B and D.
However, when I ran it, I got an "invalid arguement" error.
Could anyone help? Thanks a lot!
Regards,
Anyi
View 1 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
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 1 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
