Lookup Field In Report
Aug 14, 2006I have a lookup field in my table - when I try to create my report, it lists all of the data in the lookup list - I just want to display the one that is stored in the table.
How do I do this?
ADVERTISEMENT
Tables :: Lookup Field Using Another Multi Value Lookup Field As Data Source
Nov 23, 2012how to do a particular thing in Access 2010 (I don't even know if it is possible).
I have a table named PRODUCTS:
ID_PRODUCT (primary key, autonumber long integer)
ALLOWED_OPTIONS (multi value text lookup field: "Option 1";"Option 2";...;"Option 9")
So I can store, for each different product, none, one, or more options to let the customers choose from.
I have a table named ORDERS:
ID_ORDER (primary key, autonumber long integer)
FK_CUSTOMER (foreign key, linked to the primary key of a CUSTOMERS table; represents the customer that places the order.)
FK_PRODUCT (foreign key, linked to PRODUCTS.ID_PRODUCT; represents the product that the customer has choosen)
CHOOSEN_OPTION (lookup text field; the customer must choose ONE option among those allowed for the product he has ordered)
The problem is that I would like the CHOOSEN_OPTION field to show as a combobox, listing the values stored into PRODUCTS.ALLOWED_OPTIONS, so that when a customer buys a product, he can choose only among the options allowed by that particular product.How can I manage a multi value field to populate a combobox, in which every item stays on its line? If I use, as a query to populate the combobox:
select [PRODUCTS].[ALLOWED_OPTIONS]
from PRODUCTS
where [PRODUCTS].[ID_PRODUCT]=[FK_PRODUCT]
I obtain an empty combobox.If I refer to the last field as [ORDERS].[FK_PRODUCT], Access asks me to type a value for "[ORDERS].[FK_PRODUCT]", treating it as an unknown parameter.I think that the problem is that when the combobox expands, the record is not committed yet, so FK_PRODUCT is unknown (NULL?). But this happens even if I commit the record typing something in FK_PRODUCT and then I re-enter the record and I expand the CHOOSEN_OPTION combobox, that is still empy although FK_PRODUCT exists, now.Is there a particular syntax to refer to a field in a record not committed yet (something like "THIS." or "ME.")?
Parm Query/Report Lookup
Dec 15, 2005Hi,
I have a report by department that the user needs to enter in the department name to produce the report(Parm.Query). I have tried to make it somewhat easier via wildcards but if they still don't know exactly how the data is stored, they will have a hard time. Example: Labor and Delivery is stored in the table as L&D. Is there a way to use a dropdown/lookup to select the department to run generate report?? I know this is very basic but I am having a "No brain Day" Thanks in advance!!!! Rick
Rowsource Of Lookup Field Dependent On Field Value In Same Record In Subform
Jul 6, 2014I want to create a different rowsource-query for a lookup field (field1) in each record in a subform. The rowsource changes dependent on the value in another field (field2) in the same record. How can this be done?
- I tried to change the rowsource-query in an eventmacro when the focus is set to field1, but this ofcourse changes the rowsource for all field1's and makes the allready selected values unvisible.
- I think I have to include the value of record 'field2' in the rowsource query, but i cannot find a way to include that value in the query.
Something like:
Lookup field1 in the subform contains this rowsource
- SELECT CUSTOMER.Id, CUSTOMER.AGE, CUSTOMER.NAME
FROM CUSTOMERS
WHERE (CUSTOMER.AGE= me![field2]);
me![field2] however does not function
Please Have A Look At My Report. It Shows Numbers (for Lookup Fiels) Instead Of Names
Mar 29, 2008In my db the look up fields show up as numbers.
for example i have a look up field called "company" and i have two companies in it (Reddot & Thingz).
But on report they come up as 1 and 2 instead of their names.
The db is attached.
21343
If anyone knows the solution, please help me.
Thanks!
Obtaining Multiple Report Parameters Via Menu Form Lookup Control
Feb 11, 2015In our Student Administration database, we have a Student Evaluation Report which prints a 1 page per student report in memo like format. The instructor had previously input rating numbers (4=Best, 1=Worst) for 9 categories for each student via a database form into the Evaluations Table.
Two other elements of the rating are an Attendance based on number of days absent and a Grade Point Average (GPA) calculated from the student's test scores. The instructor then prints and reviews the report with each student.
There is an Evaluations Parameter table which has the following fields:
Field
Description
ID
The autonum key field.
Class
eg., 2015-1
Evaluation Number
A single digit (eg., 1, 2). There may be more than 1 evaluation for each class.
EffectiveDate
The "as of" date of the evaluation.
There are then several other tables that are input to a query that will be the record source for the report:
Table
Purpose/Data
Students
Student Name, Class
Absence
Has a record for each student's absence with date and a 1 or .5 indicating a whole or half day absent.
Test Grades
Has a record with each student's test results with date and score.
Evaluations
Holds the rating score for each of the 9 rating categories.
The Student Evaluation report is launched from an unbound Reports menu form via a button. On the Reports menu form I wish to have controls for the user to select which evaluation to report on. The Absence and Test Grade information needs to include records that are <= the EffectiveDate in the Evaluation Parameter table. The Evaluations records need to match the Class and Evaluation Number in the Evaluations Parameter table.
I would like the user to be able to select the Evaluation Parameter via a combo box vs. specifying the Class, Evaluation Number and Effective Date separately and then have the query record source for the report use those two fields as criteria. But my limited understanding of the Combo box indicates that only 1 field from the lookup query of the Evaluations Parameter table can be stored in the control whereas I need 3 (Class Evaluation Number and EffectiveDate).
How the user can select the desired Evaluation Parameter record on the Reports menu form and use the 3 fields from the selected record as criteria in the report's record source query.
Lookup 2 Field To Auto Fill Another Field?
Dec 30, 2012Is it possible to look up 2 field to auto fill another field?
Field 1 is "Exposure" this autofill with "Probability" is user input.
I need to lookup Exposure and Probability and autofill a score in to "Risk Rating". This to stop incorrect data being inputted.
I have a table with all the results combination in it.
Would it be possible to use a Dlookup to look at Exposure and Probability to give me the score
I tried a Combox with autofill. But the power to be would like it done without user input.
Lookup Field
Jan 11, 2008Hi,
my only other issue!!! i have a lookup field on a table and when i view it in datasheet, i can see the actual lookup value i need however i have tried the following options:
create a form from the table
added a combo box to an existing form and gone through the wizard
both options show the id number and NOT the value i need to show. what can i do to show my actual information?
many thanks,
NS
Using The Lookup Field...
May 3, 2005Let's say that I have two tables:
Table Numbers:
ID VALUE
-- ------
1 123
2 456
3 789
Table Letters
ID VALUE NUM_LOOKUP
-- ------ -------------
1 ABC 1
2 DEF 3
Now, I'd like to use the lookup feature so that NUM_LOOKUP is a combo that displays the data in VALUE based on the relationship between ID in the two tables.
So, when you open the Letters table, you see the above. If you click in the NUM_LOOKUP field, you get a box that displays "123" for the first record and "789" for the second record.
I'm very close -- Here's what I have:
I tried the following:
Display Control: Combo Box
Row Source Type: Table/Query
Row Source: SELECT [ID],[VALUE] FROM [Numbers] AS [NUM_LOOKUP];
Bound Column: 1
Column Count: 2
Problem: This selects ALL values of ID from [Numbers].
I tried adding the following to my row source:
WHERE [Letters].[NUM_LOOKUP]=[Numbers].[ID]
This just gives me the "enter parameter" dialog box for [NUM_LOOKUP] and [ID].
Any help?
Thanks!
Lookup Field
Jul 31, 2006Alright, I have a field called metrics that looks up the text in another table caled Metric Name. I want to delete the table that is being used to supply the values, but I still want a listbox with all those names. Also I want to be able to add names to that listbox. I am not sure how to do this.
View 3 Replies View RelatedHelp With Lookup Field
Dec 11, 2006Hi all,
It is along since i have used access for anything and I am having problmes getting it to do what i want it to do.
I have 2 tables one which hold the data needed and the other is being used to hold a list of products with prices.
In my main talbe in which the data will be entered into I have a drop down so the product can be selected, once it is selcted i want the next field filled in automaticaly with the price. The look up looks at my products table which hold the price too. I tried with setting the look up based on a query but it get errors about using the same table
Anyone help me???
Thankyou for looking
Lookup Field
Sep 25, 2006I would like to create a lookup for a field - I want to display 2 columns, but store only one in the table.
Id City
1 Sheboygan
2 Sheboygan Falls
3 Plymouth
Show both fields in the lookup, but only store the Id in the table.
I can't seem to get the second column to display.
Lookup Field Value
Mar 13, 2007I have two columns in the look up field. When I select the lookup value in the form I need value from one column to be displayed in one box and the value from the second column displayed in another box. Is there a simple way to do it?
Thanks
Lookup For Value In A Field
Apr 30, 2013I am working on a database where i have to show the user last modification date of the files they are getting the data from. I have already completed that process. Used GetDatetime to find the files last modification date and time. And now i am moving on to the next step and Here is the scenario.
User will click the IMPORT button and first, it will check the last modified date in the form of those files(which is an unbound textbox) and compare that date with the Date Column that exist in the tbl_Import. If the Date matches it will show them a message Saying "Data cannot be imported since it already exist. If it doesnt match it will run the macro i have created to run the data import process
Restrict Lookup Field By Value Of Another Field
Mar 9, 2007Hi gurus,
I have two fields in the main record table with values lookup from two other tables. These two table has 1-many mapping (like country - States). Can I make change within the record table. When I change the value in country field for a record, the States field will only show the states of that country?
Now I have lookup query for these fields from country and states seperately, so no matter what I change in the country field, the states field show all states for all countries.
Thanks for your advice!
Lookup A Field Based On Another Field
Mar 15, 2006Hi, I'm pretty new to access but almost have a database working the way I want it to. I just need help with one thing. I am using PPC Creations on a PDA to fill out a service docket. All the fields export to the DB ok, the problem I have is displaying the info on a report. I have one field to display one of 8 manufacturers and then 8 fields (one for each manufacturer) that display the model. A bit like this...
IntruderPanelType (results are code eg. 1=ADE 2=ADEMCO)
ADE (results are text eg. Optima)
ADMECO
DABOSCH
GARDTEC
MENVIER
SCANTRONIC
TEXECOM
OTHER
OtherIntruderPanelType
So, on my report I want a field (maybe needs to be new) that will display the contents of the relevant manufacturer field, depening on what is in the "IntruderPanelType" field. On the report if "IntruderPanelType" is "1" and "ADE" is "Optima", I want it to display.......
Control Panel Type: ADE Optima
I'm sorry if I'm not making myself clear but if you can understand my ramblings I'd really appreciate a simple solution to this. I've attached a copy of my database so far, in case it helps any! TIA for any help you can give.
Regards,
Brian
Lookup Field Row Dependant
Mar 18, 2005Hi Everyone,
I have a question about lookup fields. I have the tables below. The main table is [vid] and i reference other tables like [manufacturer], [model].. etc. to fill in [vid]. I have pull down lookups for all of [vid] but it is showing all models, makes, etc. I need helping making the lookup row dependant. For example when I am in [vid] and click on the [model] combo box for vid=1, I only want to see the models for that manufacturer(Ford), so it will only show mustang, escape, and navigator; not all of the models. For vid=2, when I click the pull down for model I only want to see Gm Models, corvette and cavalier. So this is what i mean be row dependant. Is this possible?
Thanks so much in advanced,
Michael
[vid]
vid,year,mft, make, model, submodel, ...
1,2005,ford,ford,Mustang,GT
2,2005,GM,Chevrolet,Cavalier, LS
3,2005,dcx,Jeep,Wrangler,base
...
30,2005,ford,Lincoln,Navigator, base
[manufacturer]
mid,mftName
1,ford
2,gm
3,dcx
[Model]
modelID,modelname,modelmft
1, Mustang,Ford
2, Escape, Ford
3, Corvette, GM
4, Cavalier, GM
5, Navigator, Ford
6, Wrangler, DCX
Field & Lookup Table
Jan 6, 2006I am very new to Access and any help would be appreciated. I have been through the entire Access Bible and many forums but can't find the answer to my specific question.
The project:
I'm setting up a database for tutors and students. Tutors will, via the web, enter data about a specific tutoring session (studentName, subject, sessionLength, etc.) Then at the end of the month a report will be generated detailing hours tutored and total compensation.
The problem:
Students pay varying rates based on subject (math or verbal). So in the Session table each record for each tutoring session has an empty field called payRate. This is the rate the student will pay based on subject. So I need this field to "fill in" automatically based on the 'student' and the 'subject' from the same record. Then I can multiply the field by sessionLength and get a totalPay field for the session. Add these at month's end and pay the tutors.
I've set up a lookup table called 'subject' with three fields (student,subject, payRate) and I have the student and subject combined as the primary key. Am I on the right track here? If so, how do I link the tables to get that Session 'payRate' field to fill in automatically. This is where is breaks down for me. I've tried the lookup wizard but all I seem to get when I open the Session table is an empty PayRate field with a drop down box containing the entire subject lookup table.
Sorry for being so long winded but I need help from someone better at his than me. Thanks in advance.
Update A Lookup Field
Apr 20, 2006I have a table (tblStudent) containing a field named fldTeacher1. Looking at the properties of fldTeacher1 I see that it has a lookup:
Bound Column: 1
Column Count: 2
Column Widths: 0";1" (id number; teacher's Name)
I am trying to run a DoCmd.RunSQL command in code and My question is how do I write a sql statement in code that updates that field with a new value?
I have tried:
DoCmd.RunSQL ("UPDATE tblStudent SET fldTeacher1 = " & Val(txtID) & " WHERE fldID = " & gintStudentID & ";")
All it does is place the id # in the field and not display the teacher's name in fldTeacher1 after the command was run.
Field Lookup On Forms
Apr 17, 2005I am a new Access (2003) user.
I am using one table (Table A) as a data source for a form, using the form to update and edit records. However, I wish to include a field on the form that uses an ID field in Table A to perform a lookup on Table B.
To explain further, I have a staff ID in Table A, and I want to look up and display, but not edit, the corresponding surname from Table B (just for display and convenience - we all recognise surnames rather than ID's).
Many thanks in advance
Setting Up A Lookup Field.
Feb 15, 2006Ok I'll premiss this by saying it has been many years since I have done much with access so I have forgotten alot and alot of things have changed. However I have been using Dataease alot but switched companies so am back to access.In dataease if I had a form called projects and that form had three fields (employee ID, employee first name, employee last initial) that pulled data from another table other than the projects table I would relate them via the employee ID. projects form employee ID = employee form employee IDthen for the two other fields I would have them lookup their data by using the employee id.projects form employee first name = if (projects employee ID = employee employee ID) then lookup employee employee first name. And the same for the last initial.The question is how do I setup for fields for first name and last initial to do the lookup in access.Employee ID TableEmployee ID #Employee First NameEmployee Last InitialProject TableEmployee ID (linked to employee id table)project idproject nameproject discriptionproject start dateproject end dateproject formproject idproject nameproject discriptionproject start dateproject end dateemployee ID (must match employee table employee id)employee first name (read only field that looks its data up from the employee table based on employee id)employee last initial (read only field that looks its data up from the employee table based on employee id)
View 1 Replies View RelatedDefault Value For A Lookup Field
Apr 14, 2006Hi Guys.
I have a look-up field in a table that looks-up a range of values from another table. How do I set a default value for the field? I have tried entering in numbers as default values (IE the ID numbers for the records that are being looked up) but that doesnt work.
Lookup Field In A Table
Jan 12, 2008hi, plz i need a fast help
i have 3 tables in access:
Country Table: fields:country Id, country name
City Table: fields: City ID,Country Name(lookup field to Country Table), City Name
Street Table: fields: Street Id, country name(lookup field to Country Table), City Name(must be lookup to City Table where City.Country name = Street. Country name), street name
my question is: how can i make the lookup of the City Name in the Street Table, in a way that only cities of the specific country of the record active are displayed ???
example:
Country table:
1 Lebanon
2 KSA
3 Kuwait
City Table:
1 KSA jeddah
2 KSA Riyadh
3 Lebanon Beirut
4 Lebanon Tripoli
STreet Table:
1 KSA (???) Sary (the ?? must be a combo Box that show only the cities of KSA: jeddah, Riyadh..which are in the City Table)
2 KSA (???) sondos (same as above)
3 Lebanon (???) hamra (the ??? must be a combo box that show only the cities of lebanon: Beirut and tripoli...which are in the city Table)
i have created all the fields in these tables...and when i put the field City Name as lookup, i am failing to mak eit read correctly from the city table according the the country of my active record..
plz anybody knows how to do this query???
One Lookup Or Auto Populate Field
Feb 25, 2006Haven't touched Access in quite some time and now I can do a simple task.
Here's what I have:
2 Tables
Table 1 includes 2 fields COURSE CODE and COURSE NAME
Table 2 will be a list of students and the courses they have taken
What I'm trying to do is as I'm entering a new student in the database I want the course name to automatically fill in when I type in the course name.
I did lookup fields for both the course code and the course name to ensure the correct codes are entered. However since the data in table 2 does not autofill there is the posibilty of having a row with a course name that does not match the course code.
How can I force table to 2 match the course name and code relationship from table 1?:confused:
Using Lookup In One Field, Auto Fills In Another
Jan 11, 2005I have 2 tables and I am trying to link them using a 3rd table, as per a previous discussion, this is a new enquiry.
I have two fields on the new table that I want to display the same information as the first table. Ie. Co ID and Co Name.
Is there a way to when selecting the Co Name auto fill in the Co ID?
This would also be usefull in my call record form, where I select a name and the Phone numbers auto fill in.
I can put several fields in the lookup combo box, but I cant get the info from them to the other field.
Thanks :)
Lookup Field / Autonumber Woes
Jun 21, 2006So, basically my database has quite a number of lookup fields in tables as there is much repeated data, each one references to a seperate table with an autonumber field (ID) and a text field (Value)
Currently I have column one (ID) as the bound column in such cases, as this is what the guy who started me on access said to do, and it's fine until I try and export any query with such a field to Excel.
I have found, through experimentation, that setting the 2nd column (Value) as the bound column allows me to export the values rather than numbers, but I don't know if this is a sensible thing to do? (ie, I don't know what other unexpected effects this might have)
Any guidance here would be very welcome, thanks,
Bogzla