Queries :: After Importing Data Query Not Picking Up Newly Added Records
Apr 21, 2013
I created a database. exported some Excel data into Access tables, created a number of lookups, default values etc. and created a pretty simple query.
My problem is that after importing and tweaking the data, when I add new records, my queries do not pick them up!
I have tried:
1. saving, closing, opening and re-running the queries.
2. putting an Nz expression for each field in the query as I read that null values may cause a problem.
3. wrote the query again, field by field to see if all records were received.
4. Exported the table back to Excel and imported to a new Access table in my database
I have a form that lets you add records to my table, it works well and I can see the records in my table but then once I use my search form, which also is working well, the new records won't come up when I do a search, it just keeps searching through the records that were there when I created the search form.
Basically I have one table with all relevant information needed, etc, and I have the original spreadsheet data inputted to the database. The original data appears fine when a search is done, eg, the user searches for something using a form that then uses a query and ultimately brings up the information that matches. In the queries, I use the same basic criteria
My question is, now that I have a form to add a new record to the table so a user can add to it, what would be the best way to get the added record to appear during the search, like the original records? I have used " IS Not Null" at the end of the criteria within the query, but it tends to bring up all the new records no matter what the user searches.
I have a combo box that pulls account name data from tblAcctInfo. the combo box has an OnChange event which updates a textbox, txtAcctAddr.
when i have a new account that i would like to be listed in the combo box, i use a form, frmAcctAdd, to add a record to tblAcctInfo.
what i would like to do, is: 1) when i type a new value in the combo box that isn't in the list, have that string value pre-populate in the frmAcctAdd. 2) when i have added the new account info into frmAcctAdd and then saved the record, i would like the new value to pre-populate in the combo box, with the txtAcctAddr textbox also updated via the OnChange event (or maybe a different event is more appropriate?).
I have created a long version of this which requires a lot more user interaction (1-user typing in a new value into the combo box, 2-user RE-typing the SAME value into a data entry form, frmAcctAdd, 3-user saving the new record, 4-user re-clicking the combo box and selecting the newly added value) but i am trying to streamline the data entry with auto-populated fields.
I have an Access 2010-form which inserts a record in a MS SQL 2008-database by using an ADODB-connection. I need to retrieve the primary key of the newly added record.
With code at the bottom I create a SQL-string which is stored in variable strSQL. If I execute the SQL-string directly in a MS SQL 2008 Query Window, the record is inserted and the MS SQL 2008-result pane shows a column "ID" with the primary key of the newly added record.
If I execute same SQL-string in MS Access 2010 the record is inserted. However, the code fails on Debug.Print rs![ID] with error "Item cannot be found in the collection corresponding to the requested name or ordinal". Same error appears if I use Debug.Print rs.Fields(0). I've enable the reference "Microsoft ActiveX Data Objects 2.8 Library". It looks as the recordset rs is closed as soon the command "Set rs = cn.Execute(strSQL)" finished.
Why I can't retrieve the Primary Key with VBA-code below?
Code: Private Sub cmdSave_Click() dim cn As ADODB.Connection dim rs As ADODB.Recordset set cn=New ADODB.Connection
Created a new Query (called qry_Temp) from and existing Query (qry_Test that has fields Field1 - Field5) using QueryDef , and inserted a new Field (FieldX) into it (see example below) using the .Parameters property, then opened a Recordset based on the new Query in order to attempt to enter data values into the new Field (FieldX) for each record in the query - but cannot assign any data values to this new Field (but can to the existing fields) in the new Query?
Dim DB As Database Dim rs As DAO.Recordset Dim qdfNew As QueryDef Dim strSQL1 As String Dim strSQL2 As String Dim Value1 As Integer
Hello, I have created a form to enable data to be easily added to the database table, however does anybody know why the query that looks up the records from the table does not return the new entered records, only what was originally in the table? Thanks
Is it possible to have the user select which field is added to the query through a parameter? In my query I have a table where each record has a part number and has sales number for each month. When the user runs the query I want them to be able to select which month is returned.
My table basically looks like this:
PartNumber,Jan,Feb,Mar,Apr,May etc. 01PartNumber,15,20,30,25,28 etc. 02PartNumber,12,14,12,16,20 etc
I want the user to enter "Mar" when prompted and return the parts numbers with values from "Mar".
I have a database used to track my personal assignments, created about six years ago using Access 2003 on Windows XP. Recently upgraded to Access 2010 on Windows 7. At some point thereafter, I started having the following issue:
When a new record is created, that record gets added to the table, but doesn't show-up in any query, form, or report until after another new record has been added. The most recently added record cannot be located to view or update, except in the table, until after another new record has been added to the table. Queries, forms, & reports now always lag behind by one record.
None of the queries, forms, or reports tested contain filters. I have several multi-user databases that I also support and none of those users have reported having this problem. This is only happening on my personal database.
I've re-created this database once or twice in the past to resolve other issues, but would like to avoid that route this time around, if possible.
I am trying to pick out the ERAP no.s out of a whole string of data. ERAP no.s are like invoice no.s. Do I Append or Make table? How do i go about doing this? I want the ERAP no. in a row next to the the description field all next to its respective string of data too....
for an example..
1)ERAP43463 STAFF TEAM LUNCH MID-YEAR REVIEW
2) 0507 SUBMITTED ITEMIZED ACCR-ERAP43159
See.. the part where the ERAP no. appears is inconsistant.. if not i could simply use excel and use text to columns. We have thousands of lines like this every week... There has to be a way to pick out just this detail and fill in the column next to the respective datastring...
Can someone please advice me on how i can go about doing this?
We are a travel company and I am just setting up a new database with two tables - [Client_Table] & [Enquiry_Table].
Most exisiting clients call in when they want to make a new enquiry so the 'user' can go it to a form which creates a 'new enquiry' for that client.
We also download 'new enquiries' from our website. This data includes info that goes in to the [Client_Table] and [Enquiry_Table]. They download in to a XL spreadsheet. Currently I copy and paste the data (not sure if there's a better way) from the XL speadsheet in to a query. This query creates a new client record & a new enquiry record which are both linked by a primary key [Client_ID].
This all works however the problem I have is that sometimes existing clients enquire through the website. They do fill in a field to say they're an existing client but if I paste them in to the query as explained above it creates a duplicate client record.
The only way around this I can think of is it to take out any exisitng clients from the XL spreadsheet first, search for their Client_ID and then paste these enquiries seperately with their Client_Id's in to different query which only adds a new record to the [Enquiry_Table] and links them to their exisitng record in the [Client_Table].
I have an append query that appends records to a table, and I have a form based on that table.
Users will click a button that will run the append query and then open a form for users to fill in remaining empty cells. How can I filter the form to show only the newly appended records?
I need to import an excel sheet containing updated tickets to my access ticket database. They have unique IDs in the form of a "Ticket ID" field.
I could just use an append query to add the new tickets to my database, but there are some changes on the excel side to tickets that already exist in access as well. What would be the easiest way to facilitate this import and overwrite all tickets with matching Ticket IDs to the new ones contained in the excel file?
I already have the excel file imported into it's own table, so it's a matter of updating my main table off of this temporary table containing the excel tickets. The field names are the same across both tables, it's just a matter of updating the info contained in them.
This will need to be repeated for multiple different excel files, so the easier the better!
I'm able to import new data from excel just fine, but I can't import updated data from excel due to duplicates not being allowed for a particular field. Is there a way to keep from importing duplicate records based on one field, but still import data from other fields where the information is different from the excel file?
I have what I think is a normalised database that uses foreign keys quite a bit.
For example if I had a 'contracts' table which refers to 'clients', then there would be a tblClients, where each client has a primary key. The tblContracts would then refer to the relevant client via that clients FK only which would be linked to the client's PK in tblClients via a one-to-many relationship.
If I need any user for any purpose to see contract related information that makes sense to a human being, I simply construct a query with the necessary relationships that will show client information alongside contract information by substituting tblContracts' client FK with required information from tblClients via the appropriate relationships.
I think that is reasonably basic stuff (hopefully correct practice!)
But what about when I come to import a block of new data that needs to go into tblContracts? I'm not going to be given a list of client keys (obviously) I'm going to given their real names.
MS Access has (in theory) all the information it needs (via the relationships) to substitute client IDs (keys) for their real names and thus slot these IDs into tblContracts with the new data as appropriate, but how do I make it do this? (I know it could kick out errors if there are any duplicate client names, but let's put that to one side for a moment).
I have two tables in the first table there is an outline number and the second table there is a task id (both are text strings, i.e. outline number and task id would be something like 8.15.3.1)
I want a query to return all the results in the first table that are not equal to the second table
I have tried the following but it returns all 54 records Code:SELECT DISTINCT RawData.TeamLeadNumber, RawData.Category, RawData.OutlineNumber, RawData.OutlineDescription, RawData.Start, RawData.Finish, RawData.Milestone, RawData.PercentComplete, RawData.DeliverableDesc, RawData.ReleasePeriod, RawData.OutlineNumber2, RawData.OutlineDescription2FROM RawData, SharePointDataWHERE (((RawData.OutlineNumber)<>SharePointData.TaskID));
I have tried changing the where clause to have it as Code:WHERE ((RawData.OutlineNumber)<SharePointData.TaskID) AND ((RawData.OutlineNumber)>SharePointData.TaskID);
but then no data is returned, and I know that of the 54 records 21 of them are the same so I should be seeing 33 records
I am getting "Truncation Error" while importing data from Excel to access 2007. I have tried changing the format to short text but the result is the same.
Can anybody see what I'm doing wrong here. I have the following query
SELECT Contacts.Cust_FK, Contacts.ContactNr, Contacts.FirstLastName FROM Contacts WHERE (Forms.ComLogDetail.Cust_FK=Contacts.Cust_FK) ORDER BY Contacts.ContactNr;
The "Forms.ComLogDetail.Cust_FK" part should render the current record in the form ComLogDetail but its not. Any ideas?? I'm an amateur programmer so my apolgies if the solution is all too simple.
I'm a bit new to SQL, and have what is probably a rather simple question:
I have a query, which i want to pick up a figure from a table.
SELECT * FROM Feb_Closed WHERE Time_to_Close>'6' AND Team_Owner='Comp& Ben';
This give me a load of rows with Time to close between 6 and 9 but nothing else. The problem it is not counting everything over 9 ie i think that it is just looking at 1 digit in the table and so ignoring 10, 11, 12, 13... etc etc.
I am setting up a database to hold staff details, and would like a query to show each member of staff's total hours and FTE.
Staff name etc is in tblStaff Staff shift details are in tblShifts linked via staffID
tblShifts will contain details of the shift worked on each day of the week, but the majority of our staff work a standard shift - e.g 8-4, 9-5. Therefore what I wanted to do was in tblStaff set a field named shiftPattern to 1 2 3 or 4. 1 indicating a custom shift, and any other number indicating a set shift defined in a separate table.
The problem I have is that my query only returns people who have details in tblShifts - regardless of their shiftPattern value. If I enter a blank record in tblShifts it will do the above as intended.
I have form setup with two cascading combo boxes (facility and date) to select a record (other navigation, including mousewheel, is disabled). There is a button to open another form which allows addition/deletion of records by facility and date. When this form is closed, the combo boxes update accordingly on the initial form. However, if I select a new record whose facility/date I have just entered, it seemingly shows the wrong values for several of the other fields. If I check the corresponding table, the values are actually at default, though, and when I reopen the form they have reverted to default. Any ideas on how to solve this? I'm tempted to simply have the first form close and reopen when the second is closed, but couldn't figure out the syntax to close a form with a button on a different form. Let me know if there's any more information I need to provide. Thanks for any suggestions.
This may not even be possible, but I am looking to create a Query that can locate records in an Access Table based on 2 columns of data that I have stored in a CSV file.
My table contains several fields, 2 of which are "Dept" and "SKU" and has over a million records.
My CSV file contains 3 fields: "Dept", "SKU" and "Total" - total being the number of times that particular Dept/SKU combination is used.
I need to be able to parse the dept/sku values from each row in the CSV to the query and locate only the records that contain the same values in the Access table.
The plan being to delete out those that are identified by the query.
