Filling Two Fields With A Single Lookup
Oct 20, 2014
I am using Access 2010 and I have a table containing a list of chemicals (Analytes) that I am working with and their Chemical Abstract Service number (CAS). I am trying to use this table as a basis for generating other tables that will be used to create quality control criteria for specific analysis methods. As such, I will need both fields populated for each subsequent table. I have been able to successfully create a lookup that will pull the Analyte name, but I want the same lookup to fill the CAS number. How do I make this work?
View Replies
ADVERTISEMENT
Sep 13, 2004
I have a table that tracks call-in's and tardies. Sometimes, a person will have a tardy and not a call-in and vice versa.
I need to perform a calc on both the call in and the tardy fields. Is there a way to tell the database that if [Tardies] is blank (nulll), that I want it to put a zero in the field? I know this must be done by a query, but can't figure it out.
I've also tried splitting these two tables into a call-in's and a tardies table, but can't get a query that will show all tardies AND all call-in's. It will only show either all call-in's with associated tardies or vice versa, so if a person has no tardies, but does have call-in's it won't show them. What can I do? Thanks!
KellyJo
View 1 Replies
View Related
Dec 7, 2004
hi all...
i am the biggest of all newbies when it comes to access and understand only the graphical parts of the process (no SQL knowledge etc.).
how would i go about auto filling a certain field based on what's in another field? for example, i have a field called TimeZone that i want filled with either West, East, Central, or Mountain based on another field for State. can i build a query to automatically take care of any empty fields for TimeZone that haven't already been filled out by me manually?
if i need to use SQL, that's fine too as long as the directions are clear and precise as i have no prior knowledge.
thanks a bunch.
View 4 Replies
View Related
Jul 6, 2015
MS Access 2010
I have a form "Admin" used to create new records in a table "RCJ"
I have a table called "Projects" that contains detailed information for projects.
I have a table called "Waterfall" that contains basic information and schedule information for each particular project.
I have a combo box "Contract" that selects information based on a query "Core" that gathers certain information I want to use of form "Admin"
I understand it's generally not a good idea to duplicate data in tables, but for specific reasons I am here. My combo button "Contract" has an event "On Click" that gathers information I want copied from table "Waterfall" via query "Core" is:
Private Sub Lookup1_Click()
Me![Project Description] = Lookup1.Column(1) =>goes to a text box
Me![TContract] = Lookup1.Column(3) => goes to a text box
Me![Requestor] = Lookup1.Column(5) => goes to a combo box
Me![AEM] = Lookup1.Column(4) => goes to a combo box
Me![AE] = Lookup1.Column(6) => goes to a combo box
Me![Priority] = Lookup1.Column(12) => goes to a combo box
End Sub
Now, the items listed above are sometimes just a text box and sometimes a combo box (because there are times, when items get entered that don't fit the original combo box.
Now, here's the rub. When I select the pull down on the combo box it lists all available projects. You find the one you want and select it. At that point, only Columns 1 (text box) and 6 (combo box) auto fill. The other information does not fill in .. BUT, if I more to the prior record and back, all information shows up OR if I click on any of the particular fields, the information shows up in that field. In theory, everything should show up right away.
I thought it might be because it doesn't like to autofill a combo box, but one of the combo boxes fills fine. And all of the information is acutally going to the various boxes, it just doesn't show up right away.
View 10 Replies
View Related
May 1, 2014
The database I am building is for Student & Alumni administration at a non-profit culinary school. In the various forms for entering student information and for defining tests and recording test scores, I would like to not have to re-select things like Class Number or Test number.
I was able to use the Test Number field as Link Master, Child in a Test Results sub-form where Test Parameters is the Master form. But I am not able to link with the Class Number, getting the error message: "The setting you entered isn't valid for this property" even though it is one of the Suggested Link Fields. The form record source is a query linking the Class Table, Test Parameters, Test Results and Students.
I noticed that I can eliminate both Class Number and Test Number from the sub-form. In the underlying query, both these fields are updated. However, the underlying Test Results table does have the Test Number but not the Class Number. While there is still a "queryable" link from the student to the Class Number, I would very much want the Class Number to be stored in the Test Results table.
Is there a way to accomplish this through the sub-form Link Master/Child property or any other/better way to inherit both the Class Number and Test Number in each Test Results record entered?
View 4 Replies
View Related
Mar 19, 2015
I am making a manual for work using access to save all the commands we use. I have a drop down box to determine the command. I want to have fields users fill in and system adds to commands and displays output on screen. Example INFO1 and INFO2 would be what person added in a box. I want the output to be able to be copied as full line.
RTRV-T3:INFO1:INFO2:<CTAG>;
I have taken some basic Access and SQL classes. Is there a way to do this in Access.
View 1 Replies
View Related
Mar 3, 2015
I have a table where I use 3 date fields in, all with different views.
Field 1: Date as dd/mm/yyyy
Field 2: Quarter: q (shows the same date, but then as quarter of the year.
Field 3: Year: YYYY
Now I have a form that fills all of my fields, but is there a simple way () to populate the other two fields Quarter and Year from the value I entered in Date?
So if I change Field "Date", Fields "Quarter" and "Year" updates as well.
Maybe I don't need to do it in the form, but in the table?
I'm not that good with VB- scripts, and I'm not sure If it is possible to auto populate the table field Quarter and Year directly in the table itself.
View 7 Replies
View Related
Mar 2, 2005
Hello,
I wonder if anybody can help me.
I have a table called ITEM, within ITEM I have three fields ITEM NUMBER (Key Field), Item, Cost,
I have another table called INVOICE ITEMS, Within INVOICE ITEMS I have six Fields, INVOICE NUMBER, ITEM NUMBER, ITEM, UNIT COST, Amount, Total Amount.
I want to use Lookup wizard to complete the fields ITEM NUMBER, ITEM, UNIT COST from the ITEM table.
Is this possible?
Regards
Nathan
View 1 Replies
View Related
Jul 26, 2012
I have a form to record student information. On my student table i have a school I.D that links to the primary key of my school table.
My problem is that in my form, i want to be able to fill in the rest of the fields about the school info based on the I.D chosen.
i.e.
student table:
Student id
name
address
school I.D (FK)
School info table:
School I.D (PK)
school name
contact name
email
address
My form that's linked to the student table needs all the information from the school info table in separate fields
but i can only select School I.D in the Control Source Property.
View 1 Replies
View Related
Dec 18, 2012
OK, so I have a database with four tables (Well, more than that, but these are the relevant ones). It's to be used for recording the results of site inspections.
"Tbl_Typicals" is a list of products. We'll call its key "Typical_ID".
"Tbl_Actions" is a list of tests performed on each product. A given product may have many tests, but each test applies to only one product. Its key is "Action_ID". Each row contains a Typical_ID to link on.
"Tbl_PlantComponents" records which products are installed on which site. Its key is "Component_ID". Each row contains a Typical_ID to link on.
The fourth table ("Tbl_Results") contains the results of each test. As a result of the relationships above, each row is specific to a single Action which applies to a particular PlantComponent, which is to say, each row has both a Component_ID and an Action_ID to link on.
So, what I need is a query that pulls all of these together, such that I can use these details as the line items of a subform.
The main form displays the details of the PlantComponent, which is a simple query to relate line items in Tbl_PlantComponents with the data about that particular product in Tbl_Typicals. So far, so easy.
The subform shows the details of each test applicable to that product. It then has toggle buttons and a comment field to indicate the results of the test, the results of which should be stored as a line item in Tbl_Results.
The "easy" way is to use an append query to generate Tbl_Results in advance. This works, but it raises a variety of new issues.
The nice way would be to use a normal SELECT query and have Access fill in the necessary linking fields (the Action_ID and Component_ID) on each row automatically. Now for trivial examples, this is very easy - my main form query manages just that: I created a link between Tbl_PlantComponents and Tbl_Comments (which stores general comments about each PlantComponent which aren't related to a specific test) based on the Component_ID and that works fine - when I edit the Comments field, the row is automatically created and the linked ID field filled in for me.
However, when I need to do it with 2 links, it all falls apart. I've tried everything I can think of, including generating a single-column unique ID to use for the link, but Access just won't autofill for me. It just makes those fields on the form (or in the datasheet view of the query) non-editable because there's no associated row in Tbl_Results. If I create a matching row in Tbl_Results the query works fine, but that's not the point.
Implementation of the query is non-trivial because it requires two outer joins involving 3 tables - All from Tbl_Actions to matching in Tbl_Results, and All from Tbl_PlantComponents to matching in Tbl_Actions. This necessiates splitting the query into two - the first relates Tbl_PlantComponents, Tbl_Typicals and Tbl_Actions (returning one row for each Action for every Component), and the second performs a single outer join (using an AND) between the first query and Tbl_Results.
View 5 Replies
View Related
Aug 14, 2015
I have staff table which is a lookup field on the phone call table.when the phonecall form opens I want the employee field to auto fill in the employees name from the lookup field according to there security level.so something like this
On Open
Select Case Forms!frmLogin!cboUser.Column(4)
Case X = 4'the employees security level'
Case X = Insert Table_tblPhoneCalls!EmployeeID"4" into Form_frmPhoneCalls!Employee.
End Select
of course this is a syntax error as I do not know the correct code words.
View 1 Replies
View Related
Oct 13, 2004
I have 3 tables, and I want to enter the data into them using 1 big form.
When I use wizard to create form, i manage to add fields from 2 tables, but once
I get to 3 tables, the form ends up not displaying anything. I am just wondering if
anyone can help me with the above problem, thanks in advance.
View 2 Replies
View Related
May 18, 2007
I have two tables(tblInvoiceItem and tblItems). On both of these tables there is; ItemNo and ItemCost. tblItems already has various records about items. I want to be able to have a lookup field for ItemNo on tblInvoiceItems which will populate the ItemCost from tblItems.
Basically when I pick an item number(lookup field on tblInoviceItem, looking up from tblItems), I want the price which corrisponds with it to appear in the correct field(ItemCost on tblInoviceItems).
Hopefully I explained that okay :confused:
View 5 Replies
View Related
Feb 20, 2005
I have a subform with two fields whose record source is a Query ("subquery")
Code in the query is as follows:
"SELECT Sum(capital) AS cq1
FROM capital_act WHERE (((capital_act.month)='jan' Or (capital_act.month)='feb' Or (capital_act.month)='mar') And ((capital_act.projectid)=forms!frmplan!projectid)) ;"
This query updates the field cq1 in my subform. I want to add 3 other fields for the 2nd, 3rd and 4th quarters. For instance the second quarter field cq2 field would have the capital_act.month as 'apr' or 'may' or 'jun' and so on...
cq1 value gets updated fine. I dont know how to add the fields cq2, cq3, and cq4 on the same subform whose record source would be the same query ("subquery").
I added a second SQL statement in the same query ("subquery") for cq2 but access does not let me add it.
Can somone point me in the right direction please?
Thanks.
View 11 Replies
View Related
Jun 27, 2005
Hello,
I've recently been told how to enable/disable fields in a form by ticking/unticking a box. Is there a way that, on a tabulated form, i can only disable/enable the field for the same record as the one the check box is in rather than for every record?
Any help will be greatly appreciated.
Thanks,
Phil
View 2 Replies
View Related
May 23, 2014
I am trying to update several fields all at once in one single query. Also, I can only use the Design View (No SQL)
The weirdest thing keeps happening though: The more fields I enter to get updated, the less rows get updated.
For example,
When I have only one field that gets updated and I click "run" the window pops up saying I'm about to update 9 rows. It asks me if I'm sure I want to update those records so I say "no".....
Then, I add in another field and click "run" and the window pops up daying I'm about to update 8 rows. When it asks me if I'm sure I want to update those records, again I say "no"......
Then, I add in another field and this time it says 7 rows..... and the trend goes on and on until it says I'm going to update 0 rows.
I have about 15 fields I need to update all at once. I don't get why its not letting me update them simultaneously.
View 6 Replies
View Related
May 28, 2007
Hello there,
I've lately come across some posts that condemn using lookup fields in tables. But how bad is this really? I mean it's quite a nifty feature, and it'd be really too bad if it hogs up resources, thus forcing us to leave it alone for performance considerations.
I'd appreciate if any of you guys/gals with more experience on the topic would like to share some, so I can take a better stance for future developement.
Regards,
Jaime
View 10 Replies
View Related
Jan 16, 2008
I have been working with a simple Dlookup to return a value based on the data held in a specific field. I am now trying to develop this further and need some help.
I have a query "QryMainForm" that brings together fields from 2 tables "TblFS" (fields: Ref, score 1, score 2) and "TblFamily" (fields: Familyname). the query adds the values in Score 1 and Score 2 and saves the value as "points".
Seperatly from this i have a table "Tbllevels" which contains the fields Score, Operational, Admin, Technical. with the points scored relating to a value within each field. What i need to do is run the lookup based on the both "points" and Familyname fields contained in the query.
So the TblLevels looks like this:
Score.....Technical.....Operational.......Admin
100........................... Level 1.........Level 2
200 ........Level 1..........Level 2.........Level 3
300.........Level 2..........Level 3
400.........Level 3
So someone who scores 300 points could be assigned to the technical or operational group (this is done manually). What i need the lookup to do is to return the level based on the points and Familyname fields located on "qrymainform"
any ideas on how i can do this one?
View 10 Replies
View Related
Feb 11, 2008
Hi,
I have a table that has 3 columns.
Issue, Review, and Information.
These 3 fields get their data from the same lookup table that holds some code values.
So I'm trying to create a query to export to excel that shows the data for this table, but for the lookup fields, I just get the ID's from the lookup table.
So the table is like this now
ID...Issue...Information....Review
1.......2..........5...............1
2.......1..........12..............6
So I would like to be able to display what the ID numbers actually are, but having trouble getting a query to do this for all 3 fields.
I can run a query that shows 1 field's but not all 3.
what I would like to see
ID.....Issue...Information....Review
1.......2a.........4c.............e5
2.......1a.........3c.............2F
all 3 columns use code_ID from the lookup table.
Is there a way to do this? or should I have used 3 different lookup tables for these?
View 13 Replies
View Related
Feb 12, 2005
I am working on a project that I know I am making more difficult then it need be...... Maybe because I started with a form and tried to build everything else around it. The database looked bad...... Now it looks good... normalized as I can see it... But trying to get the information in is being difficult.
The DB has customers, thats no problem. These customers have inspections done to areas for cleaning.
I have done the areas in a drop down, in one table, and the task to be rated as a dropdown in another table. Then finally a rating table....1 to 5.
It is very easy to go in in datasheet view and input the info for each area, then pick a task from the next dropdown and then pick a rating from the final.
Problem is this won't be as easy for someone else to input. So here is the form problem.
Heres how I would like to lay it out. An over all inspection form. Areas inspected and rating (average)
I will use a command button from the main form to open up say "area1"
On the area one form is the first problem....
I want 6 different task to give a rating to...... But these task are on a dropdown....so I can not bring over the field more then once.... or I dont know how..It's getting confusing and I don't think it should be.... its a pretty simple one.
So can anyone guild me to a databse similiar to this? I need to see how one like this is done.
Thanks
View 1 Replies
View Related
Apr 25, 2015
The lookup has a two column dropdown: FirstName and LastName. This is for the field, Sponsor, however I want to concatenate the First and Last name and have those stored in Sponsor. How do I do that?
Note: I just did it in the Lookup query.
Now a related problem is when I make a change to the table (e.g. Lookup), the change does not show in the form. Is there a way to "refresh" the form? My work around was to delete and re-add the field...I'm sure that is not the correct way to do it.
And when I added the field, the caption comes along with it bound to the field. Is there some magic keystroke to break the bond?
View 8 Replies
View Related
Nov 6, 2014
I wrote a database several years ago and recently pulled it out to give to a friend. The problem is, back then (not knowing better) I set the Employee table up with as a single field "NAME". Now, in order to make it effective, I need the Employee's name in four (4) parts (First, Middle, Last, Suffix).
I have several queries based on the "NAME" field and and would like to avoid changing all of them. I have a simple form "frmUpdateEmployees" that populates the Employee table and Name field. I was hoping to change the form and/or add a query that would be easier and more simple.
Table: Employee
Field: Name
Form: frmUpdateEmployees
Queries: 16 that depend on the table and field above.
View 5 Replies
View Related
Apr 12, 2005
I have a database that keeps track of inspection records. What I want to be able to do is open the form Inspection, and select a part # in a dropdown. When I do that I want it to fill in all of the critical item/dimension boxes on that same form for that part number that I selected above. I tried to figure it out from the sample database example on here, but I just cannot figure it out. Any help is greatly appreciated. A copie of my database is attached.
View 4 Replies
View Related
Jul 12, 2007
Hi, all! I'm once again needing your sage, wise advice.
I was reading on MVPS.org, and found a page that denounces the use of lookup fields in tables, because of the way it skews relationships, weighing down the database.
http://www.mvps.org/access/tencommandments.htm
(#2)
Do you all agree with this?
What do you recommend I do, in lieu of lookup fields, when I need to maintain integrity of the data.
Thanks SO much. You guys are my heroes!
KellyJo
View 2 Replies
View Related
Oct 13, 2014
When I set up lookup fields that point to a table do I save those tables in the back end with the main tables or is it okay to have in in the front end and not the back end? Users will need to make udpates to the tables for instance an employee table they would need to add employee names.
View 2 Replies
View Related
Jun 5, 2013
I have a query that based on certain codes in a record does multiple joins to pull in other codes... I want the query (based on these pulled in codes) to do a lookup in another table to extract 1 field that has a description...
Can I do this in one query??? Do I need to do a Make Table to first pull in the codes and then do a second query with a DLookup based on those fields???
View 3 Replies
View Related
