Access populate field in table from another field in the same table
I have two fields, "Item" and "Information" in the same table.
"Information" contains three items from a seperate table created by a
lookup. I would like to substring out the first item and place it in
the "Item" field. Is there an easy way to do this? I would like the
"Item" field to populate after the contents of "Information" have been
selected. Thanks in advance for any assistance.
Item1 Item1 Item2 Item3
On Tue, 20 May 2008 13:40:45 -0700 (PDT), sd_eds <firstname.lastname@example.org>
With a combination of Left$ and InStr you can pick apart the
Information value and arrive at the Item value.
You are dangerously close to storing redundant information, a no-no in
proper relational database design.
>I have two fields, "Item" and "Information" in the same table.
>"Information" contains three items from a seperate table created by a
>lookup. I would like to substring out the first item and place it in
>the "Item" field. Is there an easy way to do this? I would like the
>"Item" field to populate after the contents of "Information" have been
>selected. Thanks in advance for any assistance.
>Item1 Item1 Item2 Item3
...Update field in one table from field in another
Hello everyone, this is my first post so apologies if i dont get it right
first time, i am a self taught Access user, i am stuck on something i am
trying to do, briefly i have 2 tables, one for vehicle details and one for
inspection details of vehicles in the first table. I have 2 fields that are
the same in both tables, lastsafe and distance, the vehicles table can only
have 1 vehicle to each record, the inspection table can have multiple records
for each vehicle, what i want to do is, when i input the last safe and
distance values in the inspection table i need it to transfer that data to
the vehicles table so that i am not having to type the same information twice
for each record.
Hope that makes sense and any help would be much appreciated.
To make it clearer, here are the fields
lastsafe (date field)
Distance (number field
date (date field)
distance (number field)
Sorry i was wrong earlier, the field names are not the same but the data type
I'm assuming that you have a fleet of vehicles for which you have info such
as make, year model, VIN, License #, etc. and you do periodic inspections on
these vehicles that you enter a safety inspection date and an odometer
I would have my vehicle info in the one file with a unique key field (set it
to autonumber.) Do not include the lastsafe and distance fields. I would
then create the inspection table with a key field set to a Long Integer
number, the same as the key field in the...Transfer values from one field to another field within the SAME table
How would I transfer the values from my WorkPhone field to my
CellPhone field within the same table IBCCP Referral?
On Tue, 8 Jul 2008 07:14:50 -0700 (PDT), zufie wrote:
> How would I transfer the values from my WorkPhone field to my
> CellPhone field within the same table IBCCP Referral?
Run an update query.
Update [IBCCP Referral] Set [IBCCP Referral].[CellPhone] = [IBCCP
Please respond only to this newsgroup.
I do not reply to personal e-mail
Back up your table first so if you make a mistake, you can try again. Run
UPDATE [IBCCP Referral]
SET CellPhone = WorkPhone;
>How would I transfer the values from my WorkPhone field to my
>CellPhone field within the same table IBCCP Referral?
Message posted via AccessMonster.com
...Basic Access user: Link field in one table to field in other?
I have Access 97 and have set up four tables, each with a Primary Key
with a file name manually entered. For example, the four tables relate
to information stored in a paper file, on four subjects, the paper file
is indexed WM/01/05. This number, a unique identifier in the database,
is the primary key and the same content in each table.
Table 1 is called "Driver Data"
Table 2 is called "Vehicle Owner"
Table 3 is called "Vehicle Expenses"
Table 4 is called "Legal Costs"
The four tables have four corresponding Data Entry Forms, similarly
I have command buttons in each form, each button linking to the
appropriate three other forms.
The tables are all linked in the same Primary Key using the
Relationships tool, in order Table 1 to Table 4.
As I add to the database, these four stacks of cards don't behave in
the manner I would like. Once all four Forms have the same file number
manually entered, I would like the current file number to go to the
exact same file number when the command button is pressed, so that each
Form in the "chain" works seamlessly between all the records. Each
form should jump from WM/09/05 to WM/09/05 to WM/09/05 to WM/09/05
Presently, when the database is first switched on, WM/01/05 comes to
the fore, which is fine. I can use the Search function to locate
WM/09/05, which is fine. However, my linear navigation design is
lacking something. It doesn't all &qu...Count combination of fields in one table occurring in another table
Please could I have some help on matching records between tables. I
want to return a check (true/false) stating whether the field
combination in table 1 occurs in table 2. Obviously it's easy with just
one field, but two is more problematic.
I have the following
Table1 Example values
Rec1: A 1
Rec2: B 1
Rec3: A 2
Table2 Example Values
Rec4: B 2
Rec5: A 1
field1 field2 OccursInTable2
Rec1: A 1 True
Rec2: B 1 False
Rec3: A 2 False
Attempts I've made include an Exists (or an IN) subquery - very very
slow. Combining the fields into their own primary Key (e.g. a field
KEY: "field1.B+field2.1") and then matching on that - seems cumbersome.
It's a dynamic query (not a once off). If you can answer this then is
there a more generic way to extend to more than two fields?
>Please could I have some help on matching records between tables. I
>want to return a check (true/false) stating whether the field
>combination in table 1 occurs in table 2. Obviously it's easy with just
>one field, but two is more problematic.
>I have the following
>Table1 Example values
> field1 field2
&...Copy field names from one table to records in another table
I am trying to write some VB to copy the field names from one table in
a db to a field in another table in the same database i.e., so they
appear as a set of records.
Any clue how to achieve this?
This is in an unbound form with a combobox (cboPickTable)
SELECT MSysObjects.Name FROM MSysObjects WHERE (((MSysObjects.Name) Not
Like "MSys*" And (MSysObjects.Name) Not Like "tblDocument*") AND
((MSysObjects.Type)=1)) ORDER BY MSysObjects.Name;
Private Function FieldList(ByVal strTable As String)
Dim db As DAO.Database
Dim tdf As D...To retrive a table from one database to and another database(same table)
i have emp table in one database , how to retrieve same table another
through query analizer
give code ,,,
use the three part notation:
SELECT * FROM database.owner.Objectname
(replace owner by schema for SQL2k5)
HTH, Jens Suessmeyer.
While the first database is "Current" as would result from:
select * from OtherDatabaseName..Emp
"surya" <email@example.com> wrote in message
news:firstname.lastname@example.org...SQL for Quering Access Tables using a Table/Field List Table
I have a Table called TableField that has the table names, fields and
Data type of all the tables and fields in the database. They are set
up as follows:
TableName FieldName Type
Table1 LName Text
Table1 FName Text
Table1 Address1 Text
Table1 Address2 Text
Table1 State Text
Table1 Zip Text
Table2 Color Text
Table2 Descrip Text
Table2 Size Text
Table2 Temp Text
Not having much Access experience, If I were looking for a specific
value "e.g., Orange" located in one o...Accessing a non-primary key field from one table to another
I am trying to grab the UnitPrice from Table 1 and store it in the
UnitPrice field in Table 2 whereever there is a match ProductID wise.
[Products Table] - ProductID, ProductName, UnitPrice
[Order Details Table] - OrderID, ProductID, UnitPrice, Quantity
In the [Order Details Table] I have accessed the ProductName from the
[Products Table] by establishing a Many-to-One relationship and using a
[Order Details Table] is the "Many"
[Products Table] is the "One"
The problem that I am having is that the UnitPrice in the [Order
Details Table]...Update MS Access table from another table
This is probably very simple but I can't figure out a way to update
one table from another table.
Here is an example:
I want to update TABLEB's "grade" column from TABLEA (join by
first_name and last_name). What's wrong with following sql?
SET GRADE = (SELECT GRADE
WHERE TABLEA.LAST_NAME = TABLEB.LAST_NAME
AND TABLEA.FIRST_NAME = TABLEB.FIRST_NAME)
Also, one more question:
How do I update TABLE's &quo...Q: How to relate field in table A to more than one other table
(Hope I can ask this clearly...)
I have three tables: tblPayments, tblCompanies, and tblPeople.
tblPayments contains a field called lngPayeeID that needs to relate to
records from tblCompanies and tblPeople. Why? Because a "payee"
(lngPayeeID) can be either a company (tblCompany.lngCompanyID) or a
I know how to relate tblPayments.lngPayeeID to a single table's field,
such as tblCompanies.lngCompanyID. I just can't figure out how to
relate it to two unlike tables (i.e., a payee can be a company or
person, which are both unrelated to each other...Field displays depending on another table field value
G'day from Australia,
I'm hoping some bright spark may be able to help me with this one. I'm
sure that it can be done, I've just hit a wall with it. So I'm opening
I'm storing student competencies for a given number of performance
criteria (which are stored in a separate reference table).
Students are rated competent or not (yes/no field) on up to 5
assessments - Task, Observation, Project, Practical Test, Exam for
each performance criteria.
But for any given performance criteria, an assessment may not be used
to assess competency so I don't want it to display on the data entry
form (with two subforms - student and performance criteria)
StudentID(PK/FK), PerformanceCriteriaID(PK/FK), Task (Yes/No), Project
(Yes/No), Obs (Yes/No), PracTest (Yes/No), Exam (Yes/No)
Sample Data in the table:
StudentID, PerformanceCriteriaID, Task, Project, Obs, PracTest, Exam
5432, 16, -1, -1, -1, 0, -1
5432, 23, -1, -1, -1, -1, 0
In this example, the field PracTest for student 5432 contains a zero
(not because the student isn't competent on Performance Criteria 16,
but because a PracTest is not used to assess this competency). I
don't want a user to accidentally tick a checkbox for this performance
criteria for a PracTest if a PracTest is not used to assess the
But student 5432 could have a zero recorded for the Exam assessment
on Performance Criteria 23 because they do not have competency and...Combining Fields From More Than One Table onto a Field on a Form
I have a db that has a main table with a PK of STRNumber. This main
table has other tables that contain software, models and documentation
information based on the STRNumber. All have a PK of STRNumber.
The main table link to the software and models are one-to-one and to
the documentation table it is one-to-many. So each STRNumber will have
only 1 Software record, 1 Models record but can have many
The software, models and documentation table each have a field called
PREPNumber (prefaced by SW, Models and Doc). Ex: SWPREPNumber,
DocPREPNumber. Each PREPNumber field is a text field and can contain
many PREPNumbers, ex: PREP-123, PREP-124, PREP-125, etc.
I have a form for the main table that I want to add a field to, that
would contain all the PREPNumbers for that particular STRNumber and
combine them into one field on the main STR form. I need that field
to go out an look for any SWPREPNumbers from the SWPREPNumbers field,
any ModelPREPNumbers, and all Documentation PREPNumbers. Since the PK
for all the tables is the STRNumber field, it would only combine all
the PREPNumbers for that particular STRNumber.
I'd appreciate any help. I'm kind of new to MS Access.
Loring Mercil wrote:
> I have a db that has a main table with a PK of STRNumber. This main
> table has other tables that contain software, models and documentation
> information based on the STRNumber. All have a PK of STRNumber.
> The main table link to the software and...Re: joining tables- one table below another #3
On Wed, 1 Nov 2006 08:27:48 -0500, Peter Crawford
>sorry to be following up on my own posting, but a logical flaw
>was pointed out in the sql union code I offered. It worked but
>not in the required way.
>Since the original poster may lack the confidence to challenge
>the advice, please allow me to correct the flaw.
>As written the results are ordered by the data columns. To achieve
>all the rows of "one table below" all the rows of the other, that
>"union" needs the qualifier "all" as in
>proc sql ;
> create table c as
> select * from a
> union all
> select * from b
Also, the CORRESPONDING option may be of use. It's important to understand
that without it, the columns are aligned according to position, left to
right, and not according to name. To people used to combining data sets in
the DATA step, this is strange and unusual. In the example here, it would
not matter. Sometimes neither mode of automatic alignment (position-based or
name-based) is appropriate; then one has to either use RENAME= options or
toss out the "*" shortcut and list the columns explicitly.
>Documentation is usually m...copy field from one table to another
i am trying to copy a completed field set from one table to another.
I am using this SQL command:
INSERT INTO doc_valid SELECT * from doc_temp where id=$id
it works, but it also copy the index field id from my temp table to the
the index are autoincrement primary key, and it should take the next index
How can i make this copy and use the auto incremental index of the
> i am trying to copy a completed field set from one table to another.
> I am using this SQL command:
> INSERT INTO doc_valid SELECT * from...Add field to table in another database
How do I use VB to create some code behind a button in one database
that will add a field to a table in another database that is not open
at the time?
The target database is C:\NewDB\statistics.mdb
The table is QualityTracking
The field to add is WrittenUpBy, which is text, 5 characters
Any help would be appreciated.
On Nov 7, 9:47 am, Jim <jlrehm...@gmail.com> wrote:
> How do I use VB to create some code behind a button in one database
> that will add a field to a table in another database that is not open
> at the time?
> The target database is C:\NewDB\statistics.mdb
> The table is QualityTracking
> The field to add is WrittenUpBy, which is text, 5 characters
> Any help would be appreciated.
This worked in my test:
Private Sub CommandButton1_Click()
Dim dbsNew As DAO.Database
Set dbsNew = DBEngine.Workspaces(0).OpenDatabase("C:\NewDB
dbsNew.Execute ("ALTER TABLE QualityTracking ADD COLUMN WrittenUpBy
You'll need to put in some error handling, though.
On Nov 7, 11:10 am, Jana <Bauer.J...@gmail.com> wrote:
> On Nov 7, 9:47 am, Jim <jlrehm...@gmail.com> wrote:> How do I use VB to create some code behind a button in one database
> > that will add a field to a table in another database that is not open
> > at the time?
> > The target database is C:\NewDB\statistics.mdb
> > The table is QualityTracking
>...Update table using data from another table
I believe the following a valid SQL statement, but MS Access returns
with an error "Operation must be an updateable query".
Any suggestions would be greatly appreciated!
UPDATE FERCPTILoad AS FL
SET FL.[Load Dist Factor] = FL.[MW]/(
select FA.[Load MW]
from FERCPTIArea AS FA
where FA.[Area Num] = FL.[Area Num of Bus]);
*** Sent via Developersdex http://www.developersdex.com ***
Bo Long wrote:
> I believe the following a valid SQL statement, but MS Access returns
> with an error "Operation must be an updateable query".
> Any suggestions would be greatly appreciated!
> UPDATE FERCPTILoad AS FL
> SET FL.[Load Dist Factor] = FL.[MW]/(
> select FA.[Load MW]
> from FERCPTIArea AS FA
> where FA.[Area Num] = FL.[Area Num of Bus]);
> *** Sent via Developersdex http://www.developersdex.com ***
Why not try using DLOOKUP instead of the subselect? My guess is that
since the subquery could conceivably return more than one record, the
update query won't work.
...I have a 2-field table I want to trim strings off of one of the fields...
My table has an autonumber field and a text field named
[RawData]. Many records have unnecessary spaces padding
the front and end of [RawData] - at least, I think they're spaces.
The look like spaces to me.
Here's the SQL I'm using...
UPDATE Addresses SET Addresses.RawData = Trim$([RawData]);
"Microsoft Access didn't update 2353 field(s) due to a type conversion
filter..." I wonder what kind of type conversion Access is trying to
do on a text field string with text in it?
"Microsoft Access didn't update 2353 field(s)
due to a type conversion failure..."
Above is how the error msg read - type conversion failure
NOT type conversion filter.
Try the following query
SELECT ASC(LEFT(RawData, 1)) FROM Addresses
If you get 32 then they are spaces if you don't then that may be the cause
of your problem.
Also try using Trim rather than Trim$.
"MLH" <CRCI@NorthState.net> wrote in message
> My table has an autonumber field and a text field named
> [RawData]. Many records have unnecessary spaces padding
> the front and end of [RawData] - at least, I think they're spaces.
> The look like spaces to me.
> Here's the SQL I'm using...
> UPDATE Addresses SET Addresses.RawData = Trim$([RawData]);
> "Microsoft Access didn't update 2353 field(s) due to a type conversion
> filter..." I wonder what kin...extracting unique fields from 2 tables into one new table
I'm trying to extract every unique field 'called MESSAGES' that exists
in table LDR2 and not in table LDR into a new table UNIQUE (each table
has only 2 fields (MESSAGES<CLOB>,ID<NUMBERIC>). We missed some data
initially, now the LDR2 has the missing data. I need to get this
missing data in LDR2 into the new table . However, I cant seem to run
many commands because its a CLOB -datatypes.
I'm Using SQL-PLUS - Oracle9i Release 18.104.22.168.0.
<My query that doesnt seem to be working fully>
insert into seebeyond.RAD_MESSAGES_UNIQUE (SELECT
FROM seebeyond.RAD_MESSAGES_LDR2, seebeyond.RAD_MESSAGES_LDR WHERE
seebeyond.RAD_MESSAGES_LDR2.MESSAGES NOT LIKE
seebeyond.RAD_MESSAGES_LDR.MESSAGES and (rownum < 33617))
-Thanks for any advise,
On 4 abr, 01:56, "mrj" <Carl.Seppa...@gmail.com> wrote:
> Hi All,
> I'm trying to extract every unique field 'called MESSAGES' that exists
> in table LDR2 and not in table LDR into a new table UNIQUE (each table
> has only 2 fields (MESSAGES<CLOB>,ID<NUMBERIC>). We missed some data
> initially, now the LDR2 has the missing data. I need to get this
> missing data in LDR2 into the new table . However, I cant seem to run
> many commands because its a CLOB -datatypes.
> I'm Using SQL-PLUS - Oracle9i Release 22.214.171.124.0.
> <My query that doesnt seem to be ...Restricting editing on one field based on another field. #3
I have a database that I need to restrict the ability to edit one
field based on another. The two fields are DOS (date) and Amount
(currency). I only want the users to be able to edit the Amount field
if the DOS is =>now()-5. Does anyone have any suggestions? Thanks,
...Show some fields below one another in query, from same table.
This is kind of convaluted, but basically I have a massive source table
Fname1, Lname1, Fname2, Lname2, Address, City, ST, Zip
and I want the query to output
Fname1a, Lname1a, Address, City, ST, Zip
Fname2a, Lname2a, Address, City, ST, Zip
Fname1b, Lname1b, Address, City, ST, Zip
Fname2b, Lname2b, Address, City, ST, Zip
Fname1c, Lname1c, Address, City, ST, Zip
Fname2c, Lname2c, Address, City, ST, Zip
Can this be done, and how?
You want a union query.
Select Frame1, Address, City, ST, Zip, 1 as DisplayOrder
Select Frame2, Address, City, ST, Zip, 2 as DisplayOrder
Order by DisplayOrder
Hope this helps.
<JeremiahBritt@gmail.com> wrote in message
> This is kind of convaluted, but basically I have a massive source table
> Fname1, Lname1, Fname2, Lname2, Address, City, ST, Zip
> and I want the query to output
> Fname1a, Lname1a, Address, City, ST, Zip
> Fname2a, Lname2a, Address, City, ST, Zip
> Fname1b, Lname1b, Address, City, ST, Zip
> Fname2b, Lname2b, Address, City, ST, Zip
> Fname1c, Lname1c, Address, City, ST, Zip
> Fname2c, Lname2c, Address, City, ST, Zip
> Can this be done, and how?
...copy multiple fields from one table to another
I have situation where I need to copy multiple records (only certain fields)
from a Rules table to an Events table based on a selection identified in a
combo box. When the selection is made in a parent table, multiple records
in a child table are affected. However, I'm a bit weak on programming
experience. INSERT INTO is not suitable. Any ideas?
When the combo box update property is triggered, the code needs to
IF rules don't exist in Events table, then copy from Rules table
FOR each row needed
create row in Events table for each qualified record in Rules ...Compare two tables and give me results of fields that differ in new table.
Please can anybody help me with the following:
I have two separate excel spreadsheets that I have imported into
access, each sheet holds the same fields. Both spreadsheets hold all
information for employees however, they are from two different dates
so by comparing both sheets I should to be able to check which records
DO NOT match. From the records that DO NOT match I need to locate the
information that has changed. But I only want to bring in the fields
that have changed, so for example 1:
Spreadsheet1 - ID No:12345 DOB 28/02/78 Grade A Department Finance
Spreadsheet2 - ID No:1234...Re: How to update one table with info found in another table #3
You could use SQL, although the format way is far better, although needs
create table table3 as select
b._name_ as holiday_name
from table1 as a left join table2 as b
on (a.date=b.col1 or a.date=b.col2 or a.date=b.col3)
This is untested code.
if you reshape Table2, as already recommended, then the above code can be
create table table3 as select
b._name_ as holiday_name
from table1 as a left join table2 as b
On Mon, 17 Dec 2007 22:55:19 -0500, Howard Schreier <hs AT dc-sug DOT org>
>On Mon, 17 Dec 2007 17:33:36 -0500, Tom White <tw2@MAIL.COM> wrote:
>>I have two tables that look like this:
>>MERCHANT ID DATE ... (many more fields)
>>1271 434 17AUG2005
>>1335 038 24FEB2003
>>1435 038 25SEP2007
>>1435 038 04JUL2005
>>220 038 31MAY2004
>>220 263 11oct2006
>>The MERCHANT field in this table is of no importance. I just have it
>to show you
>>this table has many more fields than the field I am interested in,
>>_NAME_ COL1 COL2 COL3 ...
>>Fist_of_year 01JAN2003 01JAN2004 ...