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
Am trying to import data into a blank database (MS access 2013). The import operation is successful. But the data in the tables gets deleted after a while. And all I can see are blank tables in Access 2013.
View 2 Replies View RelatedHello, 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
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!
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 simple database with a single table and a form so I can track patients enrolled in a study. I added a few test records to make sure everything was working properly. Now I'm ready to use the database for real. Only trouble is, when I erased the test records I find that I can't add new ones.
View 1 Replies View RelatedI am losing records in one table when I delete from another, i.e., I do want to delete records in one, but the related records in the other are also being deleted. This was not happening until I did a recent up date.I created a database in Access 2002 about 8 years ago and my client has been using it successfully ever since. I have occasionally made updates and enhancements. They have over the years upgraded and are now on Office 2010. I'm still using Access 2002.
I recently (3 weeks ago) added a new feature and installed the new code. The database is split, code and data. The new feature was working well, but suddenly they were losing records and they cannot operate until I have it resolved. I spent 2 hours today in their office and could not work out why it was happening.
I have a straight forward client table with names and addresses and some other information. I have another table which links together Clients, Counsellors and Supervisors and works out a room allocation for Therapy sessions. Once the Therapy sessions have been completed, we need to delete the Client, Counsellor, Supervisor + room allocation, but we do not want to delete the Client record in the Client table. The User is offered the opportunity to either archive the client record or not archive it, that's all. The Archive procedure is simply to put a tick in the "Archive" field, not delete the record.
However, when the Client, Counsellor, Supervisor + room allocation is deleted, it is also removing the Client record. I do have a relationship between Clients, Counsellor, Supervisor, but it doesn't have "enforce referential integrity", it is just a one-to-one relationship. The odd thing is that when I delete the allocation here at home on my Access 2002 system, it does not delete the Client Record, but it does in their office, using exactly the same code and data.
The Allocation is on a sub form and the way they delete is by highlighting the Allocation and then clicking on the X - Delete button on the Access menu (the program is not very sophisticated, but has worked until now). I have some code in the "on delete" event, but even if I take out all the code and just allow the deletion with the usual Access message "you are about to delete 1 record... " I have seen that the related Client record gets deleted at the same time. it does not happen on my system, only in their office.
Three tables:
Employee, Sessions, EmployeeSessions.
Many sessions can have many employees - thus the joining table has been included.
When trying to delete an employee from the database using a form, I encounter the error:
The record cannot be deleted or changed because table 'tblEmployeeSessions' includes related records
Is there a problem with my table relationship structure? Or is it 'correct' that as the employee is supervising a session he/she cannot be deleted as this would interfere and maybe mess up the session record?
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 :)
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!
I am importing about 1.5 million records from a .txt file into an Access database. I have the code that splits the records and adds them to the database. However, the database has a 'specialty' table that lists certain specialty codes. The incoming record has a field that is a specialty code. Any way to search the 'specialty' table for the code that is coming in on the record to be added, and then, if the record 'code' is not found, skip it, and if it is found, add it to the database. There are about 70 codes in the 'specialty' table.
View 9 Replies View RelatedHello 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
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.
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 RelatedI 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.
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?
Hi all,
I have the following issue:
In my job we work with several raw data .txt files exported from Oracle ERP system. These data include information about: inventory, sales, backorders, purchase orders, forecasts, product line.
My goal is to put information from all of these imported txt files together to create an easy to use snapshot file.
The common field between al of these files is the item description. I have an issue where records are repeated for several fields every time the same item description is showed. For example for the same product description the january sales forecast QTY is repeated on several rows because there are several orders for this product description. Is there a way to make forecast QTY appear only once but keep the multiple orders and their information?
Example of current result
http://img178.imageshack.us/img178/5659/currentsu7.th.png (http://img178.imageshack.us/my.php?image=currentsu7.png)
to achieve result
http://img179.imageshack.us/img179/5100/toachievefk4.th.png (http://img179.imageshack.us/my.php?image=toachievefk4.png)
thank you for any comments
No records are being imported. I am getting a validation rule violation but I don't have any rules. The forename and surname are straightforward text boxes and the ID is an autonumber.
Code:
INSERT INTO table_candidate ( Cand_ID, Cand_forename, Cand_surname )
SELECT candidates.Cand_ID, candidates.Cand_forename, candidates.Cand_surname
FROM candidates;
1. Is your append query trying to assign values to the primary key field? Could that be the source of the duplicate?
Yes but there are no duplicates
2. Do you have any other fields that are "Indexed: No Duplicates"? Any compound indexes?
No
3. Is the query assigning values to a field that is a foreign key to another table? Is it possible that these values do not match the values in that other table?
No. All child tables are empty.
4. Is there a validation rule on the table itself?
What this means.
5. Does the query attempt to assign a string with no characters in it (as opposed to a Null value) to a text field that has its Allow Zero Length property set to No?
Both fields are text and all records contain information
Required = No
Zero length = Yes
6. Is there a Default Value in one of the fields that is NOT being assigned by the query? For example, a foreign key with zero as the Default Value?
No
7. Is there data that is outside the range a field can accept, e.g. an integer larger than 32767, or a Null to a Yes/No field?
The length of the text in each record is not greater than the set limit
8. Is one of the query fields arriving in a format that Access is not understanding, e.g. for a date, or for a currency?
No
Access Database 2010 is used to capture progress on accounts. We are able to perform remedies on multiple accounts in the field and would like to update the records in Access all at once (by batch) rather than one-by-one.
I would like to export specific records from Access into Excel, make the updates to the records in Excel, then import the changes back into Access. I am looking for the updated Excel spreadsheet to overwrite the existing data in Access for that particular record.
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!
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
I have a main table that is imported weekly from another Access DB which I have no control of. I also have a new table with a notes field and a product ID field. The issue is the product ID field in the main table is constantly growing. When I created a query with all of the fields from the main table and the notes field from the notes table I could not enter any data into the notes field unless the product ID was already listed in the notes table. Is there a way to make a query update the notes table or a macro to add the missing product IDs from the main table to the notes table?
View 3 Replies View RelatedI enter records into MS Access and then when I close it and reopen it, every once in awhile records that I entered previously are deleted. I am new to Access so it may be a simple problem such as saving the database but it is extremely frusturating when I enter customers into a database and then reopen it to find out voila half the customers I entered are not there. If anyone can help me it would be really appreciated. Thank you.
View 1 Replies View RelatedHi everyone
I have a problem that I have deleted some records from a table in my database and now after one week I need these records again. Can someone tell me how to get them?
This has got me stumped.
We have a massive db and on one of the screens the users can scroll through the records and view information they can also add new records. When the users enter the new record and go back to it it has been successfully stored in the relevant table even on exitin the form via the exit button (DoCmd.Close). However from time to time when the user enters the new record and closes the for via the close form button the new entry is not stored in the table and seems to have been deleted or not appended to the table.
We have no idea why this is happening as it doesnt happen all the time just from time to time.
Can anyone help please or has anyone come across this before !
Thanks for any help !