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.
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 am working with Independent School Districts (ISD) in the state of Texas. The State is divided into 20 regions. Over 1000 ISDs are divided among those 20 regions. Each of the 1000+ ISDs has between 3 and several hundred individual school campuses. Each has a name, but as you would imagine, there is some duplication - not within an ISD, but between different ISDs. For instance, both Dallas ISD and San Antonio ISD may have a Lyndon B. Johnson campus.
We are an association that has members on most of the campuses.
I am using an autonumber ID as primary key on the member, campus, and ISD tables.
During entry/editing within the member table using the basic datasheet, I want to use a lookup field (showing campus names) to select the campus. That is not a problem by itself. But with many tens of thousands of potential campuses to choose from that doesn't work very well. What I would like to do is restrict the campus lookup to the names within the ISD, that is, filter the lookup by ISD.
However, I don't want to include the ISD in the member records because that will not be full normalization, but I am willing to do so if necessary. But if I do, I want to include only the ISD number (another autonumber primary key for the ISD table). So during member entry, I want to translate the ISD key into the related ISD name for selecting the correct ISD, before I try to select the correct campus within the ISD.
Essentially what I want to do, is member entry thru a datasheet and first look up the ISD to find it by name, then use that selection to filter the lookup on campus to limit the selections to only campus names within the chosen ISD.
I would be grateful for any assistance with this. I am not married to this approach, so if someone has a better but different idea, please suggest it. However, I am a relative novice, so bear that in mind.
When designing a table I've created a field and set its lookup properties to display a combobox with a row source that returns a DISTINCT set of values already entered into the field.
After a row insert or row update the combobox needs to be required to ensure its list is complete.
If I create a form to display my datasheet this is easy. But I'd prefer to enter data directly into the table datasheet directly. I need to enter simple data into about 20 different tables and I'd prefer not to create 20 forms unless it's really necessary.
The lookup wizard generated entries similar to those I'd previously created manually, except the wizard generated a couple of extra settings that appeared briefly that were not part of the regular set of lookup tab properties. These additional settings referred to 'update propagation'. Once they'd disappeared I couldn't see any way to get them back..
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.
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 have a subform that is displayed as a datasheet, and the source of this data is from another database. Essentially, it shows someone's name and status, but the status is displayed as a number. The people using the database don't know what this value means, so I was asked to display the label instead.
The values still need to remain in the database where the data resides, but can I use a lookup table or some other mechanism so that the label is displayed in the database the users are viewing? How exactly do I do this?
i.e...the data below resides in Database A but is displayed in Database B: Col1 | Col2 | Col3 John | Smith | 2 Jane | Doe | 1
And in Database B only, it should show as: Col1 | Col2 | Col3 John | Smith | Inactive Jane | Doe | Active
Note that the users of Database B will not be updating Col3 data, they will just be viewing it.
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.
Back in A2003, I could filter a table by the In() function with the table open in datasheet view. A2007 doesn't seem to want me to be able to do that. How do I filter a table by multiple values while in datasheet view to be able to delete some records.
Background: I get a datasheet in every month that, due to marketing to the same customers in different month, the YTD file I get has duplicate accounts. I have to delete one of the records, but the criteria for deletion aren't something I can query for. This is why I need it open in datasheet view.
My alternative is to make a table out of the duplicate entries, delete those from the original table, then append the cleaned accounts.
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.
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
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.
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.
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 building a small database to automate the process of producing sales reports for our sales staff using data from our customers (distributors). They provide us with Excel spreadsheets with detailed sales data for our brands at THEIR customers (retail stores).The problem is that many stores receive from two distributors, and each distributor of course has different "customer numbers" for the store. I've built the database with the following:
tblStores (containing the list of stores) StoreID Distributor1StoreID Distributor2StoreID Distributor3StoreID
tblSalesData (containing the monthly sales per store, by brand) StoreID Brand1Sales Brand2Sales ... etc
When the distributor provides the spreadsheet, they use their Distributor1StoreID (or 2 or 3, depending on the distributor). I want to import it to tblSalesData but would need to lookup the StoreID from the tblStores during the import, using Distributor1StoreID, etc.
Hi, not sure if this is possible or the most economical approach but here goes:
I have a lookup table (tblHolidays) with a list of holiday dates (fieldname "HolidayDate") in it.
I need an update query that can check all "StartDate" values on a table called "tblMasterLog" and where this date matches the ones on "tblHolidays", will add 1 day to the "StartDate" and then keep repeating until there are no more dates to change.
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.
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 have a database for scheduling students' for tests. They can take up to six tests in a day. There are about 80 different tests that they can take.
In my table, I created columns titled Test1, Test2, Test3, etc. They are lookup columns and I chose to enter my own values, putting in the tests titles for the values in each column.
When I add these drop-down lookup fields onto the form, it will only display 37 of these values. When I go back to the table and select "edit list items," it shows that it did cut the list off at 37, even though originally it allowed me to enter all 80-ish titles.
Anyway. It appears that there are limited values you can have in a lookup column, though after doing a lot of searching online I can't find anything to indicate that is true.
It seems to me that it would be smarter to set this up with two different tables, storing the reg info in one table and the test titles in another table. However, I am having a hard time figuring out the relationship aspect of this solution and how to make it pull up the correct values for queries/reports as well.
I wanted to create a form where you can select multiple values from the table "years" and on a button it would open a query displaying all the records in "students" in those years selected.
A have a query that selects a multivalue field. The query forms the record source for a subform. The text values in the multivalue field are displayed in the query, but when I save this and view the data in the subform datasheet view the values revert to the primary key values. They are 1,2,3,4 instead of the text values
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?
