Select ... (select ...);
Apr 5, 2006
I need to build a query that concatenates the values in my table as follows:
The table:
monkey
horse
pelican
zebra
The query output:
monkeymonkey
monkeyhorse
monkeypelican
monkeyzebra
horsemonkey
horsehorse
horsepelican
horsezebra
pelicanmonkey
pelicanhorse
pelicanpelican
pelicanzebra
zebramonkey
zebrahorse
zebrapelican
zebrazebra
I thought the following would do the trick, but it didnt ...
SELECT table.animal & (SELECT table.animal FROM table) FROM table;
I get an error that says that the subquery max. has 1 record :confused:
Help!! :o
View Replies
ADVERTISEMENT
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
Mar 24, 2005
I have a form with a textbox and a button, when I type a value in the textbox and click the button I want it to check a record in a table for a value if it is then, I will do step A if it is not there then I am going to do step B.
What is wrong with the code below place code in on click event of button:
View 2 Replies
View Related
Sep 2, 2004
I want to create a query (b) from a first query (a) without saving the first one in the Access database. Here is the example, which does not work:
SELECT a.call_date, COUNT(a.extension) AS call_in
FROM (SELECT DISTINCT call_date, extension
FROM tblabsenteeism
WHERE call_date=#9/1/2004# AND type In ('FMLA','Personal','Sick')) a
GROUP BY a.call_date
Is it possible in Access to Select .. from (select...)?
Any insight is greately appreciated.
Thanks,
Dan
View 5 Replies
View Related
Sep 15, 2004
Hi all
I have a combo box, based on a query, with four category options. When the form opens I want the combo box to show the category previously entered for the item, based on the categoryid in a text box on the same form.
The user still needs to be able to change the category to one of the other options in the combo box, if they want.
Please help
View 1 Replies
View Related
Jan 10, 2005
I have a table with the following data.
Code: uidWeight MaxWeight Description150 50Kg 260 60Kg370 70Kg480 80Kg590 90Kg6100100Kg7125125Kg8130130Kg+
When the user enter a weight of 131 or more I want to display the description of the maximum weight in the table, the same if the user enter a weight less than 50, then I want to display the minimum weight in the table. Any other weight and I want to display the description range of the weight => to the weight entered.
Anyone who can help me with the query?
Regards
View 4 Replies
View Related
Jan 17, 2005
I have one form.In this form i have one field with name Count
(interger)
I want with VB code to give value in this field and the value is return from
Select count(*) ....
How i can do it???
Thanks
View 5 Replies
View Related
Sep 3, 2005
Hello World!
I would like to know how is the syntax of the IN option of the SELECT statement. Iīm looking for this during a while and I didnīt find anything helpful!
Thanks and sorry for my beginer english!
Gleyson
View 1 Replies
View Related
Oct 17, 2005
Is this a valid statement please!
Code:mySQL = "SELECT * FROM tblWebContent WHERE lineOrder = '"& options &"'"
lineOrder is an Autonumber field!
options = 50
Run the above code, i get an error:
Quote: Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression.
View 2 Replies
View Related
Jul 24, 2005
Can I use a [system number] value stored in a table in a select case?
Select Case [system number] "Tble system numbers"
Case 42144
do this
Case 88754
do this
???
Jon
View 6 Replies
View Related
Jul 26, 2005
I have this code in an event property on a report and it is working great. However, I now need to do a second case based on which modules are down. Is it possible to have a select case within a select case? I have the select case for the systems now with in each system I need a select case based on modules?
Dim dbs As DAO.Database
Dim rst AS DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Select * From YourTable")
While Not rst.EOF And Not rst.BOF
Select Case rst![System Number]
Case 42144
' do this
Case 88754
' do this
Case Else
' do this
End Select
rst.MoveNext
Wend
rst.Close
Set rst = Nothing
Set dbs = Nothing
View 4 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
Feb 27, 2006
I have 2 tables with the following fields.
OrderInfo
PO Number
Status
CustomerInfo
Purchase Order Number
Customer Name
Customer Phone
Order Status
This is what I am trying to do:
get the Status for a the PO Numbers from the first table and update the Order Status field for those same PO Numbers in the second table.
This is how I am doing it currently:
I created a select query and saved it as ExistingPO. This is what ExistingPO looks like.
SELECT OrderInfo.*
FROM CustomerInfo INNER JOIN OrderInfo ON CustomerInfo.[Purchase Order Number] = OrderInfo.[PO Number];
and then I created another query which makes use of this first select query. The second query looks like this
UPDATE CustomerInfo INNER JOIN ExistingPO ON CustomerInfo.[Purchase Order Number] = ExistingPO.[PO Number] SET CustomerInfo.[Order Status] = ExistingPO.Status;
But I was wondering is there was a way to combine the first query and the second query into one query?
View 1 Replies
View Related
Mar 29, 2006
I have the following code where C is the listbox control
wCount = C.ListCount
Screen.ActiveForm.Painting = False
For wCounter = 0 To wCount
.Selected(wCounter) = True
Next
Screen.ActiveForm.Painting = True
This works fine - but is slow if a listbox has lots of records.
Is there a quicker way to select all items in a listbox?
(Both simple and extended - maybe this occasion will never happen that someone wants to select so many items that speed becaomes a factor, but I would like to have this covered just in case)
Thanks - Paul
View 2 Replies
View Related
Jun 9, 2006
I wondered if someone could help
I am querying a table using a select query. The data I am pulling from the table is in format:
Period Year Value
0 2006 1000
1 2006 100000
2 2006 500
3 2006 5000
4 2006 50000
5 2006 200000
In my query, I wish to seperate:
Period 0 + value
from
Periods 1-5 which should be cumulative.
Thanks
Paul
View 3 Replies
View Related
Mar 19, 2007
Hi,
I hope I posted this in the right forum. It didn't seem specific enough for the other ones.
I have a select statement in a form that reads as follows:
Select [Port of Origin] from [Port of Origin]
It works great. It does exactly what I want it to do.
I want to add another field to the statement but it is from a differnet table. If it was to stand alone it would read as follows:
Select [VendorName] from Container_Vendor_Information
How would I combine these two statements? I have tried various things can't get it to work. Any thoughts?
Thanks so much in advance.
Eddie.
View 2 Replies
View Related
Jul 14, 2005
I have 2 identic tables: T1 and T2 which contain only one field NR.
T1 contains the data:
1
2
3
4
5
6
7
8
9
10
11
12
T2 contains the data:
5
6
7
8
A
B
C
D
I want to select all the records found in T1, but not found in T2. So, I wrote the following SQL query:
SELECT T1.NR
FROM T1
WHERE NOT EXISTS
(select T2.NR
from T2);
Unfortunately, this query doesn`t return any record. And the strangest thing is that the query:
SELECT T1.NR
FROM T1
WHERE NOT EXISTS
(select T2.NR
from T2);
have the same effect like:
SELECT * FROM T1,
I mean it returns all the records of T1. I mention that the query was written in Access. What I have to do? Can anybody help me? What solutions do I have? I need a query, not a VBA code!
Thank you in advance!
View 10 Replies
View Related
Aug 9, 2005
I need to select two fields in a select DISTINCT query
Select tbl_Aplaws.AplawID, DISTINCT tbl_Aplaws.Level2 FROM tbl_Aplaws WHERE tbl_Aplaws.Level1 = 'Business' ORDER BY tbl_Aplaws.Level2;
anyone know how to make this work?
View 1 Replies
View Related
