Tables :: Limited Number Of Values In Lookup Column
Nov 27, 2012
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.
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 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 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.
I am creating a customer database for an independent insurance agency. Within that customer database, we have a lookup field that references another table "Carriers". This is where we select the insurance carrier we have placed the clients insurance with (ex. Geico, progressive, etc). This field allows multiple entries since clients can have policies with different companies.
We also have contact detail forms for each of our insurance carriers where we store information like web logins, contact phone numbers, and other notes. These forms are based on the same table as the lookup field.
I would like to create a hyperlink so, if we are looking at a clients form with all of their contact info we can click the insurance company in the carrier field and it will bring us to the contact details form of that carrier.
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 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?
There is a autonumber column in one table Order.Everyday I need delete records in Order and then append records into it.Doing so, autonumber keeps on increasing.Is there any way to reset Autonumber from 0 after deleting records?
I have a column that's called "Date" which stores values for begining to end date as 'Text' like: 070314-073114 So I need to split it to 2 columns that has the Start Date as 070314 and End Date as 073114?
This database is still in Microsoft Access, and eventually after cleaning it up, we will move it to SQL Server.
I am using a form based on a table in MS Access 2010 for data entry.
The Data Type for column ID is AutoNumber with Increment=1. When I found ID for recent entries gets an anomaly (e.g. should be ... 3, 4, 5 ..., but it shows ... 3, 5, 6 ... that misses 4), I tried to refresh/change the ID column with the following steps:
1). Change the Data Type for column ID from AutoNumber to Number 2). Add a new column IDD and set its Data Type as AutoNumber 3). Delete the original column ID 4). Change column name IDD into column name ID
However, I got a problem with step 2): here the order # for the records becomes messed up, e.g.
Should be: ID IDD 1 1 2 2 3 3 5 4 6 5 7 6 ... ...
But, I got: ID IDD 1 3 2 4 3 5 5 1 6 2 7 6 ... ...
Why? Is this caused by the relationships between this table and other tables?
I have a datasheet with 7 columns. Two of the columns I'm working with are listed below.
File # Region # 2DE2-12345 2 3DE2-@@@ 3
In data sheet view I would like the Region # column to autofill based on the first digit of the file #. So, if file # entered = 3DE3-@@@@ then 3 would auto fill in Region Column. (data is entered in data sheet view)
I have used Left ([File #],1) to get value but cant make it auto populate the Region # column......
Currently I have a calculated field in my table; however, under certain circumstances I need to change the value to another value which is not related to the calculation. I understand that the calculated fields are read only (why is that?), so I was wondering if there was any way to change values within a calculated column without actually changing the expression itself.
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.
One shows my form with the Transporters Subform with 3 entries, and 1 entry.The three line items that say "Transporter" are in one subform. I used this code
Code: Private Sub Form_Current() If Me.RecordsetClone.RecordCount >= 3 Then Me.AllowAdditions = False End If End Sub
to limit the number of records I can add to 3 or less.My issue is that I lost the blank text box that allows you to add another record. So, if I only have one Transporter listed, there's no box to let me add a second or third.I have the following properties for the Transporters Subform set to "Yes":
Data Entry Allow Additions Allow Deletions Allow Edits Allow Filters
I'm wrestling with the issues; in other threads, it became apparent that because I could not know ahead of time what I will need to know about a given entity, I will use a table to enumerate attributes that is applicable for a given entity.
However, the stumper is that what if an attribute should conform to a set list of values? Since they are dynamic, I would have problem predicting what I will need to be able to lookup, and am even don't know whether I will need a one-many lookup or many-many lookup.
I thought that generic lookup table with a table listing "classes" of lookup would allow me to have one big generic lookup table while using "classes" to act like virtual tables so I can then set the query to appropriate "class" to return just right set of values.
But as I thought about it, I ran into some issues which is pulling me toward the crazy idea that I should have freestanding tables, and use a field in tblAttribute to give me the table's name so I'd know which free-standing table it points to, and have the necessary key to lookup the values within that table.
Even though my gut instincts tell me that I shouldn't be going against the conventions of database design (who the frick goes around creating free-standing lookups?!?), I'm simply not sure how I can use a generic lookup table to hold all information.
For example, suppose I was given a list of values that has its own categories. Since the former design allows only for two level (lookup and lookupclass), where am I to insert that extra level?
Furthermore, I found myself needing a set of virtual keys to reference a certain "class" of lookups for report purposes. That means I need an extra field in my lookup table than I originally anticipates. What if I find myself needing one more field that just won't fit the generic lookup table?
So does anyone have suggestions on how we would create a placeholder for a lookup table that will be made just in time?
In the organisation that I work for employees get paid every 2 weeks on a Saturday. So for this financial year the pay period end dates have been 08/07/2006, 22/07/2006, 05/08/2006 etc
I have a column in an Access table listing various dates. I want the next column to be populated with the next pay period end date after that date.
So if DATE is 05/07/2006 I want PAY PERIOD END to be 08/07/2006 and if DATE is 09/07/2006 I want PAY PERIOD END to be 22/07/2006 etc
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.
I have this table ("people") and an example of possible rows:
id(key) COL 1 COL2 department country name 1 xx yy KPP USA John 2 zz kk KPP USA John 3 ss ff TLL USA John 4 ww qq PPO Italy Marco 5 jj uu PPO Italy Marco
I have to count the number of distinct DEPARTMENT for each NAME; so, for John should be 2 (KPP and TLL) and for Marco 1 (PPO).
I have tryed in this way:
SELECT COUNT(DISTINCT department) AS NumberOfDifferentDepartments FROM people GROUP BY name;
I have a form which is based on a query that displays the expenses for a given month(the parameter value).
In my expense table i had set up the month field as a lookup column which basically consisted of two columns, one was the month and the other the numerical value of that month. for eg. if the month is August, then the numerical value is 8. i had set this lookup column in such a way that if the user were to directly edit the table (which i don't want) he would have to click the drop down box and he would see only the name of the months, not the numerical value. (i set the numerical value column to width 0")
if the user makes a selection (in the table) that field takes the numerical equivalent of the month which is fine.
The trouble is that things screw up when i use the form. This form is actually a subform. In my main form i have an option group (with toggle buttons to represent the 12 months). Everytime the user clicks any of the toggle buttons in the option group the subform is requeried, and the chosen month's records are displayed. The trouble is when i do this, the month is displayed not as aug, sept etc but as its numerical equivalent. (However since the month field is shown with a drop down arrow, the user can still make a selection, and when he/she does so, the month for that field is shown as the name!)
I need the form to display the records as the name of the month and not the number. the number was only so that the form qould be requeried using the option group. Can someone help me out here!!!!
I have a access table with 32 columns and 42,000 rows of numbers. I need to find the MIN number in the row and if the MIN number has duplicates then I need them all placed into another column by column name. Example: Starting file DEST,ORIGIN1,ORIGIN2,ORIGIN3,ORIGIN4, ETC 05512,3,2,4,2
What I am wanting to do is to have columns at the end of my imported data that reference another table to tell me based on the queue what service type and centre it relates to.
I have attached 2 pictures one with table 1 and the other with table 2. Table one shows the last column 'SERVICE_TYPE' which I need to lookup from the reference table (aka table 2 - which has been attached).
