I have a problem building a query using multiple check boxes. The situation is as follows:
I have a database to monitor enquiries which come into a central email account. I have devised a system where several check boxes are used to define the nature of the enquiry. Users can tick any number or none of the options to describe the enquiry.
I would like to create a query where users can search the database based on the check boxes which have been ticked. For example, if users want to search for enquiries where a specific three check boxes have been ticked, I would like all of the records where these three boxes are checked to appear, and so on for all different combinations of check boxes ticked.
I've tried a few methods, including creating a seperate form for the query where users check their search criteria in line with the enquiry form, but I can't link this with the original form.
I hope my description adequetly conveys the problem I have!
I have a problem building a query using multiple check boxes. The situation is as follows:
I have a database to monitor enquiries which come into a central email account. I have devised a system where several check boxes are used to define the nature of the enquiry. Users can tick any number or none of the options to describe the enquiry.
I would like to create a query where users can search the database based on the check boxes which have been ticked. For example, if users want to search for enquiries where a specific three check boxes have been ticked, I would like all of the records where these three boxes are checked to appear, and so on for all different combinations of check boxes ticked.
I've tried a few methods, including creating a seperate form for the query where users check their search criteria in line with the enquiry form, but I can't link this with the original form.
I hope my description adequetly conveys the problem I have!
I want to perform queries that count the number of customers using combinations of different products. The data is organised like so:
CustomerID ProductType No of Items 1 A 5 1 B 1 1 C 2 2 D 2 3 B 2 3 B 3
and so on.
There are 5 or 6 product types. I need to be able to produce a table showing all the various combinations. For example, number of customers using JUST product type A and nothing else, number of customers using product type A and product type B, customers using product type A, B and C and so on.
I think there are around 64 different combinations! I was wondering if there is a quicker way to do this apart from writing 64 different queries!!
i am very new to access and even vba. I have a form called FrmAgent. Here there are 5 check boxes [CheckBob] [CheckJim] [CheckNikita] [CheckNoe] [CheckKyle]. I have one button named "RecieveData". I have one table named "Agent" and a query called "QryAgent". in the same column there are five people Bob Jim Nikita Noe and kyle. What i need is to have the button when its clicked run my query to produce the data. However, if multiple boxs are checked i need the data for those people. As of right now if i click the button (doesnt matter how many boxs are checked) it gives me data for all five people. Is there a way i can do this where i can receive the data only for the people that their boxs are marked?
I have a table(Product Change) with these fields: Tracking Number Approved (a check box) Engineering (text box that represents department) Purchasing (text box that represents department) Quality (text box that represents department) Production (text box that represents department) Customer Service (text box that represents department)
I want my query results to show all records that have the "Approved" check box...checked, and then only the records that have one of the Department fields with a null value. So I'm looking to see only records that are "Approved" and out of those....only the records with at least one department field empty(Null).
Any help doing this is SQL view would be great...or even design view.
I really hope someone will be able to help me with this one as I am sure im just missing something simple.
I have an unbound form which has 20 yes/no unbound check boxes. The purpose of the form is to allow users to tick the various fields and a subform return the results. The subform, which does requery when a check box is ticked is based off a query. Initially, I wanted all the records to display before any check boxes are ticked so I have used the following criteria:
Like IIf([Forms]![Search]![Field1]=False,"*",[Forms]![Search]![Field1])
Which basically reads if field1 is no then display all records, else display all yes. Now that works fine but what I would like to have working is that if a client ticks field1, field2, and field3 it displays all records that have ‘yes’ in either field. Currently, if more than field is ticked the query treats it like:
Field1 And Field2 And Field3 And etc = true
I want to be able to select several check boxes and have the query return results for each check box that was checked. I would like to avoid doing this by having an append and delete query per checkbox.
I have the following dilemma. I have to make multiple tables with many columns and if I were to type in the data manually it would take me a few weeks. But if I could somehow specify the number of columns(and the number of choices in each column) and then have Access(or Excel) create a table with all the possible combinations it would make my job a LOT easier. For example, let's say I have department stores in multiple cities. I need to create the following table:
CITY DEPT CLOTHING
San Jose Men's Shirts San Jose Men's Pants San Jose Men's Shoes San Jose Women's Shirts San Jose Women's Pants San Jose Women's Shoes Los AngelesMen's Shirts Los AngelesMen's Pants Los AngelesMen's Shoes Los AngelesWomen's Shirts Los AngelesWomen's Pants Los AngelesWomen's Shoes
Instead of typing that out manually, is it possible to create some sort of list like:
Cities: San Jose, Los Angeles Dept: Men's, Women's Clothing: Shirts, Pants, Shoes
And have Access or Excel create the table with all of the possible combinations?
I need to be able to find duplicate combinations (or, I suppose, permutations). I have four columns in a table that all contain inter values. I want to be able to query so that I can see if there are more than one records that have, say, 12, 28, 5, 14 in columns 1, 2, 3, and 4, respectively.
I have 8 tables all with a membership number as the unique identifier, I need to find all the possible combinations and then create queries for each combination, is there a quick and easy way to do this? or do I have to sit and define a query for each of the 250ish combinations?!
I'm pulling all the data together using a union query. What I need to do now is to find the combinations using either "is null" or "is not null" criteria (in the queries).
ok this is weird post but im wonderin how to go about it easily cause i can think of a few ways, dunno if they will work of not, so im asking for ur opinion. I need to find out possible cominations for a salary cap football league based on dollar amounts for the players and points based on last years results, yet it cant go past $100.00...so one possible choice is (QB Tom Brady $22.56 183.50 points). its 1 QB, 2 RB, 3 WR, 1 TE 1 Kicker, 1 DEF team and i have to do all 9 for it to count...so would i have to set up 6 columns, one for each position and have a way of picking 1 of A 2 of B 3 of C 1 of D 1 of E 1 of F for 1 qb, 2 Rb, etc? and have it so sum of values is < $100 and it also must add the points so i can see the best possible combination/most points i can get for $100.00...i know its last years points and this year will be diff but I am just comparing...all i need is an outline i dont need u guys to do it for me i have some experience with access...thanks for anyone who can help
I am looking for a piece of code (either in access or excel) which will return all combinations of records in a particular field (or column of cells) which will sum up to a given value (or a close approximation thereof - ie a margin of error of, say, 2).
In other words, imagine you have a number field with 100 records in it. I would like to know all combinations of those records which add up to a value that the code could specify (eg 223). This would be incredibly valuable to assist in performing reconciliations that I perform in my role.
I am trying to create a cross join or Cartesian product TABLE, not a query.
I am creating a training database. For each and employee and each training event, I want to know - is this event required, who approved it, and when was it completed. The table I envision looks like this:
Code: EMPLOYEE_ID EVENT_ID REQUIRED APPROVER EVENT_DATE 1 1 Y WPD 9/5/14 1 2 N
I currently have 39 employees and 473 events - 39*473 = 18447 records
I was able to make a cross join query and use make table, but whenever I add a new employee or event, if I update the make table query I will lose all my existing data.
I am new to Access and am trying to reduce a dataset I am working with to make it managable for Excel. I have three columns which have three unique parameters and one column with numberical content.
Column A Column B Column C Column D NJ Red Monday 10 NJ Red Tuesday 20 NJ Yellow Wednesday 30 NY Red Monday 35 NY Green Tuesday 40 NY Green Wednesday 60
I want to elimiate column C from the dataset, and sum column D for every unique combination of Column A & Column B. I have figured out how to reduce down the dataset to get a list of all unique combinations of Column A&B,what formula I can use in a query to sum column D on my table for every unique combination of column A&B. This is a simple SUMIF in excel, but my dataset is too large for excel.
I have value in the form call (welder) & based on the other data i will enter in form i need VBA to check if this welder is certified to do the job or not.
I have several check boxes, that are however categorised, in my form. I was now wondering, in order to save some time, how could I make a check box that marks/unmarks all the boxes under that specific category ??
I am using a yes/no field to select a number of records for a report. Is it possible to deselect and turn them all back to no at one time when finished with the report. It would be nice if I did not need to deselect all of them individually.
I have a table and in that table I have multiple columns one being check boxes and another is the description. There are others but these are the relevant ones. I filter under description for HR Payroll but this description is not uniform. After HR Payroll it could vary so the description is not always the same. I want to select all check boxes under this description and was told use a query no clue how to do that though.
When UseDDelivery is checked the three textboxes should be disable and when the form is opened UseDDelivery is set to be checked by default so the textboxes should be disabled when the form opens but they aren't. the two different ways of doing it are shown below.
Elements specific to my system :UseDDelivery = checkbox AltDeliveryAddress = textbox1 AltDeliveryTown = textbox 2 AltdeliveryPostcode = textbox3 Solution 1:
This is a bit of vba a friend wrote for me quickly, it includes all three textboxes but the checkbox enables them instead of disables.
solution 2:
Code: Private Sub UseDDelivery_AfterUpdate() If AltDeliveryAddress.Enabled = True Then AltDeliveryAddress.Enabled = False Else AltDeliveryAddress.Enabled = True End If End Sub
With this bit of vba I found the checkbox enables the textbox instead of disabling it and I can't figure out how to include the other two textboxes
I have a form that has three checkboxes (IDDocument, DrivingLicence and Certification) on it. What the form is looking to do is based on the selection it will open a scanned document of the requested documents. There are 5 permutations that could be selected (either first on only, second one only, third one only, first and second and first and third). How do I code the IF Statement this so that the system will look at the different options and then do certain things accordingly.The code I have at the moment is:
Public Sub ProcessRequest_Click() Dim Form1 As String Dim Form2 As String Path = "Staff" & Me!EmployeeName & "" If Me!IDDocument = True And Me!DrivingLicence = False And Me!Certification = False Then
Do one thing
ElseIf Me!IDDocument = False And Me!DrivingLicence = True And Me!Certification = False Then
Do a second thing
ElseIf Me!IDDocument = False And Me!DrivingLicence = False And Me!Certification = True Then
Do a thrid thing
ElseIf Me!IDDocument = True And Me!DrivingLicence = True And Me!Certification = False Then
Do a forth thing
ElseIf Me!IDDocument = True And Me!DrivingLicence = False And Me!Certification = True Then
Do a final thing
End If End Sub
All the bits work (the form stuff) but my IF statements are not working. It seems that access is seeing the checkboxes as null and so the IF is not working when i check box 1 and leave the other two unchecked.
The way I'm using the lending library is to check out something that may have multiple of the same item. Rather than making the same exact asset information, is there a way I can check-out the same asset to several people? I think this would cause issues with the Check-in/Check-out query but I'd rather have a function where I could check-out one ID to several people and change the check in where I would have to pick one of the people who have it checked out.
I have a form named Member, this form has field named hobbies, this field has multiple values selection i.e. a user could select football, cricket, listening to music and etc. these values are stored in another table and i have looked up the values in my form named member, i know access has multiple value option in combo box while we look up values from another table, but i don't want that the check boxes appear in combo box, they should be listed right in form so that i can see them without going inside the combo box and at the same time the values of these populated check boxes should be stored in a single field.
I have a table of data regarding companies (contact info, etc). The company I work for provides these companies with up to 10 different products. On my input form, I have created 10 check boxes (and thus, 10 columns of Yes/No data in the corresponding table).
Each company has at least 1 product checked off, and up to all 10.
I would like to create a combo box that lists all 10 products, and upon selecting one, a list box then populates with the names of the companies (primary key) that use that specific product.
So, for example,
Company A buys CATS and DOGS from us Company B buys DOGS Company C buys CATS and ELEPHANTS Combo box options: CATS, DOGS, ELEPHANTS
If I choose DOGS, then a list box gets populated with:
Company A Company B
and when I click either of these, the record should be brought up for the respective company.
All of this should take place in the header of the form, while the form itself can be updated based on search selections.
I have run into an issue with a basic DLookup. The database has grown in size and now we could have multiple entries, but I want it to return a certain one. So the information could be in it three times. Of course DLookup stops after the first one. How do I get it to loop to check the entire table? Someone mentioned to me to use a recordset, but how to write that as I have never used it before. Below is what I was using until this new request came up.
<code> Private Sub txtloan1_AfterUpdate() If IsNull(DLookup("[loan1]", _ "settlement", _ "[loan1]=""" & Me.txtloan1.Text & """ AND [status] = 'Open'")) = False Then Cancel = True MsgBox "Test", vbOKOnly, "Warning" End If End Sub </code>
This was also executing after the user entered the information within a text field. I did not want them to enter all the data and then have it come back as a duplicate.
