I 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?
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. )
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]...
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 Relatedhave a look on the attached sample database.
How can I get the query to work in order to count specific values per day.
Counting number of appearing records show in query
Name Drinks Date purchased Count
John Milk 8/3/13 1
->Mike Beer 8/4/13 2
John Beer 8/4/13 1
Peter Wine 8/6/13 1
-> Mike Beer 8/6/13 2
Counting conditions: counting will continue if NAME and DRINKS are the same
Access 2010. I've had to learn it at work because our DBA was let go and I was the only one willing to give it a go. Read a book or two and picked up some stuff on the internet.
Here's my problem:
I have a simple table -Employee with 4 fields. FirstName, LastName, Office and JobTitle. I have form called Form1 that has 3 control fields cboJobTitle (a combo box that is populated by a query that finds all the unique values of that field in the Employee table), cboOffice (same as above) and txtName (a text box to allow user input) that are used as the criteria for a multi-field query triggered by a button at the bottom of the form. The idea being that you could do a search using this form to find all the employees in one office or all the accountants in one office, or any other combination.
The main search query has the following criteria for each field -
Like [forms]![Form1]![cboOffice] & "*"
Like [forms]![Form1]![cboJobTitle] & "*"
Like [forms]![Form1]![txtName] & "*"
It works great...until I enter a record where one of those fields may be null, such as if I leave the JobTitle blank.
If I have two employees in an office in one city and then do a search for all the employees in that office, it only returns one record and ignores the one that has the null value in the JobTitle field.
On [Form1] I have a Multi-Select Listbox[List1] which shows Job Numbers [WBS] (The job numbers displaying are a result of a separate query filtered by the Fiscal Year combobox). When I click [CMD1] I want [Query1] results to only be selected [WBS] from the form.What is the best way to code this? I'm a beginner when it comes to VBA.
View 12 Replies View RelatedIm 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.
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.
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:
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.
Hi everybody, I have a little problem that I've been trying to solve since last night.
I have two table queries I would like to join.
First one 'query1' is as follows:
TextA TextB
A "a10"
B
C
D
E
F
Second query is:
TextA TextB
A "a14"
A "a10"
B "b23"
C "c28"
D "d43"
D "d42"
D "d41"
E
F "f63"
F "e56"
Now what I want to do is join the two queries together based on TextA so that all the duplicates in query2 are removed e.g. AADDD etc
I tried a union query but its still duplicating the letters.
Secondly (although of lesser importance) I need to get "e56" into E in query2.
Anyone else have any ideas?
Many thanks
is it possible to join these two queries together, so that the 2nd query appears at the bottom of the first?
Code:SELECT OEEModelMCTotals.Machine, OEEModelMCTotals.EventCode, OEEModelMCTotals.CodeDescription, OEEModelMCTotals.SumOfTotalTime, OEEModelMCTotals.Occur, OEEModelMCTotals.PlannedTime, [OEEModelMCTotals]![SumOfTotalTime]/[OEEModelMCTotals]![PlannedTime] AS [%Schedule], [OEEModelMCTotals]![SumOfTotalTime]/[OEEModelMCTotals]![Occur] AS Avg, OEEModelMCTotals.Output, OEEModelMCTotals.Throughput, OEEModelMCTotals.ValueAddedimeFROM OEEModelMCTotalsWHERE (((OEEModelMCTotals.EventCode) Not Like "n204" And (OEEModelMCTotals.EventCode)<>"n301" And (OEEModelMCTotals.EventCode)<>"n303" And (OEEModelMCTotals.EventCode)<>"x104" And (OEEModelMCTotals.EventCode)<>"x117"));
and
Code:SELECT OEEModelOtherTotals.EventCode, OEEModelOtherTotals.CodeDescription, OEEModelOtherTotals.Machine, Sum(OEEModelOtherTotals.TotalTime) AS SumOfTotalTime, Sum(OEEModelOtherTotals.Occur) AS SumOfOccurFROM OEEModelOtherTotalsGROUP BY OEEModelOtherTotals.EventCode, OEEModelOtherTotals.CodeDescription, OEEModelOtherTotals.Machine;
i have attached a spreadsheet with the outcome i am after....i dont want it in excel but have used this for my demonstration.
you will see at the bottom of the sheet i have highlighted the info added in blue.
can this be achieved ?
if so how please.
cheers
Andy
I have two tables, joined together with a Union query, and what I'd like out of it is a Sum of Categories & Sub Categories, based on all days this year. When run on their own, they give me exactly what I need, but when used with UNION ALL, I have duplicate "Sub Category" Entries...
One from one side of the query, and one from the other..Is there anyway this can be put on just the one line?the SQL I'm using is this...
Quote:
SELECT tblTopLines.Category, tblTopLines.SubCategory, Sum(tblTopLines.Credit) AS SumOfCredit, Sum(tblTopLines.Debit) AS SumOfDebit
FROM tblTopLines
WHERE (((tblTopLines.TransDate) Between #1/1/2015# And #12/31/2015#))
GROUP BY tblTopLines.Category, tblTopLines.SubCategory
HAVING (((tblTopLines.Category)="Car"))
UNION ALL
SELECT tblSplits.Category, tblSplits.SubCategory, Sum(tblSplits.Credit) AS SumOfCredit, Sum(tblSplits.Debit) AS SumOfDebit
FROM tblSplits
WHERE (((tblSplits.TransDate) Between #1/1/2015# And #12/31/2015#))
GROUP BY tblSplits.Category, tblSplits.SubCategory
HAVING (((tblSplits.Category)="Car"));
As I said, SQL isn't my thing, but every stone unturned is another feather and all that..
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 RelatedI have a table and a query. The first 4 fields of the table correspond to that of the query. The query does not have any other fields, but the table has 26 more fields. Is it possible to use SQL OUTER JOIN or UNION or whatever to append the data of the query to the table or do I have to go with recordsets of VBA?
View 6 Replies View RelatedI used UNION ALL to get results from two queries and I Succeeded.Now I want these results to be in a date range, so I want to enter the "starting date" then the "End Date" to have may results in specific date range.This is the original code out of UNION ALL which is working fine:
SELECT Count(Patient.PatientID) AS CountOfPatientID, Patient.CauseOfAmpLowerLt
FROM Patient
GROUP BY Patient.CauseOfAmpLowerLt
HAVING (((Patient.CauseOfAmpLowerLt) Not Like ""))
UNION ALL SELECT Count(Patient.[PatientID]) AS CountOfPatientID, Patient.[CauseOfAmpLowerRt]
FROM Patient
GROUP BY Patient.[CauseOfAmpLowerRt]
HAVING (((Patient.CauseOfAmpLowerRt) Not Like ""));
And this is what I tried:
SELECT Count(Patient.PatientID) AS CountOfPatientID, Patient.CauseOfAmpLowerLt
FROM Patient
GROUP BY Patient.CauseOfAmpLowerLt
HAVING (((Patient.CauseOfAmpLowerLt) Not Like ""))
UNION ALL SELECT Count(Patient.[PatientID]) AS CountOfPatientID, Patient.[CauseOfAmpLowerRt]
FROM Patient
GROUP BY Patient.[CauseOfAmpLowerRt]
HAVING (((Patient.CauseOfAmpLowerRt) Not Like "") AND (PatientService.[Date of Service]) BETWEEN [Start Date] AND [End Date]);
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.
I have created a database for my hobby of being an (American) football official. I have one table of officials and another for matches to track the matches that I have worked. There are 3-7 positions that officials can work in any one match.
I want a query that will tell me which officials I have worked with and in how many matches (in total regardless of position). I have created a union all query to give me a list of officials names each time they appear. Rather than giving me the result as the names it comes back as the ID numbers from the Officials table.
The SQL query is:
SELECT Ref FROM Matches WHERE Ref Is Not Null
UNION ALL
SELECT Umpire FROM Matches WHERE Umpire Is Not Null
UNION ALL
SELECT LineJudge FROM Matches WHERE LineJudge Is Not Null
UNION ALL
SELECT HeadLine FROM Matches WHERE HeadLine Is Not Null
UNION ALL
SELECT BackJudge FROM Matches WHERE BackJudge Is Not Null
UNION ALL
SELECT SideJudge FROM Matches WHERE SideJudge Is Not Null
UNION ALL SELECT FieldJudge FROM Matches WHERE FieldJudge Is Not Null;
The field used in each position is a full name field ([Surname] & ", " & [Name])
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];
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.
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]));
What I am trying to do is merge data from several tables into one query, based on user defined options through multiple listboxes. I've figured out the VBA for the union all for the different selections, my problem is I can't determine how to make it dynamic and skip over a select statement if the list value is null. My code looks something like this so far.
Code:
Dim db As DAO.Database
Set db = CurrentDb
Dim qdf As DAO.QueryDef
Set qdf = db.QueryDefs("Flex2")
[code]...
I searched the internet and this forum for the answer to this. I did find the exact question posted in this forum way back in 2002 but there was no reponse...
When updating an Excel Pivot table using an Access Union Query as it's source, I receive the message "[Microsoft][ODBC Microsoft Access Driver] Too Few Parameters".
I found a rather elegant solution here (http://groups.google.co.uk/group/microsoft.public.excel.querydao/browse_thread/thread/1ca76034adc10c1a/204261bda38c118c) Unfortunately, this appears only to work for Access 2003. Does anyone have a solution for Access 2000?
Perhaps I should insist that our IT department upgrade. :p
Thanks in advance for any assistance.
- Matt
I am trying to combine fields (different columns) into one field. However I am trying to have a criteria for the fieldname. For example I wish only to combine the columns that contain the text 2003 (ie [Value 2003],[Price 2003]). I have tried to use wildcards in SQl ie select * from [query] where * = %2003. I must be missing something because I can't seem to work it out. It must be simple. I have also tried several ways through the design grid with wildcards in expressions. I also don't want to union the columns by using the exact names ie [Value 2003]& [Price 2003] because additional columns may be added.
Any help, extremely appreciated, I have wasted a whole day with this.
Deesy.