Queries :: Hide Field In Union Query?
Dec 11, 2013Is there a way to hide a field in a union query? I need to keep the field in the SQL statement because I need to order by it. The field is "Rank," but I don't want it showing up.
View Replies
ADVERTISEMENT
Queries :: UNION / UNION ALL Query Crashed Access
Oct 24, 2013I'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]...
Queries :: Multi-Field Union Query (Joining Questions And Pictures Into One Report)
Apr 5, 2013I have two tables containing (let's say for simplicity) questions and attachments (pictures). I am trying to perform a union query to join all the questions and pictures into one report, but it won't let me union the attachment because 'the multi-valued field 'TableA.Pictures' cannot be used in a union query'.
I have searched and searched for a solution (and got kind of close) but i still can't get it to work. The best I can do is union everything like below, which gives all the questions as desired, but says #Error in the pictures column:
SELECT TableA.*
FROM TableA
Union
SELECT TableB.*
FROM TableB;
(Note tables A and B have the same structure, several yes/no and open text questions as well as one attachment field. )
Queries :: Union (all) Does Not Like Multi-Value Field?
May 14, 2013I have got a db and that has 10+ ref tables, and i need to show everything from those tables in one, and these tables includes Attachment field. I have tried union but did not work.
How can i achieve this?
Queries :: How To Hide A Field
Jul 25, 2013I have created a query that will create a table automatically. I want one of the fields that I have defined in the query, not to show up on the table. the purpose of that field is just to check a minimum value so that the field next to it can use the value. when I uncheck the "show" box in design view the field completely disappears and Access asks the user to enter a value for that name since the value is used in the field next to it.
View 8 Replies View RelatedQueries :: Checkboxes Becoming -1 And 0 In Union Query?
Oct 11, 2013After putting in fields there were checkboxes into a Union Query, they now show up as -1s and 0s. Is a -1 a blank, and a 0 checked?
View 1 Replies View RelatedQueries :: Max UNION ALL In Single Query
Sep 12, 2013I'm having a small problem where Access returns a "Query too complex" error when I try to run a Union query of the form:
Code:
SELECT CourseNumber, CourseTitle, [Date], TotalScore, "Two (2) zero visibility dives?" AS [Question], CountZeroVisibility AS [NegativeResponses]
FROM qryScore
UNION ALL
SELECT CourseNumber, CourseTitle, [Date], TotalScore, "One (1) night dive?" AS [Question], CountNightDive AS [NegativeResponses]
FROM qryScore
UNION ALL
...etc
With more than 13 unions.
Queries :: Make A Union Query
Dec 18, 2014I have two basic lists of employee names, ID's, phone numbers etc, with one field containing an attachment with a picture of their ID. I don't want to permanently combine these two lists of employees, as they work in different departments, but for certain events I need to be able to print a report with a list of all their names, ID numbers, and corresponding ID photos.
I tried to make a UNION query, but can't do it with the attachment field. If I leave that field out, it's no problem. To simplify, I have been doing a test run as follows, with just the name field and photo field (field 1).
SELECT [Starting Gate employees].[Employee name], [Starting Gate employees].Field2
FROM [Starting Gate employees]
UNION
SELECT [Farrier employees].[Employee name], [Farrier employees].Field2
FROM [Farrier employees];
Queries :: Union Query - Group By Then Sum
Aug 5, 2013I have a Union Query which the result was like this:
Date Cash_In Cash_Out
08/06/2013 100.00
08/06/2013 25.00
08/06/2013 50.00
08/06/2013 20.00
Note: The Date Field comes from two different tables. Original field names are Cash_In_Date and Cash_Out_Date.
SQL - Union Query To Combine 3 Queries
Jun 3, 2015I have three queries that make a training list based on a person's role, team, and ad-hoc exceptions. The personnelID field is filtered by a listbox on a form. Each of these work great on their own (nice!). Now I want to combine them. I made the below union query that works... however when it is run, I am prompted to enter the personnelID again. This prompt only happens once. Do I need to incorporate a qhere statement somewhere, even though each of the individual queries have them already?
SELECT First_Name, Last_Name, TrainingTitle, PersonnelID
FROM qryPersonnelTrainingByRole
UNION ALL
SELECT First_Name, Last_Name, TrainingTitle, PersonnelID
FROM qryPersonnelTrainingByTeam
UNION ALL SELECT First_Name, Last_Name, TrainingTitle, PersonnelID
FROM qryPersonnelTrainingByAdHocPersonnel;
The where statement of potential use:
Code:
WHERE (((tblPersonnel.PersonnelID)=[Forms]![frmMain]![lbxPersonnel]));
Queries :: Combining Two Queries Without Union Query
May 7, 2014Im trying to join two queries as I am unable to use just a single query but I cant use a union query as the query fields aren't exactly the same.
Both queries have a PO_Detail field as every PO has a PO_Detail number assigned to it. 3 of the same records are in both query results but one query is missing the other two results.
If I create a join between the two queries based on this field I don't get all the results. Unfortunately I cant upload the database as it has sensitive data which would take me ages to clear out but I can show a picture of the results.
Queries :: How To Do Combine Two Different Queries In UNION Query
Oct 28, 2013Am trying many times in UNION query but its not working because there is different field names and only some fields are matched. So I need to both query's are combine in 1 query.
They Quays details:
1.Customer Credit Transaction1
2.Customer Credit Transaction2
Above both queries are already combined in UNION query as (Customer Credit Transaction Final) its not a problem.So now I need to do combine the above Union query Customer Credit Transaction Final & ReceiptformQry.
The both query details:
Customer Credit Transaction Final
SELECT BillBook1.TID, BillBook1.BILLNo, BillBook1.BILLDate, BillBook1.Customer, BillBook1.BillMode, [BillBook1 Vat Details].[TOTAL Rs] AS [CC Amount]
FROM BillBook1 INNER JOIN [BillBook1 Vat Details] ON BillBook1.TID = [BillBook1 Vat Details].TID
WHERE (((BillBook1.BillMode)="Credit"));
UNION ALL
[code]...
Above fields are need to merge in Union query or otherwise. and remaining fields are needed to show separately.
Hyperlink Field In Union Query
Mar 8, 2006Hi,
I have a query that I build using VBA based on some user input. One of the fields I pull out is a hyperlink to particular files on our local network, so the user can click the link and open the relevant file. My problem is that if the SELECT statement contains one (or more) UNIONs the hyperlinks no longer work. The query returns all the fields but the hyperlink field is just text of the form "display_text#link_address#".
I am using MS Access 2000. The UNION statements are required so I can search for multiple keywords in various fields within the table.
An example of the SQL query I generate is:
SELECT DocRef, DocTitle, DocAbstract, DocLink, DocAuthor, DocDate FROM TechDocs
WHERE DocType IN ('TechRep', 'Misc')
AND DocAbstract LIKE ('*drug*')
UNION
SELECT DocRef, DocTitle, DocAbstract, DocLink, DocAuthor, DocDate FROM TechDocs
WHERE DocType IN ('TechRep', 'Misc')
AND DocAbstract LIKE ('*release*');
If I do the query a different way (when I am searching for phrases, not keywords) the hyperlinks work fine. As you can imagine this is very frustrating! I have read that Access 2000 has some problems with UNIONs where it has to order the individual SELECT results before the union or something, but I can't work it out. :confused:
Any help is much appreciated.
Using VBA To Change The Name Of A Field In A Union Query
Feb 16, 2007I have a table called LLD Import Table with the following fields in it.
FileNumber, LandDescription, Qtr1, Sec1, Qtr2, Sec2, Qtr3, Sec3, Qtr4, Sec4, Qtr5, Sec5
Data Example:
123456, T43 R2 W5M, S, 6
123457, T43 R1 W5M, SE, 18, SW, 17
123456, T43 R1 W5M, E, 19, E, 30, SW, 29, E, 31, NE, 18
What I have done so far is to create a Union query to create a new record with the file and land description repeating for each row where there is quarter and section data with the following code:
SELECT [LLD Import Table].[FileNumber], [LLD Import Table].[LandDescription], [LLD Import Table].Sec1 AS Section, [LLD Import Table].Qtr1 AS Quarter
FROM [LLD Import Table]
WHERE ((([LLD Import Table].Sec1) Is Not Null));
UNION
SELECT [LLD Import Table].[FileNumber], [LLD Import Table].[LandDescription], [LLD Import Table].Sec2 AS Section, [LLD Import Table].Qtr2 AS Quarter
FROM [LLD Import Table]
WHERE ((([LLD Import Table].Sec2) Is Not Null));
UNION
SELECT [LLD Import Table].[FileNumber], [LLD Import Table].[LandDescription], [LLD Import Table].Sec3 AS Section, [LLD Import Table].Qtr3 AS Quarter
FROM [LLD Import Table]
WHERE ((([LLD Import Table].Sec3) Is Not Null));
UNION
SELECT [LLD Import Table].[FileNumber], [LLD Import Table].[LandDescription], [LLD Import Table].Sec4 AS Section, [LLD Import Table].Qtr4 AS Quarter
FROM [LLD Import Table]
WHERE ((([LLD Import Table].Sec4) Is Not Null));
UNION
SELECT [LLD Import Table].[FileNumber], [LLD Import Table].[LandDescription], [LLD Import Table].Sec5 AS Section, [LLD Import Table].Qtr5 AS Quarter
FROM [LLD Import Table]
WHERE ((([LLD Import Table].Sec5) Is Not Null));
To give this result:
FileNumber, LandDescription, Quarter, Section
123456, T 43 R 1 W5M, NE, 18
123456, T 43 R 1 W5M, E, 19
123456, T 43 R 1 W5M, SW, 29
123456, T 43 R 1 W5M, E, 30
123456, T 43 R 1 W5M, E, 31
123456, T 43 R 2 W5M, S, 6
123457, T 43 R 1 W5M, SW, 17
123457, T 43 R 1 W5M, SE, 18
However the number of Quarters and Sections under a file changes, so next time I my table could have fields up to Qtr20 and Sec20 What I’d like to do is to create a function that will automatically change the # behind the field names (to replace the * in the example below) so that I don’t have to rewrite the Union Query each time. I’ve seen some code examples that can change the value, but don’t quite understand them enough to write one.
SELECT [LLD Import Table].[FileNumber], [LLD Import Table].[LandDescription], [LLD Import Table].Sec* AS Section, [LLD Import Table].Qtr* AS Quarter
FROM [LLD Import Table]
WHERE ((([LLD Import Table].Sec*) Is Not Null));
Your help would be GREATLY appreciated!
Queries :: Union Query For Mail Merge?
Jun 3, 2015it's possible to use a union query as a mail merge? I haven't found anything that says I can't do it, but I'm not getting my merge to complete, and when I switch to a plain query (and not changing anything else) my merge is successful, so I'm thinking there might be a limitation.
View 14 Replies View RelatedQueries :: Does Rnd Function Work With A Union Query
Aug 29, 2013I have 3 queries that I need to join. the 3 queries work on their own. They are all similar to below
SELECT TOP 5 ASTDATA.[ID], ASTDATA.[Weight], ASTDATA.[StockCode], ASTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM ASTDATA
ORDER BY Rnd([ID]);
But when I join them, like below, the data doesn't change. Does the rnd function work with a union query?
SELECT TOP 5 ASTDATA.[ID], ASTDATA.[Weight], ASTDATA.[StockCode], ASTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM ASTDATA
UNION
SELECT TOP 5 BSTDATA.[ID], BSTDATA.[Weight], BSTDATA.[StockCode], BSTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM BSTDATA
UNION SELECT TOP 5 CSTDATA.[ID], CSTDATA.[Weight], CSTDATA.[StockCode], CSTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM CSTDATA
Queries :: UNION Query Will Not Execute Properly
Apr 1, 2015UNION Query will not execute properly. Remove "WHERE" statement query runs.The WHERE statement is used to locate all duplicate records, based on serial number, in a table. Also, it excludes certain data contained in two of the four tables. There are four separate queries for four separate tables. They all work until incorporated into a UNION QUERY.
Question, is there another way to structure the "WHERE" statement for this UNION Query and achieve the same results?
Queries :: Union Query Not Separating Results
Feb 18, 2014I have a simple union query that looks like this:
Code:
Select DPTag from tbleqDP UNION ALL select ESGTag from tbleqESG
It is returning all the tags from both tables but putting them all under "DPTag." What I want is two columns in a report. One would have the header "DPTag" and all the values under it would be the tags from tbleqDP and the other would have the header "ESGTag" and all the values under it would be from the table tbleqESG.
What am I doing wrong such that it is returning all the values under the header DPTag?
Queries :: Date Format In SQL Union Query
Nov 11, 2014I have unified three queries , each query has a field "date",
format is dd/mm/yyyy.
The Union select query however, displays this "date" with different format , dd/mmm/yy
How can I fetch same format in the union query ??
Queries :: Update Table With The Value Of Union Query
Sep 25, 2013I have a table with a field I want to update with the value of a Union Query
The field I want to update is Yes/No format and I can update using 0 or -1 OK.
However, when I link in the union query (and check that I only have 1 update result which is either 0 or -1) I get an error message saying that Operation must use an updatable query.
I guess this is referring to the Union Query (although I am trying to update the table and not the query.
In short - I want to update a table based on the value of a union query.
Code:
UPDATE tbl_StaMe_NGR_Subscription INNER JOIN qry_QDF_QRYDEF1 ON (tbl_StaMe_NGR_Subscription.EmailType = qry_QDF_QRYDEF1.Type) AND (tbl_StaMe_NGR_Subscription.AgentEmail = qry_QDF_QRYDEF1.Email)
Code:
SET tbl_StaMe_NGR_Subscription.Subscribed = [Subscription];
Queries :: Combining Like Records In Union Query
Nov 25, 2013How can I add the resulting records from a union query. The results of the union are something like this:
Quantity Item
2 Cats
3 Cats
1 Dog
4 Mice
What I need to display is
Quantity Item
5 Cats
1 Dog
4 Mice
How can I get the query to "do the math"?
Queries :: Unpivot Table With Union Query
Feb 2, 2014I am using below Union Query to unpivot attached table. Basically this is a table with some clothes and their sizes (in yellow). Now my problem is that below query works well only when all sizes are in the same row but as per attachment I may have them potentially over three different rows.
I can determine which row is for which item based on size range column (in red) but I still do not know how to modify my query to look at different rows depending on value of size range column, especially that as you can see there are multiple values pointing out to the same size range i.e. 0-6 and 1-4 point out to Size Range 1.
Unfortunately I cannot change the layout of the table to have all sizes in one row
Code:
Select Field1, Field2, "0" as Size,Field3 as Qty from orderform where Field3 > 0;
Union All Select Field1, Field2, "1" as Size, Field4 as Qty from orderform where Field4 > 0;
Union All Select Field1, Field2, "2" as Size, Field5 as Qty from orderform where Field5 > 0;
Union All Select Field1, Field2, "3" as Size, Field6 as Qty from orderform where Field6 > 0;
Union All Select Field1, Field2, "4" as Size, Field7 as Qty from orderform where Field7 > 0;
Union All Select Field1, Field2, "5" as Size, Field8 as Qty from orderform where Field8 > 0;
Union All Select Field1, Field2, "6" as Size, Field9 as Qty from orderform where Field9 > 0;
Setting A Format In A Field In A Union Query
Jun 21, 2007Hi everyone,
Please would someone be able to help me?
I have created a union query however, one of the columns, has not picked up the same format as it has in the tables. As in the tables it has this format
'00000'.
Please woud you be able to advise me how I can change the format on one of the 'columns' in my union query. As one column is 'numbers' and the other is 'text'. I need to change the number column so the format is '00000'.
Thank you in advance for your help.
Nats
Union Query Truncates Memo Field????
Jan 4, 2005I created a Union Query for several linked Excel tables. Certain fields in the Excel table exceed 256 characters and Access (rightfully so) assigns these fields as "Memo". I have create a report based upon the Union Query; however, it will truncate the "Memo" fields to 255 (or 256 characters).
As a side note, if I create a report based upon a simple query using only one of the linked tables, it does not truncate the field.
Any suggestions on what maybe causing this truncation issue?
Queries :: Union Query Ruins Numbered Order?
Jan 19, 2015I 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".
Queries :: Union Query - Multiple Records On One Report
May 7, 2014I have a database that has over 20 tables in it and am using Access 2000. Unfortunately I cannot change the structure as it performs specific functions, so I am stuck with it.
I have created a Union Query from these tables yaking data from 5 fields using the Serial Number entered by the user.
SELECT[Workstation].UnitPart,PropertyTag,UnitSerial,Date,Technician
FROM[Workstation]
WHERE((([Workstation].UnitSerial)=[Enter Serial No.];
UNION
SELECT[LAPTOP].UnitPart,PropertyTag,UnitSerial,Date,Technician
[Code] ....
I use a bar code scanner to scan the serial number, and it goes through the tables and returns the results along with the other specified fields.
I would like to scan up to 16 or more different serial numbers and have it return the results. Perhaps scan the first 16 serial numbers, then run the query? Is that possible. The serial numbers are unique and will return a combination of laptops, printers, monitors, etc...
I have created a report from the above union query and it works perfectly with only one serial number entered.