Combing Tables With 'Union' Statement Leave A Column
Jan 4, 2006
I inadvertently deleted my previous thread. Sargeant reply to my previous thread recommending using the 'Union' statement. I did and below are the results. Below is what I used. Please advise. Thanks again ! ! !
SELECT Table1.Column1, Table1.Column2, ""
FROM Table1
UNION SELECT Table2.Column1, "", Table2.Column3
FROM Table2;
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?
I am trying to add a value to a column in a query based on the value of another column.
I am using an iif statement for it but can't get it to work. I have a column called [Was Worker Born in UK] which has 3 options of "Yes, "No" and "Unknown". The next column is the [COUNTRYOFBIRTH] column.
I basically want [COUNTRYOFBIRTH] to say "866" if [Was Worker Born in UK] is "Yes".
I think I need a union query for this. I have 2 tables with the exact same field names. I need a query that will return all of the records together. I haven't been able to figure out how to write the sql code. I do best when I am working from an example, but all the examples I've seen by searching this site are a little confusing...
My two Tables:
Direct Table: |_Title_|_Number_|_Location_| |_Youth_|_25212__|__Storage_| |_Aging_|_93839__|__Stacks__|
The New Query: |_Title_|_Number_|_Location_| |_Youth_|_25212__|__Storage_| |_Aging_|_93839__|__Stacks__| |Generic|_123456_|__Storage_| |_Pizza_|_948282_|__Stacks__|
Fairly simple...but hard enough that I'm having trouble figuring it out. Previously, I was just combining the two tables into a new table, but that means whenever I update information, I have to update it in two places--which defeats the whole purpose of a database.
So I have this If statement that goes something like this;
Code: IIf {ttblEQAnswer_FCLIT.ActionType} = 'Answer Affir Defense' andnot isnull({ttblEQAnswer_FCLIT.METPrep}) and not isnull({ttblEQAnswer_FCLIT.AttyInstr}) and ({ttblEQAnswer_FCLIT.METPrep})> date(1900,1,1) and not isnull({ttblEQAnswer_FCLIT.AttyInstr}) and {ttblEQAnswer_FCLIT.METPrep} < currentdate - 10 then 'Y' as [MET InstructNeed?],
I want to use the above formula to calculate a column. However, do I need to join the tables referenced above in order to get this formula to work? As it stands I'm getting an error that says "Malformed GUID in query expression".
I have Access2003 and a problem creating a simple method of constructing a union query.
I am using a commercial paradox databse that has a habit of creating a new table every time a certain action is performed. There is currently a set of 160 tables with identical fields. The database allows me to view data in these tables individually. I would like to pull the information together, from between 20 and 30 specific tables at any one time, and view the data in one table.
I have linked the tables in a an access database and would go about viewing the data by creating a union query containing each of the tables of interest. Then appending the info in these tables to a new table.
Unfortunately manually adjusting the SQL expression is a bit cumbersome to say the least I am wondering if it possible to select the tables of interest from a List Box on a simple form and have those selected inserted into the union query ?
Alternatively, the table names are actually a field within a different table. Another field within this table actually determines whether they will be of interest. So ideally I could query that table to tell me which tables need to appear in the union query. Again I would like the union query generated automatically as a result of that query.
I am confident, perhaps without good reason , that this must be possible ....
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 was just wondering if this is a possibility to do in one query or if it has to be run from a number of different queries.
I am currently developing a database from scratch for work (with very little Access experience).
The current query I am trying to run, if linked to a number of tables with different information.
What I am trying to do primarily is link stock to a specific "Host Name", "Serial Number" and "Part Description".
In the "Host Name" there is for example - A1-TX10-10001, B1-TX2-10004, C1-TX-10004 - The latter part of the name is a unique identifier number. The first part is the compartment in which the "stock" sits. So you may have all three components (A1-TX1, B1-TX2, C1-TX3) linked to the same unique identifier (10001 for example)
The serial numbers naturally are different for every single one and of course the srial numbers are linked to the "Part Description" - which will read something like....."C1-TX3 Transmitter", "B1-TX2 Combiner" etc.....
When I run the query like this the Host Name (which is also linked to the unique identifier on its own (10001) it returns everything under "A1-TX1-10001"
What I would ideally like to do is write a statement so that if the "Part Description" contains "A1-TX1" it will only return rows that contain "A1-TX1" in the Host Name and the same for "B1-TX2" and "C1-TX3" in the same query.
If "Host Name" contains "A1-TX1" to return "Part Description" to contain "A1-TX1"
I have 5 tables in access 2010, 4 of them have data in them and I need to populate the 5th table with all data from the 4 tables. I know you have to create a Union query, but i dont know the sql statement
Table 1 - data table 2 - data table 3 - data table 4 - data table 5 - combine data from table 1, table 2, table 3, table
I've a button that position the table on a new record:
DoCmd.GoToRecord , , acNewRec
And I create a button to cancel that action:
Me.Undo
But the code field (autoincrement) continues autoincrementing! Explaning:
1. I click on "Add" button 2. The table's positioned on autoincrement code #1 3. I click on "Cancel" button 4. The register is cancelled, it isn't includded on table 5. I click on "Add" button 6. The table's positioned on autoincrement code #2 and the #1 isn't exists...
Hai guys Iam looking out for a sample DB for employees leave management. # With leave application form # Leave approval form # Leave status # All with username and password
I'm new to Access. We have a database that was created years ago and has been working fine. Now suddendly we get the following error message on a query.
"The number of columns in the two selected tables or queries of a union query do not match"
This is the code
SELECT [TimeSheets All].[Job Number], [TimeSheets All].Date, [TimeSheets All].Details, [TimeSheets All].[Start Time], [TimeSheets All].[End Time], [TimeSheets All].[Unbillable hours], [TimeSheets All].Who, * FROM [TimeSheets All] WHERE ((([TimeSheets All].[Job Number]) Like [Forms]![Search]![Job Number])) ORDER BY [TimeSheets All].[Job Number], [TimeSheets All].Date;
I have 2 tables... one with 'answers' (on questions) and one with these 'questions'. Now I want to show the questions with their answers on a rapport (based on a query) .. but only these whereby the answer-field is NOT NULL..
So I have already: ansques1: IIf([answers.1] Is Null;Null;[questions.1]+" "+[questions.1])
But now if the answer-field is NULL, then the ansques-field is also NULL... I want if the answer-field is NULL, to have no new field.. like ansques1.. just want to leave it out of the query... can someone help me with this?
I have an unbound text box that displays when certain criteria exits, which I placed in the Form's properties, "On Current" ...
If [Decor6Date] <= Date - 30 And [NonrecommendQuestion] = 0 And IsNull([DateDecReceived]) Then
[LateDecoration2] = "Late Decoration"
Else
[LateDecoration2] = ""
End If
I then made a timer event to show/flash the message
Me!LateDecoration2.Visible = Not (LateDecoration2.Visible)
Everything works fine ... the problem is the message will only flash if I exit the record then return to the record. How can I fix that problem? I tried Refresh ... no luck.
I have a database with shifts in for staff. They have a bunch of times in and times out over a four week period. I have gotten an average weekly amount of hours for each staff member based on this but I need another equasion to work out their leave entitlement. It breaks down like this...
Average weekly hours x 5.6 x number of days working in this period (ie start date and end of financial year day count) divided by number of days in the financial year (ie 1/4/2015 - 31/3/2016 day count)
I'm just wondering of a way to do the day count based on me keying in the start date of the staff member (default 1/4/2015) and that access can work out the days in that financial year left and the actual days in that financial year.
It sounds simple enough but I want to get it to automate based on my start date.
I am trying to create a database to record employee sick leave and to indicate when an employee has surpass they allotted sick leave yearly.
I have the following tables and attributes
Employee Table ID#, FName, MInitial, LName
Status Table Category Allocation
Transaction Table EntryDate StartDate
EndDate TotalDays Certify? DaysBalance
Category indicates if a person is Temporary, Appointed or Substitute and Allocation indicates how many sick days is allocated per category yearly. I place both in the same table so when the status is selected the allocation is autofill
I am thinking of creating a form with the Employee Table info at top and a subform with the status and the transaction information.
I would like when a new year begins the sick days not used is discarded and a new allocation of days per employee is generated also when an employees status is change how to deal with that. When an employee used all of there sick days is there a way to have all those names display when you open the database.
Trying to modify this calendar database to track employee leave. I need the calendar form (frmCalendar) to show all employee leave on the calendar using the "Show All" command button on click event. This works if I specify a uID (UserID from employee table) in the code, but only for that specific employee. I need all employees at the same time up on the calendar so I can see if there is more than one employee off on any given day.
Events or leave is populated on the form through "Private Sub DisplayEvents" and the mdlCalendar module.
I am using Access 2010.I have a table that I am using to pull my data from other tables and a query.My table is called tblMyData.One of the field names is level1. This field points to another table, and gives the user the choices for states (examples California, Texas, Maine).
Another field name is level2. This field points to another table and give the user the choice for type of customer (examples Business, Consumer) The field name level3 points to a query. The query, qryFinalChoice matches up the choices based on level1 and level2.for example, the user can pick California for level1, business in level2 and California Widgets or Los Angeles Clothing store in level3 (plus about 20 other choices).
If the user picks Texas for level1, business in level2, business in level2 and Houston rugs, or Texas style restaurant in level3 (or about 15 other choices).
-I am recording 1 for California on level1, 2 for Texas and 3 for Maine in level1. -I am recording 1 for business and 2 for consumer on level2.
The query qryFinalChoice has all the combinations for state, business or consumer, and lastly business name or consumer name.
-qryFinalChoice has line1 to match up the choices for level1 in my table. -qryFinalChoice has line2 to match up the choices for level2 in my table. -qryFinalChoice has line3 to match up the choices for level3 in my table.
I do not want any of the Texas business names appearing when the user picks California, or vice versa.
My SQL in my tblMyData tab for level3 looks like this: Select line1 from qryFinalChoice where line1=1;
I am able to get all the line items where California is a selection.How do I change my SQL to pull all the line1 choices where I have selected from level1, and all the line2 choices where I have selected from level2 automatically based on my pulldowns?
I have a form with an image embedded in it. I want to be able to click on the image and leave a "dot" or "mark" where i have clicked, allowing me to click in multiple places on the image and leave multiple "dots"
I am assuming i need to crate a new shape each time i click, how is this done?
i would like to sum all the column fields in a tables column where an id is the same as the id in that table. I know you can use the SUM function in the select statement of your query.
But how can i set this sum result into a variable, so i can use it for calculation further on in the program?
Set mdb = DBEngine.OpenDatabase(StrDestDB) Debug.Print strSQL Call mdb.Execute(strSQL) mdb.Close Set mdb = Nothing DoEvents MsgBox ("The Geodatabase tables have been successfully appended!") End If Exit Sub
ErrorHandler: strTemp = Err.Description & " [Update_SystemTab]" Call MsgBox(strTemp, vbCritical, "Contact Help Desk") End Sub
