Hi guys,
I’m creating a report on which I want to group data base on a field that has two different data one is KEY and OTHER. Now I need to be able to show on the detail section all records base on KEY, and group the rest of the records by OTHER.
Can anyone help me please?
Million of thanks
I have a form that shows a list of all of my records in my database. I want to be able to click a button called "Report" and have that print a report that has all the records I have filtered on my form. I have a report in the format that i want it in, however, currently it prints every record and not just what is shown on my form. (The form is dynamic and I want the Report to be dynamically based on the form) HELP PLEASE!
View 6 Replies View RelatedI'm trying to group data in a report from single table using grouping and sorting and I want the percentile of every record over group total. I'm using a query to fetch data from table, however I'm unable to get percentage of every single record over group total.
I want to display the report as attached image in single report. I'm unable to get data in "Perc" field. It's populating wrong values.
Is there a way to have an expression in the control source of a text box in a report, that re-starts or is exclusive for every group within the report?
View 5 Replies View RelatedHello to all,
I created a dynamic report based on cross tab query, the header is a sub report.
Number of lines and rows depend on the data typed in a form.
Here is my problem. When numbers of rows exceeds for exemple 15 the header and detail line continue on the line below i would like the report continue on another page (see below).
Actualy i have this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
16 17 18 19 20
line1xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxx
line2xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxx
etc..
I would like this:
page 1:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
line1xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
line2xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
etc...
Page 2:
16 17 18 19 20
line1xxxxxxxxx
line2xxxxxxxxx
etc...
THANKS IN ADVANCE FOR HELP.
VINCENT
I am using the following cde to generate a Dynamic Crosstab Report. I can get the column labels to work but Ican't get the values to appear in the detail section all I get is #Name?
I am using the following code in the report
Private Sub Report_Open(Cancel As Integer)
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("select * from test2")
rst.MoveFirst
j = 0
i = 0
For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).NAME Like "*test" Then GoTo skip_it
j = j + 1
Select Case j
Case 0
Me.Field0.ControlSource = rst.Fields(i).NAME
Case 1
Me.Field1.ControlSource = rst.Fields(i).NAME
Case 2
Me.Field2.ControlSource = rst.Fields(i).NAME
Case 3
Me.Field3.ControlSource = rst.Fields(i).NAME
Case 4
Me.Field4.ControlSource = rst.Fields(i).NAME
Case 5
Me.Field5.ControlSource = rst.Fields(i).NAME
Case 6
Me.Field6.ControlSource = rst.Fields(i).NAME
Case 7
Me.Field7.ControlSource = rst.Fields(i).NAME
Case 8
Me.Field8.ControlSource = rst.Fields(i).NAME
Case 9
Me.Field9.ControlSource = rst.Fields(i).NAME
End Select
skip_it:
Next i
rst.Close
Set rst = Nothing
End Sub
Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("select * from test2")
rst.MoveFirst
j = 0
i = 0
For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).NAME Like "*test" Then GoTo skip_it
j = j + 1
Select Case j
Case 0
Me.Label0.Caption = rst.Fields(1).NAME
Case 1
Me.Label1.Caption = rst.Fields(i).NAME
Case 2
Me.Label2.Caption = rst.Fields(i).NAME
Case 3
Me.Label3.Caption = rst.Fields(i).NAME
Case 4
Me.Label4.Caption = rst.Fields(i).NAME
Case 5
Me.Label5.Caption = rst.Fields(i).NAME
Case 6
Me.Label6.Caption = rst.Fields(i).NAME
Case 7
Me.Label7.Caption = rst.Fields(i).NAME
Case 8
Me.Label8.Caption = rst.Fields(i).NAME
Case 9
Me.Label9.Caption = rst.Fields(i).NAME
End Select
skip_it:
Next
rst.Clone
Set rst = Nothing
End Sub
What else do I need to do to get data in the detail section
In anticipation
errolf
I have a crosstab Query as the source for my report, of course the issue is the column headings on the report. I have Purchasers as a row heading, Year as a row heading, Month/Year as a row heading, Meter as a column heading, PaidMCF as Value and, a total as a Row heading. My issue is feeding the column headings on my report with the meter names.
Purchaser 1 has 23 meters attached
Purchaser 2 has 1 meter attached
Purchaser 3 has 6 meters attached
Purchaser 4 has 2 meters attached
Purchaser 5 has 16 meters attached
Purchaser 6 has 11 meters attached.
I created a dynamic crosstab report with 4 unbound fields in the details section and 4 unbound fields in the header section, which all work perfectly well. The crosstab query contains 17 columns. The last 4 columns contain the values I need to take the sum of. I have put some code in the open event procedure of the report.
Private Sub Report_Open(Cancel As Integer)
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from Que_ProjectUren_Sel_Dept_Test")
[code]...
I'm currently creating an invoicing report for a company, and for record purposes they need a dynamic field on the report for the specific Invoice Number of each client they invoice. I have the field set up currently as a text box =[Invoice#] so they have to enter an invoice number when they generate the report. However, when I generate the report and put in a number, I'm getting some whacky outputs.
For example, I've tested it with a test client, and when I give the client an invoice number of 1, the report somehow changes it to 49.
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?
Is it possible to generate a report from 2 listboxes?
I have attached a pic of what I am trying to accomplish. I am wanting to have a report open with only the criteria that a user selects from the listboxes.
listbox 1: User selects a case number(s)
listbox 2: User selects the fields they would like in the report
So, if the user selects:
Case number: 13-001
Then selects fields:
Allegation
Incident date
Focus_Last name
This report would be:
Code:
Case Number Allegation Incident Date Focus_Last Name
13-001 DUI 06/01/2013 Doe
I have a master recordset containing sales data for numerous products. I'd like to create a dynamic "dashboard" (i.e. a single report or form that the user can interact with dynamically) where the user can click a product name from a ListBox and see a summary report generate in the space below. If they click a different product name from the ListBox, I'd like the report space to automatically refresh and show the next summary report.
I've created my desired report and dragged it into the form but I'm having trouble embedding it dynamically into the form: I tried using the "Link Child Fields"/"Link Master Fields" properties, but get an error message saying I can't build a link between unbound forms. Here's a screenshot what I'm trying to accomplish...
I've made a crosstab query and would like to use it to create a subreport. In the column headers I have names of courses. Courses can be added or removed. How can I make a crosstab report with dynamic columns?
PHP Code:
TRANSFORMÂ Count(tblCourses.CourseName)Â ASÂ CountOfCourseName
SELECT tblNmscStaff.NmscStaffFirstName, tblNmscStaff.NmscStaffLastName,Â
tblNmscStaff.PtOrFtNtl, tblNmscStaff.Ntl, tblNmscStaff.NmscID
FROM tblNmscStaff LEFT JOIN (tblCourses RIGHT JOIN [tblNmscStaff/CoursesPointer] ONÂ
tblCourses.CourseIDÂ =Â [tblNmscStaff/CoursesPointer].CourseID)Â ONÂ
tblNmscStaff.NmscStaffIDÂ =Â [tblNmscStaff/CoursesPointer].NmscStaffID
GROUP BY tblNmscStaff.NmscStaffFirstName, tblNmscStaff.NmscStaffLastName,Â
tblNmscStaff.PtOrFtNtl, tblNmscStaff.Ntl, tblNmscStaff.NmscID
PIVOTÂ tblCourses.CourseName;Â
I have made a cross-tab query which works fine. I also have made a report which is based on this cross-tab query. Due to nature of the cross-tab query the Value column(s) is dynamic. The report I made is based on all available data types (columns). The report works if all data type is available but the report fails if some of the data types are not available (i.e columns are missing due to not having any value or data). I hope I am explaining this correctly,
Is there a way I can use expressions in the report to place a conditional clause that if the Control Source doesn't exist place a Null or 0 in the report or in its place.
I have created a database and have a form (searchpartsfrm) with multiple combo boxes and a text box that builds up all the entries and creates a perfect SQL statement and stores this in strisql variable.
I have a report (showallpartsrpt) embedded on the page that is currently showing every record in the database.
Is there anyway to have this form "refresh" and show only the results of the query by clicking a "search" button?
I believe I need to use the filter command somehow but im not sure how to reference the embedded form. If I try with some test code it cannot locate showallpartsrpt.
This code runs to the set frm part then i get a type mismatch? ive tried a few different things and still nothing?
Code:
Private Sub Report_Open(Cancel As Integer)
' Create underlying recordset for report using criteria entered in
' EmployeeSalesDialogBox form.
Dim intX As Integer
Dim qdf As QueryDef
Dim frm As Form
' Set database variable to current database.
Set dbsReport = CurrentDb
[code]....
I'm still learning Access 2010 and having issues getting my buttons to work. I'm working on a simple address database.edit/save button. On form load, my fields are locked and my button will read "edit". After clicking, my fields are unlocked, my search features are locked, and my button reads "save" just how I need it to. The issue happens when I try and save the field edits, lock fields, return search features, and get button to read "edit" again. I know I need to add some code into what I already have, but I'm running into a wall as I have tried many options to get it to work. Here is the code for this button:
Code:
Private Function Lockdown() 'locks controls at load
Dim tb As Control
Dim cb As Control
Dim subf As Control
[code]...
Second issue is with my report button. I have not been able to get this to work once. I have done many searches on single record reports, and have found the same code every time. I added that code into my database, but can't seem to get it to work. In my database there are two address (shipping and work location) which I would like to print out together. I have the work location on the main form and the shipping on a subform. There are and upwards of 150 locations I will have in my database, Here is the code I'm working with:
Code:
Private Sub cmdrptadd_Click()
Dim strReportName As String
Dim strCriteria As String
[code]...
Hello
I have a log which is updated by users in a spreadsheet. I have a front end which allows others members of the team to view the information being entered. What i want to know is if there is a way to link the spreadsheet to the database frontend which can automatically update the informationd daily.
I know you can do a SQL link but you have to pay for licences is there any way of doing this? We can not use a schudules job either??
I'm new to Access. I started building a database for work (I work at a nursery) and I used John's Dynamic Search code for an Inventory Input Database but I was wondering is it possible to use this code to input data into a table.
So basically I'm creating a database where I can input Item ID, Description(Plant name), Date, Location, Yard, and notes. For the Item ID and Description that is where I used the dynamic search code (as a combo box) so I can easily enter the Item ID and it will search for the Description. I got all this to work except that when I choose my selection it does not save on to the records. The other fields does save just not the dynamic search.
I want to be able to connect dynamic web data such as live gold price, exchange rates from the relevant web pages to a table in Access 2010. I have searched the forum without finding any related answers.
I understood that this can be done in Excel by using "Get External Data from Web" where the connected cells will be updated along with live data changes in the linked web page. I could set up a link table from Excel in Access but this would be tedious since the Excel file has to be opened to get the data refreshed everytime I open the Access file.
How to load dynamic data directly from web site to an Access table?
I'm a BA so i'm converting my excel issues list into a database with a front end where i can create new issue records this uses a form that sits on top of the issues_table.
Howver, i have created a table called Projects and a table called Test_Plans
Each issue is logged against a project and a test plan. Lets say ProjectA has TestPlan1 and ProjectB has Testplan2.
On my issues form, i can select ProjectA in the project field.
In the Testplan field i can select EITHER TestPlan1 or Testplan2
Testplan1 is the only valid entry.
How do i
a) Once I have selected ProjectA in the Project field only display Testplan1 in the Testplan field?
or
b) If the user tries to enter testplan2 for it to error?
I've tried creating a query and linking the controlsource field to it but it doesn't like that!
Right now I have a subform with a combobox that pulls it's data from a table. I want the user to either select an existing item or type in a new item and have a macro create the new table row. What I have right now works in the sense that it prompts the user if they want to creat a new item and the new item is created (and I can see it in the combobox list), but I'm still getting an error saying that the item does not exist in the table forcing the user to manually select the newly created list item they just typed in.
Code:
Private Sub MaterialCostCode_NotInList(NewData As String, Response As Integer)
Dim rst As DAO.Recordset
'Update value list with user input.
On Error GoTo ErrHandler
Dim bytUpdate As Byte
[Code] ....
It appears that the new item doesn't always show up automatically and requires the form to be refreshed, so now I need to figure out how to get it to consistently appear right away without a refresh.
I have a group of inspectors who are assigned a group of buildings. I want to show a count of how many each inspector has. The end result should look like this:
Jones FSL 0: 0 FSL 1: 0 FSL 2: 1 FSL 3: 2 FSL 4: 2 Total: 5
Smith FSL 0: 0 FSL 1: 1 FSL 2: 2 FSL 3: 2 FSL 4: 1 Total: 6
White FSL 0: 1 FSL 1: 3 FSl 2: 0 FSL 3: 0 FSl 4: 0 Total: 4
I'm looking for a way to summarize multiple fields of data from the same column in a single field. I've seen this page but have been unable to get it to work. I save the code listed but do not know which of the example fields would relate to my table.
Code:
SELECT CompanyName, ConcatRelated("OrderDate", "tblOrders", "CompanyID = " & [CompanyID])
FROM tblCompany;
I saw that I can create a report and select "group by," but then all the columns appear on different lines rather than summarized on one line. Here is an example of how I would like the data to appear, separated by commas or with specific dates within parentheses. The reason I want them all on one line is to provide an easier to read/more succinct summary.
e.g.
ITEM
LOCATION
DATE
GORP
EAST
1/1/2012
GORP
EAST
2/1/2012
[code]...
I have a database, in this database I have a "group and sort" set up on a report that I built from the database. The report is sorted by the "shape" field, then the "weight" field. The weight field is where I am having an issue. I have values in this field that range from .01 to 9. whatever..... nothing has ever gone over 9.*.
We now have a item with a 10.* value and when access sorts the report the "10" weight item is listed at the bottom of the 1's not after the 9's. I need to have access treat the 10 as a larger number than 9 not as the end of the 1's. So right now 10 is right before it goes to 2, I need it to be after 9. Is this possible ?
I need to write a report with the following groupings
Customer_GroupHeader
State_Groupheader
Client_Groupheader
Detail Section
Client_Groupfooter
State_Groupfooter
Customer_Groupfooter
What I have noticed, I maybe wrong, the record is a available for the "group_header" section closest to the detai section, in my case, "Client_GroupHeader". However, I need information in the record to format goup header information in the "Customer_Groupheader" and "State_Groupheader. Can anyone help me with this.
thank you
newbie and learning