Query To Update Data To Foreign Key Field?

Jul 1, 2007

I've sent a post about this before and got some of the answer from 'Dennisk'- thanks.

I want to replace composite keys with a primary. I've just made an autonumber field on one table for my new primary key and already populated it. On the foreign table ive made a new field with 'number' datatype; this field is empty at the moment.

I'm not sure how to update the foriegn tables new field with the primary tables data. I've saw it before and remember it being easy, just can't remember how to do it.

I obviously want the numbers in the primary key field to relate to the correct records in the foriegn table.

Anyone know what the query is or if there's another way?

Thanks:confused:

View Replies


ADVERTISEMENT

Basic Query Form, Foreign Key Field

Oct 18, 2006

hello.. i have 2 tables

CUSTOMER TABLE
customerid
customername
customerphone


BOOKING TABLE
bookingid
bookingdate
customerid


At the moment i have 2 forms.. one customer form based on the customer table and one booking form based on the booking table.

at the moment, my user enters a customer record using the customer form.. they then save and close the customer form..

they then open the booking form.. and in the customer id field they enter the customers id.. this way, they successfully register that customer to that booking..


what i want to do is allow the user to enter the customer details and the booking details in one form..

i assume i would have to create a third form based on a query..

if i include all the fields from both tables in this query.. and then make a form based on this query, how can i make the following 2 things happen..?

1. when the third form is opened and customer details are entered... a new customer record will be added to the customer table (with a new customerid)..

2. this id will then need to be auto placed into the foreign key customer id field in the booking table.


do you get me?

View 3 Replies View Related

Queries :: Update Query To Replace All Data In Field

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

Linked Forms- Foreign Key Update

Apr 30, 2006

Hi,

i need help regarding linked forms..

i have a combo box in a form, where one has to select a country for example UK is chosen, then there is a button that when clicked it opens in a pop-up form and displays all the information related to UK.

so there are two tables : Country (CountryNo : primary key) linked to CountryInfo (CountryNo: foreign key) linked with a one- to -many relationship.

my problem is when you have to add new records to the form CountryInfo, how can i make the foreign key update automatically? thus when adding data to CountryInfo, the CountryNo must be the same to the CountryNo in the CountryTable..

any help will be appreciated thanks a lot.

View 5 Replies View Related

Retaining Foreign Key Values After Data Input

Nov 11, 2005

Hi

I want to be able to invoke a form from a main form that allows me to add, delete or amend records in a table related to the one updated by the main form.

As many details records are being updated, I want to retain the value of a couple of foreign keys (but be able to amend them if required) based on the value of the previous record.

I have looked at the Dlookup function and wonder if this is what I should be using. If it is, I am having trouble trying to understand how you could differentiate between the first invocation of the pop up form and any subsequent table entries. The way I understand this function is that to get the value of the previous record in the table you use "ID-1", but this would almost certainly lead to an error for records being updated for a new master record, or if the table happended to be empty then there would be no ID-1 record.

Any help would be greatly appreciated.

Thanks

View 3 Replies View Related

Tables :: Populating Foreign Key Field

Nov 16, 2013

i have made two tables with data from an excel sheet. The excel sheet has many duplicates and im trying to eliminate this. The tables are:

tblTasks and tblTeam. Both have autoincrementing primary keys, and the tblTasks table has the TeamID (primary key from tblTeam) as a foreign key.

My question is, how do i populate the TeamID field via perhaps a query, as it is blank on all records. I have over 5000 tasks so a manual approach is what im trying to avoid. A sample of the fields in the tables is as follows:

tblTasks

TaskID - PK
Task Name
Team ID - FK

tblTeam
TeamID - PK
Team Name

there's also a Staff table. tblTeam has a one to many relationship with the Staff table. tblTeam also has a one to many relationship with the tblTasks table.

View 3 Replies View Related

Update Field To Todays Date When Data In Other Field Changes

Apr 27, 2008

Hi

i have 2 fields in a form - 'Balance' and 'Date'. Quite simply I need the date field to change automatically to todays date each time the data in the 'Balance' field is changed. Can anyone advise me on how to accomplish this.

Many thanks

Geoff

View 2 Replies View Related

Update A Field Based On Data Entered In Another Field?

Jan 9, 2015

I am wanting to update data in one field which is being pulled in from another table based on an entry in another field in a form

Attached is the database. In the sales form I want to enter a customer ID which will then pull in the customer name from the customer table and put it in the Customer Field in the sales table.

I know I am duplicating the data by having customer name in both tables which is bad database design!

View 3 Replies View Related

Queries :: Importing Data Where Foreign Keys Are Involved

Jul 10, 2014

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).

View 1 Replies View Related

Why Does Access Randomly Display Data In A Foreign Language

Jun 10, 2014

I noticed that when I made a few changes to one of my queries, the results in my data sheet view were in some sort of Asian looking language. See below. Why is it doing this? I've only been using English with this program and have not changed any of my settings so I'm very confused. Also the other fields are in English. This one field is the only one I'm having issues with.

















[code]...

View 8 Replies View Related

Populate New Record's Foreign Key Field Automatically

Aug 2, 2005

I have a form where all records are listed on one page with a vertical scrollbar (all simple textboxes):

----------------------------------------------
PrimaryKey Name ForeignKey
----------------------------------------------
1 Pam 20
2 John 20
3 Kim 20
4 Larry 20

... underneath these on the form is a blank record for NEW additions, which obviously has nothing in any of its textboxes, waiting for user to input some data, as normal.

The Foreign Key, due to the nature of my DB, will always be the SAME value for each record in the DB (don't ask, it's part of an export/import-to-main-db app). So I know that the NEW record, if created, will have the same value as the rest of the records.

How do I get it so that when a new record is created, the Foriegn Key textbox is automatically filled in with the value from the row above? (Due to referential integ reasons the Foreign Key field MUST be filled in accuratly or else an error ocurrs.)

Looking at the various events, I realise that it is the BeforeInsert event that will sort this out, but I'm at a loss regarding how to get the NEW record's Foreign Key textbox to match the Foreign Key value above it.

Any advice?

View 4 Replies View Related

Tables :: Inserting Foreign Keys Within A Text Field?

Aug 28, 2013

I've got a table - "Products" - in my database, with a text field - "Info" - which contains info about products.

Within this field I would like to have footnotes. To do this, I think the best way to do it is by putting numbers inside the text at the location of where I want the footnote to refer to. These numbers will actually be foreign keys to a table called 'Footnotes'.

I can then program the forms and reports to show any numbers as superscripts or whatever.

(Of course, if the user will actually want to insert a number into the text field which is NOT a reference to a footnote, I will have to make a workaround e.g. by making access put a symbol in front of the number, so access will know the number is just part of the text (and I will program the form to not show the symbol in front of the number)).

Is it wrong to have foreign keys within a text field? I think if executed correctly, it should work perfectly.

View 8 Replies View Related

Forms :: Auto Complete Form Field With Foreign Key

May 24, 2014

I am trying to auto populate a form from a table containing CustomerID, CustomerName and PCCID_FK

Now in the form I want to auto populate the field cmbPCC when I update the cmbCustomer

using dlookup I've managed to show it manually
=DLookUp("PCCID_FK","tblCustomer","CustomerName='T est'")

It gives the ID of PCCID_FK which is what I want. how do it I modify the expression to replace the TEST part with a value in a combo box.

or is there a better way of accomplishing this.

View 1 Replies View Related

Forms :: How Data Is Passed Using Foreign Keys - Cannot Display Proper Information

Jan 25, 2015

I'm trying to understand how data is passed using foreign keys.

I'm using Allen Browne's 'Don't use Yes/No fields to store preferences' at: [URL] ....

I've also downloaded his sample DB, RelationBasics, to use as a guide.

Attached is my version of the Student / Sport DB as described on the webpage.

I use 2010 at home (saved as 2003 version) and 2003 at work (JPNSE OS). Both result in the same thing.

The problem I'm having is I cannot get the actual sports to display in the combo-box, only the Sport_ID number.

I've tried building both forms with and without actual data in the TBL_Student & TBL_Sports tables, but no mater what the result is the same.

View 2 Replies View Related

Import Excel Data Into Existing Access Table With Foreign Key Fields

Apr 25, 2015

I used to import excel data into access successfully, many times but now I have to import excel data into an existing Access table with foreign key fields, which makes me problems.

Its just doesn't work...and Im sure the forien key fields are the prob cause, the other fields are going well ...

View 1 Replies View Related

How To Update A Field Data To A Certain Mask?

Feb 2, 2005

Hi everyone,

I've been thinking of this for days.
I have a table field that contains SSN data. Unfortunately, during the data import from the legacy database, all the front "0"s had been truncated. The current DB SSN field is a text data type which can handle the "0"s at the front. The table has about 50000 records which unable to modify each individually.
I need to modify the SSN data back to fixed length by adding "0"s at front. I think it will put too much effort to write a VB app to do this. I prefer to do this through the SQL. Dose anyone know how to do this? Thanks in advance.

View 3 Replies View Related

Update Data From One Table To Another Only If Field Matches

Aug 28, 2014

I have to two tables and I was add data to second table only if the field matches. Lets I have two fields site and recid on both and I want to update site on the second table only if recid from the first table matches the recid from the second table.

View 1 Replies View Related

Update A Form Field By Double Clicking A Query Field

Apr 25, 2013

I have 3 peices of data that I am working with: Group number, plans and benefit elections. On my form I have fields for the group number and plan and 2 queries. When I enter a group number the first query displays a list of plan descriptions for that group. From there I can enter the plan description into the form's plan field and that runs the second query to give me a list of valid elections for that plan. This all works fine but I want to make the plan selection a little less tedious. What I would like is to be able to double click the query field housing the plan description and have it copied to the form's plan field. I could use the ID instead of the plan description but I work in a production environment and very key stroke counts so I would really like to have a simple double click process.

Is this even possible? If so how would I set that up? I tried using the double click on event macro builder but it does not seem to have this kind of option.

My form contains fields for group number and Plan. The same form houses 2 querys, one that pulls plans based on the group number and the other to pull elections based on the plan field (not the plan query). Would like to double click a plan within the plan query and have that description populated into the form's plan field.

View 6 Replies View Related

New Recordset's Memo Field Data Lost On Update

Feb 29, 2008

morning

i am adding a new row to an adodb.recordset, one of the fields being a Memo datatype.

all the other fields will write to the database fine, but with the memo, it will mysteriously disappear when i call rs.update

response.write(rs("my_memo_field")) 'give correct output
rs.update
response.write(rs("my_memo_field")) 'gives nothing!

any one else had this happen and have a solution?


cheers

View 2 Replies View Related

I Need To Update Data In A Table With Information From A Calculated Field In A Form

Oct 12, 2006

I've read over and over that calculated data is bad, that's fine with me... but here is my dillema...

I have four fields that interact with my calculation in a single form. There is only one table in the database.

AmountF (The stored data in the table) (amount financed)
FC (calculated by =[amountf]*.25) (finance charge)
Amount Paid (used in calculation of total due)
total due (calculated by =[amountf]+[fc]-[amount paid])

The math already works, everything works. I can choose to print the form and it looks great. However, I would like for it to (when I'm done printing the form) somehow save the total due data to the AmountF column in the table. AmountF is in the same line as all the other information, so I would like it to not create a new record just update the old one.

All four of these fields have a text box in the form for themselves.

View 4 Replies View Related

Forms :: Update Table If User Add New Data In Combo Box Field

Jan 9, 2014

I have a combo box that get its values from another table the problem i am having is when a user don't see the info they have in the combo box then enter the new data into the combo box field but it don't update my table with the entered values. How can i fix this to update my table if the user add new data in the combo box field.

View 3 Replies View Related

How To Update A Field In Subform When Main Form Is Data Entry

Sep 19, 2012

I have a mainform set to data entry to add records to TABLE1. I inserted a subform that shows fields from TABLE2. I have a field in TABLE2, let's say TBL2FLD1(NUMBER). I want to add TBL1FLD2(NUMBER) from TABLE1 in the mainform to TBL2FLD1 and update said field in TABLE2.

My problem is:

(1) I get a #TYPE! error when I try to add the two fields, which I have tried many, many ways (adding them together in the Default Value; creating an unbound field to hold the calculation and then putting that field into the Default Value of TBL2FLD1; ETC), and
(2) I don't know how to get the subform to update TABLE2. It seems like the control gets stuck in the subform. Is there a way to accomplish this without coding it?

View 3 Replies View Related

Forms :: Select From Combo Box During Data Entry And Automatically Update Field

Mar 13, 2013

I have one field AccountName in customer table and another field AccountID.

In my form I would like to select from the combo box AccountName during data entry and then have the AccountID automatically update in the Account ID field.

View 2 Replies View Related

Different Data Type In An Update Query

Jun 9, 2005

I want to say update set field1 = field2

But field1's data type is Number and Field2 is Text. (Field1 is fed by a lookup table). Am I doomed to go through the 700 or so by hand, gurus, please?

View 5 Replies View Related

Query Data To Update Table

Aug 10, 2006

I would like to run a query and then be able to edit that information. I want the data I change to be put into the table the info was gathered from. Right now when I edit the query data it only saves it there, not on the other table. I tried to do the copy to table but the original table was going to be erased and then written over. What should I do? thanks

View 3 Replies View Related

Query Wont Allow Data Update

Mar 17, 2008

This should be easy, but I have spent hours on it and can't find the answer. Can anyone see why the query 1qryMaintAssets does not allow a change to or an addition to the data??

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved