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;
Results
NewTable
Column1 Column2 Column3
A...............................3.1.......
A...............................3.2.......
A...............................3.3.......
A...............................3.4.......
A...............2.1.......................
A...............2.2.......................
A...............2.3.......................
A...............2.4.......................
View Replies
ADVERTISEMENT
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 14 Replies
View Related
Nov 12, 2013
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".
View 14 Replies
View Related
Jul 8, 2005
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__|
EBSCO Table:
|_Title_|_Number_|_Location_|
|Generic|_123456_|__Storage_|
|_Pizza_|_948282_|__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.
Thanks,
Siena
View 2 Replies
View Related
Jul 22, 2013
Code:
strSQL = ("INSERT INTO " & strTable & " Select c.OrderNumber, c.Item, c.RepId, p.ProductNbr, p.Name, [tbl_LBP_Sales Location Num].[Rep Region Code] FROM CalculateTotal c, dbo_PartNew p, [tbl_LBP_Sales Location Num] WHERE ([Structure] like '*" & u & "*') AND ([ProductNbr] = '" & txtPartNumber & "')")
CurrentDb.Execute strSQL
It keeps giving me this error and I don't know why?...
I tried replacing the brackets around Rep Region Code with quotation marks as well as these things `` but kept getting the same error...
View 3 Replies
View Related
Apr 10, 2015
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".
View 3 Replies
View Related
Jun 28, 2006
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 ....
any help much appreciated.
View 14 Replies
View Related
May 1, 2014
I'm trying to create an access table (with a Primary key) that loads in the data from a Union SQL view.
Im hoping there is a way of achieving this? Ive tried an append and create table queries which copies the data but still no primary key.
I've created a blank table with the fields i want but cant think how to import the fields from the view into it?
Am i better off loading the view into excel?
View 2 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
Nov 3, 2014
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"
View 10 Replies
View Related
Sep 24, 2014
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
View 4 Replies
View Related
Jan 27, 2005
Hi there,
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...
How it's happen? How I solve it?
Thanks
View 8 Replies
View Related
Nov 2, 2005
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
Kindly help me out.
View 4 Replies
View Related
Jul 10, 2013
I am trying to select a few values for my report, can this be done in the report design view or do I have to make a query and take from that?
I am using access 02!
EDIT: I want to leave out all the values that are 0!
View 2 Replies
View Related
Jun 20, 2013
I am writing a very complex 'if statement' query expression. I need to create an alias column based on data from two data fields.
Here's the logic:
True: If [PP] = "WG" or "WS" or "WL" and If [GR] > 10 Then "Skilled" or
[GR] Between 6-9 Then "Semi - Skilled" or [GR] <6 Then "Unskilled"
False: "GS"
View 2 Replies
View Related
Dec 9, 2013
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;
View 3 Replies
View Related
Apr 26, 2006
Hi,
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?
Thanks in advance,
Ankie
View 2 Replies
View Related
Oct 5, 2005
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.
Any help would be appreciated.
View 1 Replies
View Related
Mar 6, 2015
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.
View 14 Replies
View Related
Apr 13, 2011
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.
View 14 Replies
View Related
Sep 14, 2013
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.
Using Access 2010
View 2 Replies
View Related
Sep 3, 2014
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?
View 1 Replies
View Related
Jan 30, 2013
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?
View 3 Replies
View Related
May 29, 2015
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?
I work with Access 2010
View 4 Replies
View Related
Sep 5, 2005
Hi there
I am hoping someone can give me a few pointers on how to get started on what I am trying to achieve....
I have 2 tables that form a 1 - to - many relationship.
I have designed a form that I wish to show fields from both tables.
Using ADO I would like to be able to open the form and be able to see my desired fields in the same form.
Can anyone provide any ideas on where I can get started.
Thanks for your help!!
View 3 Replies
View Related
Dec 3, 2005
Dear members,
I have two databases, Source and destination ;
strSrceDB = "C:WSS_DBRehabilitated_Water_Supply_Kulyob.mdb"
StrDestDB = "C:DBWSS_Khatlon.mdb"
I would like to append records to the destination table (Contractor_GIS) when there are no matching records in the source table (Contractor).
My code is as follows:
Private Sub CommandButton1_Click()
Dim strTemp As String
Dim strSQL As String
Dim StrDestDB As String
Dim strSrceDB As String
Dim mdb As DAO.Database
On Error GoTo ErrorHandler
strSrceDB = "C:WSS_DBRehabilitated_Water_Supply_Kulyob.mdb"
StrDestDB = "C:DBWSS_Khatlon.mdb"
'Make sure it is there
If Dir(StrDestDB) = "" Then
Call MsgBox(StrDestDB & " does not exist", vbOKOnly, "Aborting...")
ElseIf Dir(strSrceDB) = "" Then
Call MsgBox(strSrceDB & " does not exist", vbOKOnly, "Aborting...")
Else
strSQL= "INSERT INTO Contractor_GIS("
strSQL = strSQL & "System_ID,"
strSQL = strSQL & "Contractor_Name)"
strSQL = strSQL & "IN '" & StrDestDB & "' "
strSQL= strSQL & "SELECT Contractor.System_ID,"
strSQL = strSQL & "Contractor.Contractor_Name "
strSQL = strSQL & "FROM Contractor IN '" & strSrceDB & "' "
strSQL = strSQL & "Contractor_GIS IN '" & StrDestDB & "' "
strSQL = strSQL & "where Contractor.System_ID <> Contractor_GIS.System_ID;"
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
I get Syntax Error in "Insert into" clause.
Any help on this will be most appreciated.
Thanks in advance.
Irshad
View 2 Replies
View Related
