Build A Query In Order To Exclude Some Records
Jul 1, 2014
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.
View Replies
ADVERTISEMENT
Jun 21, 2013
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
Sep 10, 2007
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
View 2 Replies
View Related
Jun 30, 2005
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.
View 10 Replies
View Related
Sep 21, 2006
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
View 2 Replies
View Related
Dec 21, 2004
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
View 2 Replies
View Related
Jul 29, 2013
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?
View 2 Replies
View Related
May 21, 2013
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 Related
Sep 6, 2012
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.
View 1 Replies
View Related
Oct 20, 2005
Hi,
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.
View 3 Replies
View Related
Nov 29, 2006
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.
View 14 Replies
View Related
Apr 24, 2014
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.
View 1 Replies
View Related
Apr 13, 2013
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.
View 9 Replies
View Related
Aug 28, 2007
I have some database with columns 'Number', 'Mfr Name' and 'Mfr Part Number'.
Number - internal catalogue item of a company.
Mfr Name - name of a manufacturer where from we purchase the item
Mfr Part Number - part number of a manufacturer where from we purchase the item
Each item may have one or more Mfr Name & Mfr Part Numbers.
----------------------------------------------------------------------
I need to build a following query:
Input gets part of Mfr Part Number and output is all Mfr Name & Mfr Part Number of match item.
View 6 Replies
View Related
Jul 31, 2014
how to exclude those records that have an alpha as the last character and also exclude the records that have the same id code without the alpha.
Example Listing:
id_code
ak_12345
ak_12345a
ak_12346
gl_2391
1009123
1009128
1009128a
1009128b
Desired Output Result:
id_code
ak_12346
gl_2391
1009123
The length of characters in the id_code is not consistent. Some id_codes are longer and some are all numeric and some don't have underscores.
View 14 Replies
View Related
Oct 7, 2005
How can I build a date in a query that is used for filtering purposes? The data is in the records in two fields - Month and Year. In our query we have an expression that builds a date - DateRange: [Month]/1/[Year] in order to qualify the record with the criteria Between FromDate and ThruDate. DateRange as it is shown here returns a date of 12/30/1899 for all records. DateRange: #[Month]/1/[Year]# is invalid syntax. DateRange: [Month] & "/" & 1 & "/" & [Year] is not recognized as a date.
View 4 Replies
View Related
Jul 5, 2006
Hi all, I have a field that stores software versions as text. The version numbers can be simple (4.0, 5.0) or unknown or more complicated (4.5.200512, etc). The user wants to be able view data in a pivotChart without seeing interim versions- so 4.5.200512 would not show up but its data would be counted as part of 4.5. Anybody know how I can do this?
thanx
View 1 Replies
View Related
Oct 26, 2006
Hi everybody,
Let's say that I've got the following tables:
- tblCustomers(IDCust,NameCust)
- tblEmployees(IDEmpl,NameEmpl)
- tblProjects(IDProj,IDCust,IDEmpl,Duration)
My query:
SELECT tblCustomers.NameCust, tblProjects.IDProj, tblProjects.Duration, tblEmployees.NameEmpl, Sum(tblProjects.Duration) AS SumOfDuration
FROM (tblProjects INNER JOIN tblEmployees ON tblProjects.IDEmpl = tblEmployees.IDEmpl) INNER JOIN tblCustomers ON tblProjects.IDCust = tblCustomers.IDCust
GROUP BY tblCustomers.NameCust, tblProjects.IDProj, tblProjects.Duration, tblEmployees.NameEmpl
ORDER BY tblCustomers.NameCust;
What I'd like get when running my query is:
NameCust ------ IDProj - Duration ----- NameEmpl ------- ?????????
CUST1 ---------- 1 ------ 3 ------------ EMPL3 ---------- 7
CUST1 ---------- 2 ----- 10 ------------ EMPL4 --------- 10
CUST1 ---------- 9 ------ 2 ------------ EMPL3 ---------- 7
CUST1 --------- 11 ------ 2 ------------ EMPL3 ---------- 7
CUST1 --------- 13 ------ 8 ------------ EMPL1 ---------- 9
CUST1 --------- 18 ------ 1 ------------ EMPL1 ---------- 9
CUST2 ---------- 3 ----- 20 ------------ EMPL1 --------- 20
CUST2 ---------- 4 ----- 12 ------------ EMPL3 --------- 17
CUST2 ---------- 5 ------ 3 ------------ EMPL2 ---------- 4
CUST2 --------- 10 ------ 5 ------------ EMPL3 --------- 17
CUST2 --------- 17 ------ 1 ------------ EMPL2 ---------- 4
CUST3 ---------- 6 ----- 25 ------------ EMPL4 --------- 27
CUST3 --------- 14 ------ 2 ------------ EMPL1 --------- 14
CUST3 --------- 15 ------ 2 ------------ EMPL4 --------- 27
CUST3 --------- 16 ----- 12 ------------ EMPL1 --------- 14
CUST3 --------- 19 ------ 2 ------------ EMPL2 ---------- 2
where ???????? (manually done) = the sum of the Employee duration for ONE Customer !!!
But what I get instead:
NameCust ------ IDProj - Duration ----- NameEmpl ------- SumOfDuration
CUST1 ---------- 1 ------ 3 ------------ EMPL3 ---------- 3
CUST1 ---------- 2 ----- 10 ------------ EMPL4 --------- 10
CUST1 ---------- 9 ------ 2 ------------ EMPL3 ---------- 2
CUST1 --------- 11 ------ 2 ------------ EMPL3 ---------- 2
CUST1 --------- 13 ------ 8 ------------ EMPL1 ---------- 8
CUST1 --------- 18 ------ 1 ------------ EMPL1 ---------- 1
CUST2 ---------- 3 ----- 20 ------------ EMPL1 --------- 20
CUST2 ---------- 4 ----- 12 ------------ EMPL3 --------- 12
CUST2 ---------- 5 ------ 3 ------------ EMPL2 ---------- 3
CUST2 --------- 10 ------ 5 ------------ EMPL3 ---------- 5
CUST2 --------- 17 ------ 1 ------------ EMPL2 ---------- 1
CUST3 ---------- 6 ----- 25 ------------ EMPL4 --------- 25
CUST3 --------- 14 ------ 2 ------------ EMPL1 ---------- 2
CUST3 --------- 15 ------ 2 ------------ EMPL4 ---------- 2
CUST3 --------- 16 ----- 12 ------------ EMPL1 --------- 12
CUST3 --------- 19 ------ 2 ------------ EMPL2 ---------- 2
Any suggestion to build this Query ??
Thanks in advance
View 3 Replies
View Related
Feb 11, 2006
(Dear Admin, don't be mad I posted this post elsewhere but had no luck with answers)
Please guys, I just forgot how to do this:
I have a table with primary key (id) and sort of secondary key (id1). Several rows can have same id1.
id id1
1 1
2 1
3 1
4 1
5 3
6 3
7 4
8 4
9 4
...
I need to count the id1 - how many different id1 values are there.
This doesn't work:
SELECT COUNT(id1) FROM table GROUP BY id1
this also doesn't work:
SELECT COUNT(id1) FROM table
As I recall, it was something about DISTINCT function or something like that...?
Please help, I'm sure it's totally simple, but I'm stuck.
View 4 Replies
View Related
Mar 21, 2006
select student from tbl_TableName where max(date_borrowed) <= dateadd("d",-730,Date())
group by student
??? How do I set this up? and do the max date_borrowed?
Thanks.
View 2 Replies
View Related
Feb 20, 2008
As example, I have a table with an Item number, introduction year and a number of historical and future Sales periods set per year, these sales columns are listed Y1990, Y1991, Y1992....... Y2015.
Based on each items introduction year, I want to list the first 5 years of sales.
I wanted to create a dimensional fieldname eg: FirstYear: "Y"&[introduction year] to get the value of that respective year. (I currently just get a text saying "Y1995", and not the content )
Any help is appreciated!:)
(Note: I can't transpose the data in the tables for other reason)
View 3 Replies
View Related
Jan 14, 2013
Currently, I use ACCESS to track reservations for various types of events. When I want to make nametags, I download the "attendee" query that I've already set up to EXCEL and then merge into a WORD document. Is there a way to set up in ACCESS so the labels can be printed straight from the database? I've tried, but I can't get the labels to space out properly (EX: 2 across and 6 down) even though I put the correct label # in there.
View 4 Replies
View Related
Sep 9, 2013
I'm making an accounts package.
I've made a graph of revenue by customer but due to the number of customers it's a bit hard to see the customer names.
So I would like to be able to select my own custom group of 5 customers to plot on the graph.
To do this I think I need a query that selects the 5 customers which is populated from 5 comboboxes on a new form that the user can select customer names from but I'm not sure of the VBA that links all this together.
View 2 Replies
View Related
Jan 2, 2014
What query would I use to build a date in m/d/yyyy format using month, day and year values from another field?
View 2 Replies
View Related
Jan 19, 2006
Hi,
I am a bit stuck now and I want to get some ideas from you.
The context first : I am writing a timesheet application and I've got a main form + a sub-form.
In the mainform, I select the engineer, the contract and the week nbr.
My mainform calculates the first and the last day of the selected week and modifies the recordsource property of my sub-form to select the existing records with the right engineer + contract nbr + range of first and last of week. This means that I can only have maximum 7 records for the seven days of the week because the application records only a global time per engineer/contract/per day.
Now I want to know how can I add new records in the subform for the missing days and how to put them in the right order ?
Example: I retrieve from my select in the subform, the times from day 2 and day 4 but I need to generate the missing days 1, 3, 5,6 & 7.
How can i display them in the right order: day 1 (auto), day 2 (table), day 3 (auto), day 4 (table), and so on...
In which event do I have to put code and what kind of code ??
Please help !
View 2 Replies
View Related
Feb 10, 2006
I am making a database that requires a list of all the crew people in our company. This list is not sorted by alphabetical order but by order of significance (or frequency of) crew person. This list is rather lengthy and if I add a crew person it can only be added to the bottom of the list. The only way I have been able to do this is by manually resorting and retyping the list. Is there any way to add a row in the middle of a table (like I can in Excel)??
Thanks, Joe
View 7 Replies
View Related
