Tables :: Using MultiValue Field Without Making It Lookup
Apr 14, 2014
I've been doing some reading on multivalue fields and it seems to be exactly what I'm looking for. My question: Is there a way to use the multivalue field without making it a lookup? I need the user to be able to put multiple values in a field but can't limit them to a list. The field that needs multiple values happens to be for SKU numbers from a large department store franchise.
One work around I've come up with is to have a pop up that asks for the separate sku numbers so they can be used for the value list each time, but then the user also would have to select those from the value list in order to enter it.... too many end user steps.
I want to avoid making 10-15 different fields each for SKU number, Item, and cost... I also need to avoid having to populate my db with the department store's full list of products...
Is there another field that can be used beside the lookup field in an Access table? I am currently using the lookup field as a multivalued lookup field and I am limited to the the things I can do with it when creating a report or a query on that field. Is there a similar field in Access 2010 that has the same look as a lookup field and allows for multiple values to be selected?
I have a form that looks up office names and will automatically populate a field called office number based upon their selection in the combo box. I have a submit button on click event set up to run a query.
Now, here's where I am running into issues: In this query, I need to pull selected columns of information based upon a multivalue lookup field. This multivalue lookup field is joined with the table that the values populate from.
The form will only have one value stored in the txt box field, and I need to be able to search for all records containing that one value.
This is what I have for code:
SELECT FilePlan.FPName, FilePlan.Description, FilePlan.[File Code], FilePlan.GRS, FilePlan.Schedule FROM Offices INNER JOIN FilePlan ON Offices.[Office Number] = FilePlan.OfficeNumb.Value WHERE (((FilePlan.OfficeNumb.Value) Like "*" & [Forms]![RetCutOff]![txtOffNumb] & "*"));
Do I need to string multiple queries together to make this work, or is it just not possible?
I have two tables "Tab_Issue_1" and "Tab_Issue_2". Tab_Issue_1 has two fields "Issue" and "AssignedTo". Tab_Issue_2 has the same fields. However, the "AssignedTo" is a multi-value field in both tables. I want to append data from Tab_Issue_1 into Tab_Issue_2. I use the following SQL but it pops up this message "An INSERT INTO query can not contain a multi-valued field".
[SQL] INSERT INTO Tab_Issues_1 ( Title, AssignedTo ) SELECT Tab_Issues_2.Title, Tab_Issues_2.AssignedTo FROM Tab_Issues_2; [SQL]
I am designing a database for a quarry for maintenance of their machines. I have a table that has a full list of all the parts on the machines, and a multiple value lookup field that says what machines said parts are used on. I am trying to have these values translate to another table, so I can then insert a field to say how many of each part are used on the respective machine, to display on an information form for each machine.
I would also like this table to update if the relevant information is updated, for example if a new machine is input, then have the table update to reflect what parts are used on it (an After Update function?) I have made a query that gives me the read out I want (attached) but just cannot figure out how to get that into a table so I can add the extra information.
how 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.")?
I've inherited a data base which has the address details of our members spread across multi fields i.e. Add 1, Add2, Add 3, Post town, Post code etc.
Not all the fields contain information which means when I do a mail merge for address labels there are blank lines.
I would like to either be able to create a single field in the table (like a memo field for example) which contains all the address detail, or create a mail merge without blank lines.
I have two tables containing data from two separate sources. The unique "link" between the two is a JobNumber field. I need to set up a relationship between the two tables using the JobNumber field.
Problem:
In one table, the JobNumber has been input in this format: 12-00345-01 In 2nd table, the JobNumber has been input in this format: 12-00345-1
How can I tie these when the 2nd table is missing the "0"?
Here is my current table structure (I have omitted some fields from this example and have given some sample data in italics to make the table structure more clear.
tblEmployees
ID (autonumber) 3 EmployeeName John EmployeePhone 555999555 EmployeeLocation New York
tblClients
ClientID (autonumber) 1 , 2 , 3 ClientName ABC Company , XYZ Company, PQR Company fkeyLocationID
tblLocations LocationID 1 , 2 Location New York , Chicago
The junction table tblEmployeeClients only stores ID of the Employee and in the second column (which is a multi-valued field), the ID of each of the clients the employee Supports.
I am trying to generate a report that lists say, EmployeeName alongside the clients supported by the Employee (listing the client location is not required, however, it would be good to know how to do that as well).
The report (for the example above), should look like this:
Name Clients Supported John ABC Company, PQR Company
Currently, I am able to get :
John 1, 2 i.e the client ID for the clients that the employee supports instead of the corresponding company names.
I've a question that is teasing me. How would I create a multivalue/multiple answer field in access 2002. P'se forgive if this question has been asked before. Thanks in advance,
I have a database which is working ok where i have a set of tasks in a table with a bunch of irrelevant fields. On creation of a task i assign users to those tasks into a multivalue field(this is all fine).
Using a query i can report to each user what tasks they are assigned to easily.
To make the database more complex i would now like to assign these users to a task and then allow them to assign there portion complete (but not the whole task complete) but i can not assign a bool variable to a .value (can i?).
My thoughts are my database is just not set up for this by using the multivalue field to hold the assigned users?
I am building a horse racing based database, two of my tables being Courses (details of each track) and Races (Type of race, where run, prize money etc). In my Courses table I have CourseID (autonumber, key) and CourseName (text, max 25). The longest name in the list is 19 characters.
In my Races table, I have CourseID set up as a lookup, related to CourseName in the Courses table. This works fine if I type the CourseName in manually, or select it from the drop-down list.
My problem arises when I import it via excel, when the whole CourseID column is deleted, as not the correct data type. I have tried formatting the CourseID column in excel as text, general, and even number prior ti importing, all to no avail. How should I format this column in excel?
I have been working on an access 2003 database for four weeks now and have been asked to create a field in a table that allows a user to select mutliple entries from a list. This information in this field of a table needs to be able to be queried and reported on in written and graphical reports.
I have tried and hit a brick wall any help with setting this up would be most appreciated.
I have a date field that is just a typical date field. When I do a query, I want to look up by just the YEAR in that date field. Can I do that or do i need a separate text field with JUST the year entered in? or can i have a field in my table that just looks up the main date field and displays/uses ONLY the year??
I'm trying to create a field with the lookup wizard, however it's a bit more complicated. I need the field to have multiple choices(3) and when a specific option is selected I need some additional choices to appear. For example: Question - have you ever used a specific product. Options: a-yes, b -no, c-other. If option b is selected then thats it, but if option a is selected I need a few other options to appear: was the brand Option1 or Option2? Also did you use it before(option1) or after(option2) smth.
I have have a "master table" with Analyst ID and Analyst fields among other fields needed. Analyst ID is a number and Analyst will be the name of someone that corresponds to that number. I have a separate table that defines who is assigned to that particular ID.
1 Kim 2 Sarah 3 Beth
I have a form for this master table that shows Analyst ID and Analyst. I would like the default value of the Analyst field to be the name that corresponds to the Analyst ID number for the record. I would also like this same field to be a drop down on the form so that my users can change it as necessary. IE. If the record shows Analyst ID =1, the value for Analyst will show "Kim" unless changed to another analyst manually per the drop down.
I'm building a database to calculate yearly fees for customers. I have a list coming from our accountancy-database that shows a field with the clients name & surname combined and I have a table named 'customers' that contains the clients names & surnames as separate values.
The first table (let's name it "accountancy") thus contains a field "Customer". Example data in this field:
The second table ("customers") contains the fields "ID, name, surname". Example data in this table:
1;"John";"Doe" 2;"Marcy";"Free" 3;"John";"McLane"
Now, what I'm looking for is a way to replace the 'Customer'-field in the first table by a lookup field that contains the correct ID for that customer in the 'Customers'-table.
Lucky thing: there are no doubles in the customer's table, so no two customers have the same name AND surname.
I created tables that have lookup fields referencing another field. Actually I have several tables that all have relationships and object dependencies in my database. Now that I am trying to create some different reports, when I run the report I get the ID rather than the contents of the field. Also, I have a 'Report Dashboard' so to speak that I can run different reports from. On the form I have Combo boxes that reference one of the tables. I can generate the report showing the information, however I tried using a text box (criteria)(=[Forms]![Reports Form]![Combo47]+" County") in the header of the report to reference the selection made in the form combo box. When I run the report I generate the ID in the header.how do I get rid of the lookup fields in the tables, or is there another work around?
i have a user permission table.that consists of PermissionPK, UserFK, CompanyFK. I also want the username to be automatically filled in?So when a user ID is filled in on the table, it also fills in what that UserID's Username should be?As i need both the UserId and Username text for code that looks at the Environ username.
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'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?
I have a multivalue text field called "Groups" that is generated through a relationship with another table called "Comments". When I set this up I left the field as a text field when it should have been a number field, and now I can't use the database with sharepoint, because the field needs to be a number field.
My Question is there a way to convert it without loosing all my data, as there are over 5000 records?
My Table with the multivalue field is called "Contacts", the field is called "Groups".
I tried update and append queries but I must be doing something wrong....
