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;"
I am attempting to filter records using a multi-select listbox, but all records are being returned. Here is my code.
Private Sub btnKeyboxCount_Click()
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
[Code] ....
I have several comboboxes on forms that are based on a query which checks the selection of an option group from other form (all are open in same tab container). However, the query stills prompt me for parameter, which tells me it's not pulling the value though there is a selection already made in the option group, and I'm sure the path is correct.
Does anyone know how to make sure that the query "remembers" the value? Would it be better done using a public variable? (I don't know if SQL is capable of accessing a public variable, though)
SQL:
SELECT tblCounty.CountyID, tblCounty.County
FROM tblCounty
WHERE (((IIf([Forms]![frmMainForm].[Form]![frmOption]![optOptions]=4,[tblCounty].[OfficeID]>0,[tblCounty].[OfficeID]=[Forms]![frmMainForm].[Form]![frmOption]![optOptions] Or [tblCounty].[OfficeID]=4))=True))
ORDER BY tblCounty.County;
(The logic is that if you select a office location, the combobox will then filter to only counties that office covers plus two generic placeholders in "4", or allow you to select all available counties if you choose so).
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
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
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
Hello all!
How can I create a query that the select depends on a variable that the user introduces?
would it be something like:
Select [@var] from table ????
it is not working. Example:
@var would be number from 1 to 31 (according to a day on the month that the user wants to view), then the result for it, is the number that the user introduced, instead of the vaule kept in that number...
Hope i make myself clear....
Thanks in advance!
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....
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 RelatedI 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.
sql = sql & " WHERE (((tblAccountBalance.DateLastPayment) < #" & reminderdate & "#))"
Im trying to select all the entrys in a table where the date of the last payment is before a variable 'reminderdate' this works fine if both dates are in the same month ie, DateLastPayment = 26/11/05 and reminder date = 30/11/05, but if the DatelastPayment is 26/11/05 and the reminder date is 01/12/05 then nothing is found.
What am I doing wrong?
I have two tables naming
1.) Criteria and 2.) IndicatorData.
Criteria table has (CriteriaID, CriteriaName,CriteriaValues) Fields. Example( CriteriaName-'Gender 'and values would be 'Male' and 'Female').
IndicatorData table has fields such as (IndicatorDataID, IndicName, Criteria_Code1,Criteria_Value1,Criteria_Code2,Crit eria_Value2,Criteria_Code3,Criteria_Value3).
I made the relationship between the IndicatorData and the Criteria as 1 to many where Criteria_Code1,Criteria_Code2 and Criteria_Code3 ...... up to Criteria_Code5 fields of IndicatorData table reference to the CriteriaID of Criteria table.
I made the sql qury to get all Criteria Names belong to a particular IndicatorDataID but it didnt work. my query is
"SELECT Criteria.tCriteriaName
FROM Criteria INNER JOIN IndicatorData ON (Criteria.nCriteriaId = IndicatorData.CriteriaCode5) AND (Criteria.nCriteriaId = IndicatorData.CriteriaCode4) AND (Criteria.nCriteriaId = IndicatorData.CriteriaCode3) AND (Criteria.nCriteriaId = IndicatorData.CriteriaCode2) AND (Criteria.nCriteriaId = IndicatorData.CriteriaCode1)
WHERE (((IndicatorData.nIndicatorId)=9));"
IS IT POSSIBLE TO GIVE THE RELATIONSHIP BETWEEN INDICATORDATA TABLE AND CRITERIA TABLE LIKE THAT OR DOES MY SQL QUERY HAVE SOMETHING TO DO WITH IT...?
answer would be greatly appriciated.
Thanx + Regards
Kosala
I have two tables naming
1.) Criteria and 2.) IndicatorData.
Criteria table has (CriteriaID, CriteriaName,CriteriaValues) Fields.
Example( CriteriaName-'Gender 'and values would be 'Male' and 'Female' and CriteriaName-'AgeGroup1' and Values-'<30','30-40','40-50','+50').
IndicatorData table has fields such as (IndicatorDataID, IndicName, Criteria_Code1,Criteria_Value1,Criteria_Code2,Crit eria_Value2,Criteria_Code3,Criteria_Value3, IndicValue).
Example-(IndicatorDataID-'ind001', IndicName='No. of participants', Criteria_Code1-'Gender',Criteria_Value1-'Male' and IndicValue-200.)
I made the relationship between the IndicatorData and the Criteria as 1 to many where Criteria_Code1,Criteria_Code2 and Criteria_Code3 ...... up to Criteria_Code5 fields of IndicatorData table reference to the CriteriaID of Criteria table.
I made the sql qury to get all Criteria Names belong to a particular IndicatorDataID but it didnt work. my query is
"SELECT Criteria.tCriteriaName
FROM Criteria INNER JOIN IndicatorData ON (Criteria.nCriteriaId = IndicatorData.CriteriaCode5) AND (Criteria.nCriteriaId = IndicatorData.CriteriaCode4) AND (Criteria.nCriteriaId = IndicatorData.CriteriaCode3) AND (Criteria.nCriteriaId = IndicatorData.CriteriaCode2) AND (Criteria.nCriteriaId = IndicatorData.CriteriaCode1)
WHERE (((IndicatorData.nIndicatorId)=9));"
IS IT POSSIBLE TO GIVE THE RELATIONSHIP BETWEEN INDICATORDATA TABLE AND CRITERIA TABLE LIKE THAT OR DOES MY SQL QUERY HAVE SOMETHING TO DO WITH IT...?
answer would be greatly appriciated.
Thanx + Regards
Kosala
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.
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")
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.
I'm very new to Access 2000. I'm working with 3 tables.
I finally got this sql statment to work:
SELECT [tlkp.Language].[LangID],
[tblRawData].[LangName],
[tblApplication].[AppID]
FROM (tlkpLanguage INNER JOIN tblRawData
ON [tlkpLanguage].[LangName]=[tblRawData].[LangName])
INNER JOIN tblApplication ON [tblRawData].[AppID]=[tblApplication].[AppId]
How can I change it to UPDATE?
I want to update the tblApplication.LangID = tlkpLanguage.LangID using the joins described in the select statement?
There is no LangName field in the tblApplication.
I have tried everything and I keep getting syntax errors.
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.
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?
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 RelatedWhen 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].....
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
I am trying to email a group of records in a table and with the code I have written, it just loops to the first record in the recordset and will not move down to the next record. Below is my code.
Code:
Private Sub TestOutlook()
Dim db As Database
Dim rstMail As Recordset
Dim appOutlook As Outlook.Application
Dim MailOutlook As Outlook.MailItem
[Code] ....
I have a date field in a form fed by an option group. Most of the options return the correct values, but I'm having a hard time with this:
If the inspection date (in another form) is in Jan Feb or March, then return the date 1 Apr of the inspection year. If not, then return 1 Apr of the following year.
Here is the trouble line:
Code:
Case 2
IIf (Month([Forms]![FrmGeneralInspection]![txtInspectionDate]) <= 3), Me![txtReInspectionDate] = DateSerial(Year([Forms]![FrmGeneralInspection]![txtInspectionDate]) + 1, 4, 1), Me![txtReInspectionDate] = DateSerial(Year([Forms]![FrmGeneralInspection]![txtInspectionDate]) + 2, 4, 1)
Code:
---------
If (Me.Variance_From_Budget < 99999 And Me.Variance_From_Budget >= -99999)
---------
This code is working when variance is less then -100,000, but does not work when variance is greater then -100,000.
I am using the code below to evaluate values for each transaction to determine the median:
Function MedianF(pTable As String, pfield As String, pgroup As String) As Single
'*******************************************
'Purpose: Return median value from a recordset
'Inputs: ? medianF("Values", "Industry") <enter>
'Output: results are currently incorrect
'*******************************************
Dim rs As Recordset
Dim strSQL As String
[code]....
The code generates median results based on the value but not the correct ones, as I check results in Excel using the Median function. I need to see the median for the transactions within the same industry and I do not know how to do that . Below are some sample transactions including the Industry, Value, mymedian (which the current results based on my code) and the expected median result (which I calculated in Excel based solely on the transactions listed here). Note that mymedian results listed in the 3rd column are determined using the complete data set (over 2000 transactions). I only included a few sample transactions below to show that I am currently getting 2 different median results: 44.62 and 50.82. Also be aware that I do not want to evaluate any zero value transactions, which I believe I am handling in the strSQL line above where I indicate <>0 . I need to see a Median result for each industry and the result should be the same for all transactions within the same industry.
Industry Value mymedian Expected Median
Aerospace 18.01 50.82 40.45
Aerospace 53.02 50.82 40.45
Aerospace 109.18 50.82 40.45
Aerospace 61.54 44.62 40.45
[code]....
This is how I am passing the values from the query to the module, passing the value of Period2 if Period 1 = 0, else passing Period1:
mymedian: IIf([Period1]=0, medianF("qry_My_Query","Period2","Classification_I ndustry"), medianF("qry_My_Query","Period1","Classification_I ndustry"))