Queries :: Building A Query For A Report - Combobox?
Oct 14, 2014
I have a combobox that I use to combine the employees first name and id number. So when you pull down the list you can select the employee you want and it is saved in the form. Now I need to create a query so that I can create a report on each employee separately. The query will not let me get the Employee Frist Name/ID from that saved field. I thought that what is saves in that field, you can retrieve it in your queries or reports.
I currently have 2 tables in a database that I'm trying to build a query off of. The first table lists personnel & their position #s, and the second table has a column for "rated person", "rater", "senior rater" and "reviewer" which are all part of our annual evaluations. I'm trying to program the query to display the names of the person attached to a position #, but can only get the query to return the actual position #s.
I'm using the table to assign each position # appropriately, i.e. position # 10202 is rated by 10103 and senior rated by 10101; I do this with the lookup data type. Once each position # is assigned, I'd like the query to display the name of the personnel instead of the position #, i.e. when queried, it would return under the rater column JOHN SMITH instead of 10202, and in the senior rater column JOHN JONES instead of 10103, etc.
I am building a database with Access 2013. The information contains data built from a workplace violence report form. I have to build a query to pick the data but must fall between two different years.The data range must be from 09/01 previous year (ex. 2012) and 08/31 current year (ex. 2013).
As the database collects more information, the year range will change but the other information will stay the same (ex 09/01/2013 to 08/31/2014).I do not want to change the query annually, just let it change the year automatically.
I don't program in Access very often. I am using Access 2003, and have a client that wants me to create a Mail Merge application using queries against some access databases. Is this something that can be done in Access?
Also, they have some reports that they would like for me to build. Can I make the reports dynamic by setting up some sort of form where they can choose the specifics they would like in the report, and then having those variables determine what will be shown in the report? How involved would the creation of that be with say 15 - 20 variables to choose from?
I have a button that opens a report. The code behind the button builds the filter criteria for the report based on some selections in a list box.
Code: DoCmd.openReport "Report", acViewPreview, "", GetCriteria For VarItem = 0 To Me.List2.listcount - 1 strCriteria = strCriteria & "([ProjectNo]= '" & Me.List2.Column(1, VarItem) & "' And [ClientID] = " & Me.List2.Column(0, VarItem) & ") Or " Next VarItem If strCriteria <> "" Then strCriteria = Left(strCriteria, Len(strCriteria) - 4) Else strCriteria = "True" End If GetCriteria = strCriteria
This is what the filter would look like with values after running the report (taken from filter bar in report properties):
([ProjectNo]= '150002' And [ClientID] = 206) Or ([ProjectNo]= '150003' And [ClientID] = 79)
Problem is that i only get records for ([ProjectNo]= '150002' And [ClientID] = 206). I this seems only filter ONE set of criteria ignoring all the others. What am i doing wrong?
I want to build a database stored on a server which can be used by our employees as basis for their Excel pivot reporting
The idea is as follows:
I make a query that will get selected data (via odbc) from several Progress database tables of our ERP system and write them to one MS access table
The idea is to run each night at midnight a query (append or make table) via a schedule and gradually build a huge database table holding sales details
This table will be used as basis for the Excel pivot reporting .These reports are currently run directly on the ERP system's database which slows things down
Question:
1. Is it possible to automate the queries (perhaps via scheduler or 3rd party program ?
2. what would be better to use an append query that adds data or a maketable query which constantly replaces the data ?
3. How many ODBC connections on a MS Access database can I have at the same time ?
Record Primary Key: ID_Wells..The TxtFedStCo has Fed, St, Co, ... in one field - and a Dt_Apd_Sub (date submitted) .There can be zero or many dates submitted for each Fed or State.
Objective: If A Fed has (1 or many) date submitted AND A St has (1 or many) date submitted Condition is TRUE
How do I get to the next step? The reason for doing this in SQL is to prototype in Access, then move this over to TSQL later.
I am trying to use a combobox to select a value which then activates a query to return results.
Here is some details
I have to tables, one called "Customers" and the other called "Calls". These two tables are linked.
The customers is literally a list of customers with their contact details, but all I am in interested in is the "Company" Field.
The Calls table has a field called "End User" which looks up the company from the Customers Table.
What I am trying to do is create a search by Customer query, furthermore, I would like to do is to create a form with a drop down that looks up from Customers table, select the company and it returns all the records with that company...
What I have done
I have created a query that has the customer and calls tables included, I have dragged down the [Company] from customer table and then all the fields from the Calls table.
Then i created a blank form, inserted a combobox - Combo7, linked the box to the Customers table.
Back to the query, under the [Company] I have put into the criteria the following
Forms!sc!Combo7
Back to the form, selected the combo box, built a macro in the AfterUpdate, to run the query.
Tested this and it does not bring anything back, however if I put into the criteria Like [Please Enter Company Name], then typed the company name, it brings back all the records for that customer.
Am I missing something?, do I need to set the form control to the query, or even the combobox....
I have been spending all my today to fill a combobox dynamically, but have not been able yet.
I have a combobox and a pass-through query in access, which is working fine and fill the details into the combobox via data source. Now what I am planning to do is to update the combobox source as soon as value in a text box changes.
Here is the code I am using, but it is not working:
Dim rs As Recordset Dim qDef As QueryDef Set qDef = CurrentDb.QueryDefs("get_data") qDef.SQL = "SELECT Initial + ' (' + Name + ')' uws FROM EM.dbo.UW" _ & " WHERE lob = '" & addSingleQuotation(Me.CMB_LOB.Value) & "'"
Me.cmbUM.RowSource = qDef.SQL Me.cmbUM.Requery
I also used Recordset, but did not work:
Set rs = CurrentDb.OpenRecordset("get_data") Me.cmbUM.RowSource = rs!uws
I have at least 15000 records and all of them should be corrected if there is existing duplicates with same EMBG and different name. More precisely if there are 2 persons with same EBMG lets say 123456789 but one with name Naim Arifi and other one Naum Arifi then query should present to me Naim and Naum. Example
So the record 1 3 and 4 and 722 should be highlited because they have same EMBG and I need to find where is the mistake manualy (correcting from Naum to Naim). In this case row number 4 is mistake instead of Naim is Naum. I need to correct it manualy.
Ok, I currently have a query built with requests. What I want to do is initially set each request with a priority. Then when one request is closed the query will take the requests with priority 2 and change it to priority 1, change the request with priority 3 to priority 2 and so on. Also any new requests that are entered I want to be able to set their priority as well. Can anyone help me with this, or is it even possible?
I'm populating an unbound ComboBox with a Query via the QueryDesigner. The value of the Rowsource depends on the value of another ComboBox at the main Form.
After several hours of trial and error, I came to the solution showed at the attached image ("works"); my concern is.. why does my first approach does not work via the IIF clause? ("not work")...
OK, here's the deal. I have two data tables, one of which holds many types of customer information and has a very large number of records, including, name, address, phone, and email. The other table contains only email addresses. Each email in the second, smaller table corresponds to a record in the larger list , by virtue of both records sharing the same email address (primary key?). I need to be able to take a short list of email addresses from one table and produce all of the records from the larger table that have corresponding email addys, essentially. Can somebody shed some light on how I can structure a query to solve this dilemma? Sorry for the newb question, just getting started out using db's.. Thanks!
Hi, I am trying to make a query from fields out of 3 tables. All tables must include following fields:
Table 1 fields: WR04 (year 2004) Date Reporting Person
Table 2 fields: WR05 Date Reporting Person
Table 3 fields: WR06 Date Reporting Person
I am trying to pull together in the query any given individual (Reporting Person) who may be included in all above tables but believe I may have a relationship problem as I am only getting results that match all tables. I hope this is clear. Any suggestions please.
I am really at the end of my teather with this problem so i really hope someone here can find a solution.
I have 2 tables; Client (Client general info, defined by their location), ClientHardware (Info on the hardware a client has and also it's condition).
I need to be able to select one or more clients and display one or more conditions of their hardware, e.g. London, York, Bury + Red, Amber, Green condition.
After the Query is working right i will need to output it to a report through a button on the form.
I attempted adapting This Method (http://www.databasedev.co.uk/query_using_listbox.html) but adding another list and query just resulted in the report showing the all the records of the selected client (e.g. london) then all the records with the selected condition (e.g. bad) it would be ok if i could merge the list box selections into one query but right now it looks like this
MyDB.QueryDefs.Delete "qry ClientName" Set qdef = MyDB.CreateQueryDef("qry ClientName", strSQL)
MyDB.QueryDefs.Delete "qry RAGType" Set qdef = MyDB.CreateQueryDef("qry ClientName", strSQL2)
Is it possible to put the variables in strSQL2 into the creation of qry ClientName somehow?
Any help, large or small will be appreciated as I'm really at a dead end with this.
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)?
Newbie working in Access 2003. Please forgive me if this seems vague. I have a form that has a combo box in which a record is selected from a table. I have a report, whose record source includes this table. This table has records for two fields-Job Number and Distribution Date. My problem is that I would like the record that I selected from the combo box to be carried to the report. I can get the report to display the last record in this table, but not the one that I select. To recap; I would like to 1) click on the combo box, 2) select a Job Number, 3) click on a command button called 'Preview The Report', 4) see that the correct Job Number and Distribution Date are carried to report, and 5) print the report. It all appears to work up until the preview, where again, the last record, and not the one I selected, is carried to the report. Thanks in advance for any help that any one is able to provide.
I currently have a report that is driven by a combobox. I was wondering if there's a simple way to create a report for each of the entries in the combobox through a "All" combobox entry. (i.e. instead of selecting each entry, one by one)
In a report using a query with a field named: "plantno" and as criteria" :Like [Fill in the plantno] I can put in a plantno.That is working Oke! I want to do the same thing as above but then that i want to see a combobox with a few plantno,s which are put in a valuelist,so that i dont have to type in plantno,s but that i only can choose out a valuelist.
I was wondering is this was possible in access. I've read material on grouping and sorting, but haven't been able to pin down what I need.
I have a combobox field that has 14 entries, beginning with 01 as the first two characters. What I'd like to do is change the order in which everything is displayed on the report.
For instance, show 07 at the top, then 09-13 below that, and so on...
I have a report, rptAllCSCS which is based on a query qryCSCS2...
One of the fields in qryCSCS2 is Status and each record is either "Current" or "Not Current"...
My report is being viewed via a navigation form, so one of the tabs says CSCS and when clicked the user can see the report...
I have added a button in the report header which when clicked opens the report in print preview so the user can print the report. (Done with a macro in the On Click of the button.)
I would like to add a combo box cmbStatus which has the values "Current" and "All" in the report header. (Current will show only when the Status field = Current and All shows all records so Current and Not Current together). This will act as a filter for the user to see the corresponding records and they can then press the print button or just view on screen.
I haven't worked with filters before except when you specify the criteria in the query and point it to a control on a form which then opens the report... As this report is already open I'm having trouble, as well as specifying the "path" when something is in a navigation form being a bit tricky...
I need to get a query back that may have more than the given number of records on either side, but need to not duplicate in either of the two columns. Works better to show what I need, I think...
Query needs to return: PersID.....Name.....Assist.....Device
Base data looks like this (after using query with Person and AssistType and Device tables):
What I've been asked for is something that looks like this:
1.....Adams.....Dress....Walker 1.....Adams.....Bath.....Lift Chair 1.....Adams.....-----....Wheelchair.......... ===> Null or blank in the empty column is OK; I can work with those 2.....Jones.....Bath......-----............................................. 3.....Case.....Dress......Wheelchair 3.....Case.....Bath......----- 3.....Case.....Feed......----- 4.....Doe......-----.....Walker 4.....Doe......-----.....Cane
I can print it with two subreports (one for each column), but that is running slower than I would like. It's workable if we absolutely have to do it that way, but I'd rather see if I can get it out in one query and use the grouping functions in the report (there are some other fields involved, but they don't really affect what I am doing here)
Is there any way to do a query (even a couple of queries if it takes that to set it up) that will give me what I am looking for?
