Join Two Fields In Select Statement Results
Apr 12, 2006
I'm sure this is easy to do, but I'm not sure how to word my search criteria when trying to look for a solution.
Let's say that I have 2 fields in MyTable: FirstName, LastName.
I've been trying to use the following query, but obviously it does not work:
Select (FirstName + ' ' + LastName) as MyName from MyTable.
I would like to have the following data returned to me in the following format:
John Doe
Jane Doe
Jim Doe
Jill Doe
Thanks,
CRhodus
View Replies
ADVERTISEMENT
Feb 12, 2014
I have the following Select Statement:
SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],
[code]....
Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?
View 2 Replies
View Related
Oct 7, 2014
I have a database with several one-to-many relationships and a nested subform based off of those relationships. Relationships are as follows:
One Lender to Many Relationship IDs
One Relationship ID to Many Tax IDs
One Tax ID to Many DocumentsRequired
My forms are nested as follows:
Relationship ID form (contains info for Relationship ID and Lender)
-> Tax ID
-> DocumentsRequired
I believe I'm just overcomplicating this. But I have simple search box (an unbound text box with a command button), which is located on the top-level form for Relationship IDs. As you'll see above, that form only contains the fields for Relationship ID and Lender. However, users have to be able to search by Relationship ID, Customer Name, or Tax ID number, the last two of which are only available on the nested subforms. Currently I have the search box reaching out to grab results from a query. When I assign the results to the Me.RecordSource, it works perfectly except that it's in read-only format. The users have to be able to edit the results of their search. I'm not even sure I'm doing this in the easiest fashion. I would have preferred to just use the select statement to search through the subform, but I'm guessing my syntax was wrong because I never got it to work. Below is what I currently have.
Dim strtext As String
Dim strsearch As String
strtext = Me.SearchBoxTxt.Value
strsearch = "SELECT [Relationship ID] " & _
"FROM CustomerNormQuery " & _
"WHERE [Relationship ID] like ""*" & strtext & "*"" OR [Customer Name] Like ""*" & strtext & "*"" " & _
"OR [EIN/SSN] Like ""*" & strtext & "*"" " & _
"GROUP BY [Relationship ID]"
Me.RecordSource = strsearch
View 4 Replies
View Related
Sep 11, 2013
I use this
'WHERE ((OperationalRiskEventTable.DateReported)>=Forms!U pdateForm!UDateBegin And (OperationalRiskEventTable.DateReported)<=Forms!Up dateForm!UDateEnd)'
in a query by form.
The problem is that you have to enter a date in the between values for results to show. If I don't enter information into a different field such as Full Name but I enter in 40 into Age then everyone that is 40 years old will show. On the other hand if I enter 40 into the Age field but I leave the Date Reported fields empty then no results will show.
How can I change it so that I don't have to enter dates into the date reported fields for results to show?
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
Oct 26, 2005
Hi.
Select Materials.ID, Materials.AvailableUnits, Substances.Name, Suppliers.CompanyName FROM Materials Right Join Substances on Materials.Substance = Substances.ID
This works in joining the substances.name (from substances table) with the value stored in materials.substance (which is ID of substances.ID)
How can I add another Join to join Suppliers.CompanyName with suppliers.ID. The current Materials.Supplier is a key of Suppliers.ID which holds the value of Suppliers.CompanyName. I've tried this a million times and as soon as I insert a second join, no matter what format I get "syntax error in join statement".
PLEASE PLEASE help!
View 3 Replies
View Related
Dec 14, 2007
Hi,
I have a problem about SQL outer join
I have two tables
-----------
table one
id value1
1 a
2 b
3 c
-------------
table two
id value2
2 d
4 e
5 f
---------------------
I like get the results
id value1 value2
1 a 0
2 b d
3 c 0
4 0 e
5 0 f
* if no value put 0 .I am using SQL Server 2005 now
I try left join ,right join and full join, but I can not get the results.Please help me.
Thanks for help
Mark
View 1 Replies
View Related
May 23, 2005
Hi,
I am trying to do an update on a table with a query which has a join as follows but getting an error:
UPDATE RoleObjects
SET AccessType = 1
FROM RoleObjects
INNER JOIN Objects On Objects.ObjectID = RoleObjects.ObjectID
WHERE (RoleObjects.RoleID <> 1) AND (MID(Objects.ObjectName, 4, 2) = 'SR')
I tried to debug the code and found that the following part of the code with the Select clause works fine.
SELECT *
FROM RoleObjects
INNER JOIN Objects On Objects.ObjectID = RoleObjects.ObjectID
WHERE (RoleObjects.RoleID <> 1) AND (MID(Objects.ObjectName, 4, 2) = 'SR')
Do u have any suggestions.. ??
Thanks a lot for your time.
View 1 Replies
View Related
Jul 29, 2007
Hi, I have a query that looks like:
SELECT * FROM ((TBL_Properties INNER JOIN TBL_Property_Landlords ON TBL_Properties.Property_ID=TBL_Property_Landlords. Property_ID) INNER JOIN TBL_Landlords ON TBL_Property_Landlords.Landlord_ID=TBL_Landlords.L andlord_ID) INNER JOIN TBL_Agents ON TBL_Properties.Agent_ID=TBL_Agents.Agent_ID;
so far. But I need to join the results from another query which will be calculating SUM(Pay). Is it possible to INNER JOIN to a: (SELECT ....) nested query?
Sorry if this is hard to understand.
Thanks.
View 3 Replies
View Related
Feb 7, 2006
I've been struggling with this for awhile now. I've been trying to research a solution but I'm not even sure what to search for.I have the following table structure:tblContactsContact_ID (PK)First_NameLast_NametblBidPackBid_Pack_ID (PK)Bid_Pack_DescriptiontblDistributionListList_ID (PK)Bid_Pack_ID (FK) (1-M)Contact_ID (FK) (1-M)What I would like to do is create a query to display all of the contacts from tblContacts that are not associated with a certain Bid_Pack_ID. My end goal is to create a form where I enter Distribution List Information. I want a listbox on the left with all of the contacts from tblContacts and a listbox on the right with contacts that have been selected for a particular Bid_Pack. When the user double clicks an item on the left it should show up on the right and be removed from the left. I'm looking for the same functionality as when you use the design wizard and you choose fields to include/exclude.For example, let's say I want to add contacts for distribution list "A". My thought is the box on the left would show all the contacts from tblContacts that aren't selected for distribution list "A" and the box on the right would show all of the contacts that are part of distribution list "A". As you double click an entry it would perform the appropriate record creation/deletion and each box would be required.So back to my original question, what is the correct method (relationship, join, querydef) to select all of the contacts from tblContacts that haven't been assocatied with a particular bid pack?Thanks for any help you can provide. Even a nudge in the right direction would be appreciated.
View 2 Replies
View Related
Jan 16, 2008
I have table1 with fields 'project', field1, field2, field3 and table2 with fields 'project', fieldA, fieldB, fieldC. I need a query that has all fields from table1 and only fieldA from table2. What kind of join property should it have in order for me NOT to have duplicates? 'project' field in both tables has the same values.
As always, any help is greatly appreciated. Thanks.
View 2 Replies
View Related
Jan 22, 2008
Now that i have read this again, i think it could be summed up into one question...if i have a form based off a query with an outer join that has various duplicate records, is there a way to use the recordset in an if statement that says something like if this recordID = that recordID then dont show one of them...hence not showing the duplicate field data in the form.If you want a more specific description of the problem, read on, otherwise don't read on.Hi All,So I hope I can explain this ok....here goes....I have a search using dynamic queries: I have a form where the user can put in various information he wants to search to find a record. In this case it is searching for Hotels. So the user can search a country to see all of the hotels in that country. Also, the user can search an interest like Beach or Nature to see those hotels that apply. Obviously each hotel may have more than one interest so I have a 1-many relationship with a table called Hotels_Interests.The kicker, and you can likely already see why, is that the user does not have to fill out every search field. He may search Country&Interest, or just one or the other, or leave everything blank to see all hotels in the database. The results are simply ordered by HotelID or something like that in a form that is based off the dynamic query. The dynamic query is of course just based off the query i explained, but with criteria added in.The problem is with the query that i am basing this search off of. Right now it has the main Hotels table as well as the 1-Many table Hotels_Interests and even another that is 1-Many Hotels_HotelTypes (say All Inclusive, Resort, etc.). So this query has various 1-Many tables as well as the main Hotels. Now, if i fill in all of those fields in the search form, there will obviously not be any duplicates returned, which is super. But if i leave Hotel_HotelTypes search field blank, i will be returned with the same hotel twice or more times, which is my problem, because i want nice search results.I have heard of people using Union queries to get rid of duplicates but this obviously does not solve my problem as i do not want to just get rid of these entries. What i think i want is some VBA method or whatever of showing in my search results each HotelID that meets the search criteria only one time.Right now i have it working with If statements that say if the user has left a specific search criteria blank then base the search off a different query. This is obviously crazy and is only a temp fix. Now that i want three or more 1-many tables in my query, i would be talking about if statements for like 6 or more queries, insane.I apologize for the length of this, but i wanted to be perfectly clear. I feel like it should be not too hard, like using a record set for the form and not showing certain records or something, but i am not sure how to do it.Thanks so much. Dillon
View 4 Replies
View Related
Nov 22, 2005
I am running VBA that "deletes" a record by putting a 1 in a byte field called "deleted_b". But first it checks to see if the record is referenced anywhere in the database. So it may not actually mark the field.
There is no error if the "deletion" fails - the stored procedure runs regardless.
So I want the following code to run - basically it checks to see what the deleted_b value of the record referenced (Call_Attorney_ID) is. The problem is, I think I'm going about it incorrectly because I get a warning saying that a function or variable is expected in the IF statement - instead of the command to run the sql. Could someone tell me how to fix the code below to be the correct syntax?
Code:Dim mySQL As StringmySQL = "SELECT Deleted_B FROM dbo.MED_Attorneys_T WHERE Attorney ID = Call_Attorney_ID" If (DoCmd.RunSQL(mySQL)) = 0 Then MsgBox "Deletion failed. Record in use.", vbOKOnly Else Me.Requery MsgBox "Deletion has been completed", vbOKOnly End If
View 11 Replies
View Related
May 11, 2014
I'm fairly new to Access. 's various select queries containing useful and useless results. I want to create a select query that will pick out all the useful figures into a 1 row table that can then be pasted into Excel.
e.g Existing Select Query 1 returns 1 row showing Average Age, Average Price, Total rainfall
Existing Select Query 2 returns 1 row showing Average Weight, Average Salary, Total snowfall
Existing Select Query 3 returns *2* rows: It returns Distance from London, Hours daylight and population for Town A and Town B
I want a select query that returns 1 row showing (6 items):
Total rainfall, Total snowfall, Town A Distance from London, Town A Population, Town B Distance from London, Town B Population.
I've been able to handle getting Total rainfall and Total snowfall. But I cant figure out how to get Town A Distance from London, Town A Population, Town B Distance from London, Town B Population to appear in the same row of the same query results as Total rainfall, Total snowfall.
View 3 Replies
View Related
Oct 10, 2005
I am going to attempt to explain the problem:
I design a form and all my work is based on that form.
I have two tables which I join together as following.
The unfitex field is calculating using fields from both tables as shown below.
Dim db As Database
Dim rst As DAO.Recordset
Dim sqlstr As String
v = Combo37.Value
sqlstr = "SELECT postdecgor.N1, postdecgor.Age, postdecgor.UNFITpc, postdecgor.DECENTpc, postdecgor.HHSRSpc, [UNFITpc]*[sample]/100 AS UNFITex, postdecgor.DECENTex, postdecgor.HHSRSex, [Sample size].[Age dwelling], [Sample size].sample FROM postdecgor LEFT JOIN [Sample size] ON postdecgor.Age=[Sample size].[Age dwelling]WHERE ((postdecgor.N1)='" & v & "'); "
Set rst = db.OpenRecordset(sqlstr, dbOpenDynaset)
Set Form.Recordset = rst
I then set up some textbox which shows the results as following.
N1.ControlSource = "N1"
Age.ControlSource = "age"
UNFITpc.ControlSource = "unfitpc"
DECENTpc.ControlSource = "decentpc"
HHSRSpc.ControlSource = "HHSRSpc"
UNFITex.ControlSource = "numunfit"
UNFITex.ControlSource = "unfitex"
DECENTex.ControlSource = "decentex"
HHSRSex.ControlSource = "HHSRSex"
It works, I can see the results on the screen, however I would like to save the results into a table and I am not quite sure how to go about it.
Can you help??
Thanks for your help
View 1 Replies
View Related
Oct 27, 2005
I am trying to narrow down a list for a combobox by specifing one of the fields value on form as a acriteria
SELECT location.LOCNAME
FROM location;
WHERE (((location.LOCCLIINIT)=[location].[LOCCLIINIT]));
I get all list of locations' name instaed of location names with client code as displayed n current form.
View 1 Replies
View Related
Jan 15, 2005
I have a table with two fk's, one is oper_id and the other is oper_detail. Is it possible to write a select statement that will look in the first fk column to determine which table to get the second fk, oper_detail, data?
or I'll describe what I need to do and if someone has a better solution, I would appreciate input:
there are several different types of operations that can take place with each operation having wholly different data associated with it. Each type of operation then has many ways that it in itself can be conducted.
I created a table for each type of operation and populated them with relevant oper_details for that operation. I have another table that has oper_ID and oper_name. The log table, where this all comes together, has fk oper_ID and fk oper_detail (the row in the table oper_ID is referencing), which is supposed to tell me which way that particular oper_ID was conducted from the appropriate operation table. Select statement or change structure?
Thanks in advance.
View 1 Replies
View Related
Jan 11, 2005
help with select statement
I have this select statement need help to fix it up....
lsupervisor1 = "SELECT Supervisors.LicenseNumber " & _
"FROM Supervisors " & _
"WHERE ('" & Supervisors.LastName & ", " & Supervisors.FirstName & " (" & Supervisors.Area & ")" & "') = '" & Supervisor1 & "' "
Basically I have a table called Supervisors with the following columns
LastName
FirstName
LicenseNumber
Area
I have a form in which the user select supervisor by
lastName, firstName (area)
this is done by a combo box on the form with the row source of
SELECT Supervisors.LastName & ", " & Supervisors.FirstName & " (" & Supervisors.Area & ")" AS SupervisorsName FROM Supervisors;
Instead of that I want to enter the supervisor's License Number so I was using the select statement to find supervisor's License Number based on lastName, firstName (area)
I guess I am asking how to create a nested select statement in vba
View 2 Replies
View Related
Jun 28, 2006
I have two tables that look more or less like this:
Member:
idMember Name Surname
1 John Watts
2 Pete Pletz
3 Carl Bekker
History
idHistory idMember DataA DataB
1 2 AAAAAAAA BBBBBBBB
All I want is an access select statement that would show me the members that does not have any History entries, thus, members 1 and 3
View 1 Replies
View Related
Jul 13, 2005
I have multiple codes assigned to records in a table. I want to be able to say IIf [code] = "FMIX",[qty]*2.2046/7.1, IIf [code] = "Liqd", [qty]*2.2046 and everything else can just equal [qty]. I have tried this statment Expr1: IIf([family-code]="FMIX",[SumOfqty-on-hand]*2.2046 IIf([family-code]="LIQD",<[SumOfqty-on-hand]*2.2046>,[SumOfqty-on-hand]),[SumOfqty-on-hand]) and it doesn't work. Can anyone tell me what is wrong?
View 2 Replies
View Related
Jan 9, 2006
Hi, I have the following Select statement where basically i want to retrieve a customer's first and last name depending on the customer id that is already shown on my form (the customer id on the form is being displayed in a text box, CustomerIDText):
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnn1
Dim mySQL As String
mySQL = "SELECT CustomerID, CustomerFirstName, CustomerLastName"
mySQL = mySQL + " FROM CustomerTBL"
mySQL = mySQL + " WHERE CustomerID = '" & Me.CustomerIDText.Value & "' "
myRecordSet.Open mySQL
But when i go to open my form I get a run time error "Data type mismatch in criteria expression"
I think the part of my statement '" & Me.CustomerIDText.Value & "' is the problem, not sure why though. Any help would be appreciated, cheers.
Also once i've got my sql statement to work, how would i get an unbound textfield to display the first name, for example that i have retrived, would it be something like: textfield.value = mySQL FirstName ?
View 8 Replies
View Related
Mar 9, 2007
I am using the SELECT TOP 3 statement to select the top 3 values from a given data field. The SELECT works great, as long as there are no duplicate values in the TOP 3 values. For example if the top 3 values are 210, 202 & 199 they are selected correctly but if there three records that have the 199 value I am selecting 210, 202, 199, 199 and 199. Is there a way to only select the first 3 top values, i.e. the first three encountered?
Thanks Kevin ....
View 1 Replies
View Related
Mar 21, 2007
Does anyone know if its possible to perform a CASE Statement in a SELECT statement in Access and if so what the syntax is? Thanks in advance.
View 9 Replies
View Related
Oct 16, 2004
Hi I had been trying to use the "Select" statment of the sql in Access.But how do I actually execute the sql command?
For example,
Dim sql As String
sql = "select * from Name"
What is the next line i should add to execute this in Access?
Thanks
View 5 Replies
View Related
Jul 12, 2013
I'm trying to do a select statement and put it in a variable which i can then output to a text box. How do I get the value into a variable? I can't seem to get my syntax right
This is what I currently have
Maxvalue = "SELECT MAX[Record Num]FROM Joblog"
View 2 Replies
View Related
May 14, 2007
Can someone simplify my feeble scratching at a Case Select statement, please?
I have two fields in a table (True/False): IsActive and IsDefault
I would like to convert some If...Else...Then statements to a Case Select statement which details what should be done when any of the possible states of the two fields exist.
Semi-Psuedo example in the (subform) Form_Current Event:
With Me
If !IsActive = True And !IsDefault = False Then
Do Something on the Parent form
ElseIf !IsActive = True And !IsDefault = False Then
Do Something Else on the Parent form
ElseIf !IsActive = True And !IsDefault = True Then
Do Something Else Again on the Parent form
Else
Do Something Entirely Different
End If
End With
What blows my mind is the Select Case Expression bit. I can't figure out how to write this. Any help is greatly appreciated.
View 6 Replies
View Related
