I've created a query where I am searching a database of job titles for contacts that are considered senior management.
One criteria I've entered into a query is as follows:
Like "*Vice*"
Which is meant to identify anyone with "Vice" in the title. The problem is I have noticed this also returns records with "service". So can I edit this query to specify that the "V" must be capitalized? I think this would serve.
SELECT idAuswertung, fiPrüffeld, fiAuswertungMaster, fiAuswertungTyp, AuswertungName, AuswertungSort, AuswertungActive, (SELECT COUNT ([idDiagramm]) FROM tkey_Diagramm WHERE fiAuswertung = [idAuswertung] ) AS AnzDiagramm
FROM tkey_Auswertung;
Why can I not edit the results when I use the Subquery above?
Without the Subquery all works fine.
Christoph
I have a query that gives me data from a table, and I then export it as text to Excel. In Excel I do some edits, insert columns with special characters as delimiters, then copy it into a JScript data set.
This all takes some time, and has a number of steps that need to be done in a certain order - prone to errors on my part...Is it possible to use VBA to make the changes in the query to reduce the manual steps perhaps even to remove Excell from the process entirely and just save the query to a text file?
The query has 5 fields:
TxtProductCode Unique to each item
curCurrentPrice The listed price
txtHeading The Main description of the item
txtDetails Additional details
txtVender A name of who has the item
In Excel I change all instances of '"' to ' inch', then insert the delimiter fields, and the final line for the item looks like this:
["TxtProductCode","curCurrentPrice","txtHeading","txtDetails","txtVender"],
Can VBA do this, or even read the query and modify each record then write it to a text file?
i've got a Form that contains Subform with an embedded Query that contains 2 tables only (Payments & Invoices) the Join properties between them show all records from payments and what matches it from Invoices where the joined fields are equal (Invoice no).. So, when i enter certain data in the main form the Subform show the results for it from Payments table and only one field needed from the Invoices table !!
The problem is.. i can't edit or add any data in the Subform results with the previous setting, but when i completely remove the Invoices table from the embedded Query then swift to the Form and it's Subform.. i become able to edit and add data in the Subform easily... !!
So, how to enable the edit/add in the Subform with the 2 tables in the embedded Query ?! Cause it's really needed to show that field from the Invoices table.
I am trying to run a query to show who has not paid for an item. I have a column in my table labeled "check amount". It is set as currency under data type. The default value that is put in if I have not entered a value, is $0.00. How do I run a query that excludes the $0.00 value?
Thanks
Wendy
Hello I am new to this site as well as MS Access and SQL. So any help would be appreciated. Sorry if this is too drawn out. I have this query that returns the previous business day's(Monday through Friday) number of docs sent from my department to another department. I would manually go into the SQL view in Access and change the date to yesterday. The easy part was having the code do the previous day. The problem comes Monday morning when the code does the previous day. It will do Sunday which will return nothing since I really want Friday's numbers. I am trying to figure out how to take into consideration if the query is run on Monday morning and to automatically choose Friday. I checked out DayOfWeek() and Case When condition statements but really unsure about syntax and whatnot. Here is the SQL view of the query...
SELECT Count(WS_FORM_TEMP.FORM_TEMP_ID) AS CountOfFORM_TEMP_ID, WS_FORM_TEMP.FILENET_DOC_ID, WS_FORM_TEMP.LINKED_FILENET_DOC_ID, WS_FORM_TEMP.SSN, WS_FORM_TEMP.FORM_TYPE, WS_FORM_TEMP.RECEIVED_TYPE, WS_FORM_TEMP.DET_ACCT_NUM, WS_FORM_TEMP.DET_ACCT_NUM_CORR, WS_FORM_TEMP.SEQUENCE_NUM, WS_FORM_TEMP.CLAIM_EFFECTIVE_DATE, WS_FORM_TEMP.EMPLOYEE_START_DATE, WS_FORM_TEMP.EMPLOYEE_END_DATE, WS_FORM_TEMP.VACATION_PAY_FLAG, WS_FORM_TEMP.RETIREMENT_BENEFITS_FLAG, WS_FORM_TEMP.SEVERANCE_PAY_FLAG, WS_FORM_TEMP.SIGNED_RELEASE_FLAG, WS_FORM_TEMP.EMPLOYER_CONTACT_NAME, WS_FORM_TEMP.EMPLOYER_CONTACT_PHONE, WS_FORM_TEMP.EMPLOYER_CONTACT_PHONE_EXT, WS_FORM_TEMP.EMPLOYER_CONTACT_FAX, WS_FORM_TEMP.DUE_DATE, WS_FORM_TEMP.COMMENTS_FLAG, WS_FORM_TEMP.ATTACHMENTS_FLAG, WS_FORM_TEMP.POSTMARK_DATE, WS_FORM_TEMP.RECEIVED_DATE, WS_FORM_TEMP.CREATED_DATE, WS_FORM_TEMP.WAGE1_END_DATE, WS_FORM_TEMP.WAGE1_WAGE, WS_FORM_TEMP.WAGE2_END_DATE, WS_FORM_TEMP.WAGE2_WAGE, WS_FORM_TEMP.WAGE3_END_DATE, WS_FORM_TEMP.WAGE3_WAGE, WS_FORM_TEMP.WAGE4_END_DATE, WS_FORM_TEMP.WAGE4_WAGE, WS_FORM_TEMP.WAGE5_END_DATE, WS_FORM_TEMP.WAGE5_WAGE, WS_FORM_TEMP.WAGE6_END_DATE, WS_FORM_TEMP.WAGE6_WAGE, WS_FORM_TEMP.WAGE7_END_DATE, WS_FORM_TEMP.WAGE7_WAGE, WS_FORM_TEMP.WAGE8_END_DATE, WS_FORM_TEMP.WAGE8_WAGE, WS_FORM_TEMP.SEP_LAIDOFF_FLAG, WS_FORM_TEMP.SEP_LAIDOFF_RECALL_DATE, WS_FORM_TEMP.SEP_QUIT_FLAG, WS_FORM_TEMP.SEP_DISCHARGED_FLAG, WS_FORM_TEMP.SEP_SUSPENDED_FLAG, WS_FORM_TEMP.SEP_CONVICTION_FLAG, WS_FORM_TEMP.SEP_PERFORMANCE_FLAG, WS_FORM_TEMP.SEP_STRIKE_FLAG, WS_FORM_TEMP.SEP_ABSENCE_FLAG, WS_FORM_TEMP.SEP_ASSURANCE_FLAG, WS_FORM_TEMP.SEP_EMPLOYED_FLAG, WS_FORM_TEMP.FORM_ID, WS_FORM_TEMP.STATUS
FROM WS_FORM_TEMP
GROUP BY WS_FORM_TEMP.FILENET_DOC_ID, WS_FORM_TEMP.LINKED_FILENET_DOC_ID, WS_FORM_TEMP.SSN, WS_FORM_TEMP.FORM_TYPE, WS_FORM_TEMP.RECEIVED_TYPE, WS_FORM_TEMP.DET_ACCT_NUM, WS_FORM_TEMP.DET_ACCT_NUM_CORR, WS_FORM_TEMP.SEQUENCE_NUM, WS_FORM_TEMP.CLAIM_EFFECTIVE_DATE, WS_FORM_TEMP.EMPLOYEE_START_DATE, WS_FORM_TEMP.EMPLOYEE_END_DATE, WS_FORM_TEMP.VACATION_PAY_FLAG, WS_FORM_TEMP.RETIREMENT_BENEFITS_FLAG, WS_FORM_TEMP.SEVERANCE_PAY_FLAG, WS_FORM_TEMP.SIGNED_RELEASE_FLAG, WS_FORM_TEMP.EMPLOYER_CONTACT_NAME, WS_FORM_TEMP.EMPLOYER_CONTACT_PHONE, WS_FORM_TEMP.EMPLOYER_CONTACT_PHONE_EXT, WS_FORM_TEMP.EMPLOYER_CONTACT_FAX, WS_FORM_TEMP.DUE_DATE, WS_FORM_TEMP.COMMENTS_FLAG, WS_FORM_TEMP.ATTACHMENTS_FLAG, WS_FORM_TEMP.POSTMARK_DATE, WS_FORM_TEMP.RECEIVED_DATE, WS_FORM_TEMP.CREATED_DATE, WS_FORM_TEMP.WAGE1_END_DATE, WS_FORM_TEMP.WAGE1_WAGE, WS_FORM_TEMP.WAGE2_END_DATE, WS_FORM_TEMP.WAGE2_WAGE, WS_FORM_TEMP.WAGE3_END_DATE, WS_FORM_TEMP.WAGE3_WAGE, WS_FORM_TEMP.WAGE4_END_DATE, WS_FORM_TEMP.WAGE4_WAGE, WS_FORM_TEMP.WAGE5_END_DATE, WS_FORM_TEMP.WAGE5_WAGE, WS_FORM_TEMP.WAGE6_END_DATE, WS_FORM_TEMP.WAGE6_WAGE, WS_FORM_TEMP.WAGE7_END_DATE, WS_FORM_TEMP.WAGE7_WAGE, WS_FORM_TEMP.WAGE8_END_DATE, WS_FORM_TEMP.WAGE8_WAGE, WS_FORM_TEMP.SEP_LAIDOFF_FLAG, WS_FORM_TEMP.SEP_LAIDOFF_RECALL_DATE, WS_FORM_TEMP.SEP_QUIT_FLAG, WS_FORM_TEMP.SEP_DISCHARGED_FLAG, WS_FORM_TEMP.SEP_SUSPENDED_FLAG, WS_FORM_TEMP.SEP_CONVICTION_FLAG, WS_FORM_TEMP.SEP_PERFORMANCE_FLAG, WS_FORM_TEMP.SEP_STRIKE_FLAG, WS_FORM_TEMP.SEP_ABSENCE_FLAG, WS_FORM_TEMP.SEP_ASSURANCE_FLAG, WS_FORM_TEMP.SEP_EMPLOYED_FLAG, WS_FORM_TEMP.FORM_ID, WS_FORM_TEMP.STATUS
HAVING (((WS_FORM_TEMP.CREATED_DATE)=to_char(sysdate-1,'DD-Mon-YYYY')) AND ((WS_FORM_TEMP.RECEIVED_TYPE='1') or (WS_FORM_TEMP.RECEIVED_TYPE='2')));
Thanks in advance.
Hi all, I have a query with a field with dates, what is the code I have to put to have as result all the records where the date is empty?
Thanks
Marco
I have made a query from a table to count something.
Now, I dont know how to exclude the null value to the new query.
example:
Query Name: School
Field: Sch_No, Sch_Name, Subject1, Sum1, Subject2, Sum2, Subject3, Sum3, Subject4, Sum4, Subject5, Sum5
After run this query, there is a field(Subject4) has a null value.
Before I make a nice report, I have to exclude Subject4 & Sum4. And this will make Subject5 the fourth subject without displaying Subject4 & Sum4.
I hope anybody can help me..i dont know how
I need to exclude non-integers in my query and have forgotten how to do this.
What i need to put into the criteria field within my query to to this?
I made a query to calculate the average of a column, suppose that I have 5 records in that column (46,35,0,19.3,12), when the query calculate the average it sums the total of the column and divide by 5 (that's 112/5 = 22.4), what I need the query to do is to divide by 4 because one record is zero (that's 112/4=28). I put in the criteria the following (Not Is Null And <>0), yes this will not show the column that has zero but it still divide by 5.
View 6 Replies View RelatedHi,
I've been searching the forums, and I find a bunch of posts about the subject, but I just can't seem to really find what i need.
What I'm looking for is how to construct a query, that will exclude any entries in my table that might fall on a Saturday or Sunday.
What is the easiest way to do this?
I'm going to be using the results of this query in a Report and DAP.
Thanks
Chris.
I have a simple dbase that lists individual staff members' qualifications, skills and hobbies.
One person may have 20 qualifications and another may have 1 or none.
I have a separate field for each qualification.
Is it possible to write a query that only returns fields that are not null in each record so that the subsequent report is not full of blanks? I know I can use Can grow - Can shrink on the report but I'd rather eliminate blanks via a query if I can.
I have data in a field and I want to write a query to return all the values except the ones that have 2014/* in them. So I want to see the blank ones as well as the one with 2013. So I basically want to return B, C, D, E, F, G, H, J, K, L.
I tried the NOT LIKE function but couldn't get it to work.
I have a difficult problem (because of my own capabilities). I have a table (tblFoods) which includes a number of food with their analyzes. I have a second table (tblExclusiveFoods) which is connected with a third table (tblCustomers) with one-to-many relation. Namely, o tblCustomers is the "One" and tblExclusiveFoods is the "Many".
Into table tblExclusiveFoods there is a field that acts like a ComboBox and gets data from the tblFoods such as drop-down-menu. So, using the combo box i can select the foodID from tblFoods and then showing into exFood field which is a part of the tblExclusiveFoods fields. When i collect the foodID's from the tblFoods, i would like to to build a query which will exclude these foods from the tblFoods.
I have been successfully using the following statement in Access 2010 to retrieve data from a large csv relational database:
SELECT [1995_1].RPT_REC_NUM, [1995_1].PRVDR_NUM, [1995_2].WKSHT_CD, [1995_2].LINE_NUM, [1995_2].CLMN_NUM, [1995_2].ITM_VAL_NUM
FROM 1995_1 INNER JOIN 1995_2 ON [1995_1].RPT_REC_NUM = [1995_2].RPT_REC_NUM
GROUP BY [1995_1].RPT_REC_NUM, [1995_1].PRVDR_NUM, [1995_2].WKSHT_CD, [1995_2].LINE_NUM, [1995_2].CLMN_NUM, [1995_2].ITM_VAL_NUM
HAVING ((([1995_2].WKSHT_CD)="A000000") AND (Not ([1995_2].LINE_NUM)="09500") AND (([1995_2].CLMN_NUM)="0100" Or ([1995_2].CLMN_NUM)="0200"))
ORDER BY [1995_1].RPT_REC_NUM, [1995_2].LINE_NUM;
This query returns one long column of line numbers (LINE_NUM) representing the itemized salary (CLMN 0100) and non-salary expenses (CLMN 0200) and a total for each of the organizations represented in the data base. My question is: is it possible to modify the query so that it returns only organizations whose data passes that following test that checks to see if the itemized line numbers 00100 to 10099 equal the total for each organizations line 10100: SUM(LINE_NUM 00100:10099)=SUM(LINE_NUM 10100:10100).
I have tried several times to upload a sample file but have been prevented from doing so because of a missing security token. I have communicated this to the administrator.
I have query that runs ok but will not let me edit the data when I run it. I have 4 tables going to that query , if I delete a certain table I can then edit the query.
View 1 Replies View RelatedNeed to know if there is a way to exclude a subform from printing when I print forms?
Here's what I'm trying to do - Have a form that contains multiple subforms that are linked back to the master form. One of these subforms is a memo which they would like to see on the "preliminary or draft" copy of this form but would like to exclude it from the "final" copy.
I would think there is some why to do it but I'm stumped at the moment.
Any ideas would be greatly appreciated.
Thanks,
I used to queries ,1 to get items that are taken ( its all about sign in sign out for equipment) and other query is list of all items.
How can i make 3rd query which will give me all but taken items from query1?
(of course items from query 1 are in query2)
thx in advance
Is there a way to edit/replace tables in a query after you've copied the query? Basically, I have created a query and would like to copy the quesry. However, I would like to easily change some of the tables within the query. Is there a way to edit/replace the tables?
Thanks
Hi ..
I have an issue I do not know how to tackel. I have a select query that selects from three tables to calculate the commission for each transaction. The query is working just fine.
My problem is that I need an additional function ... What if I need to charge a special commission for that specific trade ?
I need to ammend the commission based on a figure I input in a text box from the form view.
Example:
- Commission (calculated by the query) is 100.00
- Special Commission (which is a text box on the form) is 80.00
Then Commission field = 80.00
PLEASE HELP .. Please let me know if you have any other suggestion in tackling this problem. Thanks
Query
Commission: IIf([Shares]![Currency]="USD" Or [Shares]![Currency]="CAD",IIf([Blotter]![Quantity]*[Clients]![USD_2]<[Clients]![USD_1],[Clients]![USD_1],[Blotter]![Quantity]*[Clients]![USD_2]),IIf([Shares]![Currency]="GBp" Or [Shares]![Currency]="EUR",IIf([Subtotal]*[Clients]![EUR_GBP_2]<[Clients]![EUR_GBP_1],[Clients]![EUR_GBP_1],[Subtotal]*[Clients]![EUR_GBP_2]),IIf([Subtotal]*[Clients]![HKD_JPY_2]<[Subtotal]*[Clients]![HKD_JPY_1],[Subtotal]*[Clients]![HKD_JPY_1],[Subtotal]*[Clients]![HKD_JPY_2])))
Hi!
I have done a query which has calculated fields, but I can't edit the stored fields directly from the query, instead I have to go to the table so as to edit it. Is this the way it works or is something wrong?
SELECT [INSTRUCCIONES DE EMBARQUE].N_OP, [INSTRUCCIONES DE EMBARQUE].[N° FAC EXP], [INSTRUCCIONES DE EMBARQUE].[FECHA CUMP], [INSTRUCCIONES DE EMBARQUE].[DERECHO PGC], [INSTRUCCIONES DE EMBARQUE].[VTO DER EXP], [INSTRUCCIONES DE EMBARQUE].[IMPORTE REINTEGRO], [DETALLE CONTENEDOR].CANTIDAD, [DETALLE CONTENEDOR].[VALOR FOB], [INSTRUCCIONES DE EMBARQUE].[TIPO DE CAMBIO], [TIPO DE CAMBIO]*[VALOR FOB] AS [PRECIO VTA (ARS)], [PRECIO VTA (ARS)]*[CANTIDAD] AS [TOTAL(ARS)], [TOTAL(ARS)]/[TIPO DE CAMBIO] AS [TOTAL(USD)]
FROM [INSTRUCCIONES DE EMBARQUE], [DETALLE CONTENEDOR];
I have a table structure that I guess could be called a dual one-to-many-to-one, as can be seen in the attached Relationships screen print. Customer loan files are maintained in an AllFiles table, tied to the Customers and Loans tables through the CustomerFiles and LoanFiles tables. Users access the data from an unbound form called FileProcessing, where they can select a customer, and a loan for that customer. The files linked to that customer and loan then appear on a form called Files. However, I need the data on Files to be updateable, and I'm having a hard time coming up with a Files datasource that can be updated. The SQL that retrieves the needed data is:
Code:
SELECT AllFiles.*
FROM (AllFiles INNER JOIN CustomerFiles ON AllFiles.FileNum = CustomerFiles.FileNum)
INNER JOIN LoanFiles ON AllFiles.FileNum = LoanFiles.FileNum
WHERE LoanFiles.LoanNum=[Forms]![FileProcessing]![tbLoanNum]
AND CustomerFiles.CIFKey=[Forms]![FileProcessing]![tbLoanCIFKey];
I tried creating another query that just had the AllFiles table linked to the above query, but the data still couldn't be updated.
For some reason i've had to make an update on some old VBA projects of mine. In this update i switch all references of DAO to ADO. I know there are some limits to what i can accomplish with ADO (where i would need to use DAO). Anyways in some of these old projects i use an SQL statement to provide me with a limited number of records which i then modify by iterating through the recordset.
I can't get this to work with ADO. It's as if it always expects me to provide a table in the argument.
In short my question is. Is it possible to change the values returned from an SQL statement using ADO
Code:
'This is the DAO version which is exactly what i'm looking to using ADO
strSqlCommandText = "SELECT tblMain.TaskID, tblMain.Heading FROM tblMain WHERE (((tblMain.TaskID)=" & ProcessForm.txtIDValue.Text & "))"
Set objRecordset = pubObjDatabase.OpenRecordset(strSqlCommandText, dbOpenDynaset)
objRecordset.Edit
objRcsToDbTable.Fields("Heading") = ProcessForm.txtBoxHeading.Text
objRecordset.Update
This is what i've done and it doesn't work.
Code:
strSqlCommandText = "SELECT tblMain.TaskID, tblMain.Heading FROM tblMain WHERE (((tblMain.TaskID)=" & ProcessForm.txtIDValue.Text & "))"
Set objRecordset = New ADODB.Recordset
objRecordset.Open strSqlCommandText, pubObjDatabase, adOpenDynamic, adLockOptimistic
objRecordset.Edit
objRcsToDbTable.Fields("Heading") = ProcessForm.txtBoxHeading.Text
objRecordset.Update
I can think of some work around like, creating a temp table and insert/update all the records from there or creating an UPDATE SQL statement.
Hi
Here's a brain-teaser for you all.
I want to create a copy of a table which I do using the code below.
SELECT * INTO My_Temp_Table FROM My_Table WHERE ColumnA = 100
Great, all works well.... but I want to exclude a column when the temp table is being built. I know I could specify all the columns I want leaving out the undesired column (rather than using SELECT *) but there are lots of them and, whilst in development, the table columns are liable to change. So I'd like to copy all coumns that are in force at any given time except one in particular?
I thought it would be something like :-
SELECT * INTO My_Temp_Table FROM My_Table WHERE ColumnA = '100' AND Columnname NOT LIKE 'ColumnB';
But this doesn't work.
Any ideas please?
Many Thanks
if you have a table of data (for example, where I have a long list of investors who should not show up in my queries and I don't want to try typing a very long list in a query's IN statement).
So, I created a "Quick Tutorial" to show how easy it is to use a table to exclude data from a query.
I have an access 2010 contacts database with 736 entries and I need a simple query to filter out entries after 1/03/2013. The date is entered by the Date Picker and there is no Input Mask set. I am using d/mm/yyyy in the table. The query I am trying design displays FirstName, LastName and DateAdmitted, I need to filter out all entries admitted after 1/03/2013. I have used <#1/03/2013# as the criteria and that returns over 49,000 entries in instead of about 700 entries.And I thought this was going tobe easy!!
View 1 Replies View Related