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
tbl_Retainer tbl_Retainer_Grant_Funding tbl_Retainer has the field, Retainer_ID
And tbl_Retainer_Grant_Funding has the fields, Retainer_ID (a lookup field from tbl_Retainer) Agreement_Num (a lookup field from tbl_Grant)
I have a form based off of a query(not sure if that matters), that uses that tables, tbl_Assignment and tbl_Assignment_Grant_Funding. These tables have the above fields as lookup fields.
So...what happens is, if an Assignment has a Retainer, I want the Agreement_Num box to show only the Agreement_Num's associated with that Retainer, otherwise just show all the Agreement Num's.
In my form, I have Retainer_ID with the row source, SELECT tbl_RETAINER.Retainer_ID FROM tbl_RETAINER;
And Agreement_Num with the row source, SELECT [tbl_GRANT].Grant_ID, [tbl_GRANT].Agreement_Num FROM tbl_GRANT ORDER BY [Agreement_Num];
In my AfterUpdate event for Retainer_ID I have,
Private Sub Retainer_ID_AfterUpdate() Dim strSql As String strSql = "SELECT [Retainer_ID]," & _ "[Agreement_Num]," & _ "FROM tbl_RETAINER_GRANT_FUNDING" & _ "WHERE [Retainer_ID] = " & Me.Retainer_ID.Value
Me.Agreement_Num.RowSource = strSql Me.Agreement_Num.Requery End Sub
When I am in my form and choose a Retainer ID, the Agreement_Num box goes blank, and there are no choices to choose from. I am wondering if this is because the Agreement_Num's are sourced from tbl_Grant and not from tbl_Retainer_Grant_Funding.
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
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’
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.
I have a database that has 2 tables. Table A and Table B. Table A is my primary table. On this table I have 2 fields. The first field is a LOOKUP Field that looks up information from Table B and displays my selection in the field on Table A. Then using DLOOKUP I automatically input the information in the Second Field on Table A based upon the selection from the First Field.
This is working mostly correctly. However, the problem is, when I click on the next record in the table, it automatically changes the Second Field on that record to the same value as the record before it and continues this trend each time I click on another record. This occurs without me making a selection in the first field. If I make a selection in the first field it does change the Second Field to the Correct Value, but then the next Record has the same issue.
How do I go about fixing this so it doesn't change the value with the change of the record. Only change if I change that particular field within that 1 record?is there a way to restrict the Value's in my lookup field to only include the Values from Table B that aren't already in Table A?
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.
The Lookup table has two fields containing values that are needed: Description and Amount ($). In the table that uses the Lookup, I'd like to have both values shown, but have a dropdown just once. In other words, when the user selects a description (the dropdown shows both the description and amount), can the amount be inserted into an Amount field as the description is done currently.
Is there anyway to have a single field in a table which is populated via the use of a lookup onto another table, but allowing multiple value selection out of the lookup table and populating those into the field...
For example
Table 1 is customer details Table 1 field 3 = areas of interest
Table 1 field 3 is populated via a lookup into Table 2 interests
Table 2 has 4 records
Sport Household Motoring Family
I want to be able to select 1 or more of the Table 2 values and populate them into Table 1 Field 3....
i am working on a school database, in data base i have create two tables tblAccounts and tblTransaction and a form frmTransaction .
tblAccounts contain two fields GLcodes Description and frmTransaction contain Glcode transaction type debit credit date narratives
in form when i enter a glcode, lookup field match the code from tblaccounts and shows the description in form against gl code.But i am facing a problem when i enter a wrong gl code my form accept it and move to the next field and when i leave blank field of glcode same problem that i am facing, i want that , when i enter a wrong glcode in a form amsgbox will apear that asking for correct glcode.
I am currently working on an instrument datebase, I have a mainquery that takes care of user inputs from a form. The main fields that have been queried on are Type, System, and Manufacturer and they are all look-up fields that contain some null values.
On the same criteria row for these fields, I have
Like IIf([forms]![User Interface].[qtype2]="","*",[forms]![User Interface].[qtype2]) Like IIf([forms]![User Interface].[qsys2]="","*",[forms]![User Interface].[qsys2]) Like IIf([forms]![User Interface].[qman1]="","*",[forms]![User Interface].[qman1])
qtype, qsys and qman are the user inputs from the user interface that returns look-up table values.
This works fine when all 3 of these fields are all filled out for a certain instrument. The problem arise when some fields of the instrument are left blank or is null. The instrument won't show up in a query at all. What I wanted it to do is to show everything including the ones with null fields when the user input are null or "". When the user specifies certain requirement I only want to show the ones that are not null. I understand that putting them on the same row means AND, I have tried to OR them and did not have the result i wanted.
I have a split database and need a field (Combo type) in the table to lookup values from a query in the front end. How do I do this as it doesn't see the querys because the front and back end are split?
I have a form which has a combobox called Task_Ref which looks up values in a table column.
I would like to be able to set the tickbox value of tickbox called P1 to True if the combobox contains the word "test", each entry on the combobox selection may vary such as:-
Test number 1 Yesterdays Test
As long as the word "Test" appears I would like the above to happen?
I was thinking of something along the lines of:-
If InStr(Task_Ref.Value, "Test") > 0 Then P1.Value = True Else P1.Value = False End If End Sub
I have a DB that that is growing slowly. To keep it simple I have 2 tables and one form. One of the tables is a “ASSET_lookup” table. The other table (ASSETS) is the real meat and potatoes. I have a form for the assets table that will populate specific fields that are looked up based on a single combo box linked to a query from the assets_lookup table. Everything worked fine until I needed to add some additional items to the asset_lookup table.
The new “records” I added to the lookup table will not show up in the combo box after it updates (a selection is made). I did some experimenting and if I recreate after adding the new records to look up table, the combo box will show the value, but I am curious what I am missing.
how data is best structured in Access.I have a table of values (for instance: weight) and I need to be able to look up a weight based on the column header (age) and row header (height).How is this sort of data best structured and accessed in Access?
I'm using a lookup table to populated a combo box on my form. I use a provided list of input strings (hundreds) in my combo box and the list is provided in all caps. How can I convert these provided words (strings) to first cap from either the quarry level or form level.
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.
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.
Am I correct in thinking the only way to have a combo box with different values in each column is to create a table and then bind the combo box to the column you are referring to if you want it to represent a value from another.
For example if I have a combo box with the words January, February etc can that combo box have a corresponding month number value in a separate column (this combo would be derived from a list) or would I need to create a table holding both the month names and numbers and then bind to the name value for selection but use the month number value?
The reason I ask is I wasn't sure if for every type of list I wanted with multiple column values, I would need to create a table.
I am trying to write a query that gets all the telephone hours from a worker done under supervision in a town (qry_svhoursbytown). I have a table that has the workers details, including town (Crisis_support_workers), then another table that has what hours each worker has done (Supervision_Hours). The town is selected from a combo box on the form (frmSearch), under the heading �total hours by location�.
I try and select Bathurst then press recalculate results and in the text box next to supervision hours, it says 66, but if you look in the Supervision_Hours table, there is only 11 hours for the workers that are in Bathurst.
In the query, I have the sum total of the hours field in the Supervision_Hours table, the Town field from the Crisis_support_workers table with the total selected as Where and in that criteria I have [forms]![frmSearch]![ComboTown], then I just have the town field displayed.
What do I need to change to get it to sum correctly?
I have attached the database below so people can take a look.
I'm working on a table for work which will serve as a database of bins and the products currently in those bins, as follows:
ID Bin Amt Product 1 34 25 110001 2 33 15 200005 3 32 23 110003 etc.
Basically: employee A will use a form to update "Bin 34" (ID=1), with information (Amt, Product) until it is emptied by employee B and cleared using a separate form. No new records will be made or deleted, just the adjacent fields (Amt, Product) cleared and filled in and cleared again over and over.
What I'm trying to figure out is, when employee A clicks on the combo box on his/her form to select a Bin and enter information, how can I filter what is listed in the combo box to display only those bins which have no values under Amt & Product. The real-world risk of overwriting a bins fields before it has been cleared by employee B (who physically empties the bin) can be extremely high monetarily so I am trying to reduce that risk by eliminating it from the combo box until employee B has cleared those fields.
I'm trying to create a combo box that takes its values from a field in a table, the thing is that this field has duplicates because:
* The table is a qualification look up table * It has 3 fields: QualificationID (autonumber), Qualification(e.g. Bachelor), Programme (e.g. Science) * E.g of duplicate values (this is not a problem as it is necessary) Bachelor of Science, Diploma of Science, Certificate of Science
What I'm trying to do is create 2 single-columned combo boxes that are a parameter for a query and it's working except the combo boxes show duplicated valuesIs there a way to get the query that is the row source for the combo box to eliminate duplicates?
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.
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:
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.
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.
I am new to Access and am currently learning it so that I can construct a database of trivia questions.
I have a "Questions" table with question, answer, type, etc. This table is linked to a "Categories" table with a many-to-many relationship using a junction table called "Questions_Categories". The "Categories" table is also linked to a "SubCategories" table with a Categories as the parent and SubCategories as the child. I have attached the database for ease of understanding.
When I enter data into the "Questions" table using datasheet view, I open up a subsheet that shows me the Category and SubCategory fields which have their own lookups.
My problem is the following: I want each category to have its own set of SubCategories. For example, the "Region-Specific" category has "Canada", "London", and "Ontario" as sub categories. However, the movies category should not have any sub categories. The problem is that when I choose Movies in the sub datasheet, the subcategory lookup still shows Canada London and Ontario when I don't want it to show anything. It doesn't make sense to have a Movies category with a London subcategory.
