Queries :: Populating New Field In Existing Database

Nov 4, 2013

I am trying to add a new email field to my existing database of 900 employees. I have imported an excel worksheet of email addresses into a new table that has lastname, firstname, employee#, email. The primary key on the new table is email. The primary key on the employee table is employee#, and there is a new field titled email.

The email field in both tables are text fields. In running an append query, it either adds 900 to 27000 additional fields of email only or it says cannot complete because of key code violations. On the join properties I have clicked on #3 all records from source to only equal fields of destination.

View Replies


ADVERTISEMENT

Queries :: Link External File To Existing Database?

Apr 30, 2013

Basically it's trying to take all the files in a given directory and add them into a existing database.

I have a database called Permit. the database has a field called "SEAL" and they will be creating an additional field "SCART" to hold the contents of the file that exist in the directory. Each image file, in directory, is named the same as a unique record in Permit.

example:
Permit-SC might have unique record 532442
An image file would be name 532442.

If the image file matches a record in Permit the image should be added to permit in a field called "SCART". At this point I'm not certain if I want a link or the actual image included in the record. since there's a possibility of including the permit file on the website it might be cleaner to have it included.

View 7 Replies View Related

Queries :: Designing A Query To Normalize Existing Database

Dec 6, 2013

I am attempting to normalize an existing database. I've created the table structures necessary and now I'm designing a query that will update the new field in my primary table: "LabelBaseProduct" with the primary key from my new table: "tblBaseProduct" where the old field from my primary table: "tblLabels.BaseProduct" equals the description field from my new table: "tblBaseProduct.BaseProductDesc".

A visual of my tables:

tblLabels (Main table)
- LabelID
- BaseProduct (old field with text data)
- LabelBaseProduct (new field, needs to be updated with PK from tblBaseProduct)

tblBaseProduct (new table)
- BaseProductID (PK and FK to tblLabels)
- BaseProductDesc (Field that should be matched to tblLabels.BaseProduct)

I tried to design a query using design view of the query design and this is what I have:

Code:

UPDATE tblLabels, tblBaseProduct
SET tblLabels.LabelBaseProduct = [baseProductID]
WHERE (((tblLabels.BaseProduct)=[tblBaseProduct].[BaseProductDesc]));

When I attemted to run the query it told me that it was going to updated over a million records. I only have just short of 2k records in my database.

View 4 Replies View Related

Tables :: Add Existing Field To Local Table Within Same Database

Oct 31, 2013

Adding a field in Access 2007.

I am adding a exisiting field which is already available on a Global Table and would like to add it on a local table within the same database. Also bearing in mind the db contains main objects - Tables/Queries/Sharepoint lists/forms/reports

Whats the process in doing this? Once added how does the data get populated?

View 1 Replies View Related

Add New Field To Existing Table And Populate New Field With Existing Data

Apr 23, 2014

-Microsoft Access 2010

-Existing Access Database contains tables with 1-2 million records

I would like to add a field[dol] to an existing table[rei]. I need this new field[rei].[dol] to be populated with existing data from another table[main] based on the associated field[main].[account1] or [main].[account2] or [main].[account3].

Table1[main]
...account1
...account2
...account3
...dol

[code]...

In the [main] table. There is always data in [dol]. But there is NOT always data in the [account] fields. Sometimes there is multiple account numbers per [dol] but not always.

Customers will ALWAYS have at least 1 [account] number and [dol]. Some will have multiple [account] numbers and [dol]. Sometimes these [account] numbers are the same in multiple fields[account1] [account2] [account3].I just need to do a lookup or something to find the [account#] and pull in its [dol] from the [main] table and populate it in [rei].[dol].

View 14 Replies View Related

Queries :: DateAdd Function Using Existing Field Values

Nov 26, 2013

I have a query in design view that consists of two calculated fields at the moment. One of the calculated fields is supposed to add one year onto a value from another date/time field called "Last_Date."

The expression I've written in the "Field:" box in Query design view is

Expr 2: DateAdd('yyyy',1,[Last_Date])

When I try to run the Query it doesn't work! Access just prompts me to enter a parameter value for Last_Date.

View 1 Replies View Related

Queries :: Data Validation Of A Field In Existing Table

Aug 13, 2015

I'm trying to write a query that will validate a field in an existing table. I want the query to show where any of the 412,000 records fail this strict validation:

AAnnnnnnA

...where A is any letter A-Z and n is a digit 0-9

Clearly I can use the LEN function to check for invalid lengths (anything <>9), but I'm looking for a simple neat expression I can use that will check the format. Does an existing function already exist before I resort to writing my own function in VBA (again)?

View 7 Replies View Related

Queries :: Update Field Based On Existing Record Fields

Jun 3, 2013

I have a field called uniqueID which I would like to update based on 2 other fields that are already populated in my recordset (from running previous queries). I heard it is not possible to do an Update Select like shown below.

select max(uniqueID) from myTable where a = "value of field a from first record in record set" and b = "value of field b from first record in record set"

If it is true that I cannot do an Update Select then I am trying to do something in VBA. How can I Loop each row in the record set and store the values from fields a and b. I would obviously then need to pass those values into the sql above and store the result in a variable. I would then do a straight update to put the value of uniqueID into myTable.

View 1 Replies View Related

Populating A Column In A Table Based On Values In An Existing Column

Mar 5, 2007

Hi all,

In the organisation that I work for employees get paid every 2 weeks on a Saturday. So for this financial year the pay period end dates have been 08/07/2006, 22/07/2006, 05/08/2006 etc


I have a column in an Access table listing various dates. I want the next column to be
populated with the next pay period end date after that date.

So if DATE is 05/07/2006 I want PAY PERIOD END to be 08/07/2006
and if DATE is 09/07/2006 I want PAY PERIOD END to be 22/07/2006 etc

How do I do this?

Kind Regards,

Matthew

View 1 Replies View Related

Updating And Existing Database

Jan 17, 2007

Hello


I have a database which i am constantly updating and improving. I have a few friends who use the database in a current form. If i made an update say to a form or to some of the coding to make the DB more efficent, how can i update the dBs my friends are using without damaging or changing the existing data, or migrating it to the new design.

I.e going from version 1 to version 1.1

Any advise would be great?

View 5 Replies View Related

Improving Existing Database

Jun 1, 2007

Hi,

I have been assigned to "improve" an Access database that has been created by a user. However, i do not know where to start. One of the things I have to do is write a stored procedure so that when the user runs the queries, the user is prompted to enter the criteria (for instance, a data range) to narrow down the number of records retrieved from the SQL backend. I have been told to improve the database. However, I do not have any experience in this and don't know how to start. The database has been created by a user so there's obviously lots of room for improvement. For instance, there are a million queries and I don't think that many are really needed. The goal is to minimize the size of the database. Can someone please guide me as to how I should go about starting to improve this database?

Thanks.

View 1 Replies View Related

Query An Existing Database

Jun 17, 2005

is there anyway to view records in an access database from an existing database? i have 3 databases that perform basically the same things, but are for different people...
i would like to create a database that can report all this information in one spot, instead of creating reports in every database. if this is not possible, i'll probably go the asp.net route, but this seems to be an easier way, if it is possible
what do u think?
*j

View 2 Replies View Related

Developing An Existing Access Database

Jun 29, 2006

We are a SME with a 4 year old access database which is in need of updating with new tabs and various other new spec. We are struggling to find an access programmer who is right for this project.

Does anyone know someone who could have a look at this project??

We are in the Greater Manchester area at present but will be rolling out on a regional and then national scale with this in the next 12-18 months

View 1 Replies View Related

Lookup Values In Existing Database

Dec 13, 2007

I've been racking my brain the last couple of days trying to figure out how to solve this problem, and I believe I've reached the end of my rope. I have a feeling that this isn't very difficult to a more savvy Access person, but I am at a loss.

Here's the situation. I have received a fairly large DB containing CCTV data for sanitary sewers. There are primarily two tables I'm dealing with, one lists a number (auto-number) for each pipe that was televised. Simple enough. The other uses this legacy number to show all deformities or service leads within a particular length of pipe. For example, for run X, there may be 7 rows in the table with X as the ID, one for each service lead along that length of pipe (I hope this is making sense :()

Ultimately, we need to tie this database into our GIS theme. To do this, I will need to add to the PipeID number from our GIS theme to the access table. What I've done so far is to create a new table in the DB with the number for each pipe televised, and I've manually added the corresponding PipeID number from the GIS in the second column. What I'm hoping to do is add a new column to my occurrence table so that for each occurrence X, I can add the GIS PipeID number. Perhaps this would make more sense:

Run # PipeID
1 S143
1 S143
1 S143
1 S143
2 S231
2 S231
2 S231
2 S231
3 S543
3 S543
3 S543

I've gone ahead and created the relationship between the newly created table and the existing table based on that auto-number field, and I've made the new PipeID column a combo box. This shows all of my PipeID numbers, which is a good thing. I'm hoping there's a way for it to automatically recognize the auto-number field and populate the PipeID field accordingly.

Have you ever known what you want to say, but not quite understood how to say it? That's kind of how I feel about this question, and I do apologize if I've made no sense. But if I have, and anyone has any suggestions for me, they would be greatly appreciated.

Cheers,

Azimuth

View 3 Replies View Related

Making A Template Out Of Existing Database?

Apr 28, 2012

We have a shared personnel access database for our department. Another department is wanting to use the same database. Is there a way to go about deleting all the info and making a template to give the other dept to use?

View 5 Replies View Related

Adding New Element To Existing Database

Dec 8, 2011

I was tasked by my CFO yesterday to add a new element to my existing database. I will try to keep what I am trying to do simple.

Basically we do finance for multiple projects. each project has employers.

My database records these employers names, SSN, dates registered, program they are in, and a few other elements as well.

What I have been tasked to do is create a new table with the project managers in that table. this way in a query or report i can pull "sally sue's" projects or whoever I needed to. There are many PM's who have more than one project.

Here is what I have for relationships:

On the Project Managers table I have a PK for ID and then the PM Name as a second field. the ID field is linked to the Programs table which has a PM ID and Program name

The Program table is also related to the employer table which has all of the employers and their info. the relation between the two tables is the Program Name.

So when I run a query or report my goal is to be able to pick any project manager and get the associated count of employers under all of that projects managers programs. I have gotten errors when running queries. Perhaps I have the relationships set up wrong?

View 1 Replies View Related

General :: Populating Database Dynamically

May 15, 2013

I'm creating a database where results of tests for students can be saved. To do this I have a database where a student can be assigned to a specific education. This education is coupled to a number of subjects which in turn have a number of tests.I have made a form in which someone can double click a student which opens up another form where he can mark results for all tests coupled to the education that particular student is following. This report however, only shows tests that have already been created as testdata. So now I'm looking for a way to populate this form with the proper tests.

I assume one way would be to create a button in this form that will create the appropriate tests (look at education of student, get subjects and tests and then create results for those) and then do a requery, but I don't have a clue on how to do this. (the creating part, a requerry shouldn't be much of a problem anymore )

If interested, my databasedesign: (not allowed to post links or images )
img7.imageshack.us/img7/2416/databaseontwerp.png
(education = Opleiding, subject = Vak, test = Toets, result=Resultaat)

View 4 Replies View Related

Tables :: Auto Populating A Field Based On Selection Of Other Field

Jun 27, 2014

I (will have) a form which a user fills in to enter new data.

I have two fields,

"Branch" and "Branch ID" (Branch ID will not be on the form, just in the table)

Branch will be fed by a combo box with seven choices. I would like it so that when "Branch" is populated it autopopulates Branch ID with a code which relates to the branch, so for example

BRANCH BRANCH ID
Braintree BRA
Colchester COL

View 4 Replies View Related

Changing Primary Key To AUTONUMBER On Existing Database ?

Mar 30, 2007

I have an existing contact information database which consists of a number of tables, the main table has a primary key which is currently set to NUMBER.

There is a relationship between it and 2 other tables via that key (ContactID).

I would now like to change the primary key (ContactID) to AUTONUMBER, so that any new records added, have a number assigned automatically. I also need any newly created primary key number to be used in the child tables created as a result.

Is this possible ? (without renumbering my existing tables/records)

many thanks

View 6 Replies View Related

Tables :: Creating Er Diagram From Existing Database?

Aug 21, 2013

Using access 2010; anyway to create an er diagram from an existing database? I have inherited a fairly large database and need to make changes to some field properties and need to find out where these fields are located. I know I can go into tools and run the documenter but doesn't give me an easy format to look at.

View 5 Replies View Related

Modules & VBA :: Importing New Records Into Existing Database

Jan 21, 2014

I have table1 in my (Access 2010) database that has exising records. I have another table2 that after I run a query, it first deletes the data in table 2, then imports new records into that table. I run the import into table 2 on a semi regular basis but have yet to copy those records into table 1.

With that said, using either a query or VB, how do I copy all the (new) records from table 2 into table 1 without altering any records that already exist in table1?

BTW, Table 1 has a main form with a subform within it

View 5 Replies View Related

General :: Designing Database Using Existing Data

Oct 25, 2014

I have to design 2 databases base on some data that was given by my instructor but I feel like I'm missing something on both specially the 2nd database. I have attached my work so far as well as the instructions and the existing data..

View 2 Replies View Related

General :: Copy New MDB File Over Existing Database

Feb 28, 2014

How can I overwrite the existing Access database with another Access database without the need for the end user to get involved in the process.

I want the code to copy a new mdb file over the database I am using (!) and then automatically start the new mdb file...

View 9 Replies View Related

New Query Field Based On Existing Field Logic

Oct 8, 2014

I'm trying to create a new field based on two existing fields as part of a select query. Two tables in the query each have a "HOSPITAL" field which is an indicator for "Y" or "N" for each department (in the DEPTLOOKUP table) and for each facility (in the Facility Lookup table). I want to create a field whereby a new indicator is created so that a Y is given for each record only where the facility and department HOSPITAL indicator field is both "Y". I have attached a word document that has a screenshot of the query in design view as well as the access sql code.

View 3 Replies View Related

General :: Changing Existing Database From Overall To Monthly Records

Nov 8, 2013

I'm altering a database to have certain fields be recorded monthly.For example: instead of a client's file having "Total X Purchased" it would now be "Total X Purchased - Jan", "Total X Purchaed - Feb".

I want to spin the monthlies out of the master clients table & in to ex. tbl_clientsJan, tbl_clientsFeb, etc. but still have them linked; & have a "Totals" table that aggregates data from all of the tables (adds them up).

I have an existing form, & I was thinking of just creating tabs for the months & subforms in each with their sources as the month tables, & removing the fields that don't change month-to-month (e.g. client name), with relationships between them. I would start by copying all of the master table data in to the month tables & allowing edits from there.

the company doesn't track when the transactions occurred; I'm unable to group them along those lines.I'm new to Access & don't want to make a mistake.

View 9 Replies View Related

Tables :: Creating Table Within Existing Database Using SQL Commands

Apr 8, 2015

I was asked to create a table within an existing database using these SQL commands;

CREATE TABLE Vehicle (reg_no TEXT(10)
CONSTRAINT VehicleKey PRIMARY KEY,
type TEXT(10),
purchase_date DATETIME,
last_service_date DATETIME, mileage_at_last_service INTEGER);

I was told to save the query and check the new table had the required records in it.

I had huge problems when I tried to save the query and kept getting error messages like: "query must have at least one destination field" and "syntax error in field destination". This was strange since I was sure I typed the query exactly as it had been written on the worksheet.

Having not changed the SQL command at all it eventually worked when I skipped the 'save query" stage and just went to the "run query" stage. I still don't know why it actually worked in the end and why I kept getting error messages.By running the query am I supposed to just click on "RUN" or can I check the information first by looking at datasheet view?

View 6 Replies View Related







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