Lookup To Show Related Values
Aug 19, 2011
In the attached Database, I have four tables. The purpose of the Database is to track training for employees. A quick description of each table:
Employees: List of employees requiring training
Course List: List of Courses offered
Course Schedule: When said courses are offered (one to many relationship with Course List). this has a Composite Primary key consisting of the Course Number and Section Number
Course Attendance: This is to track which employees attended which class.
Question 1:
In the Course Attendance table, the first field (SOS Course Number) looks to the Course Schedule table. This field uses a lookup to select the course and section number, but only displays the course number. How do I get it to also display both the course and section number (don't care if it is displayed in one or two columns)?
Question 2:
Similar problem, except the second field is Employee Last Name which is a lookup from the Employee table. I want to display both last and first name in two separate columns.
Note, I realize there are spaces in table and field names. Please ignore this for now. It will be fixed later.
View Replies
ADVERTISEMENT
Sep 9, 2005
I have a table of 'things' and two related tables, type and subtype. Each thing has a type and a subtype. The types table contains simply Index (autonumber) and Type (text). The Subtype table Index (autonumber), type (number) and subtype (text), where index and type form the primary key and type is a foreign key to the types table. Thus each type has its own sub-set of subtypes.
In the design of the Things table I have set the lookup for Type and Subtype to be a dropdown, but of course the subtype drop-down shows all subtypes, not just valid ones. If I select a subtype that is incorrect for the selected type then of course I get an error, but it would be nice to set the query in the subtype lookup's Row Source to just display the valid subtypes. At the moment it just says "SELECT index, type FROM subtype" How do I put in a WHERE clause that references the Type field for the current row: "SELECT index, type FROM subtype WHERE type=<type selected in current row>"?
View 3 Replies
View Related
Jul 27, 2015
I have three large source tables imported into my database. I have created queries to retrieve relevant values from fields in each source table which feeds into my form. Each field on my form that is connected to the relevant query is a lookup field. For example, one field called "Supplier_Name" another called "Supplier_Code" and a third called "Route_Number".
Needless to say each of my lookup fields are very long. I am trying to filter my search based upon the selection from the previous Lookup field. How I can filter a lookup field's value based upon the previous lookup field selection? Each Supplier has a code and assign route(s) and I have already established these relationships.
View 4 Replies
View Related
Oct 14, 2006
I have a feeling this is a common and probably easy question, but I couldn't find anything on it in the forums.
I have a main customer form with multiple subforms on it to keep track of my clients insurance policies and claims. I enter their contact data in the main form, and the policy data on one of the subforms. Then when they have a claim, I enter the policy information on the claim subform as well.
How could I have the combo boxes in the claims subform (which is storing data in a claims table) only show the policy numbers (stored in a policies table) assigned to that particular person? I would assume I need to somehow filter the policies query so it only shows records for that particular person, but I'm not sure how to do that on the fly.
View 6 Replies
View Related
Dec 16, 2005
Hi,
I've been trying to get this for ages now - both in the design view and in sql:
I have 2 tables - one called DrawingsRegister and a related one called DrawingRevisions. Each drawing has one or more drawing revisions. I want a query that will show each drawing (just once) that has more than one revision:
SELECT tblDrawingsRegister.DrawingNum, tblDrawingsRegister.DrawingName, tblDrawingRevisions.DrawingNum
FROM tblDrawingsRegister INNER JOIN tblDrawingRevisions ON tblDrawingsRegister.DrawingNum = tblDrawingRevisions.DrawingNum
WHERE ((Count([tblDrawingRevisions]![DrawingNum])>"0"));
Thanks for your time,
RCurtin.
View 1 Replies
View Related
Apr 16, 2015
In a form of mine, I have the user input other person's initials, but I don't want those initials showing up on the report. It's a set group of initials to work with so I have each set associated with a random 3 digit number (that I input into the table myself). I'd like to be able to have the person input the initials in the form, but have the numbers display on the report, I'm not sure how to go about doing that.
View 5 Replies
View Related
Oct 10, 2012
I have 2 tables one, Contacts, primary key ContactID this table contains names, addresses etc and one group called Form primary key Form ID, foreign Key Contact ID. I want to store the related contact ID in my form but display firstname ad surname from Contaacts list. This works fine when I am selecting name. List box shows 2 columns with correct data but when I recall the form after making other entries it only displays the firstname. The correct ContactID is stored. Why does this happen.
If I can get this working I would like to concatenate the Firstname and surname to make it more readable but don't know where to begin.
View 3 Replies
View Related
Feb 23, 2015
I have a lookup value in a table, and I would like to remove all values of the lookup value from vba. How can i do it via vba? i've been trying to do it in a query but i have always some errors and i can not remove them.
View 2 Replies
View Related
May 18, 2015
Is it possible to have the control tip show related records from another table?
I've been looking around the internet, and I've seen many examples for showing concatenated fields on current record, but had no luck with what I'm trying to do.
For example.
I have a continuous form, and on it shows top line data. Where a record in this form shows as "Split" it means I have more data related to this record on another table.
Is it possible to show however many fields are related to this record, when I control tip, or mouseover?
I've been playing with
Code:
Me.textbox.ControlTipText = Me.textbox
but was wondering, if this is possible, how I'd reference the other table,
If said column of continuous form is "split"...
lookup related table by ID number
show however many columns of data..
View 2 Replies
View Related
May 11, 2014
I was creating a program using ms access. consist of 2 table of database called employee and userlevel.. in employee table have ID, name, username, password,userlevel, and others related employee profile.. and in userlevel table, it consist of admin level and normal user level.. Userlevel table allow to differenciate admin and normal user(employee) .. in my program each employee having their own loginID and pass to access their own data.. the problem with my program now is displaying the data that belong to the logged in employee. its mean that it only show the data that owned by currently user that logged in..
View 7 Replies
View Related
Mar 26, 2014
I would like to create a report that would really impress my supervisors, i just started at the company. I'm trying to create a call action plan, so i'm recording clients information on one table, and meetings we have had with each respective client on another table. Some clients will have multiple meetings, some few, and some none. I have a relationship set between them from the client's id number on the client table to the ClientID on the meetings table. one to many.
When I go to create the report, only the clients with meetings show up on the report, I would like client info to always show up on the report and meeting info to only show up under each respective client when it exists. I have worked out how to shrink and hide any text box without any info on it. It just seems like the existence of a meeting dictates where the client will show up at all in the report.
View 1 Replies
View Related
Jul 4, 2014
I have a form with a tabbed control on it. The tabbed control has 4 tabs that each have a report on them. When I select a company name on the main form, I requery these 4 reports to show data related to that company.
For some strange reason, 2 of my reports requery with no problem, and 2 of them give me a message that the report can't be found. Here is the code:
Code:
Private Sub cboCompanyName_AfterUpdate()
On Error GoTo cboCompanyName_AfterUpdate_Err
' DoCmd.SearchForRecord , "", acFirst, "[CompanyID] = " & str(Nz(Screen.ActiveControl, 0))
' After selecting a company, requery the subreports to display the appropriate data
' These 2 work
Forms!frmCompanyHistory!RptCompany_Projects_Brief.Requery
Forms!frmCompanyHistory!RptEmployeesSpecificCompany.Requery
'These 2 Fail
Forms!frmCompanyHistory!SubRptCompany_Payback.Requery
Forms!frmCompanyHistory!SubRptCompany_Revenue.Requery
'Other things I've tried
'Forms!frmCompanyHistory!TabCtl66!SubRptCompany_Payback.Requery
'Me.TabCtl66!SubRptCompany_Revenue.Requery
As a side note, if I chose the company, say "ok" to the error, and then go run the queries that these reports are based on, I get the correct records. Also, if I chose the company and run the 2 failing reports standalone, they work fine, so I know the problem isn't the reports or the queries themselves.
I have also checked and rechecked for any spelling mistakes and have found none. I should mention that the error message is "Programs cannot find the field 'SubrptCompany_Payback' referred to in your expression".
View 8 Replies
View Related
Feb 7, 2007
Hi!
I have a tables.
One is called: products => prdouctID,productName, ProductPrice
I created a dropdown list.
To read in the values of productName.
I wanted to have a textbox / label which will update the productPrice.
If i select productName as "Pirates", the textbox/label will show $50.00
If i select productName as "Who let them out?", the textbox/label will show $80.00
I have managed to do the dropdown list but cant seem to figure out the label / textbox.
Please advice?
Cheers!
Darence
View 2 Replies
View Related
Mar 27, 2014
I have two related tables. (related with AcqID)
tblAcq and tblAcqDetail
In tblAcq I have information about the delivery and in tblAcqDetail delivered articles, price and quantity.
tblAcq
[AcqID] ; [Contractor]; [TransportCosts]; [CustomDuties]; [SumWithoutVAT]; [LimSUM](this total cost of shipment Goods+other cost)
[Code].....
View 4 Replies
View Related
Jul 27, 2007
Hi,
I want to make the default value of my column "TimeonOW" to be the difference in months(past 12) from the entered date in "OWStartDate" and the current system date.
For for example
mm/dd/yyyy
CurrentDate - OWStartDate = TimeonOW
07/01/2007 - 05/01/2005 = 26
I played around with the DateDiff function but to no avail.
=DateDiff("d",Now(),[OWStartDate])
=DateDiff("d",Now(),#2004-31-12#) etc
The reason behind this is because I need to count how many people fall into different month span categories, maybe theres a better way?
Thanks in advance.
View 4 Replies
View Related
Aug 4, 2014
I have three tables: Event related on to many with Procedure Procedure relates on a one to one basis with the description of the procedure in a table called ProcedureCodes.
I wish to have a query which outputs three columns:
Event - Prcedure1, Procedure2, etc - Description1, Description2, etc.
I have tried the Allen Browne module [URL] .... This gives me:
Event - Procedure1, Procedure2, etc using the expresion:
Expr1: ConcatRelated("[Procedure Code]","[tbl-procedures]","[tbl-procedures].[Event number]=" & [Event no]) to concatenate the procedures.
But I am struggling to get the final column! I have tried the following:
Expr2: ConcatRelated("[short description]","[tbl-procedure code]","[tbl-procedure code].[ProcedureCode]=""" & [Procedure Code] & """" And "[tbl-procedures].[Event number]=" & [Event no])
But this does not return what I want.
View 14 Replies
View Related
Jan 26, 2013
I am fairly new to Access and have no formal training on this program, just a lot of trial and error. I have a database with 4 tables. They are tblEmployees, tblCourses, tblDates, and tblTraining. The key for each was an autonumber that is EMPLOYEEID, COURSEID, DATEID, and TRAININGID.
tblEmployee lists pertinent information regarding an employee (name, serial #, shift, etc). tblCourse lists all courses that are available for an employee (course name, #hours, required attendees, type of training, etc).
tblDates lists all available class dates and times for the courses in tblCourses and has a lookup field for COURSEID and COURSETITLE from tblCourses.
tblTraining lists all the training scheduled for and completed by an employee and has a lookup/relationship with tblEmployees for EMPLOYEEID and EMPLOYEENAME.
It also has a lookup/relationship field with tblCourses for COURSEID and COURSETITLE. Finally, it has a lookup/relationship with tblDates with lookup field/relationship with DATEID and CLASSDATE. I have successfully created a form where the training can be added to an employee (frmTrainingUpdated) and it has a subform (frmTraining) with all classes for that employee in a multilist at the bottom (from a query of tblTraining). The subform also has a field for whether the training was completed and then the hours are credited to the employee (txtCredit).
The problem comes when I try to add all of the credited hours for a single employee in a separate field. I am trying to create a field somewhere on the form that will total all of the hours for all of the completed classes for the one employee on the form. I have tried to list the data for the textbox as =DSUM([txtCredit], tblTraining, WHERE (EMPLOYEE=Me.Employee)) and only get an error message displayed in the textbox. I tried to do a totals SUM on the query and it only gives me the individual hours for each class on the same line for that record.
View 9 Replies
View Related
Mar 8, 2015
I have a table with following two columns
TechName Version
SQLServer 2000
SQLServer 2003
SQLServer 2005
SQLServer 2008
SQLServer 2008R2
Oracle 11G
MSOffice 2000
MSOffice 2003
MSOffice 2007
MSOffice 2010
How can i show this data as components eqch version for a techname to be one component order need not follow
TechName Component1 Component2 Component3 Component4 Component5
SqlServer 2000 2003 2005 2008 2008R2
Oralce 11G NULL NULL NULL NULL
MSOffice 2000 2003 2007 2010 NULL
View 4 Replies
View Related
Jul 31, 2006
In an order entry system I have two tables relating to products available: one listing the products (e.g. mugs, pencils, etc), and another listing options. The options table has two fields, one for the product type (e.g. mugs, pencils, etc), and the second has the options (e.g. red, yellow, green).
So, taking mugs as an example, there is one mug record in the products table, and three corresponding records in the second table because there are three different colours available.
I have created a lookup in my Orders table to lookup the product and another to look up the option.
What I now want to do is make the Options lookup only display options which are available for the product type selected.
So, say for example that pencils are standard and without options, then the options lists should not contain any items for the user to select. But, if a mug is the selected product, then the colour options should be in the list of selectable options.
How can I do this?
Gary
View 4 Replies
View Related
Jun 13, 2006
BACKGROUND
I am desperate to solve this problem but unfortunately I have not been able to figure it out. Below I will outline a design of a database and the desired results.
I know what I want to do but I don’t know how to do it (or whether it is impossible!)
DATABASE DESIGN
The design below is a simplified version of the real thing but it contains the essential information needed to understand my database.
Staff Data
Contains daily data for several members of staff
Staff ID
Staff Name
Date
Data Field 1
Example records:
600-001, Bob Smith, 01/03/2006, 50
600-001, Bob Smith, 02/03/2006, 50
600-001, Bob Smith, 03/03/2006, 50
600-001, Bob Smith, 04/03/2006, 50
600-001, Bob Smith, 05/03/2006, 50
600-002, Jayne Cole, 01/03/2006, 60
600-002, Jayne Cole, 02/03/2006, 60
600-002, Jayne Cole, 03/03/2006, 60
600-002, Jayne Cole, 04/03/2006, 60
600-002, Jayne Cole, 05/03/2006, 60
600-003, Alex Winter, 01/03/2006, 20
600-003, Alex Winter, 02/03/2006, 20
600-003, Alex Winter, 03/03/2006, 20
600-003, Alex Winter, 04/03/2006, 20
600-003, Alex Winter, 05/03/2006, 20
Team Lookup
Shows what team each staff member belongs to and what date this is effective.
Staff ID
Team
Start Date
Example records:
600-001, Sales, 01/01/06
600-002, Sales, 01/01/06
600-003, Accounts, 01/01/06
600-002, Accounts, 04/03/06
Please note:
The first three records show that at the start of the year Bob (600-001) and Jayne (600-002) worked for Sales and that Alex (600-003) worked for Accounts.
The last record shows that from 04/03/06 Jayne switched teams to Accounts
Query Assign Team
Assigns the correct team to Staff ID for each date
Staff ID
Staff Name
Team
Date
Data Field 1
Desired Results:
600-001, Bob Smith, Sales, 01/03/2006, 50
600-001, Bob Smith, Sales, 02/03/2006, 50
600-001, Bob Smith, Sales, 03/03/2006, 50
600-001, Bob Smith, Sales, 04/03/2006, 50
600-001, Bob Smith, Sales, 05/03/2006, 50
600-002, Jayne Cole, Sales, 01/03/2006, 60
600-002, Jayne Cole, Sales, 02/03/2006, 60
600-002, Jayne Cole, Sales, 03/03/2006, 60
600-002, Jayne Cole, Accounts, 04/03/2006, 60
600-002, Jayne Cole, Accounts, 05/03/2006, 60
600-003, Alex Winter, Accounts, 01/03/2006, 20
600-003, Alex Winter, Accounts, 02/03/2006, 20
600-003, Alex Winter, Accounts, 03/03/2006, 20
600-003, Alex Winter, Accounts, 04/03/2006, 20
600-003, Alex Winter, Accounts, 05/03/2006, 20
Query Group By Team
Summarises data by team/date
Team – Group By
Date – Group By
Data Field 1 - Sum
Desired Results:
Sales, 01/03/06, 110
Sales, 02/03/06, 110
Sales, 03/03/06, 110
Sales, 04/03/06, 50
Sales, 05/03/06, 50
Accounts, 01/03/06, 20
Accounts, 02/03/06, 20
Accounts, 03/03/06, 20
Accounts, 04/03/06, 80
Accounts, 05/03/06, 80
PROBLEM: WHAT I AM TRYING TO DO
I don’t know how to get the query “Query Assign Team” to work!!
I would like to lookup up the ‘Staff ID’ and ‘Date’ in “Team Lookup” and return the appropriate value for ‘Team’
If the only two records in Tbl Staff data were:
600-002, Jayne Cole, 03/03/2006, 60
600-002, Jayne Cole, 04/03/2006, 60
I want the query to return:
600-002, Jayne Cole, Sales, 03/03/2006, 60
600-002, Jayne Cole, Accounts, 04/03/2006, 60
Can anybody help me?
Should I be using DLOOKUP? If so, how?
Is VBA the only way around my problem? If so, can you tell me what it is?
Am I attempting the impossible?
View 9 Replies
View Related
Jan 22, 2007
I just started a new database and I'm new at this so I have a question about ID numbers for my two tables.
In Table1, I have faculty demographic information, and a FacultyID (unique ID number created by AutoNumber).
Table2 has the courses those faculty members taught - one faculty teaches many courses. But this table does not have the FacultyID that I added to Table1.
So, how do I automate the process of looking up the FacultyID number from Table1 and adding it to the currently blank FacultyID field in Table2? Theres hundreds of records and this will be done every semester, so I need an automatic way of doing this lookup.
Any help would be appreciated. Thanks.
View 1 Replies
View Related
May 9, 2014
I have 2 tables...a lookup table with possible responses (i.e. yes, no, don't know) and the second with 8 fields that contain the values selected from the first table. Tables are related one-to-many. I need to count the number of times each of the responses shows from the lookup table shows in each of the fields in the main table. Have tried to query with the lookup field set to "group by" and each field set to "count".
View 2 Replies
View Related
Jul 7, 2014
I have a field in a table where it is a lookup is a combo box. So this makes the data type of the field a number when in reality it is a text. When I have this field show in a list box it shows the number instead of the text.
View 5 Replies
View Related
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
Nov 16, 2006
I have a small database with 3 tables.
tblBilltoCustomer
Fields - Key - BillCustID , companyname, address1, address2, city, state
tblOrders
tblCustomers
I have a form that has tblCustomers as the main form then tblOrders as a sub form.
I want to place a combo box on the form that will lookup a company name from the tblBilltoCustomer table then brin in the address1, address2, city,state , into the form for that record. But then I need that same info to print on a rpt.
I can get the lookup to work using =cboCompanyName.Column() but the addresses , city and state will not show on report
Is there any good samples of lookup fields
Any Ideas are greatly appreciated
Dean
View 6 Replies
View Related
Feb 12, 2014
II have been working in Access 2010 and by no means would I call myself an expert. I have two different tables, one is called 'JobsList' and the other one is called 'StatHolidays'. On the Jobslist form, I have a field that requires a ship date, however, I don't want to allow the user to select the dates listed in the StatHolidays table. Is there a way that when a user picks a date that is listed in StatHolidays that a pop up box will say "This date is Christmas, do not choose this as a Ship date". The 'StatHolidays' table has a field for a date and for a description of the holiday.
View 7 Replies
View Related
