Selecting A Field Twice From The Same Table ?
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 Replies
ADVERTISEMENT
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
May 2, 2005
I have text boxes on a Form that I need to update with figures each week.
I have tried to have the entire field selected by right clicking with the mouse but I keep getting an error.
The coding I am using in the 'OnClick event' is:
Private Sub Ctl1_Click()
Me!R1.SelStart = 0
Me!R1.SelLength = Len(Me!R1)
End Sub
R1 being the txtName.
The errors I have received are Object doesn't support this property or method.
I have tried to replace the Ctl1 with R1 but still get errors.
Can anyone please shed some light on what I am doing wrong.
I am using Access 2002.
View 2 Replies
View Related
Sep 24, 2006
I have a form with bound textboxes used for entering currency values. When the form loads the fields are automatically populated with default values. When the user clicks on one of the fields, I would like to automatically select all of the data in the field, so as new values are entered, the defualt value is overwritten. I imagine that I should use the OnFocus property, but is there a command to highlight the data?
-Don
View 1 Replies
View Related
May 29, 2005
Hi All,
I am not sure if this is an easy one or not..
I have a field in a table named Stock_Alias. This field was originally set as a Number field. I have a combo box on a form, which contains the Stock_Alias numbers. When a number is selected, the remaining fields are shown in text boxes.
I have had to change the Stock_Alias field to a memo, to incorporate Numbers & Characters.
I cannot seem to hit on the right code to get the same results as i did when the field was a number field..
This was the code I was using to select the field as a number...
Private Sub Cmbo_Stock_Alias_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Stock_Alias] = " & Str(Me![Cmbo_Stock_Alias])
Me.Bookmark = rs.Bookmark
End Sub
View 4 Replies
View Related
Aug 8, 2006
I have one field with complete urlīs.
Example
http://www.rocketspanish.com/links_spanish_immersion.php
or
http://apps5.oingo.com/apps/domainpark/results.cgi?q=naco&cid=ca-dp-260com_nopop&url=http%3A%2F%2Fnaco.com%2F&hl=en&ac=s
I like to make a groupby but only from the left side up to ".com"
Is there a way to select only the text untill ".com"
Thanks
Wouter
View 2 Replies
View Related
Jan 2, 2007
I want to be able to perform an AND search on a field where both specified values must be present for an item to be selected.
I have a table with some properties of items. Each record in the table is for a specific item and one of its properties. Say for instance that I want to list the items that are described with both "red and green". Assume that there are entries for widgetA to widgetG with colors "red, blue, yellow, purple" and widgetB and widgetE also has the property "green" in addition to the others ... the query should return widgetB and widgetE only.
I tried to use MSAccess 2003 query builder but cannot get the AND result to work. I switched to an OR and verified that the two values I am searching for do exist for two items in my table.
Here is the SQL statement that did not work.
SELECT Properties.PropertyIndex, Properties.HerbOilLink, Properties.PropertyTypeIndex, Properties.PropertyDescriptionIndex
FROM PropertyType INNER JOIN Properties ON PropertyType.PropertyTypeIndex = Properties.PropertyTypeIndex
WHERE (((Properties.PropertyDescriptionIndex)=80 And (Properties.PropertyDescriptionIndex)=15));
View 3 Replies
View Related
Feb 2, 2005
Two tables, Contacts and Jobs
On the Jobs I need to select the ID and show the Last Name, but only enter the ID. This has been done okay.
How do I get the Last Name to auto fill in from the selection of the ID number?
View 6 Replies
View Related
Aug 27, 2013
I am having some trouble with an iif statement as selecting the field..I have two columns, one called [sdCounterpartyprice] and one called [bid]I want to select the [bid] if it's not equal to Null or 0, if it is equal to that then pick [sdCounterpartyprice]this doesn't work
Code:
CptyPrice: IIf ([bid] is null or = 0; [sdCounterpartyprice]; [bid])
View 3 Replies
View Related
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 1 Replies
View Related
Jul 23, 2006
I trying to create a query that looks at 2 field in one table and give me the resolves in a new field.
Example
Field 1 Field 2 New field
Hat Cap Cap
Dress Dress
Pants Slacks Slacks
Gloves Gloves
Socks Socks
Can you help?
View 4 Replies
View Related
Jun 3, 2015
I have a table of prices for commodity contracts, with my first field showing the dates the prices are from and the subsequent fields relating to the individual contracts (contract A, contract B, contract C, etc.)
I want to run a query that allows a date range to be selected, and a contract to be selected.
the first part I am pretty sure i know how to do (Between [Enter Period Start:] And [Enter Period End:]), but its selecting which contract i want this range to apply to that I am not sure how to do.
Can this be done in the same query? or would i have to do something like create a separate query for each contract and then use a form with a selection box that chooses which query to run?
View 7 Replies
View Related
Apr 16, 2014
I am working on a school database, today I am working for fee collection form and I am facing trouble.In attachment At the top right area the hidden field I would like here changes according to selecting Fee Type under Fee Detail. I want to achieve this,
If feetype is ABF then Hidden field = 1500110
If feetype is ASF then Hidden field = 1500120
If feetype is EXF then Hidden field = 1500130
If feetype is MTF then Hidden field = 1500140
If feetype is RBF then Hidden field = 1500150
How I can perform this ?
Table structure is
(tblstudents) Contain students data
(tblFee) Contain, Student Id linked with student table, month, year, amount, date, narratives and FeeType that looup values from FeeType tbl, this hidden field also in tbl fee.
The codes that are 1500100,1500120,1500130
These are necessary for GL entries that's why I must have it on my form. I had as well apply method of Default value but in that case I only present only a single value, but I want that in hidden field values take changes according to the fee type.
View 3 Replies
View Related
Aug 30, 2013
I want to calculate a field that is Sales*6+Salary, then in that same query I want to select ONLY the greatest Salary per employee. So for example
SELECT EmpId, MonthDate, Sales, Salary, [Sales]*6+[Salary] AS SalTot
FROM EmpTable S1
WHERE SalTot = (SELECT MAX(SalTot) FROM EmpTable S2 WHERE S1.EmpId = S2.EmpId);
Can I not select a value that has been calculated this query?
View 5 Replies
View Related
Mar 27, 2015
I am trying to create a query in Access 2010. I have 2 tables BU and Color. BU has employee ID, the BU the employee is assigned as of a date. The Color table has the employee ID, a color and a date. Based on the date in the Color table, I want to select the BU from the BU table. The query should bring in the BU that the employee was assigned to as of the date in the Color table.
For example, if emplid 12345 was hired into BU abc as of 12/31/2013. Emplid 12345 was transferred to BU xyz on 1/15/2015. In the Color there are 2 rows for 12345. One with a date of 12/31/2014 and one with a date of 2/15/2015. I want the query to return abc on the row with the 12/31/2014 date and xyz on the row that contains 2/15/2015.
Attached are the two tables. I have highlighted the expected results in yellow on the Color spreadsheet.
View 1 Replies
View Related
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
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
Jul 16, 2007
Hello, I'm working with SQL and databases in general for the first time, and was wondering: how would I select just the most recent entry for each device? my data looks [roughly] like this:
device1 data_1a 15.2.2000
device1 data_1b 15.2.2001
device1 data_1c 15.2.2002
device2 data_2a 15.2.2000
device3 data_3a 15.2.2001
device3 data_3b 15.2.2002
So what I'm looking for is:
device1 data_1c 15.2.2002
device2 data_2a 15.2.2000
device3 data_3b 15.2.2002
Thanks for any help you can offer!
-Eric
View 2 Replies
View Related
Apr 6, 2008
I have a field on frmOutpatient which is called EpisodeID.
I need to populate this field from a selection in a pop-up form.
The pop-up form (frmSelect) is a continuous form with a number of different EpisodeID numbers according to the date the patient was admitted. At the end of each record I want to put a command button (cmdSelect) and when pushed I want this SelectEpisodeID to be transferred to the field on the frmOutpatient.
Any ideas??
View 6 Replies
View Related
Oct 12, 2006
I'm working on a table which has a country field, but this field may contain a text string consisting of more than one country, eg "France, Belgium, Spain"
I want to run a query against the table to select records for any one country, but not sure how to do this.
Any suggestions please?
View 4 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
Apr 25, 2005
Trying to update a field in a record in my form after selecting from a comboox.
This an orders form with a record per row for order items. I select the product from a dropdown list which is populated by a dynamic query from the afterupdate event on another combo. When I do the selection access reports the following error:
Runtime error 3331
to make changes to this field, first save the record
Debug takes me to the line :
Me!product_id = DLookup("product_id", "products_table", myvar)
from :
[code]
Private Sub comboProd_description_AfterUpdate()
Dim strFilter As Integer
comboProd_description.Value = comboProd_description.Column(1)
myvar = comboProd_description.Column(0)
Me!product_id = DLookup("product_id", "products_table", myvar)
[end code]
I'm lost as to what to do (no such thing as beginners luck!!). I'm not even sure if the error is from the combo box or from the field that it is trying to update (product_id)
Thx for looking
Kev.
View 1 Replies
View Related
