Select Statment Problem
Jun 13, 2005
hello all,
i have a pretty simple problem related to ACCESS query.
I have two tables having similiar columns ( i had to do this to compare the values given to me, because both the table data came from different sources)
The tables named are
OFFICERS
token
full_name
division
EMPLOYEE
token
full_name
division
Now the table EMPLOYEE contains more than 5000 records and the table OFFICERS contains around 2400 records which are already present in the EMPLOYEE table.
Now I want to build a query that will return me all the records in the EMPLOYEE table that are not present in the OFFICERS table. The criteria for comparing 'full_name'. so the query should return me 2600 records that are not present in the OFFICERS TABLE.
I tried doing this
Code:" SELECT DISTINCT EMPLOYEE.full_name,EMPLOYEE.token FROM EMPLOYEE,OFFICERS WHERE EMPLOYEE.full_name<> OFFICERS.full_name"
But this query returns me records that is not present in the OFFICERS table as well as some more 1000 records which are present in both tables.
please do help
thanks a lot
View Replies
ADVERTISEMENT
Sep 7, 2006
I have typed this SQL in a query; however, access doesn't seem to like it. Does anyone have any idea why please?....
View 13 Replies
View Related
Dec 28, 2006
I am having issues with a multiple option on my If Statement. I do not want to create and If statement for each if I can combine what I want it to do.
If [cboType] = "Class II with 16K / 16K" Or "Class III with 16K / 16K" Or "Class III with 32K / 16K" Or "Class III with 32K / 32K" Or "Class IV" Then
The code just die when it is combined. Is OR and option.
Thanks
View 3 Replies
View Related
Oct 15, 2004
Dim mysql
mysql = "insert into [OwnerOccupierDetails] (owner_no,owner_name,owner_address,owner_phone,own er_confirmation,owner_reps,owner_reps_planner,owne r_herd_no,owner_area_status,owner_date,owner_detai ls,occupier_no,occupier_name,occupier_address,occu pier_phone,occupier_confirmation,occupier_reps,occ upier_reps_planner,occupier_herd_no,occupier_area_ status,occupier_date,occupier_details,source_name, details) VALUES ('" & Me.[txtownerNo] & "','" & Me.[txtownerName] & "','" & Me.[txtownerAddress] & "','" & Me.[txtOwnerConfirmation] & "','" & Me.[txtOwnerReps] & "','" & Me.[txtownerRepsplanner] & "','" & Me.[txtOwnerHerdNo] & "','" & Me.[txtOwnerAreaStatus] & "','" & Me.[txtOwnerDate] & "','" & Me.[txtOwnerDetails] & "','" & Me.[txtoccupierNo] & "','" & Me.[txtoccupierName] & "','" & Me.[txtoccupierAddress] & "','" & Me.[txtOccupierPhone] & "','" & Me.[txtOccupierConfirmation] & "','" & Me.[txtOccupierReps] & "','" & Me.[txtOccupierRepsPlanner] & "','" & Me.[txtOccupierHerdNo] & "','"
& Me.[txtOccupierAreaStatus] & "','" & Me.[txtOccupierDate] & "','" & Me.[txtOccupierDetails] & "','" & Me.[txtSourceName] & "','" & Me.[txtDetails] & "');"
DoCmd.RunSQL mysql
im writing this really long query in vb so it needs to go on two lines, but whats the syntax for going on the next line(theres a quote or something but i cant remember for carrying on from one line to the next)
View 6 Replies
View Related
May 2, 2007
Hello again all... Today's problem is as follows.... I have an "after update" combo box that is coded as "CurrentDb.Execute "uno", dbFailOnError". I have an SQL Query named "uno" that is showing the following: "UPDATE [Input] SET [Input].Loc_Lng = [Loc-1L].Combo8, [Input].Loc_ID = "1", [Input].Loc_Desc = "xxxx";" I am trying to have the combo box update the values from the combo box into a blank table. When I try to run this, it comes up with the error: "Too few parameters. Expected 1". Any idea what that means, and then how to fix?
View 2 Replies
View Related
Aug 15, 2006
Hi there!
I've got the following code on my database:
strcriteria = "[date] = #txtDay1.value# "
Set rst = dbs.OpenRecordset("tblPlanner", dbOpenDynaset)
rst.FindFirst strcriteria
The problem is when I run the code it have the following error:
Run time error '3077'
"syntax error in date in expression"
I've formated boths date field and txtday1 on short date.
Is there anyone that could tell me what wrong am I doing?
Thanks
Jonybravo
View 3 Replies
View Related
Sep 29, 2004
I am very new to ASP
I would like to learn how we can insert a data from a web form into MS access file.
This is the situation
Well i have a db.mdb file located at c:/www/db.mdb
db.mdb contain only one table that is named as students which in turn contains only three columne i.s StudentsID , FirstName and LastName
StudentsID is autonumber field in db file and FirstName & LastName are text fields
Now i have a self.asf file where i have a form with only two input fields
i.e
<form action=self.asp method=post>
<input type=text name="first" value="">
<input type=text name="last" value="">
<input type=submit value=submit name=submit>
</form>
Please let me know how i can write a SQL insert statement and database connection so that i can insert the form data into the db.mdb file
With Regards
View 2 Replies
View Related
Jan 12, 2005
I have a select statement that returns information into a sting
Then I try to insert this value into a table but I am not sure of the correct syntax. The SQL select statement works but not the insert into...
sRequirement1 = category1.value & ">" & subcategory1.value & ">" & Me!Label1.Caption
selectRequirementid1 = "SELECT Requirements.RequirementID " & _
"FROM Requirements " & _
"WHERE Requirements.RequirementName = '" + [sRequirement1] + "' " & _
"AND Requirements.Area = '" & Me.AArea1.value & "' " & _
"AND Requirements.Level = '" & Me.level1.value & "' "
DoCmd.RunSQL "INSERT INTO Hours ([RequirementID1]) VALUES( " & selectRequirementid1 & ");"
not sure how to treat selectRequirementid1 in Insert into statement
I tried the following yet non work...
'" & selectRequirementid1 & "'
'" + [selectRequirementid1] + "'
View 12 Replies
View Related
Nov 8, 2006
I was wondering if anyone knows how to insert more than one record at a time with a SQL statment? I have a form that asks the person how many records they would like to put into the system, this is a text box that they can enter a number.
My SQL for input one record is this:
[CODE]
SQL = "INSERT INTO tblTemp ([Name],[Number]) VALUES (TName,Len)"
DoCmd.RunSQL SQL
If the person wants to put in more than one I want the system to take the Number field and add 1 to it for ever record the person asks to be put into the system. the Name and Number fields will be that same except the Number field will increase by 1 for however many they choose to put in.
View 11 Replies
View Related
Nov 20, 2007
Hello guys and Girls
I am trying to setup a query that will return only the records in a date range that is user specified. In the Criteria of the date field I entered this
Between [Forms]![Report Switchboard]![txtStartTrend] And [Forms]![Report Switchboard]![txtEndTrend]
But the query does not work. Is the syntax correct? Both txtStartTrend and txtEndTrend are format Short Date.
Thanks for any Help
Anthony
View 1 Replies
View Related
Jun 22, 2005
problem with this insert statment in vba
Hi guys i got vba code that suppose to write table name ,column name , rquired ,feild type and feild size
to an external db. The first part of this code write tables naem to external db and it is working well.
I get the following error when i press the button on my form :
Code:Run-time error '424':Object required
and when i click on debut it points to this part with yellow collor.
Code: metadb.Execute " Insert Into SysColumns(tablename,columnname,required,type,leng ht) " & _ " Values ('" & TableDef.Name & "','" & Feild.Name & "'," & Feild.Required & ",'" & FieldType(Feild.Type) & "'," & Feild.Size & ")"
I be happy if some one help me fix this erro.Thanks
my complete code
Code:Sub InsertSystemCatalogPopulation(db As Database, metadb As Database)'''now locating all the non system tables in current db and then writing it'''to systables tableFor Each tbl In db.TableDefs''' excluding the system tables If Left(tbl.Name, 4) <> "MSys" Then ''' writing the tables name to systables metadb.Execute " Insert Into SysTables(TableName) Values ('" & tbl.Name & "')" End If Next tbl MsgBox (" All tables names coped to systables system cataloge ") '''############################################### #################################### ''' now we go find all non syste feilds in the current db and then writing ity ''' to syscolumns For Each TableDef In CurrentDb.TableDefs'''this if statment remove the system feilds If Left(TableDef.Name, 4) <> "Msys" Then For Each Field In TableDef.Fields metadb.Execute " Insert Into SysColumns(tablename,columnname,required,type,leng ht) " & _ " Values ('" & TableDef.Name & "','" & Feild.Name & "'," & Feild.Required & ",'" & FieldType(Feild.Type) & "'," & Feild.Size & ")" Next Field End IfNext TableDefEnd SubFunction FieldType(intType As Integer) As String Select Case intType Case dbBoolean FieldType = "dbBoolean" Case dbByte FieldType = "dbByte" Case dbInteger FieldType = "dbInteger" Case dbLong FieldType = "dbLong" Case dbCurrency FieldType = "dbCurrency" Case dbSingle FieldType = "dbSingle" Case dbDouble FieldType = "dbDouble" Case dbDate FieldType = "dbDate" Case dbText FieldType = "dbText" Case dbLongBinary FieldType = "dbLongBinary" Case dbMemo FieldType = "dbMemo" Case dbGUID FieldType = "dbGUID" End SelectEnd Function
View 1 Replies
View Related
Apr 29, 2008
Here is my CASE WHEN statement in SQL. What would the If statement be in MS access?
CASE WHEN LEN(Serial) = 9 THEN Serial
WHEN LEN(CAST(Serial as Varchar(255))) = 8 THEN '0'+Serial
WHEN LEN(CAST(Serial as Varchar(255))) = 7 THEN '00'+Serial
WHEN LEN(CAST(Serial as Varchar(255))) = 6 THEN '000'+Serial
ELSE 'Error'
END
AS NewSerial
Thanks for your help!
View 5 Replies
View Related
Dec 9, 2006
Hey All,
Im using MS Visual Web Developer with an Access Database, i am trying to enter a new row of data into a database via a select statment attached to a button_click event. the code i am using looks something like this.
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.AccessDataSource2.SelectCommand = "INSERT INTO [Tblissues] ([IssueDate], [IssueTime], [PlantLocation], [MachineName]) VALUES ('12/9/06', '3:00 PM', 'Gville', 'Snack2')"
End Sub
that being said. when i click the button, NOTHING happens, no error and no insert!!!
I did test the connection to the Database and its fine, i can do an update and i can read the data that exists in the database i just cant insert a new row.
This is not the first time i have delt with INSERT statments but this has me at my whits end and any help would be great.
Thanks
View 1 Replies
View Related
Feb 1, 2005
"ALTER TABLE MOVIE ADD ( MULTIPLEX NUMBER(3))"
while i'm trying to execute this query ...
Its displayed as 'SYNTAX ERROR IN FIELD DEFINITION'
Pls help me.... :confused:
View 1 Replies
View Related
Jun 1, 2006
I've got an unbound form which has a field that I need to relate to from a query, I've got no problems relating to specific records, but I've not quite cracked the ability to select all other records if the field is left at 0.
I've tried using in the query under the 'table' field (for selecting a specific group of people seated at a particular table)
Iif([Forms]![TablePlannerFrm]![TableSel]=0,>0,[Forms]![TablePlannerFrm]![TableSel])
Which I thought would evaluate a true/false with the two results coming from the two options, so if the Table selected on the unbound form was set to 0 then it would set the criteria to >0 (everything table 1 and up), or if anything else was selected other than 0, it would use the number from the unbound form from the TableSel field.
I'm getting blank results from both positive and negative results. I assume that I'm not using the Iif statement correctly, or that there is another way of approaching the problem that my headache is blocking :)
Thanks everyone
View 1 Replies
View Related
Nov 21, 2006
Anyone knows if I can predefine decimal places in a query statement? For example, when I run the select query below, I want the MedicalPremAmt to have two digits after the "." It should look something like this: 12.38
Thank you.
SQL = "Select [Barg Unit], First([Medical Option]) As Carrier, First([Medical Coverage Tier]) As Tier, Sum([Medical Premium Amount]) As MedicalPremAmt,Sum([Total Grant]) As TotalGrant,Sum([Health Allocation]) As HealthAllocation,sum([Medicare Allocation]) As MedicareAllocation FROM RetireeCensus Group By [" & Category & "];"
View 1 Replies
View Related
Jan 22, 2008
Hello All,
I'm trying to build a recordset joining two tables together.
I can get the join to work, but I need to use an "or" statement to
fill my rs.values
Here my statement so far:
mocommand.CommandText = "Select JUNK.[MS], JUNK.[CNT]," & _
" LINK.[FROM], LINK.[TO] from JUNK, LINK " & _
" where JUNK.[CNT] = LINK.[FROM] or " & _
" where JUNK.[CNT] = LINK.[TO]"
I'm getting an error with the syntax.
Basically, I'm trying to either find one matching value or another. I need to find the first matching value (junk.cnt = link.from), or the second one where (junk.cnt = link.to) using an "OR" statement.
Can anyone guide me as per the correct syntax?
....CementCarver
View 2 Replies
View Related
Apr 23, 2015
I have a multi slect list box (simple) and I need to find and select an item using vba - e.g., the bound column is the ID field and I need to select a specific ID (which will be different each time) as opposed to selecting the 100th record for example. How do I do this?
View 2 Replies
View Related
May 6, 2014
I have a subform containing a list of Funds and attributes such as Asset Type, Fund Manager, etc.
Currently, I have a textbox, where the the control source is set so that it will be updated with the Asset Type from the subform.
I also have an unbound combo box that contains a list of Asset Types queried from a table via row source, where user can select the Asset Type.
What I would like is when a record is selected from the subform, the Asset Type is selected on the combo box as a default value. User can select another Asset Type if required. How can I do this?
View 1 Replies
View Related
Aug 28, 2004
Hi,
is there any (reasonably simple) way to select or deselect multiple items from the List Box with individual clicks without using Ctrl key. Eg first click on an item would select it leaving all other items as they are, subsequent click on the already selected item would deselect it etc. I hope this is not too confusing and I would appreciate some help.
Thanks!
View 1 Replies
View Related
Aug 19, 2005
i have a multiselect listbox in my form.
The multiselectlistbox contains the names of different persons from tblUsers.
it's allready possible to write the id's of the names to another table (tblPresent).
But what I can't manage to do is re-select the values in another multiselect listbox. This multiselectlistbox is located on my editform.
I can display the values using a valuelistbox, but i need to see the non-selected items too..
hope someone can help me out
View 1 Replies
View Related
Sep 5, 2014
When I run the below code I am getting the error "End Select without Select Case" I figured it might be because I have the "End Select" before the "End With" however when I move the "End Select" after the "End With" I get the error "Loop Without Do".
Code:
Private Sub cmd_Update_Conditional_Codes_Click()
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
[Code].....
View 3 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
Jun 28, 2006
This is driving me nuts guys...
I need a query to show only those Company's that have a relationship to ALL Departments. These are the tables:
tblCompanies
CompanyID
tblUserDepts
UserDeptID
tjxCompanySubscriptions
CompanyID
UserDeptID
tjxCompanySubscriptions is a junction table that defines which Department has a subscription to a Company.
I need to know which Company's are subscribed by ALL Departments.
Probably simple, but not for me!
Scott
View 2 Replies
View Related
Jun 28, 2007
I have a confession to make - I am using select * in parts of my db.
Everything I read says not to do this, ie I should be selecting just the columns I need.
However what I need is a consistent "Presenation" of my data ie say Company Details - whatever kind of report or view is run, the company details must always contain the same data.
So I have a specific View_CompanyDetails which holds exactly the data my users want.
Literally 10s or Hundreds of other views or sps will then include the SELECT * FROM View_CompanyDetails for the user.
If I follow the general advice of never using SELECT * - if for example it beacme critical to have differnat data in View_CompanyDetails - I would have to go any manually change potentailly hundreds of other objects?
IN essence I would be explitly choosing may data in View_CompanyDEtails and then being forced into explicitly choosing the data again (redundantly cos View_CompanyDetails is the data I really want)
IN essence can I use SELECT *
?
It seems ridiculous not too? Or is there something else I need to think about?
Cheers
View 2 Replies
View Related
Aug 2, 2005
I'm having trouble with a select first query. I'm getting an error saying whichever item follows my select first statement (in this example TariffID) "is not included in the aggregate function." See example. Background is below.
SELECT First (SchedulePage) as FirstPage, TariffID, TariffCPUCID, ScheduleID, [C&E]
FROM Tariffs
WHERE (((FirstPage) Is Not Null) AND ((ScheduleID)=[Forms]!...))
ORDER BY FirstPage, Tariffs.[C&E] DESC;
Background:
I have a table where records are assigned a Page. I need a query to pull up only one Page per page number. (ie - there are ten Page 1, and five Page 2, but I only need one of each). Preferably, this would pull up the one that is Current and Effective ([C&E], yes I know it's bad naming, oops), but the purpose of this query is to help when something gets messed up, so I can't rely on C&E being correct, and thus need to bring up the page, even if there isn't one marked C&E.
Long story short, I thought that by sorting by C&E, I could pull up only the first record. Similarly, I tried using Min to pull up the lowest C&E, though this may not work if there isn't one marked C&E.
View 1 Replies
View Related
