Queries :: How To Sort A Union Query SQL Statement For A Report
Oct 1, 2013
I have a Union Query (that works perfectly fine) with the following code:
Code:
SELECT * FROM sbqryUseBattery
UNION
SELECT * FROM sbqryUseBeltsDeck;
UNION
SELECT * FROM sbqryUseBeltsHydro;
UNION
SELECT * FROM sbqryUseBeltsPTO;
UNION
SELECT * FROM sbqryUseFiltersAir
UNION
SELECT * FROM sbqryUseFiltersFuel
UNION SELECT * FROM sbqryUseFiltersOil;
I am using this information on a Report.
The problem is that the Report shows the data in random order. Is there a way to filter either the Union Query or the Report?
View Replies
ADVERTISEMENT
May 7, 2014
I 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.
View 14 Replies
View Related
Apr 5, 2013
I 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. )
View 8 Replies
View Related
Jul 28, 2015
This is a query, report and vba question. I'm using Ms Access 2007.
TABLE 1: projectname, activityname, totalhoursworked, employeename
TABLE 2: employeename, employeelevel
TABLE 3: employeelevel, rate
I created a select query to join the info that I need.
SELECT QUERY 1: projectname, activityname, employeename, totalhoursworked, rate, cost (calculated field (totalhoursworked*rate))
I have 2 crosstab queries.
CROSSTAB QRY 1: ROW (projectname, activityname) COLUMN (employeename) VALUE (totalhoursworked (summed))
CROSSTAB QRY 2: ROW (projectname, activityname) COLUMN (employeename) VALUE (cost (summed))
I then created a 2nd select query with inner joins to join both crosstab queries on similar fields (activity & projectname).
SELECT QUERY 2: projectname, activityname, employeename (totalhoursworked as value), employeename (calculatedcost as value)
It gives me this:
However, I want it like this:
Those employeename... refers to more employees being added after a period of time. Hence I want to know if I could use vba to generate a report every time a button is pressed on a form? I know how to link the form to the query.
View 8 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
Mar 3, 2006
i made a report based on a query. when i run the query, my results are sorted by Job Time Ascending, like i want...
when i made my report i chose job id as the main group priority in the wizard..
now in my report it displays the jobs in ID ascending, rather than Job time ascending as my query says..
i dont want to re-do the report it took ages.. how can i fix this?
View 3 Replies
View Related
Jan 9, 2005
I have a query that sorts players batting averages from highest to lowest. When I run the query just by itself it sorts it right however when I open the report based on that query it does not sort in descending order like I asked. What am I missing??
View 3 Replies
View Related
Oct 22, 2004
I'll ask in stages:
Can the results of a Union query show up on a report?
My expected results would be something like this.
SumofPT SumofOT SumofST Range
75 80 80 5-Day
120 14 Day
175 120 30 Day
Or am I going about this the wrong way.
I have 5 queries named the different ranges
I prompt for a sum total for a given date range
Individually they work.
The Union Query prompts me correctly. That's as far as I
have gotten.
View 3 Replies
View Related
Aug 6, 2007
Morning Everyone,
I'm currently working on a database that will store Employee Names and their Hours Worked for bookkeeping purposes. The form we have composed currently takes information on a Job to Job basis (i.e., 1 sheet per job) but has several text input fields for the various employees who may have worked on that job (i.e., EmployeeNameA, EmployeeNameB, etc.). The problem lies in our report. Since we have to report on an employee basis rather than a job basis our report requires more complex code.
Through browsing several forums and doing a lot of googling, I learned that the best way to report several fields is to run a union query and compile the data. I did, and now all the EmployeeNameA's B's and C's are simply "Employee" under a new query titled "Employee Query".
I've hit a brick wall since I have no idea how to make the report produce one employee's name alphabetically from that query, then subsequently report all his hours worked for each day of a pay-period week; then list the next employee's name, then all his data, etc. I have the dates set up fine, and the employee union query includes the hours worked and everything else that needed to be compiled, but names refuse to show up when I try to DLookup the union query. I may not need to do a Dlookup, I may be way off. I need some help!
Thanks in advance and feel free to ask any questions!
View 10 Replies
View Related
Sep 16, 2013
So i have a union query pulling together about 3,000 records from other queries.
I arranged all the counts and data I need to fit onto one page, but the report is copying that page (minus the header) for each record in the union query. I'd much prefer a one page report rather than a 3,000 page report.
Why does it do that? All I want is a one page report, how can I make that happen?
View 3 Replies
View Related
Apr 24, 2015
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
View 3 Replies
View Related
May 20, 2005
I have a front end that is connected to three back end files. The front end is on my local computer while the back end files are on a network drive.
There are a lot of calculations that go into the queries and intermediate queries. For a report, I have based it on a UNION query.
But when trying to design the report it takes about 45 seconds just to do any one thing, e.g.;
- Add Groupings
- Add Grouping Headers//Footer, sorting option
- Add bound textbox
:eek:
Needless to say this is very annoying.
:mad:
The union query itself runs fine (takes about 15 seconds to run) and returns about 12,000 Rows. The union query looks like this (I changed the field names to make it read easier, hopefully);
SELECT a1, a2, a3, a4, a5
FROM qry_A;
UNION SELECT ALL a1, b2 AS a2, b3 AS a3, a4, a5
FROM qry_B;
UNION SELECT ALL a1, c2 AS a2, c3 AS a3, a4, a5
FROM qry_C;
UNION SELECT ALL a1, d2 AS a2, a3, a4, a5
FROM qry_D;
UNION SELECT ALL a1, e2 AS a2, a3, a4, a5
FROM qry_E;
UNION SELECT ALL a1, f2AS a2, tblG.f3 AS a3, tblG.f4 AS a4, a5
FROM qry_F;
One solution I came across when searching the forums was to use an Append Query to append the query results to a table and base my report on that. This does indeed fix the problem.
But what I was wondering if it was is my query design that is causing it to be slow or is it just the fact that I am returning 12,000 rows?
:confused:
In case it matters, I wanted to mention that I can’t use the report wizard to create the report. When I select the union query, the fields will be showed for awhile then they just disappear. That in and of itself doesn’t cause any trouble since I am creating the report using the design view and not the wizard.
View 7 Replies
View Related
Oct 11, 2013
After 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 Related
Sep 12, 2013
I'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.
View 13 Replies
View Related
Dec 18, 2014
I 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];
View 2 Replies
View Related
Aug 5, 2013
I 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.
View 5 Replies
View Related
Jun 3, 2015
I 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]));
View 6 Replies
View Related
May 7, 2014
Im 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.
View 10 Replies
View Related
Oct 28, 2013
Am 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.
View 6 Replies
View Related
May 5, 2006
Hi,
I need help with an access 2000 multiple queries problem.
I have the following:
Table 1 = info below / table 2 = computer types / table 3 = offices 1 through 10
field 1 = name (20 employees)
field 2 = computer (5 different type of computers)
field 3 = office (10 different offices)
field 4 = issued
field 5 = not issued
field 6 = stolen
On a form I have the 2 combo boxes (computer and office) to select and check boxes to select issued or not issued or stolen.
I need a report printed based on the multiple criteria query only showing the results that I ask for.
For e.g. in office 5, computer IBM, issued.
Print Report: lists names of all in office 5, with IBM’s, issued only!
etc.
Please help.
View 2 Replies
View Related
Jun 3, 2015
it'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 Related
Aug 29, 2013
I 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
View 9 Replies
View Related
Apr 1, 2015
UNION 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?
View 6 Replies
View Related
Feb 18, 2014
I 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?
View 10 Replies
View Related
Dec 11, 2013
Is 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 3 Replies
View Related
Nov 11, 2014
I 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 ??
View 3 Replies
View Related
