Multiple Valued Fields
Sep 10, 2007
I need to have a field that is labeled zones. This field can have anywhere from 1 to 7 or so values. The Values are numbered 1-15. If I have a person that operates in zones 2,3, and 4 I need to be able to have the annotated in the record so that when I want to see all the people operating in zone 2 it will recognize that Joe Smith works in zone 2, even though he also operates in zones 3 and 4. I need to do this in Access 2003 since that is what they have at work. I know that 2007 has multivalued fields but I don't have that version at work. So if I understand this right I create a separate table with headings Zone and Autonumber as the PK. Then I link that to the main table in a one to many relationship between the zone table(many) and the main table(one)? Then when I create a query that searches for all records that have a 2 in the zone it will find Joe Smith's record? Is this right? Thanks for your help. If there is an easier way please explain it to me. Thanks again.
View Replies
ADVERTISEMENT
Jun 22, 2013
I have two fields in a table that have multiple values. Example:
Field A:
CT, CA, PA
Field B:
CT, CA
I want to compare the two fields and indicate that there is a match because in this example CT and CA are in both fields. I would like to create a function. I'm not sure if I would have to use something with like or create a loop.
View 5 Replies
View Related
Dec 4, 2013
I have a subform query which includes EventName, Date, and Tags. Tags is a multivalued field.
What I need to do is allow people to filter this subform using the inbuilt filter, and then use the filtered data to populate a graph.
I am doing this by taking the Filter property from the form and then using VBA to build an SQL statement with the right data, and using that on the graph. It works fine when people filter on the EventName and Date fields.
However, when someone filters the Tags field, it fails. The filter property gets set to something along the lines of
([Lookup_Tags].[TagName]="whatevertagtheypick")
This syntax seems not to be supported when I put it into the SQL statement.
View 1 Replies
View Related
Jul 5, 2013
i'm trying to connect to a table valued function on sql (2005) but keep getting the following error: argument are of the wrong type, are out of acceptable range, or in conflict with one another.here's the code
Code:
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = New ADODB.Connection
cnn.ConnectionString = "DSN=mydsn;UID=myuser;PWD=mypw;"
cnn.Open
[code]...
View 4 Replies
View Related
Aug 21, 2013
I have a main form with multiple sub-forms (each a separate tab). Having imported the fields from the equivalent tables, none of the multi-value combo box fields on the sub-forms work as they do not show anything. I tried implementing one on the main form (to test) and it worked fine. What am I doing wrong with the sub-forms fields?
View 1 Replies
View Related
Oct 27, 2015
I have 2 tables with text fields
Table 1 has 3 fields and 2 rows
DEVICE| DISPLAY1 | DISPLAY2
_______________________________
A | A1 | A2
B | B1 | B2
Table 2 has 2 fields, but the second is a multi-value field
DEVICE | MDISPLAY
_____________________________
A | **MUTI-VALUE** based off the table 1
In the row source of the Multi-value Field MDISPLAY, I have
Select DISPLAY1, DISPLAY2 from TABLE1
The problem. I can get the MDISPLAY field to display the items from Table 1, but it grabs ALL of them.I need it to display ONLY the DISPLAY1 and 2 field associated with the value of the DEVICE for the current row in table 2, which is 'A'
SO if I look at the row of the table 2 that has the device 'A', the MDISPLAY field for that row should have.Just A1 and A2, NOT A1,A2,B1,B2(all the rows). how to access / syntax of the current value of the DEVICE field in my row source.Select DISPLAY1, DISPLAY2 from TABLE1 where table1.Device = Table2.device /or Device ... etc.. 'doesn't work
View 1 Replies
View Related
Apr 14, 2015
I have a field in a table that I want it to be multivalued. The values are stored in a different table. I tried to do that and it worked fine. But when I wanted to display a pie chart in a report based on a query, the values didn't show up.
Example:
EmployeeName: X
TermenationReasons: 1,2,3 (multivalued field - values stored in a different table)
I though of just changing the field type to a text, and create a combo box in the form that allows me to add multiple values, and appear on the table and the query, and the pie chart too.how to make a multi-valued combo box in a form.
View 3 Replies
View Related
Mar 20, 2013
I am trying to make a database containing the technicians of our companies.
I found a online template i am using, and this has a quick search field.
I have created a multivalued field that contains the technicians skills. (pulldown with checkboxes)
When i try to use the quick search field access returns a error message saying something like:
"The multivalued field '[Category]' can not be used in a WHERE- or HAVING-string."
The field i use for skills is an old category field.... Just renamed and changed a bit..
View 4 Replies
View Related
Dec 31, 2014
have a look on the attached sample database.
How can I get the query to work in order to count specific values per day.
View 5 Replies
View Related
Sep 10, 2012
i have table on sharepoint which i can not link to my access database so i have to import it. table on sharepoint is same as in access and i need table from sharepoint updated with my access. unfortunately this table has multi valued fields. when i import table from sharepoint it, the table gets renamed so if the original table is tablea the imported table becomes tablea1. im trying to update my table in access but then i get error "An INSERT INTO query cannot contain multi-valued field." if i remove multi valued fields from query, the query is working fine. how i can get the values into my table in access?
View 4 Replies
View Related
Oct 9, 2014
How to add a multi valued fields in a form for example a student may have multiple hobbies, access can do this using using lookup option, but access stores multiple values in one field which is difficult to analyze, looking for efficient way of adding multiple option.
View 8 Replies
View Related
Nov 30, 2013
The main form has textbox & a Listbox with which I filtered a datasheet inside a sub form. Everything worked fine only the listbox [Discipline] is not working !! it cuase Run-time error: 3831. The multi-valued field "[Category]" cannot be used in a WHERE or HAVING clause.So how do I filter a multivalued Listbox field [Discipline] ?
Private Sub cmdSearch_Click()
'On erorr GoTo errr
Me.tblFLM_subform1.Form.RecordSource = "SELECT * FROM tblFLM " & BuildFilter
Me.tblFLM_subform1.Requery
Exit Sub
errr:
MsgBox Err.Description
End Sub
[code]....
View 3 Replies
View Related
Sep 25, 2014
I want to create a form that allows users to update multiple fields for multiple assets. Below is what I came up with:
Ideally, I'd like the subform to be filled in by having the user select multiple Assets from the S/N combobox field which would then auto-populate the "Type" field. Then they would fill out the appropriate fields they want edited in the top part of the form. They hit save and magic happens. This would also be nice because only assets they want edited would be displayed (easier on the eyes) and no distinguishing would be necessary. To do it this way, I know I would need to use a temp table but I wanna avoid using temp tables.
I know I can do this by adding a Yes/No field in the "Asset" table, setting the "Asset" table as the subform's recordsource, and then putting a checkbox in the subform and allowing them to check the assets that they want to edit (which would also allow me to sort it instantly so that checked Assets are at the top of the datasheet for easy viewing), but I would like to know if there's a way of accomplishing this without the use of checkboxes.
I know I could also use a listbox and that allows them to multi-select items, but I'm not sure if that allows me to group all selected items at the top of the listbox for easy viewing of selected items. Plus it would involve a lot of scrolling (there are over 2k assets).
View 2 Replies
View Related
Nov 16, 2006
I HAVE A DATASETS cSV TEXT FORMAT WHICH HAS A SELECTION OF FIELDS THAT NEED TO BE IMPORTED INTO A TABLE. USING THE ADVANCED IMPORT FACILLITY I HAVE BEEN UNABLE TO IMPORT THE DATA PLEASE HELP AS THIS IS FOR MY A-LEVEL STUDENTS.
THE FIELDS REQUIRED IN THE SESSION TABLE ARE,
Booking ID, Customer ID, Date Booking Made, Pickup Point, Payment Made
The CSV data set attached
tHANK YOU
SIMON
View 2 Replies
View Related
Oct 28, 2013
A small issue I was wondering of for a few day . Is it possible in SQL query to SELECT multiple fields from multiple tables ? Example for the question is
Code:
dim my_var as String
my_var = "SELECT Emp_FName , Emp_LName , Emp_Adress " _
& " FROM Table1 " _
& " AND Emp_Date_Of_Payment , Emp_Sum_Of_Payment " _
& "FROM Table2 " _
& " WHERE Emp_ID = 3 "
Is this code actually valid in SQL gramatics , and is it usable if passed to a Recordset variable ( rs = CurrentDB.OpenRecordset(my_var) ) ? Just FYI - The two tables are not related and I want to keep them that way (If possible relate their records just via SQL/Vba )
View 7 Replies
View Related
Apr 12, 2013
I have 10 tables, 30+ fields on each table (every table has the same 'account number' field). I only need from 5 - 20 fields from each table. How do I get the certain fields from each table and put them in a table, query or report?
View 1 Replies
View Related
Jun 27, 2006
I created the below query to come up with a new form. When I enter a single parameter, it works fine. When I modify the code and enter multiple patameters, it also works fine if I do not enter any information for the parameters. But once I enter one of the parameters information, then it does not come up with anything. I double checked and made sure it was typed in correctly. Is there a trick when entering multiple parameters on a query?
SELECT [JE 06 Log].[Operational Region Name], [JE 06 Log].[Period Name], [JE 06 Log].[Source System], [JE 06 Log].[Source Name], [JE 06 Log].[Category Name], [JE 06 Log].[Associated Category Name], [JE 06 Log].[JE Name], [JE 06 Log].[JE Base #], [JE 06 Log].Area, [JE 06 Log].[Line Description], [JE 06 Log].[Natural Account], [JE 06 Log].Description, [JE 06 Log].[JE Entry Date], [JE 06 Log].[Debit Amount], [JE 06 Log].[Credit Amount], [JE 06 Log].Amount
FROM [JE 06 Log]
WHERE ((([JE 06 Log].[Period Name])=[Enter Period]) AND (([JE 06 Log].[Source Name])=[Enter Path]) AND (([JE 06 Log].[JE Base #])=[Enter Base Number]) AND (([JE 06 Log].[Natural Account])=[Enter Natural Accnt])) OR ((([Enter Period]) Is Null) AND (([Enter Path]) Is Null) AND (([Enter Base Number]) Is Null) AND (([Enter Natural Accnt]) Is Null));
View 6 Replies
View Related
Feb 16, 2013
I am using MS Access 2007.
I have created a multi-valued field "Product Category" that lookups data items from a linked table. So, the Data Type for the multi-valued field "Product Category" is Number.
Now I want to change the Data Type of "Product Category" from Number to Text, and make a value list that I can type values in and can provide the same data items as the linked table.
How to change item source for the multi-valued field from a linked table to a list that I can type in values? Is there a feature provided by MS Access 2007 can enable such a conversion?
View 2 Replies
View Related
Feb 16, 2013
I am using MS Access 2007.
I have created a multi-valued field "Product Category" that lookups data items from a linked table. So, the Data Type for the multi-valued field "Product Category" is Number.
Now I want to change the Data Type of "Product Category" from Number to Text, and make a value list that I can type values in and can provide the same data items as the linked table.
How to change item source for the multi-valued field from a linked table to a list that I can type in values?
Is there a feature provided by MS Access 2007 can enable such a conversion?
View 8 Replies
View Related
Mar 1, 2012
I am creating a CAD Drawing database / Distribution list. I can only seem to distribute one drawing i.e single field to one person.Is there a way to have multiple distribtion on one field without creating multiple distribution fields?
What I have is a database which contains Drawing Numbers - I need to create a report that shows where the drawing has been distributed to, with it's current revision status and the date is was sent.I am having trouble trying to use multiple revisions and mutiple people in the distribution list with one drawing.
View 4 Replies
View Related
Jul 19, 2005
Hi All,
I have multiple fields on a form, does anyone know how I can make them unvisible without writing 30 times .visible = false.. Someone told me I could use the tag property.
Thanks in advance.
View 3 Replies
View Related
Dec 28, 2005
Hello, I have what I believe to be a very basic, albeit wordy question regarding an Access based mailing list that I am designing. For the scope of this project, I have 7 fields: Position (the position of the person recieveing the mailer, So far all I have is the "Principal", these are all schools) Name (The name of the school), Address, City, State, Zip and SchoolID (this is the primary key and will be hidden)
My question is: How can I add multiple positions per School (Name)? I need to add 1st through 6th grade teachers as well as the councilor to each location. So each of these people will be sent a mailer. Any help on this would be greatly appreciated. Feel free to ask any questions needed.
Thank you
View 11 Replies
View Related
Feb 11, 2008
I have an order form that requires me to change the current promotion we're running continually. I need to populate all rows based on the update changes of one textbox. Is this possible? Right now when I update the promotion text box, it only updates the text for row one.
View 1 Replies
View Related
Nov 25, 2004
Hi There,
I have a question which is not so easy to discribe but maybe easy to answer, so i'm giving my best shot.
I'm making something to store information about patients. If made the forms en table with the wizard within access and some extra functions with VBA. The first problem i have is this:
I have to many control elements on 1 form (control boxes, I get a error message) so I need to split this form up into 2 forms. Other control elements or no option, because the input should be like this.
The information form both forms need to be kept together in the linked table as 1 record. But now I have split the form up into 2 forms I get 2 separated records. 1 record with the information from the first form and the other record with information from the second form. My question is what can I do the merge both records to one or maybe better to make sure that both forms only generate one complete filled record, so my table won't grow unnecessary big.
Please keep your answer as easy as possible so I can keepup. Thanks!
Greatings,
Pascal
View 4 Replies
View Related
Jul 21, 2005
I am trying to display five columns from three seperate tables. The query is fine but I have redundant fields showing. I thought the GROUP BY operator would fix this, but I have more than one column to select.
SELECT ID, FirstName,LastName, Salary,City
FROM worksat INNER JOIN store ON worksat.store = store.storenumber, employee
WHERE worksat.store=store.storenumber
GROUP BY city, id, firstname, lastname, salary
ORDER BY city;
Thank you.
View 2 Replies
View Related
Jun 26, 2006
hi there,
I have been struggling over this the past few days and get no where. I am trying to count the number of times Yes appears in a few fields. This is what I have right now in design view:
Field1YesCount: Count(IIF([Field1] = True,1,0))
Field2YesCount: Count(IIF([Field2] = True,1,0))
Field3YesCount: Count(IIF([Field3] = True,1,0))
This doesnt seem to work, could someone please help.
Thanks
View 2 Replies
View Related
