Select Group By Having In (SELECT), HELP !
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 Replies
ADVERTISEMENT
Jan 17, 2007
SELECT contractid, min(RTRbalance) AS minRB
FROM ACHACT
GROUP BY contractid;
will have two culumn
What if I want to select all columns of ACHACT associated with minEB in each contractid group?
THX.
Jeff
View 1 Replies
View Related
Feb 9, 2015
how I would write a code that will recognize information by same “ID” and then only take the first 6 years of that information. Each row has an ID and a corresponding year with other information associated with it. The data looks similar to this:
ID Year Avg HR
JohnSmith 1988 .789 78
JohnSmith 1989 .854 85
JohnSmith 1990 .456 85
TomJones 1978 .465 56
TomJones 1979 .165 85
View 9 Replies
View Related
Jul 14, 2005
I have a query which I want to based on a group selected on a form using a selector button. EG if a person is a member of the accounts team that button would be selected. I could then just run the query to get members of the accounts team.
I am using the following SQL, but find when I run it "every" record is selected....
View 1 Replies
View Related
Nov 18, 2006
Hi I have 2 tables(tblA and tblB) and I want a query result(tblC) where ‘group by’ only one record per individual, with the total added together and the type starts with ‘01’. Below(tblA and tblB) are examples of the two table. TblC is the query result that I want. tblAName---Type---Amount---StatusJoe---01FAE---$10---NewJoe---01FBE---$10---NewLee---01FBE---$10---OldBob---30FBE---$30---NewtblBName---Type----Amount---StatusJoe----01FBE---$5------NewLee----01NAE---$20-------OldLee----30FBE--$30------NewtblCName---Type---AmountJoe---01FAE---$25---NewLee---01FBE--$30---OldI have tried linking/joining, adding SUM function, First, Group By, etc. but all the results came out to be all different. Also, am I support to add a ‘join’ of tblA to tblB on BOTH the NAME and TYPE? I am so confused! Please help.Thank you very much. Joe
View 1 Replies
View Related
Apr 17, 2005
I have a user who can't operate a mouse and I want to make it possible for him to select from an option group using only the keyboard.This is possible if a default button is stipulated, but can it be done when there is no default?
There is no event associated with the keyboard in an option group, only with the controls within it. I tried using the Got Focus event on an option Button, but entering the control doesn't cause the options to receive the focus, seemingly.
Any ideas, (they don't have to be fully formed) would be gratefully accepted.
View 7 Replies
View Related
Nov 8, 2014
I have a select clause, it executes fine, BUT the totamt is not calculated. The field is Amount. The code is not working.
strSQL = "SELECT Account, Dept, " & "Sum([Amount])" & " As [totamt] FROM TB201410 GROUP BY Account, Dept;"
View 5 Replies
View Related
May 24, 2007
I have several records to search for. I need to select just 1 record for each Tkt_no group using Tkt_id (max no).
Example, I only want to select
Tkt_NoTkt_IDTicket_ProblemTicket_StatusTicket_Finish_Time
114problem 1Done
210problem 2Done00/01/1900 20:00
32problem 3Ongoing00/01/1900 20:00
43problem 4Done00/01/1900 20:00
711problem 5Done21/05/2007 9:15
913problem 6Done22/05/2007 0:00
from below data
Tkt_NoTkt_IDTicket_ProblemTicket_StatusTicket_Finish_Time
14problem 1Ongoing00/01/1900 20:00
114problem 1Done
25problem 2Ongoing00/01/1900 14:30
26problem 2Ongoing00/01/1900 11:30
27problem 2Ongoing00/01/1900 10:15
28problem 2Ongoing00/01/1900 15:00
29problem 2Ongoing00/01/1900 8:00
210problem 2Done00/01/1900 20:00
31problem 3Ongoing00/01/1900 20:00
32problem 3Ongoing00/01/1900 20:00
43problem 4Done00/01/1900 20:00
711problem 5Done21/05/2007 9:15
913problem 6Done22/05/2007 0:00
Any ideas?
Appreciate the feedbacks.
View 4 Replies
View Related
Sep 26, 2013
I am trying to get the average of a select group of records within a query. It appears the davg function should give me what I need, however my query returns no results. Here is a sample of my data.
Item Cost Basis Group Cost
1HF20812 1HF208 6.17
1HF20816 1HF208 8.63
1HF20820 1HF208 9.44
Here is the davg string I am trying to use.
Group: davg("Cost","Cost Basis Group")
View 2 Replies
View Related
Sep 19, 2014
I have built a database that shows the purchase of items that have serial numbers, and so are unique. The database shows the purchase oe each item, and subsequent sale, including "Date In" and Date Out".
Each "item" however has a unique transaction reference (Stock No.) I would like to be able to show what items are currently in stock, and therein lies my problem.I am able to use a select count to find all the instances where the stock number there are two stock numbers (ie In and then Out), but have been unable to find a way to filter the records in a query, to show the stock currently held.
only Single instances of the "Stock No." appear in a table, as that would show the current stock held.I have tried numerous ways to achieve this but I have reached a dead end. I am not experienced in writing with SQL.
View 14 Replies
View Related
Mar 11, 2013
I need to create a simple database where I have a list of people, a list of groups and all I want to do is select which people belong to specific groups.
All I need is to create a form where I have a list of my people and a tick box next to the groups to show who belongs to which group.
View 5 Replies
View Related
Aug 23, 2014
I have a form with an option group to select subforms to be displayed .To save space on the main form I have added a subform (frmSubService) to the option group to display some of the data that was originally on the main form. The subforms data source is the same as that of the main form but only displays five fields.
After editing data in the subform frmSubService, if I then click on a field on the main form no problem, however if I click on an option button to view another subform I get a messagebox pop up with the following message;
The data has been changed. Another user edited this record and saved the changes before you attempted to save your changes. Re-Edit the record.
If I click OK,and without re-editing the record, I can open another subform.
I have checked and the data changes made on frmSubService, in both cases, has been saved.
How do I prevent the pop-up message?
View 3 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
Aug 22, 2005
Hi
I've got an Option Group with 3 options; Yes, No and Future.
I also have a range of Check Boxes; 1 for every day of the week.
When one of the check boxes is selected I want the Option Group to change to Yes.
Currently, the Option Group defaults to Future and No will only be available by manually selecting it. This doesn't need to be changed.
Any help will be greatly appreciated!
Cheers
View 2 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, 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
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 2 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
