I have some tables with identical field names, but the fields are in a different order. Is it possible to do a union like this, or do I have to rearrange the data within each table first?
For instance:
Table A: Field1, Field4, Field2, Field3
Table B: Field3, Field1, Field4, Field2
Table C: Field2, Field3, Field1, Field4
And I'm trying to combine those tables into TableABC: Field1, Field2, Field3, Field4.
Hello all,
Does anyone know (and would be willing to share) how one can order the results of a Union query in Access by something other than the returned values?
I have a simple Union query that would work perfectly - if I could get the thing to order the results in a particular order everytime:mad:. The query returns counts (all from one table) of separate select statements that each meet certain conditions (e.g. having state=Nevada, having state=California, etc.) as records - but since Union queries in Access order results in ascending or descending order by returned value, it puts my records in a different order every time the record values change. I need to have them ordered the same way (in the same order as they appear in the Union query, if possible) everytime so that I can return those values to specific cells in MS Excel. Any suggestions would be much appreciated!
I have a union query with 2 fields: Order and Row_Heading_Full.
Code:
SELECT DummyClientType.Order, DummyClientType.Client_Type AS Row_Heading_Full
FROM DummyClientType
UNION SELECT ("25") AS [Order], ("Totals") AS Row_Heading_Full
FROM DummyClientType;
The purpose is to pull the Order and Row_Heading_Full fields from a table and add another entry that with "25" as the Order and "Totals" as the Row_Heading_Full at the end of the list.
I want it to be ordered sequentially based on the Order field. But instead it is ordered like this: 1, 10, 11, 12... 20, 21, 22... 3, 4, 5... In other words it is ordering only by the first digit and not by the number as a whole.
When I remove the union aspect it is ordered properly:
Code:
SELECT DummyClientType.Order, DummyClientType.Client_Type AS Row_Heading_Full
FROM DummyClientType;
But that defeats the purpose because I am not adding a final entry of "25" and "Totals".
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]...
Hello people.
Im just after a bit of advice please??
I have created an audit trail for data held on a form so that when a field is changed it adds a line of text to a memo field with the date of the change and the old and new field values. The problem I have is that each time something is changed the line of text is added beneath the existing text in the memo field. This means that for users to view the most recent change they have to scroll all the way down to the bottom of the memo field. Is there a "Quick" way of me having the most recent entry first.
I am using the vbnewline command when adding the text. A sample is shown below:
Dim Response As Integer
Dim AnotherItem As Integer
Dim sqlstr As String
Set Myform = Forms![DataDetail]
Producer = Forms![ListOfProducers(Existing Data Item)]![LstProducers].Column(1)
Response = MsgBox("Are you sure you wish to add a Producer?", vbYesNo, "Data Dictionary v1.0")
sqlstr = "INSERT INTO DataProducers ( Data_Id, Producer_Id ) SELECT Data.Data_Id, Producers.Producer_Id FROM Data, Producers WHERE (((Data.Data_Id)=[Forms]![DataDetail]![Data_ID]) AND ((Producers.Producer_Id)=[Forms]![ListOfProducers(Existing Data Item)]![LstProducers]));"
If Response = 6 Then
DoCmd.RunSQL sqlstr
Myform!LstHistory = Myform!LstHistory & vbNewLine & "Changes made on " & Now & ""
Myform!LstHistory = Myform!LstHistory & vbNewLine & "The Producer '" & Producer & "' was added to this data item"""
Myform!LstHistory = Myform!LstHistory & vbNewLine & "-----------------------------------------------------"
MsgBox "Producer has been successfully added.", vbInformation, "Data Dictionary v1.0"
Thanks folks...
i have a query which looks at 2 fields: "employee number" & "agency number", i would like the query to list the employee numbers in order first (1,2,3...) then the agency numbers in order (100, 101, 102...). ive changed the ascending/ descending order for them both but doesnt help. Its mainly the form that looks at this query i would like to fix so that is shows all employee records in order of employee number first and then underneath all agency numbers in order of their number (some employee numbers & agency numbers are null) hope this makes sense , any help would be great
View 7 Replies View RelatedHi,
I have created a form with 3 subforms. This form is split in to 3 parts on a 3-page tab control.
I want the user to enter data in the order below. All fields are required. My problem is that Access97 wants all fields of the main form to be completed in the order set by the table. ie. before I fill in a sub form. I have tried setting the form tab order property but it is being overridden.
First page:
a) Complete first 6 fields of main form
b) Complete subform 1
c) Complete subform 2
Second page:
d) Complete 5th field of main form
e) Complete subform 3
f) Complete 6th field of main form
Third page
g) Complete three fields of main form
Any help appreciated,
Seth
am trying to change the tab order but have noticed that some of the fields are missing from the tab order form. I am attaching a screenshot to show which tabs are missing.
Screen Shot 2015-04-09 at 21.44.55.png
I have a Table with 6 fields, 2 are named Initial and Surname. Is it at all possible to have a combo box on a form with these to fields "joined" with the alphabetical order on the Surname. I understand that using a query will not work as the result is based on an expression
View 11 Replies View RelatedIs it possible to direct the placement/order new of fields when modifying an Ms Access database in code?
I need to modify the schema of an MS Access database via code - but I want to be able to direct the order or placement of the fields within the tables.
For example - if TableOne has 3 fields - Field10, Field20 and Field30.
I would like to be able to add say Field15 between Field10 and Field20 - not just append it to the end of existing fields.
I believe it is possible as you can do it within Ms Access itself. I can use ADO, DAO, ADOX or SQL for that matter - but it seems all of these offer no placement of the field within the table.
Help - Why don’t my date fields sort in date order ?
I’m having a very annoying problem with my date fields. I have a database that keeps records of patients details and their visits to the practioner.
I have a form based on Patients table with name address etc, and a subform based on a Case Details table with details of visits and advice given etc.
I need the Case Details subform to display the latest visit/record at the bottom (the earlier visits at the top of the list). For some reason I just cannot achieve this. Dates seem to be scattered all over and I can’t understand why ??
I have tried sorting the Case Details table in ascending order, sorting the ‘Date of Visit’ field on the form in ascending order (which does work but as soon as the form is closed and re-opened, all the dates are mixed up again).
In the ‘OrderBy’ property of my Case Details Subform, it says:-
[Case Details Table].[Date of Action]
which according the help I have tried to look at should sort the records ??
I’d really appreciate any pointers as to where I’m going wrong
I have a problem when I want to create an "ORDER" form, that will allow user to enter more than one order.
I have no clue how to do it.
Hi,
Can anyone please tell me what I am doing wrong here?
SELECT *
from Qry_300_Sel_In Jan not Dec
UNION ALL
SELECT *
from Qry_301_Sel_In Apr not Dec
UNION ALL
SELECT *
from Qry_302_Sel_In Jul not Dec
UNION ALL SELECT *
Qry_303_Sel_In Oct not Dec;
It is telling me that my syntax is wron in the From clause - But I don't get it!
Hello again.
If anyone can help me it would be greatly appreciated.
I’m not sure shy I’m having so much trouble with this, but I’m sure that this will be a great learning opportunity for me.
I’ll explain it two different ways, and that way hopefully it will make sense
First off I’m trying to take all the Companies I work with and get there Net Sales
Example:
Company 1 $1,625.25
Company 2 $6,782.12
ect
I have two queries
The first one is:
qrtySym_Global_ALL_and_SUBFILE_A3
The second one is:
FROM qrtySym_Global_ALL_and_SUBFILE_A4
Below is the Union Query I currently have, and it puts together both queries and shows the totals separately
I would like it to show the Company Name and next to that the Net Sales for that company
SELECT "Total" , Sum(NETSALES)
FROM qrtySym_Global_ALL_and_SUBFILE_A3
union All
SELECT "Total" , Sum(NETSALES)
FROM qrtySym_Global_ALL_and_SUBFILE_A4
Let me see if I can explain it better
The are three columns in the two queries that are the same.
Company Business/ Company Name/Net Sales
I’m trying to take all the
A: Company Business that has the same name from query 1 and query 2 and merge them.
B: Company Name that has the same name from query 1 and query 2 and merge them.
C: Then with the merging of Company Business and the Company Name, Sum the total for the Net Sales.
I really appreciate you helping, so let me know if this doesn’t make sense
Thanks
Corey
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));
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
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?
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]
I have a UNION qry in which I have Out, Incoming and internal invoices as RELATIONSHIPs, and based on it a CROSSTAB qry, and now there are 2 new categories which are goods that were returned - and there are 2 because:
1. is return from Internal and
2. is return from Outgoing invoices.
I need to calculate the current InStock (based on articalNumbers) with this in mind:
InStock = Incoming - OUTgoing - Internal + returnfromInternal + returnFromOutgoing
However, I cant seem to get a returns to the same crosstab qry in order to include them in calculation
the real names of variables are not important but the principle on which I should base my qry is the problem for me (please have in mind that I first started working in access about 2 months ago)
Is there anything wrong with the principle of my work here?
t i a...
How do I use the union all command to combine 3 queries together and create a new field. I am trying to create a search that will combine the names in 3 fields and let me select any of them and pull up the records that are associated with that person.
I have written a database for an apartment complex. I am trying to simpify the search capabilities. They have 3 types of tenants, listed as HT-Head Tenant, CT-Co-Tenant & CT2- Co-Tenant 2. These fields are in a data base with all the info that applies to each apartment. I would like to be able to search from one combo box any of these tenants and have the correct information come up. Some one told me to use 3 queries and the SQL Union all command to get the fields combined into one field. But I can't get that to work. I am kind of a rookie as I have taught myself to do this so I might not be doing something correct. I was also told by doing the above steps I could use the ID field at the bound field. But not sure How to accomplish that either.
Appreciate any help I can get.
Thanks
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
To fill a ddl list I could use a MINUS query. I found out that UNION and UNION ALL work, but MINUS doesn't.
Is there a way to make it work, or maybe an other way to solve this kind of problem?
Hi Everyone.
I need help with union queries. The database that i am working with has union queries within it. I need to added information from a certain table into this query so that i can have a report generate more information when it is run. Please help if anyone knows how to work with union queries, or where i can go to find information on this.
Thank you:confused:
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.
I have a question regarding union queries, for the most part I understand them. You have to have the same amount of fields in both tables/queries, but what I do not know is do both tables/queries need to mimic each other 100%.
For example I have 2 Queries, both match each other identically in terms of fields and the order they are placed. What does not match is that I have one query with a "true" criteria under one field and the other query has a "true" in a seperate field.
When the query is ran I get "The number of colums in the two selected tables or queries of a union query do no match."
What is the purpose of the query, I am needing to combine these 2 queries into 1 for a report. If you need the code just ask and I shall post it.
Thanks ahead of time guys.
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.