General :: Equal Fields Across Tables / Forms

Aug 22, 2013

I have a few fields that are the same across a couple of forms and sub-forms (each form/sub-form being represented by a different table). I would like for data entry into one field to ensure that the data is autofilled into the other. ie if I type 'ENG' into field 1 on form 1, it will autofill the equivalent field in sub-form 2 as 'ENG' so that I do not have to type the same thing twice. These entries are not unique or in any order as it is variable depending on the entry and so they can't be linked as primary keys and foreign keys. So how would I do this? I would like to avoid VBA is possible.

View Replies


ADVERTISEMENT

Auto-populate Junction Table Based On Equal Fields In Different Tables?

Jun 24, 2014

Is there an easy way to auto-populate a Junction table [in access 2010] given the following two tables with a many-to-many relationship for Tasks? The two tables are

Table 1) tblTasks (TaskID (PK), Description), and

Table 2) tblMeasures (MeasureID (PK), Description, Tasks)

If JCTN table is JCTN_Tasks_Measures (TaskID, MeasureID), is there a way to populate when tblTasks(TaskID) == tblMeasures (Tasks)?

View 1 Replies View Related

How To Make Particular Fields Of One Record Equal The Value Of Another

Nov 14, 2012

I have created my first two tables ever.

The first is ClientList, which contains typical contact and biographical information (name, address, citizenship, etc), and unique ClientID# for each client. The primary key for this table is the default Autonumber ID that comes with each new table.

The second table is WillInfo, which contains information specific to drafting the client's Will (e.g., spouse name, spouse address, spouse citizenship, similar data on beneficiaries, similar data on executors, etc). The primary key for this table is ClientID#.

I then created a One-to-One relationship between ClientList and WillInfo, binding by Client ID. All this appears to work.

My question arises because I have two clients who are married to each other, which means much of the spouse info I require for the WillInfo table in respect of these particular clients is already accurately recorded as client info in the Clientlist table. So for these specific clients (but not generally!), I want the spouse information in the WillInfo table (e.g., SpouseAddress, SpouseCitizenship for ClientID# 12.001) to EQUAL specific values provided in the ClientInfo table (i.e., ClientAddress, ClientCitizenship for ClientID# 12.002).

I read and understand this is the best approach, following the principle that data should not be entered twice, so as to increase efficiency and avoid mistakes and future problems.

My question is: How do I do this? In Excel, if the client info I wanted to replicate was in cells B4-B9, I would enter =B4, or =B5, or =B6 and so on in the cells for spouse info. What is the equivalent expression for replicating specific client info from a different table.

View 7 Replies View Related

Queries :: Remove Duplicates - If 2 Fields Are Equal

Nov 19, 2013

I have a large table with many fields and many rows. There is no primary key. I'll call one field ParentPN, and another field ChildPN. There are many other fields as well. I want to identify all rows where BOTH the ParentPN and ChildPN occur more than once. I know how to create a query to identify duplicates of ONE field in the table, but not two. I can solve this with VBA: I will read the two fields of interest in the first row, then compare both values with every other row. If it finds another row with BOTH ParentPN and ChildPN identical with the first, that's a "hit". Then, repeat with all the other rows. I could find ways to make this run faster, but I was wondering if there are any build in functions to accomplish this. I looked at the Find Duplicates query builder, and all I see is I can select ONE field to search for dupes, not two.

View 14 Replies View Related

Compare 2 Tables Where NOT EQUAL <>

Feb 7, 2006

It need to compare two tables where the ID Number is not equal. Specifically I need to update new poducts in a table, but only those ID#s that are not already listed.

I know how to compare two feilds where they are not equal...
i.e. [New_Table].[ITEM_NUM]<>[Old_Table].[ITEM_NUM]
...but I'm currently limited by the join properties in the tables, since I'm comparing IDs and not the feilds. ANY IDEAS?

View 2 Replies View Related

Not To Display Data With Equal Value From Two Tables

Apr 29, 2013

I have a query that pulls data from 2 different tables. The query should display two columns made up from data of the two tables. Some of the data in the two tables are equal. How can avoid from displaying equal results.

Dog
Cat
Dog
Dog
Elephant
Cow
Cow
Chicken
Dog
Chicken

I do not want to display the result in row 2 seeing that both are equal.

View 1 Replies View Related

Tables :: Default Value Equal To Date Entered By User

Dec 21, 2012

I want a default value to equal the first 2 characters of field [first name] and first 3 characters of field [last name] and the numeric datevalue of [DOB] 'date of birth'.

In excel its easy,
C D E resultformula
AndrewTester12/12/1980AnTes-29567

Formula LEFT(c9,2)&LEFT(d9,3)&"-"&e9

How do I get same result in Access?

tried

Field 'PRN', a text field set as default =""""& left([first name],2}+LEFT(Last name],3), date()&""""

Access accepted the above statement but when I input user details the default doesn't work.

View 5 Replies View Related

General :: Divide Groups By Equal Distribution Of Merit / Talent?

Aug 9, 2013

I am with an educational organisation. Now we need to allocate various fields to students depending upon their merit, where the difference between the average percentages between any two groups cannot be more than 2% and that also implies equal distribution of talent. However, First few have to be given choice of field and also students recommended have to be given choice. The number of fields varies from 2 to 8. . Further the vacancies are also not equal in all fields. E.g. for 100 students to be divided in four fields the vacancies can be 35 for ZZSS, 24 for UYTE, 29 for OIUI and 12 for HGFT.

I need a solution preferably in MS Excel or MS access for the same with nil/ least usage of VBA.

Table examples

Name REg. No. Percentage

Aaa 986544a 89.3%

Bbb 547521j 87.2%

Ddae v44887y 95%

View 8 Replies View Related

General :: Enter Number Equal To Or Over Ten Million - Getting Last Two Values Rounded

Feb 28, 2013

When I enter a number equal to or over ten million, the last two values get rounded and I don't want them to be. My field is setup as a single, standard, two decimals. When I tested it I typed in 123456789, what I got was 123,456,800.00

enter 9999999 get 9,999,999.00
enter 10000199 get 10,000,200.00
enter 10000001.75 get 10,000,000.00

Single is supposed to handle up to 10^38

I have checked my "Region and Language" settings for the OS (W7) and there is nothing in there about rounding or maximum number size. I would like to leave the data type at single for the space considerations, and because it should work as single.

View 8 Replies View Related

General :: Linking Fields In Tables

Aug 23, 2013

Im in the process of developing my first database for my bonsai (small trees) collection. So far, ive developed a table of each of the trees I have, with fields such as Species, Where Bought, Cost etc and ultimately a link to a recent photograph (my next challenge). Ill be adding to this as my collection grows (in number and size). To avoid information duplication I have a separate table of care instructions for each species of tree with type, position they favour (eg Hardy/Delicate), watering requirements, wiring advice etc.

I now want to have a field in each of the records in the first table to its particular species care instructions as simple as that, or so I thought. ie for each different pine I want a link to the care guide for pines, and only pines. And preferably in a table format.

I need a one (second table) to many (first table) relationship and have linked them appropriately through primary field (species) and foreign field (care details). I have tried various Controls in the Design View but cannot get the field to show just one care record (ie the one for that particular species of tree). I realize this is possible, as its a common feature of a good database, and have found it being used in the various templates available - but not how they were created. The best Ive got to is using a Combo Box but this shows all of the care details records and in a column view.

View 5 Replies View Related

General :: Using Fields From Unrelated Tables

Jul 2, 2012

I am trying to take similar fields from unrelated tables. both fields contain information about the age of my clients. The tables they are in are unrelated, as they are different types of clients, and therefore can not find anyway to relate the tables. I need to come up with a total number of clients at particular age groups. Meaning i need to know how many clients from both tables are between the ages of 8-20, even though the records have no relation. All i need is a count, i dont need to display the records, i dont need to change anything, I just need to know how many.

View 4 Replies View Related

Forms :: How To Lock A Field If Equal To A Value

Oct 9, 2014

I'm still using Access 2003 to manage our membership database, is it possible to lock a field(member's name) if the membership is equal to withdrawal or terminated? Possible to use "on click"?

View 3 Replies View Related

Forms :: Automatically Add New Record If Field Is Equal To Certain Value?

May 26, 2014

A have an OrderDetail form where I add the articles to a specific order. My articles can be ordered with 4 different attributes and one of them cost more than the others. If they want the article that cost more, I would like access to add a new record with some information.

also:
4 different attributes to my articles. (A, B, C, D)
if ArticleAttribute = A then Null
if ArticleAttribute = B then Null
if ArticleAttribute = C then Null
if ArticleAttribute = D then add NewRecord with ArticleId = X

View 14 Replies View Related

Forms :: Equal Field In Form And Subform?

Aug 28, 2013

I have a main form with many subforms, each form /subform representing a table. One of the fields on the main form (ie engine type) is also in the engine subform. How do I make it so that filling in the engine type field on the main form automatically fills in the subform field?

View 9 Replies View Related

Forms :: Value Of Text Box Is Equal With Column 1 From List Box

Nov 7, 2013

i create a project for salary company , i have a list box and have 6 columns & i have 1 textbox , i work with code and a listbox is the query know i need the value of textbox is equal with the column 1 from listbox how to do it ? like when i run form need to be enter the code when entered the code in listbox is equaled with the textbox .another question , in the list box i have one column it is about salary i want the total of salary.

View 4 Replies View Related

Setting Numbers Equal To Words To Filter Forms?

Sep 19, 2014

I'm working on a form that when opened has a pop up filter that asks you to enter "1, 2, 3, 4 or 5". These numbers have to be equal to an actual word within the form. For example, on the form we have a box called "business unit". One of the options in "business unit" is "Human Resources".

I want to set 1 = to Human Resources so that way in the filter box all a person has to type is 1, and all of HR's records will pop up. The reason for this is we are trying to eliminate typing as much as possible.

View 1 Replies View Related

General :: Export Multiple Tables To XML But Excluding Linking Fields

Feb 4, 2015

I've been using a series of 5 tables to export an XML file using Access's native XML export. In order to keep the XML intact, there are relationships setup to link each sets of elements together via an ID number(link) field in each table.

Access does a great job at exporting, but then we need to rely on another program to delete these link fields that links the tables together so our XML validates. Additionally, we use this program to reorganize some of the XML, as one of our tables puts itself at the end of the XML element and not in the middle where it should be. This is not a problem when I'm dealing with a few hundred records, as the 3rd part program does the trick. The problem is when I have enough data to generate a 30+MB file that my other program chokes on when trying to manipulate the XML.

So my question is...how can I export these 5 tables to XML while omitting the linking member fields and organizing it how it should be? I have examples of code below. How Access Exports it

Code:
<MASTER-RECORDS>
<RECORD-ID-NUMBER>5648743524654</RECORD-ID-NUMBER>
<RECORD-ID-NAME>JOHN SMITH</RECORD-ID-NAME>
<link-for-pseudonyms>123456</<link-for-pseudonyms>
<PLACE>USA</PLACE>
<DAY>MONDAY</DAY>

[Code] .....

View 5 Replies View Related

General :: Macro To Create Table Fields From Another Tables Records

Jul 10, 2012

I think what I want is:

1 table(1): record of people & contact details
1 table(2): list of events with check box's with the names of people from the other table
1 report: listing how many events people have attended.

When I add a new person to table 1 I want a field to be added to table 2 in the form of a checkbox, also when I delete this person I want this field to be deleted in table 2.how to make this an automated process.

View 1 Replies View Related

General :: Auto Fill Multiple Fields In A Form From 2 Tables

Apr 3, 2015

I have two tables, "Summary" and 'POC Information". In the "POC Information Table I have all my Contacts Information (Name, Title, Phone, Email, etc...) and I am trying to assign 2 POCs to each of my multiple projects located in the "Summary" Table. I am using a Form called "JCIDS Tracker Input Form" as the link. So far I am able to assign one POC by a combo box that lists "Full Name", then it autofills the other information...Phone Number, Email, etc... The problem come into being when I want to assign a second POC to the same Project...I can assign a name, but it won't correctly autofill the rest of the information...it just autofills in the information from the first POC that was selected.

View 8 Replies View Related

Forms :: Combo Box Search To Include Records That Equal And Larger Than

Dec 3, 2013

I have a DB of books and a form to search for multiple fields including ranking (combo box). The way I have it now is to pick "3 stars", "4 stars", "5 stars". What I want to do is have it so that if I pick "4+", it will show all records that have 4 stars and 5 stars and "3+", it will show records with 3, 4, 5 stars.

View 14 Replies View Related

General :: 2 Fields From Separate Tables Needed To Create Calculated Field?

Aug 19, 2013

The interface being used is a main form with various tabs and a subform on each of these tabs.

There is one field ('max power density') in my database that is calculated using 'Max Rated Power' and 'Cylinder Capacity' however these are in different tables and subforms. The 'max power density' and 'max rated power' are in table and subform 1 but 'cylinder capacity' is in table and subform 2. Is it possible to keep them in separate tables/subforms and still calculate the field?

View 7 Replies View Related

"Not Equal And Not Equal" Query Not Working

Apr 14, 2006

I am trying to run a query where one field has a Not Equal parameter AND another field has Not Equal parameter (for example, Product Code Not Equal "A" AND Client State Not Equal "NY)

The result of the above query are all records that have Product Code = "A" are missing from the result. I want some of the "A" Product Codes; I just want to exclude the ones where the Client state is NY.

The list of values for each field is too big to state it in a positive way (I would have to list all the other 28 product codes and all the other 49 states).

What am I doing wrong? Thank you in advance for any suggestions.

View 1 Replies View Related

General :: Fixed Fields In Forms

Aug 19, 2013

I've been creating a database. I prepared forms to enter records. In these forms there are many fields. I want some fields to stay fixed while going up to next record unless changed. How can I do this?

View 6 Replies View Related

Adding Fields From New Tables Into Forms

Oct 21, 2005

I created a form from the wizard. In the wizard, I added fields from two tables. Now, after the form is complete (and I've edited it in Design view so that it looks how I want it), I want to add another field from a third table.

Although I can add fields from the first two tables that were originally included (by clicking on the "Field List" icon and dragging fields from that list), I can't find a way to add fields from any other tables in my database. The "Field List" dialog box doesn't contain any fields from anything but the first two tables.

How to I get fields from other tables into the "Field List" dialog box?

Thanks,

Kelly

View 7 Replies View Related

Forms :: Two Tables One Form For Different Fields

Oct 24, 2014

Can have one form calling two tables for the different fields?

View 2 Replies View Related

General :: Linking Access Forms And Fields To Word?

Nov 3, 2013

I am trying to take the data that is put into a form or report and export it to a certain document in word.

The user will be filling out a Reciept. On the form there will be a to and from box for them to fill out, along with many other options. Once the user enters in all the data required I want them to be able to click a button and it generate onto the attached file, in its perspective fields. Is there a way to match the documents to correspond to my program?

View 2 Replies View Related







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