Update Table To Replace Primary Key

Mar 8, 2007

My database tracks all of the staff development sessions provided for the past ten years, thus we have thousands of records. Our school district wants all departments to move away from using an employee's Social Security number to a number assigned by the district. Each employee has been assigned a unique six digit number.

In my staff development database, the employees data (Social Security number, campus, job description, etc.) are in a table, "EmployeesTbl", with the primary key being the Social Security Number. Classes data are in a table, "ClassesTbl" with some of the fields being ClassNum (an autonumber), class name, class description, etc., with the ClassNum being the primary key
in this table.

A third table, "AttendTbl", links the two and shows each class (staff development session) each employee has taken. This table has a a foreign key, "SocNum", that links it to the EmployeesTbl, and another foreign key, "ClassNum", that links it to the ClassesTbl.

I have added the EmpNum field to the EmployeesTbl, and all of the the district assigned employees' numbers have been entered. I have also added this field to the AttendTbl. Is there a means of using a query to fill in the employees number in this table. Is so, I'll then change the primary key in the EmployeesTbl to be the employee number and the foreign key in the AttendTbl to be the same. If not, we face the task of having to key in 18,000 records!

Thanks for any help,
R. Sanders

View Replies


ADVERTISEMENT

Update Query - Replace Function Problem

Oct 31, 2004

I'm having problems with an assignment in an online course I am taking.

The assignment is to create one Update Query to find & remove typos in a field. Specifically, the typos are multiple f's & g's embedded in a field.

I created an Update Query that finds the typos & removes just the f's. I'm having trouble with the syntax to also remove the g's.

This is what I have so far that works great.

Update: Replace([Field2],"fff","")
Criteria: Like “*fff*”
or: Like “*ggg*”

The Query finds the records that contain both the "fff" & the "ggg" typos but I'm having trouble with Syntax for removing both.

Does anyone know what I have to change in the "Update:" line to include removing the g's?

I know I could easily create 2 Querys to remove the f's then the g's but the assignment requires only 1 Query to remove both.

Thanks,
Yaani-Mai

View 5 Replies View Related

Queries :: SQL UPDATE That Adds But Doesn't Replace?

Aug 27, 2013

Right now, I'm working with an SQL code of

Code:
"UPDATE Individuals SET [ShareholderOf] = " & Me.CompanyNo & " WHERE [Name] = '" & PerName & "';"

However, doing so will obviously change the "Shareholder of" field into what the user inputs (Me.CompanyNo). What should I use if I want it to ADD the user input rather than REPLACING the old [shareholderof] value?

View 6 Replies View Related

General :: Update Query To Replace Letters And Digits

Feb 27, 2015

We have a field of 7m records of varying length, some of which are numbers, some just letters, some alphanumeric, and most which include a dash, space or some sort of punctuation mark.

We need to replace all letters with "L", then replace all digits with "@".

We can replace characters individually using this update query:

Replace([Ref Digit Or Letter],"A","L")

But would like to use wildcard searches to resolve this, something like (these don't work!)...

Replace([Ref Digit Or Letter],"A-Z","L")
Replace([Ref Digit Or Letter],"LIKE [*A-Z*]","L")

View 13 Replies View Related

Queries :: Update Query To Replace Number With Text

Apr 15, 2014

I've made a simple form to Login/Logout with radio buttons but the buttons only allow me to push a number as a value, in my case 1 or 2 for Login or Logout.

How would I make an update query to change those numbers to the equivalent text? Or is that not possible in the same field because that is 2 different data types?

View 5 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

Possible To Update 180 Queries With Find / Replace Script Or Function?

Apr 2, 2014

The company I work for is consolidating regional server space onto a single server. I have relinked all of my tables, but my ~180 queries are still pointing to the old server.

My question: Is there a script or other process that can be executed that will search through all my queries within the database to find the string "dbo_tbl" and replace with "dbo_vwtbl"? I would very much like to avoid taking each individual query to a notepad...

View 3 Replies View Related

Queries :: Make Replace Query To Look In Order To Find What To Replace

May 6, 2014

I have this working query:

Code:
INSERT INTO TB_SISTEMAS ( LOGIN, SISTEMA, PERFIL, DATA )
SELECT Left([dbo_BACKUP_ACESSOS.LOGIN],255) AS LOGIN, dbo_BACKUP_ACESSOS.SISTEMA, Left([dbo_BACKUP_ACESSOS.PERFIL],255) AS PERFIL, dbo_BACKUP_ACESSOS.DATA
FROM dbo_BACKUP_ACESSOS
WHERE (((dbo_BACKUP_ACESSOS.SISTEMA)<>"ACTIVE DIRECTORY") AND ((dbo_BACKUP_ACESSOS.DATA)="2014-03-23"));

But Iwant to be able to use a set of data to be used in the Replace Statement, so I create a table to add each string I would like to have replaced by "nothing", and trying to make the replace query to look there in order to find what to replace.I also created a table where I will list the systems that I dont want in the select, so I removed the "ACTIVE DIRECTORY" and replaced by the colum that have the list of system I dont want listed.This is the result:

Code:
INSERT INTO TB_SISTEMAS ( LOGIN, SISTEMA, PERFIL, DATA )
SELECT Replace((Left([dbo_BACKUP_ACESSOS.LOGIN],255)),[PREFIXOS_E_SUFIXOS]![Valor],"") AS LOGIN, dbo_BACKUP_ACESSOS.SISTEMA, Left([dbo_BACKUP_ACESSOS.PERFIL],255) AS PERFIL, dbo_BACKUP_ACESSOS.DATA
FROM dbo_BACKUP_ACESSOS
WHERE (((dbo_BACKUP_ACESSOS.SISTEMA)<>[SISTEMAS_EXCLUIDOS]![Sistema]) AND ((dbo_BACKUP_ACESSOS.DATA)="2014-03-23"));

The thin is that this keeps asking me to enter the parameter value for "PREFIXOS_E_SUFIXOS!Valor" and for "SISTEMAS_EXCLUIDOS!Sistema"

View 6 Replies View Related

Change Primary Key And Update Data?

Jul 25, 2007

Hello,

Firstly, I am very much self-taught on access, so there are several gaping holes in my knowledge, and my database structure is probably not wonderful! I have been learning as I go along, so in the initial stages I have built in several problems which I am now discovering!

My db is designed to track suggestions - i have a table where the idea, progress etc is stored [tblIdeasBank]. This contains an 'originator' (person's name) which is linked to a second table, where the names are stored [tblPeople].

Originally I set up tblPeople with the Primary Key as FullName (e.g. Joe Bloggs) (guaranteed to be unique with the small number of people that would be involved), and a second field called InitialLastName (e.g. J Bloggs). It is this second field that is stored in the tblIdeasBank. Data verification came from the user having to select a name from a drop down box, so there is no actual 'relationship' between the tables. (Didn't realise how useful they were at the time!)

The db has been so successful :confused: that it is now going to be rolled out across the company, which gives me two problems. To populate all the names, I need to import them from Excel, where I will have FirstName and LastName. I have now realised that it would be more sensible to just store these, and create InitialLastName where needed. Also, I may well have duplicate names, so I need to create a unique ID, and a proper relationship. (Yes, I know I should have done that in the first place).

So, I have:

tblPeople:
.FullName (Primary Key)
.InitialLastName

tblIdeasBank:
.Originator (stored as InitialLastName)


And I would like:

tblPeople:
.PersonID (Primary Key)
.FirstName
.LastName

tblIdeasBank:
.IdeaID (Primary Key)
.Originator (stored as PersonID)

With a One-Many relationship from PersonID to Originator

Any ideas how I would go about doing this and changing the Originator for each idea from InitialLastName to the relevant PersonID number, without corrupting the data?

I have searched through Google and various groups, but cannot find a useful answer, so any pointers gratefully appreciated!

Thanks,

Nick

View 6 Replies View Related

Primary Key Violation In Update Query

Apr 21, 2006

Hey, I am getting a primary key violation when I try to run an update query.

My primary key is a combination of two fields, ScheduleID and SchedulePage. That way for each schedule I can only have one Page 1, one Page 2, etc.


When I want to insert a page (say a new Page 2), I need to update the table so that Page 2 becomes Page 3, Page 3 becomes Page 4, and so on.

The problem is, since it starts at the bottom, when I tell it to increase the page number by one, it's conflicting with the primary key of the next record.

Any ideas? BTW, if the solution has to do with sorting, I need to make sure it's something that always defaults back to the correct sort, since users may be able to change the sort and accidentally save it. Plus, I'll need to do the same thing in reverse (delete a page).

DoCmd.RunSQL "UPDATE Pages " & _
"SET Pages.SchedulePage = [SchedulePage]+1 " & _
"WHERE (((Pages.ScheduleID)= " & varScheduleID & ") AND ((Pages.SchedulePage)>" & varSchedulePage & "));"

View 3 Replies View Related

Modules & VBA :: Replace Values In One Table Based On 2nd Table

Jan 12, 2015

I have a table (tblConversions) that I'm using as a base for replacing values in a different table. tblConversions is set up as such and is made up of nearly 100 records:

ID LABEL CODE
1 Ashaway ASH
2 Barrington BAR
3 Bristol BRI
4 Jesse Smith BUR
....

Another table (tblSysItemLoc) has nearly 1,000 records with a field for Location Names such as Ashaway New Books, Ashaway Fiction, Ashaway Non-Fiction, Barrington DVDs, Barrington Reference, etc. Other fields in this table are just statistics.

What I need to do is loop through tblConversions, and find in tblSysItemLoc where the Location field Starts With the value from tblConversion.LABEL and replace the Entire field with the CODE. For example, from tblSysItemLoc "Ashaway New Books" gets changed to "ASH", "Barrington Reference" gets changed to "BAR", etc.

I feel like I need a loop inside of a loop, but I'm not sure where to begin. Loops are not my specialty.

View 10 Replies View Related

Forms :: Update Event - Record Based Primary Key

Dec 16, 2013

I want my primary key to be

First 3 letters of surname + first 3 letters of forename + DD + YY (Date of birth)

I can use the after update event to update the primary key field but it won't get saved for some reason so have I missed something?

View 13 Replies View Related

Tables :: Update A Specific Record Based On Matching Primary Key ID?

May 22, 2014

My problem is that I am trying to update a field (called 'Sold' which is a yes/no checkbox column) for a specific record whenever an event is triggered. I have two forms (derived from two tables), one is called frmInventory and the other is called frmSales. In frmSales, I made a combo box called 'cboItemID' that allows the user to select from a list of items from my inventory table. Each selection from the list has 4 properties, the first of which is the 'Item ID' from the inventory table. Lastly, I have a field in both frmSales and frmInventory called 'Sold' as mentioned above. What I want to do is that whenever I check/uncheck the box in the 'Sold' field in frmSales, I want the 'Sold' field in frmInventory to check/uncheck as well, but only in the record with an 'Item ID' that matches the 'Item ID' from the combo box selection. In other words, I want to match the 'Sold' field in frmInventory with the 'Sold' field in frmSales, but for only the record that has the same 'Item ID' primary key as the one I picked from my selection in the combo box from frmSales.

how to reference another table and check whether or not it's 'Item ID' primary key is identical to the one I specified from the combo box, and then take action to update the 'Sold' field if the IDs match.

View 1 Replies View Related

Access Table - Find And Replace

Feb 4, 2007

Is there a way for me to do a find/replace on a '#'? I am trying to remove all '#' in a text field. When I do a find/replace (replace '#' with ''), it removes all my numbers from the text field.

Any suggestions?

Thanks,
Warren

View 2 Replies View Related

Append Or Replace Data In Table

Dec 12, 2005

Hi, I'm fairly new with working with Access and programming in VBA, and I was looking for some help. I have a database with many tables, and relationships between the tables. In particular, I export two of these tables to text files from one machine and need to import them back into the database on another machine. I have this working if I delete all the records before useing the transfertext command to import that table. But my problem is that I need to either append a record if it doesn't exist in the promary keys, or if the new record exists in a primary key, I need to replace it. I was thinking about reading the table to a temp table from the text file and checking each record in some sort of query to see if it needs to be appended or replaced in the table. Does anyone have any ideas of how else this could be accomplished a little easier that looping through each record, for this might take a while, as I do not know how many records can be importing each time. All I know is that I need a way of taking data from a text file and either appending or replacing the records in the table in the database. I appreciate any help I can get with this. Thank you.

View 3 Replies View Related

General :: Table Find And Replace

Jul 22, 2015

I have a requirement to export an Access 2013 table query to XML and i need to be able to replace all the invalid XML characters before I can export it. How can I do a table wide find and replace for these.

For instance:
& needs to be replaced with &
< needs to be replaces with <
> needs to be replaced with >
' needs to be replaced with &pos;
" needs to be replaced with "

I have to search the entire table and replace all these wherever it sees them and i have not found a way to do it

View 12 Replies View Related

Forms :: Find And Replace With New Data In Table

Jan 7, 2014

Is it possible to have a Find & Replace in the table using access forms. ( Like the inbuild option in Excel ), I want the user to search some data & replace with the new data which may present in more than one record.

I want to do it with forms, because i dont want the user to use the tables.

View 3 Replies View Related

Queries :: Replace Function In Query With Sub Table

Feb 14, 2014

I am running a query for an apparel manufacturing facility. In my query I have a table called 'OrderForm' which is where the orders are put in. There are more than one type of fabric that can be a part of an apparel item, so as a result, there are multiple fields pulling from the 'FabricType' table. In order to get this to work in my query I created 'SubTables' for the different fabric fields. For example, I have tblFrontfab, tblbackfab, tblsleevefab, and tblcollarfab which are just extra copies of the 'FabricType' table. The actual question is that when I want to replace characters like ,./& in the fabric field but I can't use the replace function. It says that it is too complex to calculate.

I used this notation
Frontfab: Replace([tblFrontfab].[fabric],".","")
and the error was that it is to complex to evaluate.

View 1 Replies View Related

Tables :: Multiple Parents Table Linked To Child Table - Primary Keys

May 28, 2015

I have a table for a multiple parents linked to a child table. I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... I thought about making Primary/Secondary/Other a primary key. But then I can only have 1 other. I would have to make a finite number of parents that could be entered and I want an infinite number.... My end goal is to have a report that only has a primary and second parent on it, but the rest of the parents still exist in the table...

*Child_ID
Parent_First_Name
*Primary/Secondary/Other

View 8 Replies View Related

How Can I Pass Data From Primary Table To Another Table?

Apr 13, 2008

ok , i have a primary table that have a field of "job", then i create another table that have a field of "job" too , then how can i do this task?

Whatever i type a data into the primary table("Job" field) i want it to duplicate the data into the another table("Job" field) . is it possible to do this? can anyone guild me to do ?

View 1 Replies View Related

Possible For Primary Key Of One Table To Act As Foreign Key In Another Table For More Than One Columns?

Nov 7, 2012

Is it possible for the primary key of one table to act as the foreign key in another table for more than one columns? What I'm trying to do is create a table for a Committee which will have 1 student and 5 professors! So Can I import the faculty ID for each of the 5 faculty members?When I try creating the second relationship, access automatically creates a new Faculty table for the relationship!

View 6 Replies View Related

Tables :: Primary Web Based Inventory System - Update Raw Data From Another System

Mar 13, 2014

I have our primary web based inventory system that I am exporting to Excel and using this as an import to Access for the main raw data for my database. This being inventory it changes daily so I am updating this table every day. When I try to append the table it ads all the records. I am wanting an easy way to add only the new records/take out the ones that are no longer there. Basically update the table with what is currently there.The only have I have found to do this is by running non-matching queries and update queries.

View 2 Replies View Related

Primary Key And Table Name

Jul 4, 2006

I am accessing a SQL database using Access but its not letting me view the table in design so I can set a primary or letting me change the table names.

I am able to do this from my home PC but not at work. Is there something in options or ODBC settings on the work the PC that I need to change.

Any Help will be greatly apprectiated.

JC

View 3 Replies View Related

Table Primary Key

Sep 22, 2006

I have searched Key in the forrum and did not find quite my situation:

I would like to have a table that has a Primary Key that is a combo of 2 fields,

Example: Don't Allow a record to be created for the Same [Name] & [Date]

In the above the primary key would be the [Name] + [Date] but primary key only allows one field for key.

View 1 Replies View Related

Should A Table Must Have A Primary Key ?

Feb 19, 2007

I'm still new in ADP development and need some advices and helps from you who has already expert on it. Fyi, I used Ms. Access 2007 and SQL Express 2005. I found a problem when working with my project. Here is the problems :

I'm using form wizard to create master/detail form but why I can't insert record in the detail section (the navigation button also became disable) ? The detail table doesn't have primary key only have foreign key that related to the master table. Should a table must have a primary key if we use ADP ? As attached is my relationship diagram.

Many thanks in advance

View 7 Replies View Related

Table Primary Key

Dec 10, 2007

I have about 500 tables that I'm upsizing to SQL Server, many do not have primary keys.

I can cycle through the tables container with VBA identifying each table and each table's respective fields.

How do I identify the primary key(s) of each table with VBA?

Thank you in advance for you assistance.

View 3 Replies View Related







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