Excluding A Single Specific Record Among Many Records
Sep 13, 2007
Hello Query Expert
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?
How would you write this in SQL?
Thanks so much.
View Replies
ADVERTISEMENT
Jan 16, 2015
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?
View 2 Replies
View Related
Sep 27, 2005
Table1 gives Case Nu.
Table2 gives Case Nu. and EventType.
Table3 gives EventType and Cleared (yes/no field)
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!
View 2 Replies
View Related
Apr 21, 2015
I've created a button on my ACCESS 2010 form that will send a PDF via email. However I only need to send the single record displayed on the form not all records.
The on click command of the button sends the PDF of the report. (I read that this is what should be sent; but still get the same results.)
I don't understand how and where to attach VBA code:
Me.Filter = "RequestsID=" & Forms![OD Reversal Requests]![RequestsID]
Me.FilterOn = True
View 5 Replies
View Related
Sep 8, 2014
Any Single line of code available?
View 2 Replies
View Related
May 15, 2014
I am trying to create a form with a button attached to each record that would allow the user to click the button and it would automatically open outlook and fill in the TO:, SUBJECT: and BODY: fields. Here is the code that I currently have:
Code:
Private Sub Command33_Click()
Dim strEmail As String
Dim strMsg As String
Dim oLook As Object
Dim oMail As Object
Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.CreateItem(0)
[code]...
There are two issues I keep running into:
1. This code opens outlook and populates all of the fields but pastes the email incorrectly. Instead of pasting just the email (email@email.com) it pastes the html tags as well (email@email.com#mailto:email@email.com#) which means that the user would have to delete everything between the #'s in order to send the email every time.
2. I currently have the email BODY pulling from a table but this obviously limits what I can do. I would like to simply encode the BODY within the VBA code. The setup I am looking for is:
one paragraph
a blank line
a hyperlink to a website
a blank line
another paragraph
View 9 Replies
View Related
Jun 11, 2013
I have a combox field on subform C that shows records from a query.How do I get this combo to only show records that relate to an ID on subform B.I have a subform for storing contracts and these are stored against a company name.On subform C I have a field that currently shows all records in a table. I need this combo to only show the records that relate the current record on subform B?
View 4 Replies
View Related
Feb 28, 2006
Hi All,
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
Any help will be appreciated
thanks :)
View 6 Replies
View Related
Feb 13, 2008
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:
SELECT [MD IMPORT PURGE PEND].PROC,
[MD IMPORT PURGE PEND].SEG,
[MD IMPORT PURGE PEND].NAME,
[MD IMPORT PURGE PEND].GROUP,
[MD IMPORT PURGE PEND].BEN,
[MD IMPORT PURGE PEND].DATE,
[MD IMPORT PURGE PEND].RISK,
[MD IMPORT PURGE PEND].REASON,
[MD IMPORT PURGE PEND].PLAN,
[MD IMPORT PURGE PEND].PAR,
[MD IMPORT PURGE PEND].IND,
[MD IMPORT PURGE PEND].REC,
[MD IMPORT PURGE PEND].SUBNO,
[MD IMPORT PURGE PEND].CLIENT,
[MD IMPORT PURGE PEND].CLAIM,
[MD IMPORT PURGE PEND].FSDT,
[MD IMPORT PURGE PEND].PDDT,
[MD IMPORT PURGE PEND].CHECK,
[MD IMPORT PURGE PEND].PROV,
[MD IMPORT PURGE PEND].SOURCE,
[MD IMPORT PURGE PEND].PAYEE,
[MD IMPORT PURGE PEND].EFF2,
[MD IMPORT PURGE PEND].CSHRCNO,
([CARR] & [CARR1] & [CARR2]) AS CPL,
[MD IMPORT PURGE PEND].CLASS,
[MD IMPORT PURGE PEND].MKTSEG,
[MD IMPORT PURGE PEND].PAID
FROM [MD IMPORT PURGE PEND]
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!
View 4 Replies
View Related
Sep 27, 2005
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?
help would be much appreciated!
thanks
Catherine
View 2 Replies
View Related
Apr 29, 2006
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.
Please help!
View 1 Replies
View Related
Sep 20, 2014
I am building a simplified re-order point system - if inventory position drops below a certain level (the yellow level is this case) one or more purchase order lines has to be created in another table.
I have one table with the following field and data:
ItemId Red Yellow Green Multiple Inventory position
0001 10 30 50 5 45
0002 5 40 47 5 23
0003 11 20 30 10 5
I would like to generate new records (in another table) based on the above fields and three records.Basically the end result should look as the following:
ItemId Qty Start inv Aggregated inventory Prioritization
0002 5 23 28 Yellow
0002 5 28 33 Yellow
0002 5 33 38 Yellow
0002 5 38 43 Green
0002 5 43 48 Green
0003 10 5 15 Red
0003 10 15 25 Yellow
0003 10 25 35 Green
The logic is quite simple - if inventory position is less than the yellow value new order lines should be created in multiple qty (based on the multiple field) until the aggregated value (in table 2) is above the green value.The priotization value should be based on the start inv (in tbl 2) compared to the values in red, yellow and green in tbl 1.
View 8 Replies
View Related
Sep 20, 2005
Hello all,
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.
Cheers,
Alex
View 4 Replies
View Related
Jun 6, 2007
Hi
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?
Thanks
View 1 Replies
View Related
May 3, 2013
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.
View 3 Replies
View Related
May 2, 2013
I have a table of around 6000 records comprising 4 fields (A,B,C,D).
- Each field can contain numbers or Nulls.
- Each record can comprise all numbers, a mixture of numbers or Nulls, or all Nulls.
I'd like to build a query that excludes all records that contain any number from a small list of numbers.
This sounds very simple but I am having problems when trying to include records that have Null's in my query output.
For test purposes I tried to exclude all records that contain the numbers 1 or 9 (these numbers can be present in any field).
This works perfectly, in isolation, on Field A (i.e. 1 or 9 but not Null are excluded from field A):
Code:
WHERE Table.A Not In (1,9) OR Table1.A Is Null;
When I try to copy the above, referencing fields B-D, I run into problems - no matter how I try to alter the Boolean operators.
View 2 Replies
View Related
Jun 29, 2006
I'm trying to sort a combo box alphabetically except for one value which is always at the top of the list.
The row source of the combo box is a query. record to remain at the top of the list is static- same value and ID number always. I've fiddled around with a few things, but at the end of the day, i'm just not smart enough! (or its not possible -which i doubt)..
any suggestions?
cheers.
View 2 Replies
View Related
Jul 23, 2015
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.
View 3 Replies
View Related
Sep 12, 2014
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
View 7 Replies
View Related
Aug 6, 2015
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
View 2 Replies
View Related
Jul 31, 2014
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#)
View 10 Replies
View Related
Jul 23, 2013
I want to hyperlink from a query direct to the relevant record in a specific form. I have a hyperlink field in the form which shows up in the query. When clicked in the query, this hyperlinks to the form but I cannot make it select the correct record in the form.How do I get it to select the correct record?
View 3 Replies
View Related
Oct 3, 2005
Hello, I have just spent ages doing searches and reading everything I can on locking. But, I have yet to find an answer as to how I can lock a specific field in a specific record.
e.g. Staff enter customer details, then at the end of the day the admin (me) checks it over and presses a big old button that stops them from locking certain fields in the current record only - they must still have access to the unlocked fields of the current record, and it must not lock any other records.
I'm guessing there's some VB code in the form of fieldname.lock = true, but then it locks the field throughout the whole table!
Can anyone tell me how to do this please?
View 11 Replies
View Related
Apr 22, 2013
I have a database with a table with company names, then a relationship to another table that shows that companies' address, but I also want there to be an address 2 and 3 and so on, and some of our companies have multiple sites.So what Im asking is how would you be able to show multiple data, on a sing record.
View 3 Replies
View Related
Jul 9, 2015
I am new to access i have a problem which is i have made a form which contains a subform and a read only subreport, what i want is the ability to select a record in read only subreport as in the picture attached and make a button that when i press on it, it should copy the values of the itemsID field, Packing field, ContainerNo field and origin field from the selected record and then paste them in the subform below.
Also i want to add more then one item, so the when i press on another record it should paste the values below the first record.
View 12 Replies
View Related
Jan 9, 2012
I am new to Microsoft Access. I have built two entry forms for product release information. I have a drop box under the label customer for each specific customer. I would like for this form to send the data entered in the form to the customer specific table from the customer names in the drop box. I have tried to research and execute this myself thus have been very unsuccessful.
View 14 Replies
View Related
