I have 2 tables, (will just call them tbl1 and tbl2 for this example) each with a column AppNumber. The AppNumber may appear in both tables or individually in either one.
I want to be able to show, or flag, on a continuous form which Appnumber's appear on tbl1, tbl2 and on both. This is so the user can click a button and bring up another form with more data relevant to the AppNumber.
I have written a union query to show all results from both tbl1 and tbl2 which contains 4 fields AppNumber, DateApp, Surname, Forename but I can't tell from the query results what AppNumber's appear on each table. Here's what I would like to show on the continuous form:
AppNumber DateApp Surname Forename tbl1 tbl2 Both
12345 11/12/2012 Simpson Homer Yes No No
56565 01/04/2013 Stallone Sly No Yes No
89788 03/03/2012 Rambo John Yes Yes Yes
89745
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
I have created inventory databases for both my job and personal use. I do not understand coding. I either need a good hand-holding with simple, detailed instructions or I need to hire a code person.
A button in my form that can be coded to ... When clicked ...
___ open a browse window of hard drives, ___ allow user to choose any single image for selection, ___ copy & paste selected image's location to a table field.
NOTE: Table field will be used to connect to image place holder on form with control source to make the image seen.
I am probably not alone in having a database which has grown over time in a slightly undisciplined way, in that standards have not been consistently applied. It's quite a large database by my standards : 20+ tables, 50 queries, 40 forms/subforms, a handful of reports and lots of VBA. It's working fine so there's no way I can justify restarting from scratch, but it can be hard to maintain, particularly when working out the type and scope of fields, variables and controls.
How to introduce a consistent naming convention retrospectively ? This seems easy enough within modules, it's identifying where things are used within forms that has me worried. I would want to use the Reddick conventions.
Based on information from a earlier thread.... I created a Union query that pulls information from multiple tables and fields.
SELECT AG_B_R1 as Num FROM dbo_ADC_Ag_B_Res WHERE Rollnmbr=[roll] UNION ALL SELECT AG_B_R2 FROM dbo_ADC_Ag_B_Res WHERE Rollnmbr=[roll] UNION ALL SELECT AG_B_R3 FROM dbo_ADC_Ag_B_Res WHERE Rollnmbr=[roll] UNION ALL SELECT AG_B_R4 FROM dbo_ADC_Ag_B_Res WHERE Rollnmbr=[roll] UNION ALL SELECT AG_m_R1 FROM dbo_ADC_Ag_m_Res WHERE Rollnmbr=[roll] UNION ALL SELECT AG_m_R2 FROM dbo_ADC_Ag_m_Res WHERE Rollnmbr=[roll] UNION ALL SELECT AG_m_R3 FROM dbo_ADC_Ag_m_Res WHERE Rollnmbr=[roll] UNION ALL SELECT AG_m_R4 FROM dbo_ADC_Ag_m_Res WHERE Rollnmbr=[roll] UNION ALL SELECT AG_e_R1 FROM dbo_ADC_Ag_e_Res WHERE Rollnmbr=[roll] UNION ALL SELECT AG_e_R2 FROM dbo_ADC_Ag_e_Res WHERE Rollnmbr=[roll] UNION ALL SELECT AG_e_R3 FROM dbo_ADC_Ag_e_Res WHERE Rollnmbr=[roll] UNION ALL SELECT AG_e_R4 FROM dbo_ADC_Ag_e_Res WHERE Rollnmbr=[roll];
And then I created another query to get the STDEV of the above query
SELECT StDev([Num]) AS StDev FROM Q_cals_ag_bme_STDEV_Union;
The result will be on a subform on my main page. How do I get my form to input the [roll] automatically and requery the subform, showing my result. Thanks
I'm currently working on a database which provides copier and postage activity in my department, using Access. I'm trying to debug a query which is called by a form that accepts input for a particular month and year. Whenever the OK button is clicked, it gives a message "The Microsoft Jet database engine does not recognize 'Combination Query' as a valid field name or expression."
"Combination Query" is a union query which calls two intermediate queries, each of which in turn calls a base query. Only one, the query responsible for gathering copier activity, is a crosstab query.
The problem I believe lies in either or both of the base queries only when the form is used. When I use a regular parameter query, I receive no error messages.
Here is the SQL code I used for each:
Query 1: Copier Activity Code:PARAMETERS [Forms]![frmObtainMonthlyInfo]![cmbMonth] Long, [Forms]![frmObtainMonthlyInfo]![cmbYear] Long;TRANSFORM Sum([big table].Copies) AS SumOfCopiesSELECT [big table].[Copy Code], [User codes].Alias, Sum([big table].Copies) AS [Total Of Copies]FROM [User codes] INNER JOIN [big table] ON [User codes].[Copy Code] = [big table].[Copy Code]WHERE (((DatePart("m",[big table].[Time]))=[Forms]![frmObtainMonthlyInfo]![cmbMonth]) AND ((DatePart("yyyy",[big table].[Time]))=[Forms]![frmObtainMonthlyInfo]![cmbYear]))GROUP BY [big table].[Copy Code], [User codes].AliasPIVOT [big table].[Copier Location];
Query 2: Postage Activity Code:PARAMETERS [Forms]![frmObtainMonthlyInfo]![cmbMonth] Long, [Forms]![frmObtainMonthlyInfo]![cmbYear] Long;SELECT [big Postage Activity].sAcctNum AS Account, Sum(Nz(cBaseRate*lNumPieces,0)+Nz((cRegisteredFee+ cCertifiedFee+cReturnRecFee+cReturnRecMerchFee+cSp ecDeliveryFee+cSpecHandlingFee+cRestrictedDelvFee+ cCallTagFee+cPODFee+cHazMatFee+cSatDeliveryFee+cAO DFee+cCourierPickupFee+cOversizeFee+cShipNotificat ionFee+cDelvConfirmFee+cSignatureConfirmFee+cPALFe e+cResidualShapeSurcharge)*lNumPieces,0)) AS TotalFROM [big Postage Activity]WHERE (((DatePart("m",[big Postage Activity].[sSysDate]))=[Forms]![frmObtainMonthlyInfo]![cmbMonth]) AND ((DatePart("yyyy",[big Postage Activity].[sSysDate]))=[Forms]![frmObtainMonthlyInfo]![cmbYear]))GROUP BY [big Postage Activity].sAcctNum;
I edited this to make it look similar to a recommendation posted on another forum, but sometimes I would get the message "The Microsoft Jet database engine does not recognize '[Forms]![frmObtainMonthlyInfo]![cmbMonth]' as a valid field name or expression."
I also included PARAMETERS [Forms]![frmObtainMonthlyInfo]![cmbMonth] Long, [Forms]![frmObtainMonthlyInfo]![cmbYear] Long; as the first line in the intermediate tables, but I still receive the same error message.
I'm wondering if there was something I overlooked or if there was a way around this. I can post the other queries if you need those. Many thanks in advance for your help.
I am creating a inventory database for work. I am trying to get the cursor to jump to the next text box for data entry using a barcode scanner. I have a limit on how many characters there can be in this field but yet even after it is all filled the cursor stays in that same field. I am fairly new to access, especially in code building. Do I use "After Update" under "Event" in the "Property Sheet"?
I need to create a census report from the employees we serve and their dependents, by company. I have a union query of the Employees table and Dependents table to put them all on one list, then a form where you can type the name of the company you need, with a button that opens the report. This works great, except for the fact that sometimes we don't need the dependents; if a company has over 100 Full-Time employees, we only need the employees. I've added a check box ([IncDep]) on the form, asking the user if they want to include the dependents, but I don't know how to get this to filter the report. I have a Relationship field on the tables that specifies an employee as a "Subscriber", so I'd like to filter the report where [Relationship] = "Subscriber" but I don't know how to do this. The union query specifies both Employees.Relationship and Dependents.Relationship to the text box [Relationship], but when I do something like
Dim stDocName As String If Me!IncDep.Value = False Then stDocName = "Census" DoCmd.OpenReport stDocName, acViewReport, , "[Relationship]='" & "Subscriber" & "'" Else stDocName = "Census" DoCmd.OpenReport stDocName, acViewReport End If
I have a quick question. I have a main form with a label and a button on it. When the button is pressed a secondary form pops up. The second form has a button on it also. I am trying to code the button on the second from so that when it is pressed the label changes its value on the first form. In the past it was quite easy as there was no second form so in the Onclick VB coding of the button on the first form had something like:
With Me.mainHeading .caption = "Recontacts" End With
But, with the secondary form, the "Me." does not work. Is there a way i can replace the "Me." with some sort of link back to the first form?
Need Help: Table has several fields, two that pertain to this question are:
[Time] and [AM/PM] User inputs both, values such as 11:00 am 11:30 am 12:10 pm I have to change the pm to am if the time is between 12:00 and 12:59. I have an update query that works but I want to place code in a report that does the same thing without using DoCmd.Open Query I would rather use an If statement. Please help. I placed this question in Microsoft and reply did not answer the question. Rather told me ampm would automatically show up. I knew that, but want to change the automatic pm for sorting purposes.
below is the code I am using to update multiple records on a sub form so that QtyReceived=QtyOrdered when you click the ReceiveButton, now my understanding of VBA is a little limited and the code I'm using won't work in my main form "frmReceiving" only in the subform "frmReceivingSubform" can anyone see what I'm doing wrong?
Private Sub ReceiveButton_Click() Dim rs As DAO.Recordset
Set rs = Me.frmReceivingSubform.RecordsetClone
With rs
.MoveFirst
Do While Not .EOF If rs("QtyReceived") = 0 Then .Edit rs("QtyReceived") = [QtyOrdered] .Update End If .MoveNext Loop
I have several list boxes, and each is populated using a query source. I want to make it so that when you double click a given entry it pops up either a form or query that says more information on the item.
Each list box is populated using two fields from the query a name and a number.
Example
Item A | Value A Item B | Value B Item C | Value C
I want it so if you were to double click Item A a form opens with the rest of the information so a text box or whatever with the following
Item A Value A AnotherValue A Etc A ....
Basically I want to be able to pop up a single line of the query based on double clicking of the list box entry.
This is what I have so far in code:
Code: Private Sub prospectLW_DblClick(Blah As Integer) Dim varItem As VariantDim strPlayer As String strPlayer = Me.prospectLW.ItemsSelected.Item(varItem) MsgBox strPlayer DoCmd.OpenForm ("Popup1") End Sub
The trouble I am having is getting the value from the listbox if I can get say Item A when you double click it ignoring the other info I want just the name of "Item A" so that when I open the form I can use that variable to filter the form I made that is basically just the query within a form. I hope that is enough information if not let me know I can add more.
I have a form (see attached) and every time someone goes to a different field I would like to change the border to red and when they leave that field to change the border back to the default color. I know I can put the code in for each fields got focus and lost focus section but would rather be able to put the code in once and for it to work for all the fields.
I have used Combo box. security_level field is the one that describes their levels and there are only two levels 1 and 2.. I have a table called User (user_ ID, User_Name, Password, Security_Level) On form there is a combo box for user name and text box for password. Two command button where one is for EXIT (Closing the application) and the second button is to run the code.
if the password in table User matches value chosen in combo box or user name and password are correct Then it should check if Security_Level of the user is equal to 1 to displays a form called Admin and when the Security_Level of the user is equal to 2 to display a form called user1.All that I want is to have a login that has two user and each user when login opens his/her own form which is different from the other user.
I am trying to hardcode in the field name. This is what I want to hardcode "TEXT(ROW(A17),"-0")" but the parenthesis and quotation marks are causing a syntax error. It appears like this
Wondering whether it is possible to use the 'Make Table' function in conjunction with a union query. My early attempts have failed and I am relatively new to Access.
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)
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));
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;
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]
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
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.
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;
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.
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 ?
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.
