Selecting From Table
Jan 17, 2007
heya all- ive got sql max working on my table beacause i changed a field type to number and then sql selects the maxium value- but i now need to select a specific number along with its record from that same table.
This specific number will depend on whats been selected in a drop down box- but when im running the sql its comming up with this error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
Could this be to do with the field- beacuse i needed to change it to numeric type as i wanted to use sql max, and what way could i get round it???
Heres my sql command that im running to get a value:
Code:sub sqlCmd( )'==SQLSTATMENT=================================set objrs = server.createobject ("ADODB.recordset")dim sqlStatmentsqlStatment = sqlStatment & "SELECT * FROM [Areas] WHERE [Area_Code] = '"& Code &"' "objRs.OPEN sqlStatment,objConn,1,3'==============================================end sub
any suggestions??
View Replies
ADVERTISEMENT
Mar 10, 2006
I have an order table and depending on the the product grp it needs to then be able to select the appropriate size scale table.
e.g.
I order suits, and select a size grade A ( there are 7 size grades ) I then then to enter the size scale for that product. I am not sure whether to create 7 differant tables with the different size grades and link that to the table, if so how do i get it select the correct table.
please help...
H
View 1 Replies
View Related
Mar 10, 2006
Selecting the correct table
--------------------------------------------------------------------------------
I have an order table and depending on the the product grp it needs to then be able to select the appropriate size scale table.
e.g.
I order suits, and select a size grade A ( there are 7 size grades ) I then then to enter the size scale for that product. I am not sure whether to create 7 differant tables with the different size grades and link that to the table, if so how do i get it select the correct table.
please help...
H
View 2 Replies
View Related
Feb 5, 2007
I have a database spread accross a number of tables, I'll simplify it a bit here - there's questions, answers, respondents each as seperate tables.
Respondents includes
RID
Name
PostCode
Questions
QID
Question
Answers
AID
QID
RID
Answer
The answer table contains answers to all the different questions.
How can I query this to compare questions ? grouping answers to spot trends in the data
So I'd want to pull out the answers for questions 1 and 2 showing someone that answered question 1 with Yes and question 2 with No.
Any help would be much appreciated, or even a point in the right direction.
I've played with Crosstabs, multiple queries and pivot tables - none seem to be able to do this.
Thanks :)
View 8 Replies
View Related
Oct 23, 2004
Hi
I want select 2 table at same time
this is my code and my first table
Code:
strSQL = "SELECT TOP "&Cint(intTopCount)& " * "
strSQL = strSQL & "FROM T_WEBLOG "
strSQL = strSQL & "WHERE b_published = true "
strSQL = strSQL & "ORDER BY b_date DESC, b_time DESC;"
the above code work very good
but I want select second table
I added this code to above code
Code:
strSQL = strSQL & "SELECT T_IMAGES.* "
strSQL = strSQL & "FROM T_IMAGES "
so this is my code
Code:
strSQL = "SELECT TOP "&Cint(intTopCount)&" * "
strSQL = strSQL & "FROM T_WEBLOG "
strSQL = strSQL & "WHERE b_published = true "
strSQL = strSQL & "ORDER BY b_date DESC, b_time DESC;"
strSQL = strSQL & "SELECT T_IMAGES.* "
strSQL = strSQL & "FROM T_IMAGES "
but after I added second code to it
I get this error
============
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Characters found after end of SQL statement.
/functions.asp, line 71
===========
whats the problem
Please help me I need it
Thanks
View 5 Replies
View Related
Aug 15, 2006
This must be the easiest of things, but I haven't used access in a while in now in my new job I need to run the following query.
I have to select a number of postcodes from a single table to see how many are in there. For example select postcodes beginning with B, CV, DY, HR, ST, TF, WR, WS, WW. Then I need to write down how many records found with the above postcodes.
Also I may have to omit some too like omit CV12
Thank you in advance
View 1 Replies
View Related
Apr 25, 2008
Hope this works, The table is as follows, ish
Name1 y/n1 y/n2 y/n3 y/n4 y/n5
a...........y......y....n.......n.....n
b...........n......n....y.......n.....y
c...........y......n....n.......n.....n
d...........n......n....n.......n.....y
e...........y......y....y.......n.....y
f............n......y....n.......y.....y
g...........y......y.....y......y.....y
what i need to be able to do is take a form/query answer some questions, y/n1= y/n2= y/n3= ......
Then off the back of this be able to pull out the correct item from column name1.
But i don't need an exact match on all columns. If i am only interested in y/n2=y i need to display all matches
My head says the following If y/n2=y and y/n3=y Then Name1 = e and g
or another example if y/n2=y and y/n4=y and yn5=y Then name1 = f and g
if a y appears in more columns it doesn't matter but it has to be in all those columns i am looking at
can anyone suggest the simplest way forward on this.
View 1 Replies
View Related
Nov 17, 2004
Hi,
I have 13 tables in all. 2 are Area and Scope which have unique entries of areas and scope and the remaining 11 will have a primary key, Tag_No.
I want to select data from these 11 tables such that ALL the data will be pulled out but say table 1 has a column MOC and remaining 10 tables dont, then it will be a blank or null in those columns for the 10 tables. How can this be done? (I hope I am not confusing!!!)
Adwait
View 3 Replies
View Related
Apr 2, 2006
Hi All,
Any ideas on how I do the following.
I have a table with 1500 records in it.
I want to select at random 4 of these records and append them into another table along with some other data that the query will not pick up, in this case Pilot and Month.
I guess I am having to go the VBA route but dont have a clue on how to do it.
Cheers in anticipation.
Andy.
INSERT INTO Assignments ( flightcode, aircraft, depart, destin, pilotcode, [Month] )
SELECT Schedule.Flightcode, Schedule.Aircraft, Schedule.Departure, Schedule.Destination, [pilot] AS Expr1, [MONTH] AS Expr2
FROM Schedule
WHERE (((Schedule.Departure) Like "man*"));
View 3 Replies
View Related
May 9, 2005
Here's my problem:
I got a Table which contains software:
TblSoft:
ID |Software
-------------
1 |Office
2 |Winzip
3 |Etc.
I made a form with a dropdown-menu in which I can select the software and it stores it in another Table named TblPC.
It stores something allright. but not the text. It stores the ID Nr.
How do I get this to work that it puts the text inside the table instead off te ID nr?
I allready tried changing the properties off the listbox but it wouldn't help.
View 2 Replies
View Related
Jul 19, 2006
in tblquery i have a field date.
it its formated to =Date()
i also have two more fields
1- month
2- year.
what i want is to extract the month and year from the date field.
but the thing is, i was it to be visiable when you are in the table "view".
but i dont know how to format this.
View 9 Replies
View Related
Dec 6, 2004
Hi,
I have a form that runs a parameter query to search for university name and then displays 2 fields, university name and course name.
I am having difficulty with a search button that i have on the form called search_command; it is supposed to run the exact query as when you enter the form, it does this but displays the result in a dataheet, i want it to repopulate my 2 text label fields as mentioned above.
In addition i want then to be able to go to a specific record, select it and then press a button to append it to another table. i ahve not started this part yet
Can anyone please help ?
View 14 Replies
View Related
Apr 7, 2014
I have to make a multi-user manage database.All the usernames are sorted in one table and exported to listbox in Form.For each user there is a separate table with 3 columns where i must fill information from listboxes.The listbox (there are 4 listboxes, one for users select and 3 for different parameters) are located in MainForm where you select the information.My question is how can I select a specific username(table) from the listbox and fill the information from the other listboxes to the selected one?
The usernames table is named - Clients
each table for separate user is named client1, client2, client3 etc.
the form where you choose what to select is named Fill form.
The listbox for users is named - List71 And the listboxes for parameters are named - List75, List77, List79 Also there are 3 textboxes with calculated fields that i also need to insert in the specific user table.
View 14 Replies
View Related
Jan 13, 2014
I am looking for a way to generate a list of all fields within a table, have the end-user select which fields he/she wants to include, and then run the query. I am trying to create this within a form for a nice, easy to use GUI.
I am using Access 2010 on Windows. The fields I need them to select from are in one table, however there are many lookup (tblkp) tables related.
View 2 Replies
View Related
Dec 12, 2006
Folks,
can someone help me on this? I am not sure why the select query inside this procedure is not working. Here is the that routine:
Public Sub temp()
Dim strDocName As String
Dim strTableName As String
Dim strTbl As String
Dim aot As Access.AccessObject
Dim strSQL As String
Dim rpt As Report
Set rpt = CreateReport
strTbl = Forms!frmSearchBoilerGuar!cboTypeOfGuar
For Each aot In CurrentData.AllTables
If aot.Name = "strTbl" Then
strTableName = strTbl
End If
Next aot
strSQL = "SELECT tblProjts1.chrProjectName, tblProjts1.chrBlrPropNum, " & _
"strTablename.memGuranItem , strTableName.memLDs FROM tblProjts1 " & _
"FROM tblProjts1 LEFT JOIN strTableName ON" & _
"tblProjts1.intProjectId = strTableName.intProjectId"
rpt.RecordSource = strSQL
strDocName = "rpt"
DoCmd.OpenReport strDocName, acPreview
End Sub
Basically, I am trying to select a table name from the combobox and then use that table name for my query. Then I want to use that query as a recordsource for my report.
Any help is greatly apprecited.
Shan.
View 2 Replies
View Related
Aug 6, 2013
I am trying to use a combo box to control which table a data entry form will write data to. I want to create a form that has a combo box to select from a top level table that I will call "Stores". Once a "Store" is selected from the drop down, the next field on the form will be a data entry field. The data entered in that field will be written to the table selected by the preceding combo box.
So, basically I would have say Wal-Mart, Macy's, Sears, K-Mart and etc, listed in my "Stores" table. Once I select one of the stores from the drop down, I would then enter a "department" name in the data entry field and based on which store I selected from the previous combo box, the data would be written to that stores department table (which each store will have its own department table), e.g., WalMartDepts, MacyDepts, SearsDepts, etc...
View 1 Replies
View Related
Apr 7, 2014
I have several lookup tables in an Access database. These tables are used to populate fields in the main table and act as filters for viewing record subsets.I want to create a form that does the following:
1.) List the lookup table via a combobox.I was able to accomplish this with the following code:
Code:
SELECT MSysObjects.Name AS [Table Name]
FROM MSysObjects
WHERE (((MSysObjects.Name) Like "tblJob*") AND ((MSysObjects.Type)=1) AND ((MSysObjects.Flags)=0))
ORDER BY MSysObjects.Name;
2.) When a table is selected from the combobox, display the table in a subform for updating.
View 7 Replies
View Related
Sep 18, 2012
I have setup a simple access database consisting of 3 tables
Volunteer
Id (PK)
Name
Job
Id (PK)
JobName
Link
Id (PK)
VolunteerId (FK)
JobId (FK)
There is a one to many relationship between Volunteer and Link and a many to one between Link and Job
I want to create a form from which I'll select a Volunteer Name and then choose a JobName from a list box of options and have it create an entry in link with the id of the volunteer selected and the id of the job selected
So let's say there are 5 entries currently in the Job Table.. let's call the JobNames A,B,C,D and E.
In my form I select my name from the list (sourced from volunteer.Name) and then select B from the dropdown. My desired result would be a single entry gets created in Link with VolunteerId = 1 (representing me) and JobId = 2 representing the selection of B. Problem that I am having is that after I select B in the dropdown it creates a new record for B in Job and the Link table gets updated with that JobId as opposed to simply using the one that was already in the table.
View 1 Replies
View Related
Jul 19, 2013
In an Access 2007 module, is there a way of sending an email to a list of people stored as a list in a table in the database, rather than having to put all the names into the function?
I have a function I'm using to generate and send out an email to certain people, but the list is constantly growing so I'm looking for a better way to manage it! The main issue is having to kick users out of the database every time I need to update the recipients list... because it's stored in the code.
If I create a simple table containing all the names, how could I then ask it to use that instead?
This is the function I'm using currently - found on here and adapted to my own purposes
Syntax to use for the function: SendNotesMail "recipients", "Body Message", "Additional Text", "Subject"
Code:
Public Function SendNotesMail(strSendTo As String, strBody As String, strExtraText As String, strSubject As String)
'This public sub will send a mail and attachment if neccessary to the recipient including the body text and additional comments from the Active record
DoCmd.OutputTo acOutputReport, "REP09emailnotification", acFormatRTF, "x: endersgroup tendering databaseTenderUpdate.rtf", False
Dim Subject As String
Dim Attachment As String
[Code] ....
So I guess my question is: how do I get my recipients from a table into the strsendto?
View 5 Replies
View Related
Nov 5, 2014
I have an on click event to mail a report which works. I want to change the text to include data from a table.
I changed the code to include the field 'office' from the table 'Checks' but get an error saying 'Object required'.
Code is :
Private Sub cmd_mailreport_Click()
Dim office As Object
Set office = Checks.office
DoCmd.SendObject acReport, "checks", "PDFFormat(*.pdf)", _
"info@company.com", "", "", office & " Daily Check - " & Date, "Attached is the report for the office", _
True, ""
End Sub
View 1 Replies
View Related
Sep 16, 2013
I have with my database. It's holds cost data including purchase made in foreign currencies which need to be converted to GBP using the correct exchange rate so a variety of reporting & stats can be performed.I have a table called Costs within which there are 2 fields
Purchase Currency
Exchange Rate
I also have another table called Exchange Rates 13/14 within which there are 2 fields
Currency
Exchange Rate
When a value is entered in the Purchase Currency field on the Costs table (this is a look up field linked to Exchange Rates 13/14 so it shows the listed currency in drop down) I need the database to automatically populate the Exchange Rate column in Costs i.e. match the value in the Purchase Currency field to the Currency field in Exchange Rate 13/14 and populate with corresponding Exchange Rate from Exchange Rate 13/14.I have tried the following and none work:
SQL Tried
1.
UPDATE Costs
SET ExchangeRate = [Exchange Rates 13/14].[Exchange Rate]
WHERE Costs.[Purchase Currency] LIKE [Exchange Rates 13/14].Currency
2.
UPDATE
Costs
SET
ExchangeRate = [Exchange Rate]
FROM
[Exchange Rates 13/14]
INNER JOIN
[Exchange Rates 13/14]
ON
Costs.[Purchase Currency] = [Exchange Rates 13/14].Currency
3.
SELECT [Exchange Rate]
FROM [Exchange Rates 13/14]
WHERE Costs.[Purchase Currency] LIKE [Exchange Rates 13/14].Currency
I know it is possible to have a drop down for Purchase Currency which shows 2 columns (both Currency and Exchange Rate) you can then use the exchange rate figure for a calculated field. The problem I have is that I am importing data into the costs table from excel. In Excel I can only have 1 value in the Purchase Currency column on the upload template. If I just have Euro in this column the database does not match it to the Euro in the Purchase Currency drop down and also store the correct exchange rate.
Or is the alternative to put this into the calculation of GBP Unit Cost where this somehow matches the Purchase Currency in the Costs table to the Currency field in Exchange Rates 13/14 tables and uses the appropriate exchange rate from Exchange Rates 13/14 to calculate GBP Unit Cost in Costs table.
View 4 Replies
View Related
Aug 14, 2015
I have staff table which is a lookup field on the phone call table.when the phonecall form opens I want the employee field to auto fill in the employees name from the lookup field according to there security level.so something like this
On Open
Select Case Forms!frmLogin!cboUser.Column(4)
Case X = 4'the employees security level'
Case X = Insert Table_tblPhoneCalls!EmployeeID"4" into Form_frmPhoneCalls!Employee.
End Select
of course this is a syntax error as I do not know the correct code words.
View 1 Replies
View Related
Nov 13, 2007
Hello All,
I have a small problem which I feel I should be able to solve but the hours of trying are proving me wrong!
From a choice of 16 events, competitors can enter as many as they like providing they are eligible. For each event they receive performance marks which are duly stored in a table. Some compete in 1 event others in 5 or 6. (I do not have a control showing ‘number of events entered’ and have tried several options, all unsuccessful, to create a ‘count’ of events entered. Is there a way?) My main problem is, from each individuals’ records, how do I select only the highest 3 marks from the classes each individual entered.
I’d be very grateful for any advice/guidance.
Bomac.
View 4 Replies
View Related
Apr 20, 2006
I have a table with customer details, probs over 3000 customer, and i need a method of selecting the customer in a form by their surname. Currently i have a combo box, but as you can imagine, this is not practical with so many records. anyone think of any other way to do it?
View 4 Replies
View Related
Apr 10, 2006
Hi,
I have a query that gives an out put of 'worst offenders'. these offenders are in order of on field ascending (number of entries made per week) and another field descending (total sales).
It is by the combination of the sorting of these fields that we have an ordered list of offenders.
For my report i want to only see the top 10 records of this sorted list.
so for this selection i simply want to select the [B]first 10 records [B]of the ordered list. I cant make a selection by any one particular field(as explained above)
i have looked into the 'select top' functions and 'dfirst', but I dont think they'll work for me.
Any ideas?
Slighlty confused,
Rosxx
View 3 Replies
View Related
Jan 17, 2007
Can anyone tell me why sometimes when i'm using access all of a sudden when i click in cells, control boxes etc that i select everything and i cant position the mouse with a string of text with selecting all of it.
VERY frustrating.
cheers,
Spinkung :confused:
View 2 Replies
View Related
