Queries :: Report For Multiple Selections Of Account Number And Work Organization
Aug 3, 2013
I have one table named Entry. This table have account number,work organization, price, date and etc.
I use a combo boxes in form which is connected to query and I can easily choose one account number,one work organization and date to see an report. There is also a possibility to left some of the fields blank, it will bring report if is null.
Now I need to make a form that have a possibility to bring up a report for multiple selections of account number and work organization?
Is it possible (without using VB) to limit the number of selections a user can make in a multiple value list?
My list is one a form and when in the form field it brings up a total of 15 names with checkboxes, allowing you to "check" each name you want. However, I want to be able to have as many names as I need in the list, but limit the user to selecting only two of them.
Table 1: Account Number Start Date End Date Cost data** Budget data**
Table 2 (Imported excel file with cost/budget data): Account Number Cost data** Budget data**
Table 1 is the main table that will be viewable in this database. The idea here is that new Account Numbers can be added to Table 1 throughout the year. It then pulls the cost/budget data into Table 1 based on the matching Account Number between table 1 and 2.
So, if the Account Number (Table 1) = Account Number (Table 2) then it pulls the cost/budget data into the cells on that row. I am trying to make this automated since this data is updated weekly and imported into Table 2 from excel.
I have a table where one of the fields is a list box, where it is possible to select more than one option. The options correspond to records in another table. I have a query that includes this list box field. The problem is that if someone has made two selections in the list box, the query creates two records, each featuring just one of selections from the list box. Can I get this query to hold the two selections in one record? This might be especially challenging because the query field is pulling attachments.
Long Version:
I have a table [Master List] (primary key is TagNumber), where one of the fields is a list box, [Standard Methods]. You can select multiple choices in this list box, which corresponds to a table also called [Standard Methods]. In the Standard Methods table, there are two fields for naming the method and attaching the PDF which describes the method.
I have a query called [Get Attachments] that is set to bring in different attachment fields from different tables, including [Standard Methods]. The problem is that the query creates a different record for each selection made in the list box. For instance, if you select two standard methods for a certain tag number in the list box, this query will have two records for that tag number, each with one of the method attachments. Is there a way so that the query just creates one record, with both attachments in that record???
It should also be noted that I am using Outer Joins, so that the query includes TagNumber records from the Master List even if there are no attachments for it.
Is it possible to do a criteria like the "Between" to pull data like a work order number?
Ex WO#: WO5551212
I would like to be prompted to enter a Work Order Number when I run a query, then have the query display all of the data for that particular work order number.
I am trying to make an Update Query that will update a table that has the same account numbers and assign them a value....ie.1,2,3,4 and so on. Does anyone know how I can do that?
Example: Accout Num Assigned Value 12345 1 12345 1 12345 1 12544 2 12544 2 12568 3 12569 4
I have a list of account numbers for members, they have distinct member numbers. I have it set up for users to search for the member, then the member number will auto-populate. I also need to account for members with no member number. I would like to have the user click on the check box "Non-Member" and somehow assign a Member number...starting with NM. So the first non-member would be NM00001, next NM00002. I thought I could create a table with the sequence of NM numbers. I am just unsure of how to tell my form to populate that field when the Nonmember box is checked.
During a promotion we are running the customers are only allowed 3 entries into the system per day but the users are occasionally putting some customers in more than that. I could use some kind of function that limits the number of times the account number can be put in on a given date. (Or daily)
I have the following text boxes: ID, Customer first name, customer last name, account number, date, time.
During a promotion we are running the customers are only allowed 3 entries into the system per day but the users are occasionally putting some customers in more than that. I could use some kind of function that limits the number of times the account number can be put in on a given date. (Or daily)
I have the following text boxes: ID, Customer first name, customer last name, account number, date, time.
I have a table called tblAccounts and a form called frmEnter_new_accounts. In my frmEnter_New_Accounts form is a Text Box for Account #. I want to have it so that when the user enters an account # it checks the tblAccounts table for a duplicate account # and then displays a message box (or Pop-Up form) to tell the user that the Account Number entered already exists. I would also like that box to offer the user the option to either Close that form without saving or return to the form to reenter a different account #.
I know I would probably do this in the LostFocus event or BeforeUpdate.
Does anyone have any ideas or samle code to assist me in this adventure?
On the opening of a form, I'd like to have an input box appear that asks the user for the account number but I'm stuck on the code to make this happen.
I have developed a database which has required many checkbox fields to enable analysis. It requires to have the facility to input random/variable date ranges for statistical purposes.
I have built a query which obtains the counts of multiple fields using the following parameters in Query Builder in Access 2010. Although this comes up with the correct results for these multiple fields when I try introduce date range the results come up blank for all results.
An example of the parameters used for one of the checkbox fields in Query Builder is as follows:
Field: SumAnger: Sum([Anger]*-1) Table: Default as only one table Total: Expression Show: Checked
This works fine.
My latest parameters for the date range are this:
Field: [cDate] Table: Default as only one table Total: WHERE Corrected! Whoops Copy & Paste Typo. Too early AM! Show: Checked or Unchecked makes no difference Criteria: Between [From Date:] And [To Date:]
This gives a statement in SQL view of:
SELECT Sum([Anger]*-1) AS SumAnger, Sum([Anxiety]*-1) AS SumAnxiety, Sum([Depression]*-1) AS SumDepression, Sum([Listening]*-1) AS SumListenig, Sum([Psychosis]*-1) AS SumPsychosis, Sum([Stress]*-1) AS SumStress, Sum([Other]*-1) AS SumOther, tblCommsLog.[cDate] FROM tblCommsLog WHERE (((tblCommsLog.[cDate]) Between [From Date:] And [To Date:]));
what I need to get this to work in Query Builder or failing that recommend some VBA script/code with embedded SQL to achieve the required report.
I hope I can form this question so it makes sense. I’ve been trying to figure out a way to do this, if it can be done at all.
Ok, imagine I’m making a database of widgets. In this database I’m creating a table of widget name, cost, weight, and color. This part is pretty simple. Now, my widgets can come in many different colors. To accommodate this, I create a separate table for colors, let’s say red, green, blue, yellow and purple. I create a color item in the first widget table, and use the look up wizard to point it to the color table. This way on my forms I can select the color widget I want and have it all standardized.
This is where my problem comes in. My widgets can be multiple colors. How can I create a field in my widget table, to somehow reference that I can have a red and blue widget? So, when I query all my red widgets, I get the red ones and the ones with red in them?
Now, I could make an item in my widget table for each color and make it a yes/no field. But, my real problem is exponentially bigger than this so that isn’t what I want to do.
How do I allow the user to select more than one answer in a combo box? It is a drop down menu with several choices. I'd like the user to be able to select more than one if desired and all choices would display when queried. Thank you much. :)
Howdy all, Ive got combo boxes, in a cascading style working well... but what id like to also have is a another dropdown list that shows the last 5 selected options (because often the data will be entered in batches and it should just save time)there is combo1 combo2 and combo3 If Sport is selected in combo1 then it displays combo2 with all the sports in it, so if golf is selected then the golf options come up in combo3 for example tiger, So id like to store Sport > Golf > Tiger Ive already got the value for Tiger being selected, so I can assign that easily enoughWhat id like to be able to do is store the name and its value in a combo box maybe showing the last 5 selections. Im just not sure what the best way to go about this would be, Should I make a table with say 5 rows? or is there a better way to go about it?Any thoughts or advice would be awesome,Cheers Ezy
I have a continuous form which selects records to view based on the stock number and the dates, beginning and end, entered by the user. This is based on query that gets data from several related tables. This is what is in the Row Source property of the combo box has in it: SELECT [qrywhobotit].[PROD_CODE] FROM qrywhobotit; don't know if that makes any difference to my problems.
The stock number is selected from an unbound combo box. The dates are selected from 2 unbound text box controls, one for start date & one for end date. I used the after-update event of the combo box and it filters the stock number. I also used the after-update event in the end date text control to filter the dates (from and to).
Question: How do I write the events so it selects the stock number for just the date range entered. I can get the stock number to get the correct records, but the dates are not selected, it shows all records for that stock number not just those matching the date range entered. If I comment out the stock number code, then records selected match the date range entered, I only see the dates I entered but it shows all the stock numbers, not just the one stock number entered. I can get the stock number to work OR the dates but not both at the same time. I think I need some sort of "and" condition, but not sure where/how to put it.
Here is the code I used.
Private Sub cbofindrecwhobotit_AfterUpdate() ' Find the record(s) that matches the control. ' DoCmd.ApplyFilter , "Prod_Code = '" & DblApp(Me.cbofindrecwhobotit.Value) & "'" ' Me.FilterOn = True End Sub
The above commands are commented out, I tried moving it all to the after update event of the end date text box control - didn't work (see following code). It works fine if I uncomment it, but then the dates don't get filtered at all.
Private Sub txtwhobotenddat_AfterUpdate() DoCmd.ApplyFilter , "Prod_Code = '" & DblApp(Me.cbofindrecwhobotit.Value) & "'" If txtwhobotstartdat.Value = "" Then Me.Filter = "FULFILL_DT <= date()" Else Me.Filter = "FULFILL_DT between " & _ "(#" & Me.txtwhobotstartdat.Value & "#) " & _ "AND (#" & Me.txtwhobotenddat.Value & "#)" End If Me.FilterOn = True End Sub
2nd Question: There are lots of records for any one stock number. When I use the combo box to select the stock number, I see the same stock number repeated again and again in the list displayed in the combo box , instead of just one of each. I tried turning both the Unique Values and Unique Records properties to YES on in the properties of the query, but that didn't fix it. How do I fix the combo box to display just a list of unique stock numbers?
I have a listbox with about 8 different choices. i want to be able to query what is selected in this list box but I need to be able to select more than one choice so that my employees don't have to create multiple forms for the same item. Is there any way to select more than one option? It would help greatly even if I have to add code in VB I will figure out how to do it, thank you.
I have a list of codes that I would like to select among 50,000 codes in MS Access Database:
I can easily find one code but I don't know how to select multiple values:
My Query is:
SELECT [Codes for District].DistrictID, Univ2012.Schoolid FROM Univ2012 INNER JOIN [Codes for District] ON Univ2012.DistrictID = [Codes for District].DistrictID WHERE IN [((Univ2012.Schoolid)= 415030301, 415030303, 415030306, 415030309)] GROUP BY [Codes for District].DistrictID, Univ2012.Schoolid HAVING ((([Codes for District].DistrictID)=15));
When I run this query it gave me Syntax Error in Where clause...
I have a three-column query that tells me how many hours I have available per week for a given resource type (e.g. welders). I have a second three-column query that tells me how many hours of work I have planned per week for a given resource type.I'm hoping to produce a query (the source for a report) that will show resource types in rows and twelve months in 24 columns. the first column for each month will show how many hours I have available for all my resources, the second column for each month will show how many hours I have allocated.
How do I produce a query that will combine the other two queries, inserting zeroes where necessary considering that for any given week I might have allocated work to a resource that isn't available (because the inconsiderate buggers think they are entitled to holiday) or I might have a resource that has no work allocated (because I'm incompetent)?
This must be pretty common but I cann't figure it out.
There is a listbox that multiple selections are allowed. This listbox is bounded with a table field. All selections must be stored. I don't mind if each selection is stored as a different record.
I keep thinking this should be pretty simple, but more than 2 hours searching through posts has revealed no answers, probably because the answer is too simple.
OK, I have an append query. I've created a prompt to enter the number of the record the user wishes to append, as it is usually one single record. However, I've recently run into a problem that now there is the possibility of needing to specify more than one record at the same time to be appended.
How do I change the criteria syntax to allow the user to enter two, three, or a dozen (if need be) different record numbers? They are not listed sequentially, so I can't use a "between" statement. I've tried separating the numbers by commas, quotes, and so forth, but can't get it to recognise the records.
I made a listbox in which get's it's source from a field in a table named Soft. The field is named Software1.
This field contains a list off software that could be installed on systems. I allready can select multiple selections from the listbox but what I want to do is the following.
I want to press a button, and after I press the button I want that the selected Software is added to a new listbox which show the Software installed. The software in the second listbox would also be stored inside a Table so that I could print them out in Reports.
To simply explain what I'm trying to do:
SoftwareListbox >> Make multple selections>>Press button>> Adds selected software to InstalledListbox which stores in the softwarelist installed in a Table.
I really hope you guys understand what I'm trying to do. And sorry for my bad english, should have paid better attention during English class ;)
I have a simple order entry system working with a subform containing combo box where a client selects various products from a table. These are recorded in separate table which allows for subsequent updating of the orders placed.
Now I want to refine this so that the user may make multiple selections as it is a bit laborious making 'n' single selections via a combo based subform.
Only a list box allows multiple selections but I would like some guidance on how to store and be able to recall and update the selected items.
I have an option group on a form, that has 4 possible options. I need to allow the user to select any combination of the 4 options, to be stored in fields in another table.
By default, the option group only allows one to be selected (hence the name "option"? ;) Maybe I should proceed a different way?
I had a bigger question earlier and it was answered but I am still having problems with a small part of it. I am trying to track the details of a meeting. who requested it, for what project, date, duration, who attended. I have everything figured out except accounting for who attended the meetings. attendees also called "analysts" have a manay to many relationship with the meetings. Because they can be a part of many meetings and a meeting can have many analysts. So I created a seperate table called MR-Analyst which was supposed to help me track which meetings each analyst attended. I don't know how the data entery form should look like, right now I have everything on it and linked correctly to track everything excep the analysts. how can I create a list box or something that I can select multiple analysts for each meeting and have that recored linked to the MR-Analyst table? Please Help!!!
I have a form called frmNotInvoicedSearch and on that form i have an list box called listCompanyClient populated with our client's names.I then have a command button called cmdOK that brings up a search results form called frmNotInvoicedSearchResults.
Currently i can select one of the records and when i click ok it brings up the search results for that selected record.Is there a way that i can hold in the ctrl and select multiple options and the search results report as such?
