I created a report where is lists sales volume by day. I was asked to create a table where data can be excluded from this report. The exclusion table consists of only three fields:
Date Department and a check box
What do I need to do in the query to have it exclude the data listed in this exclusion table. The table only holds data we want excluded, not all dates and departments.
I originally added the table to the source query and made the criteria "False" for the check box but when the query runs it does return any data at all.
Hello, I'm new to the forum and relatively new to Access. There's something I'm trying to do but I'm not sure if it's even possible so I'm hoping someone can help me out and point me in the right direction
the situation:
I have one very big table containing data for around 250 petrol stations. The data is collected monthly, so each site has a record relating to each month. E.g site A has data for month 1, 2, 3 etc.
The problem is that in any month, some of this data needs to be excluded from analysis. And this will vary over the months. What I want to do is run a query where specific records are excluded, for example, exclude Site A data for month 1 and 3, Site B data for month 2 etc.
I have another table containing a list of which site + month data is bad and to be excluded. Ideally I want to simply add to the list each month before I run the select query.
the question:
Is there any way to exclude records from a select query based on their details appearing on another table?
I have a table that I am importing from FOXPRO DATABASE. When someone goes into the system and deletes a record, Foxpro does not really delete the records but marks it for deletion. There is no field to query on that I can see in the table, but when I go to Foxpro the record is "marked" for deletion.
Anyway to exclude these records when doing a query or importing the table?
I want to run a query on a table that holds all speed information for our trucks imported from a third party. Some of the speed alerts in that table are not correct so we set up a second table managed by the users to enter a speed exception. So if we know that Main St in Dallas TX generates false alerts for speeding we know not to call the driver, the third party db speed limit is not up to date.
So I want my query to pull all the speed data from tblSpeedData, except leave out the records where the street and zip are listed in the tblSpeedExceptions.
I am trying to write a query that will check all records in a table but exclude the 10 newest records in the table.
The table is from a stock program i have wrote for the company i work for (i am a novice access user). what ive been asked to do is write a duplicate order system that will flag up if the order has already been packed.
the table logs the [OrderID] with each item [barcode] scanned out with a barcode scanner what i want is a query that checks the OrderID for a duplicate entry in the entire table but because the OrderID is entered with every item scanned i want to ommit the last 10 records as prety much no order has more than 10 items i understand this may not be 100% fool proof but it is close enough.
The other option is to have it ommit all records within the last 15 minutes there is also a [Time] and [PackDate] Field which im guessing could be used for this the time field records Now() and the PackDate records Date(). After searching the web i cant seem to find anyway to ommit the last 'n' records and the few things i have found with the Date("m",-15,Date()) doesnt seem to work
I created a report where is lists sales volume by day. I was asked to create a table where data can be excluded from this report. The exclusion table consists of only three fields:
Date Department and a check box
What do I need to do in the query to have it exclude the data listed in this exclusion table. The table only holds data we want excluded, not all dates and departments.
I originally added the table to the source query and made the criteria "False" for the check box but when the query runs it does return any data at all.
I have 12,000 cvs that i need to get into An access database so i can start to extract email info etc. They are stored in one folder All Cvs under each of their names edc.txt (i have converted them to .txt) i want to create one table with two fields name (taken from the cv filename and contents (taken fromthe contents of the .txt file). I am using Access 2007.
I'm trying to extract records that start with 10 but I need to exclude all that start with 1011,1015 & 1025
so far I can only extract records that I need but cant exlude the others when I use the SQL below
INSERT INTO tblConceptOrders ( TA_TASK_ID ) SELECT dbo_F_TASKS.TA_TASK_ID FROM dbo_F_TASKS GROUP BY dbo_F_TASKS.TA_TASK_ID HAVING (((dbo_F_TASKS.TA_TASK_ID) Like "10*")) ORDER BY dbo_F_TASKS.TA_TASK_ID;
I'm looking for a better way to exclude the records I don't want & collect the ones that I do
Good morning, I'm trying to create a query and I'm asking for specific records, but also need to exclude certain records within that criteria. Here is my Select statement:
WHERE ((([MD IMPORT PURGE PEND].SEG)="CONTROL") AND (([MD IMPORT PURGE PEND].REASON)="44" Or ([MD IMPORT PURGE PEND].REASON)="51") AND (([MD IMPORT PURGE PEND].IND)="HOMES" Or ([MD IMPORT PURGE PEND].IND)="OTHER") AND (([MD IMPORT PURGE PEND].SOURCE)<>"IN" And ([MD IMPORT PURGE PEND].SOURCE)<>"IP") AND (([MD IMPORT PURGE PEND].PAYEE)="J" Or ([MD IMPORT PURGE PEND].PAYEE)="N")) OR
((([MD IMPORT PURGE PEND].SEG)="CONTROL") AND (([MD IMPORT PURGE PEND].REASON)="44" Or ([MD IMPORT PURGE PEND].REASON)="51") AND (([MD IMPORT PURGE PEND].IND)="HOMES" Or ([MD IMPORT PURGE PEND].IND)="OTHER") AND (([MD IMPORT PURGE PEND].PAYEE)="S" Or ([MD IMPORT PURGE PEND].PAYEE)="M")) OR
((([MD IMPORT PURGE PEND].SEG)="CONTROL") AND (([MD IMPORT PURGE PEND].REASON)="35") AND (([MD IMPORT PURGE PEND].IND)="HOMES" Or ([MD IMPORT PURGE PEND].IND)="OTHER") AND (([MD IMPORT PURGE PEND].EFF2)="3")) OR
((([MD IMPORT PURGE PEND].SEG)="NASCO-NASCO") AND (([MD IMPORT PURGE PEND].REASON)="44" Or ([MD IMPORT PURGE PEND].REASON)="51") AND (([MD IMPORT PURGE PEND].IND)="HOSTP" Or ([MD IMPORT PURGE PEND].IND)="OTHER") AND (([MD IMPORT PURGE PEND].SOURCE)<>"IN" And ([MD IMPORT PURGE PEND].SOURCE)<>"IP") AND (([MD IMPORT PURGE PEND].PAYEE)="J" Or ([MD IMPORT PURGE PEND].PAYEE)="N")) OR
((([MD IMPORT PURGE PEND].SEG)="NASCO-NASCO") AND (([MD IMPORT PURGE PEND].REASON)="44" Or ([MD IMPORT PURGE PEND].REASON)="51") AND (([MD IMPORT PURGE PEND].IND)="HOSTP" Or ([MD IMPORT PURGE PEND].IND)="OTHER") AND (([MD IMPORT PURGE PEND].PAYEE)="S" Or ([MD IMPORT PURGE PEND].PAYEE)="M")) OR
((([MD IMPORT PURGE PEND].SEG)="NASCO-NASCO") AND (([MD IMPORT PURGE PEND].REASON)="35") AND (([MD IMPORT PURGE PEND].IND)="HOSTP" Or ([MD IMPORT PURGE PEND].IND)="OTHER") AND (([MD IMPORT PURGE PEND].EFF2)="3")) OR
((([MD IMPORT PURGE PEND].SEG)="PAR") AND (([MD IMPORT PURGE PEND].REASON)="44" Or ([MD IMPORT PURGE PEND].REASON)="51") AND (([MD IMPORT PURGE PEND].IND)<>"HOSTS") AND (([MD IMPORT PURGE PEND].SOURCE)<>"IN" And ([MD IMPORT PURGE PEND].SOURCE)<>"IP") AND (([MD IMPORT PURGE PEND].PAYEE)="J" Or ([MD IMPORT PURGE PEND].PAYEE)="N")) OR
((([MD IMPORT PURGE PEND].SEG)="PAR") AND (([MD IMPORT PURGE PEND].REASON)="44" Or ([MD IMPORT PURGE PEND].REASON)="51") AND (([MD IMPORT PURGE PEND].IND)<>"HOSTS") AND (([MD IMPORT PURGE PEND].PAYEE)="S" Or ([MD IMPORT PURGE PEND].PAYEE)="M")) OR
((([MD IMPORT PURGE PEND].SEG)="PAR") AND (([MD IMPORT PURGE PEND].REASON)="35") AND (([MD IMPORT PURGE PEND].IND)<>"HOSTS") AND (([MD IMPORT PURGE PEND].EFF2)="3")) OR
((([MD IMPORT PURGE PEND].REASON)<>"35" And ([MD IMPORT PURGE PEND].REASON)<>"44" And ([MD IMPORT PURGE PEND].REASON)<>"51") AND (([MD IMPORT PURGE PEND].IND)="HOMES") AND (([MD IMPORT PURGE PEND].CHECK)="000000000") AND (([MD IMPORT PURGE PEND].PAYEE)="S" Or ([MD IMPORT PURGE PEND].PAYEE)="M") AND (([MD IMPORT PURGE PEND].EFF2)="3") AND (([MD IMPORT PURGE PEND].CSHRCNO) Is Null));
The part in bold is the exclusion. I want it to exclude records where the reason is 35 or 44 or 51, IND is HOMES, check number is 000000000, payee is s or m, eff2 is 3 AND cshrcno is null.
Can anyone tell me how to achieve this? I really appreciate your help!
I'm having some difficulty obtaining the answers I want from my query. I'd have been more comfortable using filters in excel to find the answers, but my dataset is too large in this instance.
I devised a query which I thought should achieve the results, but based on a subset analysed in Excel the answers are significantly different.
My table has about thirty fields, the query I've produced is this: SELECT AH_orphans.Product, AH_orphans.Comments, AH_orphans.ESP, AH_orphans.[ESP Parts], <snip, all other fields in table> FROM AH_orphans WHERE (((AH_orphans.ESP)="n") AND ((AH_orphans.FRU)="y") AND ((AH_orphans.EntitlementName) Like "*uptime*") AND ((AH_orphans.Account) Like "nation*") AND ((AH_orphans.Type) Like "Primar*") AND ((AH_orphans.[Is a parent?])="#n/a")) OR (((AH_orphans.ESP) Is Null) AND ((AH_orphans.FRU) Is Null) AND ((AH_orphans.EntitlementName) Like "*uptime*") AND ((AH_orphans.Account) Like "nation*") AND ((AH_orphans.Type) Like "Primar*") AND ((AH_orphans.[Is a parent?])="#n/a"));
What I'm hoping for is the subset which match the following conditions: - ESP field is not "Y". Possible values Y, N, null - FRU field is not "N". Possible values Y, N, null - Only those records from EntitlementName which include the substring 'uptime' - Only those records from Account which start with the subtring nation - Only those records from Type which include the substring primar - Only those records from [Is a parent?] which equal #N/A.
Asking the same questions in Excel I get around 1.5k records. If I use the quoted query on the same dataset in Access I get precisely 11.
Thanks for reading this far! If anyone can spot the flaw/s in my query, or indeed advise that I'm going at it completely the wrong way I'd be very grateful of some advice.
Hi I would like to exclude two specific records from my recordset.
For example suppose I have a Flights Database and I want to see all the flight records for Aircrafts 132 and 232 only, between the date jan 1, 2007 to Sep 12, 2007.
The above I know how to do achieve...
([FltNum] = 132 or [FltNum] 232) AND ([FltDate] >= Jan 1, 2007 and [FltDate] <= Sep 12, 2007)
However, among this set of records I want to exclude flight 132 on Aug 1, 2007 and flight 232 on Sept 1, 2007.
([FltNum] = 132 or [FltNum] 232) AND ([FltDate] >= Jan 1, 2007 AND [FltDate] <= Sep 12, 2007) AND NOT ([FltNum]=132 AND [FltDate] = Aug 1, 2007 ) AND NOT ([FltNum]=232 AND [FltDate] = Sep 1, 2007)
I haven't been able to accomplish this using the query grid. Is it possible or do I have to write SQL?
All using access 2010. I have a query1, query2 and query3. Query1 is my master. Query2 and Query3 was created based on different criteria but derived from the Query1. I now want to exclude the records from Query1 that are in Query2 and Query3. When I try to put isnull in criteria of both queries Im trying to exclude; instead of returning the remainder records in the master I get none.
Trying to build a CRM system for the office but am getting stuck with the below...
Each company (tblCompany) in the database has at least 1 enquiry (tblEnquiries) linked to it and normally at least 1 (sometimes 0) people (tblPeople).
I have a form which loads company specific information e.g. notes/quotes/orders/people/enquiries. All data is loaded based on the company unique id (c_id).
The attached image shows Company 1 (c_id = 1). It has 6 enquiries. However the subform only displays 5 of these. It does not display any which do not have a person (or p_id) linked to it - this is consistent throughout the database. I have included the table relationships and the enquiry as well in case they are needed!
Searching the net seems to suggest 2 possibilities:
1 - the relationship join type needs to be set to include all from tblEnquiries and only those from tblPeople where joined fields are equal, however changing the join properties does not appear to have an effect. 2 - table field types do not match (all _ID fields are set to number, unique numbers only).
[edit]: am using Access 2010!
I have re-designed the query to pull through tblEnquiries and tblPeople data based on the c_id field on the open form, which is now showing all records, including those not assigned to a person.
However, in the form I can select one from the query datasheet and open to see additional info. Those without a blank p_id number do not open, I receive the error:
"Run-time error '2113': The value you entered isn't valid for this field.
All _id fields are set to numeric so am not sure how to correct this?
I want to run a query that gives Case Nu and Event where if there is even one event marked Cleared for a Case, then that case number and its events (even those not marked cleared) don't show up at all.
Any ideas? I know there must be an obvious solution, but my mind is stuck right now!
I have a search form with 12 fields. In my query I use
Code:
Like "*" & [Forms]![CustomerRetestDatabaseSearch]![RetestLocation] & "*" Or Is Null
for each field on the search form.
I get the results I expect, it finds all records that match the criteria. Even if some of the fields in a record are null.
But if the query finds a record that matches one field I enter criteria into, and nulls for the other fields I enter criteria into it displays the record. I want to show exact matches. (If what I entered is null... don't show the record).
The reason I have "Or Is Null" is to include the records for the fields I left blank on the form.
Search Form with Criteria.PNG
Search Query.jpg
Search Results With Missing Entered Criteria(Dont Want These Records Included).jpg
Why I am not able to get all records by excluding the Numrows argument in the getrows method?
Sub Test2() Dim myrset As Recordset Set myrset = CurrentDb.OpenRecordset("SELECT * FROM Holidays;") myrset.MoveLast myrset.MoveFirst MsgBox Excel.Application.WorksheetFunction.Networkdays(#8/1/2014#, #8/31/2014#, myrset.GetRows(myrset.RecordCount)) MsgBox Excel.Application.WorksheetFunction.Networkdays(#8/1/2014#, #8/31/2014#, myrset.GetRows()) End Sub
Second MessageBox is giving a wrong value.
Is it a mandatory one? Or Do I have to do some ritual like (Movelast) before that?
(Holidays table is just having the values in the array only ie. #08/15/2014# and #08/29/2014#)
I have one table that will consists of about 1,000 rows. I have another table that consists of product codes that we want to exclude from the report. I know how to include the two tables by joining, but I want to EXCLUDE the 2nd table of product codes. Basically, if any of the product codes listed on table 2 are on table 1, I don't want them to appear. So how can I do an exclude function in a query?
I am trying to import several hundred records into Access from a txt file but I can't find a way to separate the file into individual records. A portion of the file is given below:
'C Olive Teece', 'Census, 1940', 'birth: 1919 New York', 'residence: 1940 Ward 4, Canandaigua, Canandaigua City, Ontario, New York', 'spouse: John H Teece', 'parents:', 'children:' 'Edith Teece', 'Census, 1940', 'birth: 1888 England', 'residence: 1940 Smithtown Town, Suffolk, New York', 'spouse:', 'parents:', 'children:' 'St Clair Teece', 'Census, 1940', 'birth: 1867 Pennsylvania', 'residence: 1940 Ward 3, New Castle, New Castle City, Lawrence, Pennsylvania', 'spouse:', 'parents:', 'child: Edward C Teece'
Let's say I have a table sort of like this one: [URL] .... (Table 1)
What I want to do is make another table that references the first table: [URL] ... (Table 2)
I want the cells in the Average field in Table 2 to calculate an average of all the values for records in Table 1 with Color fields that correspond to the Color field in Table 2 (this makes a little more sense if you look at the pictures). I could do this in Excel, but then problems would arise whenever I would add a new entry to the database, or re-alphabetized the data, since Excel math is depends entirely on the positions of cells, and I want these averages to be continually calculated correctly and to change whenever I add related records to the database.
