Transfer data from one table to another

[guywhite4]

Hey everyone,

I am a complete lamen on this, I am finding. Here is what I want to do.

I have an access database file with two tables inside it: Distributions AND Plan Data

In both tables, I have the following fields:

CRS ID Number:
Plan Name:
Company Name:
Company Address 1:
Company Address 2:
Company City:
Company Zip:
Company Federal Tax ID:
Company State Tax ID:

I want to be able to type in a 3-digit CRS ID Number in my form for DISTRIBUTIONS and for it AUTOMATICALLY to find that CRS ID number in PLAN DATA and populate all these common fields using data from PLAN DATA, inserting it into the DISTRIBUTIONS table.

The reason I need it to pull the data from PLAN DATA and insert it into DISTRIBUTIONS table is because we are using some out-of-access features that require us to have all data in one table. Thanks!

 

[GingerR]

How about writing an APPEND query? So you'd enter a CRS ID number and hit a button that fires the APPEND query.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[guywhite4]

Can you be more specific? I dont know much about this, as I am finding out.

 

[lespaul]

First off, storing the same data in two locations is normally discouraged; having duplicate tables is even worse....

Additionally, you say:

two tables inside it: Distributions AND Plan Data

but then you say:

require us to have all data in one table.

So which is it? one table or two?

Why not just have one table and when you enter the 3 digit CRS use the data you have instead of duplicating the data you have?




Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[guywhite4]

In the DISTRIBUTIONS table, there is client data. In PLAN DATA there is tax information. One department at my firm works with the client data -- the others work with the tax info held in PLAN DATA.

So, can I type in a 3 digit code and have it find the matching entry in PLAN DATA and copy the data to distributions?

Thanks

 

[GingerR]

Hi--Leslie is right, altho I didn't harsh on you about it. What if a company's address changes? Are you going to go change it in both tables?

In any case, have you ever written a query? Please try it now. Say your form has a text box called txtCRSID. Write a query on your PlanData table with Forms!FormName!txtCRSID as the criteria. Type in a CRSID into your form, and run this query and see that it filters on the CRSID. Then go back to the query, change it to an APPEND query, Append it to table DISTRIBUTIONS and set which fields are to be populated. Run it again, see that it appends data to your table. If you are very confused, please search HELP or get a text book as this is very basic stuff.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[guywhite4]

Thanks for not hitting me with a hammer over this. Can you be more specific on this process?

 

[GingerR]

Please try making a query and get back to us after that.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[guywhite4]

Alright:

I went to Queries.

Created a query in design view.

Double clicked on Plan Data (my source).

Clicked Close

Field: [CRS ID Number:]
Table: Plan Data
Sort: *blank*
Show: (checked)
Criteria: [Forms]![Distribution Database]![CRS ID Number:]

Saved as Query1

Went to Macros. Created an OpenQuery macro that opens Query1 in datasheet view, in edit data mode. Saved as Macro1.

Went to Distribution Database form.

Edited event property of CRS ID Number: to run Macro1 onchange.

Typed in CRS ID Number: 1.

A screen appears showing my query. It seems to work.

Now, how do I get it to move the data?

 

[guywhite4]

Ok, so now, it appends the data to the table as separate rows.

What I want it to do is to simply update the null fields in my destination database to the source databases data.

 

[GingerR]

Oh, so you already have the CRSID in the destination table?

Write an UPDATE query instead. You'd bring in the other table to, and make a join (draw a line) from CRSID in one to CRSID in the other.Put the fields of the destination table in the grid below, and in the UPDATE TO row, put

[SourceTableName]![FieldName]

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[guywhite4]

So is it like this....

Field: sourceField
Table: sourceTable
Update to: [SourceTableName]![FieldName]
Criteria: ?

or

Field: destinationField
Table: destinationTable
UPdate to: [SourceTableName]![FieldName]
Criteria: ?

 

[GingerR]

Field: CRSID
Table: Destinationtable
UpdateTo: nothing
Criteria: Forms!FormName!txtCRSID

Field: BusinessAddress
TableestinationTable
UpdateTo: [SourceTableName]!BusinessAddress
Criteria: NONE

no other criteria for any field


You are updating the DESTINATION TABLE with data from the SOURCE TABLE. So you TABLE here would be the destination table, the one with no data in it THAT YOU WANT TO "UPDATE". This is called an UPDATE QUERY, updating data that is already there. Just in your case, the "data" is blank. But the record is already there that you want to "update".

you can put the criteria of the txtCRSID on either table, because you'll have a join between the two tables on CRSID.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[guywhite4]

Ok, we are definatley getting somewhere.

I have the CRS ID field in the Distribution form set to run the query when I update the field. It warns me that I am going to be updating some fields, but then nothing happens. When I go to the next record, it tells me that some "other user" has edited this and that I can drop the changes or copy them to the clipboard? When I click copy to clipboard, they appear?

Wierd.

 

[GingerR]

Ok, so you are on a form, and click the button. The data is added to the table, but the form will not REFRESH and show the data unless you tell it to. At the end of your code, try Me.Requery.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[guywhite4]

... Where do I place this code? I am so new to this.

 

[guywhite4]

Ok, Ginger, you have been a huge help! Thanks! I have it working... with the exception of the requery... in that I have to click a button twice for it to work. But I am fine with that! Thank you for all of your help!