Queries :: Update Data From One Database In Another One
Aug 23, 2013
I'm relatively new to MS Access (using MS Access 2013 but the db should work on 2010, too) and try to develop a database for an NGO I'm working in. [...].
However now I start to create forms and later reports for the actual user. The database will store information about clients and track consultations and assistance the NGO gives to them. There will be around 50.000 to 70.000 clients in the main table. Every client has a specific Individual ID and is member of a family which itself has another specific Group ID.
So now, I am almost done with forms (at least I want to believe that). But I ran into an issue I would love to have your comment and different approach on:
The Database I create is projected on another, significantly bigger database! The one I create is a kinda light version for other field offices with only the information they need to have, and additional tables for them to gather their own data and track their own activities. This being said, the light database needs to be updated every month once by the big (mother version). Both DBs cannot be connected!!!
So we will send to the field offices altogether 5 tables in one mdb-file every month. These 5 tables exist with the same structure ( name, field data-types, etc.) in the light version.
I now want to make an automatic update option (by pressing a key and select the "update-file"), i.e. based on the the respective Primary Key of every table the entry in the smaller DB should be updated!
Update for me means the following three things (Assume big DB is A, small DB is B):
1) If Primary Key (PK) exists in both tables of A and B, update the rest of the fields in B belonging to that key based on the specific record of A
2) If PK does not exist in A but in B, delete record from the database B (case: record was deleted in A after last update)
3) If PK does not exist in B but in A, add the record to database B (case: record was added to database A after last update)
I would like to show you what I already found in the internet on solving task 1) but I cannot post links due to my status.
Both links basically describe how to update one table by another in importing or linking the update-source table in the current DB, create a query with both tables, link the PKs of both and then using the Update To Criteria [UpdateSourceTable].[Field] for each field that I want to update.
But I wanted to ask before I implemented that if I should make a complete different approach since I also have 2) and 3) as tasks....
View Replies
ADVERTISEMENT
May 12, 2015
I have a database that updates daily. Each day I need to set the values of certain columns back to zero. At the moment I'm doing this manually and it's taking time since I have to update a number of columns. Very simple, I just create an update query, update to 0, run it and done.What I'm wondering is if there is any way to do this with a click of a button external to Access? Like export the update query to the desktop, double click it and it just updates those columns to zero.
The file location is always the same and the table structure is always the same (one table with about 40 columns of numbered data) but, the database is newly generated daily. So if I save the update query today, when it's generated tomorrow, I have to create it again as it's a new database.
I was thinking maybe a batch file to open access, open the database and then run the query but I think this would only work if the query was within the database and since it's newly generated daily, it won't work.if it's possible to export an update query somehow so I can click on it and apply it to the new database each day. As above, the structure and tables/names are the same very day.
View 3 Replies
View Related
Jun 30, 2014
I have data in all sorts of tables and databases that are linked together through different systems. I'd like to update some of the information automatically in the evenings or in the middle of the night without needing someone to push a button. Is there a way to do this?
View 9 Replies
View Related
Oct 28, 2013
I have made a database for data entry, currently i have a challenge of getting it update and navigate.
On the form if the staff name is xyz it should only shows the records filled by xyz in form and navigate that records only. I am attaching the data base also....
View 3 Replies
View Related
Apr 15, 2015
I am building a stakeholder database in Microsoft access 2010 and I want to be able to say that a stakeholder attended an event. I have managed to do this but I can only say that one stakeholder attended an event at one time. This is quite a problem as there can be up to 800 or even more stakeholders attending an event so to go through and click each one would be very time consuming.
My current set up is I have a stakeholder table an events table and an event attendance table. It all works fine apart from only being to edit one record at a time. I have tried update queries with no success, I can attach the database but would need to remove the data for data protection reasons.
View 3 Replies
View Related
Jun 5, 2013
I have two tables "TABLEA" and "TEMP"
fields in both tables are
Cust ID (Primary key)
Cust Name
Address
Cheque No
Amount
Location
Zone
I need query when i click on command button on form
if "Cust ID" which is primary key in "TEMP" Table match with "Cust ID" from "TABLEA"
It will update the record in "TABLEA" if not then append the record
View 1 Replies
View Related
Apr 25, 2014
I need to update data in a bunch of tables of a sql server database. The database has 300 tables which I have linked via odbc. I'm hoping there is a simple way, using vba, to loop through my linked sql server tables and determine which of them are views as opposed to tables.
View 1 Replies
View Related
Oct 23, 2014
How do I get an update query to only copy part of a fields value?For example: The original field has a date in month, day, and year. I only want to update the new field with only the year.
View 7 Replies
View Related
Jul 24, 2014
I have been spending all my today to fill a combobox dynamically, but have not been able yet.
I have a combobox and a pass-through query in access, which is working fine and fill the details into the combobox via data source. Now what I am planning to do is to update the combobox source as soon as value in a text box changes.
Here is the code I am using, but it is not working:
Dim rs As Recordset
Dim qDef As QueryDef
Set qDef = CurrentDb.QueryDefs("get_data")
qDef.SQL = "SELECT Initial + ' (' + Name + ')' uws FROM EM.dbo.UW" _
& " WHERE lob = '" & addSingleQuotation(Me.CMB_LOB.Value) & "'"
Me.cmbUM.RowSource = qDef.SQL
Me.cmbUM.Requery
I also used Recordset, but did not work:
Set rs = CurrentDb.OpenRecordset("get_data")
Me.cmbUM.RowSource = rs!uws
View 1 Replies
View Related
Aug 6, 2013
In access Im working with two tables, this is my setup
tableA.documentnr
tableA.revison
tableB.documentnr
tableB.revision
Both tables are filled with data, Table B contains the same kind of data as table A, But tableA has documentnumbers with different revisions (for example revision a,b,c, for each revision a seperate row). Table B might have an identical document, but just one revision (like revision a).
Now I like to append the data of tableA to tableB, except if a revision is similiar to a revision in table A. (There is more metadata involved, but I will do it step by step)
Im not working with primarykey data, becayse in the end result table B will also have multiple (identical)document numbers with different revisions on different rows.
I tried to use the update query but it doenst append the documentnumbers where the revision is not present in table B I attached a image of the tables.
View 9 Replies
View Related
Apr 8, 2013
Is it possible for an update query to simply replace all the data in a table's field with the results from a query?
For example, I have Table A with the field Years. This table performs various calculations within based on the field Years.
I would like an update query that simply replaces the Years field data in Table A with a new set of years based on a query's search criteria.
When I run the below query, I get an error saying the query must be updateable- but Im not trying to update the query, rather the Table
I've attached a photo ......
View 1 Replies
View Related
May 8, 2013
All; using 2010. I have a table that I need to update some data from another table. I want to use the SSN but one of the SSN fields in the table has letters at the end of it and doesnt return any records. How can I join fields?
View 1 Replies
View Related
Apr 23, 2015
I have a table that is updated from an update query which gets its data from a table, which gets its data from a form. I have set the query to only update the current record, this is done by a macro when the user exits the input box. The problem arises when a user goes to visit past record, I would like it to not overwrite the old data with the new data. If the query was only allowed to ADD data and not overwrite then this would fix the problem!
My SQL code is as follows:
UPDATE [Run Info], [Sieve Weights] SET [Run Info].[315 Tare] = [Sieve Weights]![315 Tare], [Run Info].[250 Tare] = [Sieve Weights]![250 Tare], [Run Info].[200 Tare] = [Sieve Weights]![200 Tare], [Run Info].[160 Tare] = [Sieve Weights]![160 Tare], [Run Info].[100 Tare] = [Sieve Weights]![100 Tare], [Run Info].[75 Tare] = [Sieve Weights]![75 Tare], [Run Info].[50 Tare] = [Sieve Weights]![50 Tare], [Run Info].[BD Tube Tare] = [Sieve Weights]![BD Tube]
WHERE ((([Forms]![1L Input form]![ID No])=[Run Info]![ID No]));
View 5 Replies
View Related
Sep 18, 2014
I have a database that needs data to be reentered every school term, at the moment i am having to delete selected data fields manually. im looking to create a query so that the data is deleted by running it. The data would be returned to a blank field. I have tried using a delete query but it is asking for the selected table, even though a selected table exists. Using the update query i am faced with updating the query to a typed word however i just want it blank.
View 1 Replies
View Related
Jul 25, 2013
I have an update query for tGLCashAccount where it adds a value from another table with the BeginningBalance to arrive at CurrentBalance.
Here's what it looks like in design view:
Field: CurrentBalance
Table: tGLCashAcct
Update to: [tMakeNewCashBal].[TotalPrice]+[tGLCashAcct].[BeginningBalance]
Here is SQL code:
UPDATE tGLCashAcct, tMakeNewCashBal SET tGLCashAcct.CurrentBalance = [tMakeNewCashBal].[TotalPrice]+[tGLCashAcct].[BeginningBalance]
WHERE (((tGLCashAcct.GLCashAcctID)="102"));
I get the error: data type mismatch in criteria expression when I run it.
View 3 Replies
View Related
May 24, 2015
What query do I use to add data from one db [same file names] to another db?
View 1 Replies
View Related
Oct 10, 2013
So I am pulling data from a SQL data base into access and appending it to certain tables. To do this I have 15ish append queries. Is there a way to have them run in a certain order with one command?
View 3 Replies
View Related
Jul 28, 2014
I have a MS Access table containing Dependent social security numbers but some of the dependents social security numbers are blank.
I have a MS Sql Database that contains a table with most of the missing MS Access table dependents Social Security numbers.
How can I take the Dependents social security number from MS Sql Database and copy/update the MS Access table.
I tried the sql code below created from MS Access and it matches 453 records out of 460 dependent social security numbers but how do I update qryGHIAccuracyFile_MissingDepSSN.SSN with the social security number from dbo_depfile1.dep_depend_ssn
Code:
SELECT dbo_depfile1.dep_depend_ssn, qryGHIAccuracyFile_MissingDepSSN.SSN
FROM qryGHIAccuracyFile_MissingDepSSN LEFT JOIN dbo_depfile1 ON qryGHIAccuracyFile_MissingDepSSN.MemberSSN = dbo_depfile1.dep_ss_nbr
WHERE (((dbo_depfile1.dep_first)=[FirstName]) AND ((dbo_depfile1.dep_last)=[LastName]));
Updating MS Access table dependent blank SSNs with SSN in MS Sql Database.
View 2 Replies
View Related
Sep 24, 2013
I am trying to run a simple update query to copy data from one column (Addrl1)to another column (Working_Addrl1) within the same file and I can't for the life of me figure it out. Then I need to repeat for addrl2 and addrl3 to working_addrl2 and working_addrl3.
View 7 Replies
View Related
Feb 21, 2014
I've been requested to make a bird count database.
The bird count data has been recorded on monthly papers like this:
[bird name] [week 1][week 2][week 3][week4]
The number of birds sighted for a given week is written in the [week x] columns.
What I need to do is make another column that shows whichever number is highest from columns [week 1], [week 2], [week 3], [week 4].
So for example:
Blackbird: Week1: 4, Week2: 2, Week3: 6, Week4: 2
highest: =6
View 5 Replies
View Related
Nov 7, 2013
I have an "order details" table that needs to populate a field called "Voucher" with data from another table called "codes". The "codes" table also has a true/false field called "allocated" because once allocated the code cannot be re-used.
I am trying to work out how to automatically allocate the next unallocated code in the "codes" table to each record in the "order details" table when that order details record has a DiscountID of "92".
Order Details Table Fields and conditions/criteria:
ID - primary key
DiscountID - only when the DiscountID = 92
Voucher - only populated when Discount ID = 92
Codes table Fields and conditions/criteria:
ID - primary key
code = text field with a code like "einstein01", "einstein02"
Allocated = False
Is there a way to put the next available code into the order details record then mark that code as allocated in the codes table. Then, move on to the next order details record that has a discountID = 92, input the next unallocated code and mark that code etc. etc.
Ideally, I would like to do this to happen via an event when the Order forms button "Close" is clicked.
View 1 Replies
View Related
Apr 13, 2013
My membership database has worked fine until recently. Now I cannot save inserted data. On attempted saving "Update or CancelUpdate without AddNew or Edit" appears.
The problem. relates to 2 tables Member and Addresses. PK in the parent table Member is ID. In the Addresses Table the FK is ID. There is a One to One relation between the tables and Referential Integrity is set. I know 1 to 1 is not good but it worked fine in this small database.
Browsing the all of the existing records is fine.
View 9 Replies
View Related
Mar 30, 2006
I am trying to stop access displaying the "You are about Update 1 Record" etc message when i run an update query. I know i can do this in Tools/Options screen but the problem is that the database is going to be used by multiple users, and rather than changing each persons Action query option I was wondering whether there is something i can put in to the code Globally to halt the message.
Any help would be appreciated.
Regards
Mark
View 2 Replies
View Related
Aug 7, 2007
This is a very simple problem most likely for the masses, but I am new to access. I have employees who enter will enter information about specific tests on electronics components into separate tables by a form. I know my method is poor, but this is how it works. I got assigned this database at my internship this summer because they ran out of thigns for me to do.....I've never even used access before, so as long as it works, they're happy. I have a form which which writes to a first table with 20 fields. I then have separate update queries which take the data in the first table and put it in all the other tables. Trust me, i know this is pretty much the opposite of the whole point of a relational database, but I am and was limited by time, for the size of the database they want, there was no time to learn about normalization etc etc. Sorry this is wordy, but THE QUESTION is......if they run multiple tests wtih the same information for some of the fields I have fields named "Run#, Unit#, Date" etc, even if there is records wtih the same information, hwo do i get it all to show. Right now, it seems to be rewriting over the same records. If they run 3 tests on unit 10 on August 1st, how do i get it so all those show up. I think its somethign wtih in the table, for the primary keys, changing the Index: No, Yes(Duplicates) Yes(No Duplicates) but I could be way off. Thanks, if the question doesnt make sense i'll try to rephrase it, I apologize I'm running on about 45 min of sleep:confused:
View 3 Replies
View Related
Nov 18, 2014
I'm using Microsoft Access 2010. I want to create a database that people without Microsoft Access can use. If I create a database is it possible to use VBA in Excel to update a table in Access and then run/export a query? I know you can use Excel to communicate with MS Access but can you do it when you don't have MS Access installed on your computer?
I have daily sales data that I want someone without MS Access to be able to load into the database and then export a query from.
View 2 Replies
View Related
Jun 18, 2007
I looked for this via the search tool and found some things sort of related, but different enough where the solution wasn't clear (to me anyway), so I'd be most grateful if someone could find a moment to help a newbie.
I created a data entry tool that's being sent to multiple users, simialr to at least a dozen or so other ones I've done. But this time, I've got a user who, the first time she tried to enter any value into the opening form, gets a message of "Cannot update database or object is read only". This database has been tested on multiple computers and worked fine...she checked the properties tab and it is not set to read-only. Any ideas? Thanks in advance...
View 2 Replies
View Related