Queries :: Pull Data Values In Array To Become Grouping Headers In A Report
Apr 23, 2014
I have a table with application records. One of the fields captures schools the applicant will work at. This field stores data in comma delimited format. There could be 1 school name; there could be 5 school names.
My ultimate goal is to build a report which shows me records of all applications, grouped by school choice. I want to see: School A was selected by 5 people, School B was selected by 7, etc.
Is it possible to write a query from this table that will enable this?
I have a query that pull data from hours worked fields that have values with decimal places. Hours is multiplied to rates to create my amount to charge in my query which is then displayed on my invoice report.
The issue is that when I sum my amount, my subtotal is off by $.01 due to the decimals entered for the hours worked (e.g. hours worked is 1.5*32.75=49.125)
I need the rounding to work in the query and display on my report as $49.13 and the subtotal to reflect it. I have the properties in my table as double with decimal place at 2, and my query and report properties with decimal place at 2 as well. It still isn't working.
I've done some basic work with arrays.. writing array data to form list objects.. How to use an array as a data source for a report?
Would i need to create a recordset and populate it with the array, then bind the report to it ?
The reason I am asking is the previous developer here built every app using arrays and UDT's... the apps are completely disconnected from the data. Everything is loaded in to arrays..
I posted the following code on a button (report to a first sergeant). I can't pull e-mail addresses from the report (rptLateByUnit).
=========
DoCmd.SendObject acSendReport, "rptLateByUnit", acFormatPDF, Me.CCF_EMail, Me.CCS_Email, , "FOUO: Open Personnel Data Discrepancies", "This document contains information which must be protected in accordance with AFI 33-332, Air Force Privacy Act Program, and DoD Regulation 5400.7R, DoD Freedom of Information Act Program; and Privacy Act of 1974 as amended applies. This document is For Official Use Only." & vbCr & vbCr & _
I have 2 reports that use a separate query to pull their data. One Query is called "Outdoor Lighting by Model Number Full Query".It is very simple, pulling just 4 pieces of data, SQL code is as follows:
SELECT DISTINCT [Call Log].[Model #], Sum([Call Log].Quantity) AS [Sum Of Quantity] FROM [Call Log] WHERE ((([Call Log].Date) Between [Forms]![Date Range Entry]![Start Date] And [Forms]![Date Range Entry]![End Date]) AND (([Call Log].[Product Type])=4)) GROUP BY [Call Log].[Model #] ORDER BY Sum([Call Log].Quantity) DESC;
I run a report named "Outdoor Lighting by Model Full Report" which takes the above query. The detail portion of the report only has 3 sections:
Model #, Quantity, Percentage. Quantity I have showing "Sum Of Quantity" and Percentage is showing "=[Sum of Quantity]/Sum([Sum of Quantity])" Therefore, for a particular model # it gives me the number and then calculates what percentage that number is of the total. Works fine.
My second report is laid out exactly the same, except it pulls data from a different query that only returns the top five results. The code for that query is as follows:
SELECT TOP 5 [Call Log].[Model #], Sum([Call Log].Quantity) AS [Sum Of Quantity] FROM [Call Log] WHERE ((([Call Log].Date) Between Forms![Date Range Entry]![Start Date] And Forms![Date Range Entry]![End Date]) And (([Call Log].[Product Type])=4)) GROUP BY [Call Log].[Model #] ORDER BY Sum([Call Log].Quantity) DESC;
The second report, appropriately named "Outdoor Lighting by Model Number Top 5 Report" only shows the top 5 and then gives a quantity and calculates a percentage. My problem is that the percentage is calculated based off of the total of just the top 5 query. I need it to calculate based off of the total of the full query.
I currently have percentage doing this "=[Sum of Quantity(Top 5 Query)]/Sum([Sum of Quantity(Top 5 Query)])".What I want it to do is this "=[Sum of Quantity(Top 5 Query]/Sum([Sum of Quantity(Full Report Query)])
I'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.
I am trying to create a query that will group my data by each week so I can sum up some numbers and display them on a per-week basis... Prefer the week to start on Sunday but really not picky about it.
Using the following in the query design window.
Week of Year: Format([Date],"ww")
This is returning: 1 10 11 12 2 3 4 and so on.. See the problem?
I tried sorting but that doesn't work.
I also tried the following:
Format([Date],"mmmm,ww")
But this is returning December,51 December,52 February,8 February,9 January,1 January,2 January,3
Yes, there are some weeks without data but that's not the problem. The issue is that its putting February before January.. Why is this? Again, I tried sorting options and several other techniques but no success.
I have a query that pulls data from the following fields in 2 different tables:
Area1FloorPrep (tblFloorPrep) ex. remove ceramic tile Area1Size (tblInstallationAreas) ex. 20 s/f, or just 20 Area2FloorPrep (tblFloorPrep) Area2Size (tblInstallationAreas)....
All the way to Area20 (Floorprep and InstallationAreas) for both tables. I have created an installer invoicing form that pulls the data from the workorder that these fields are located in, but the problem I'm having is that I don't know what kind of query to create to concatenate the data in the 20 fields and concatenate the size of the areas next to the appropriate concatenated floor prep description. Is there a way to do this without coding?
I have 9 seperate tables - each of the tables has similar headers
Claim # Agent Pass/Fail Request type Record Date
I have built relationships between all the tables. I am attempting have a singular query be run based on start and end dates under "record date" . I have the criteria already set. But when I run the Query no information is pulled. How do I get the query to pull this data from all 9 tables?
I have a text file linked to this database, and I'm trying to create a Query that will pull all of the data out of this text file, and add a rank if two of the values match in multipe records.So in other words, when multiple records have the same [JOBNBR] , I want to evaluate the [TIMESTAMP] value to see if it is the smallest one, and so on.I don't want to create multiple queries to do this, is there any way to do this? Here's what I have:
Code: DDR: (Select count(*) from tblMyDataImport Where [tblMyDataImport].[TIMESTAMP] < [TIMESTAMP] AND [tblMyDataImport].[JOBNBR]=[JOBNBR] )
I am trying to create an expression to pull data from 2011 to present date. I need the data for 2011 to only reflect 1/1/11 through 10/7/11 (today's date in 2011). I need the same for 2012 and 2013.
I don't want to have to enter dates each time I run this, therefore, a formula would be preferred rather than hard numbers.
I'm populating a combo box from a query. I'm running a DCount on 2 criteria. The user selects the criteria from 2 combo boxes. If the user types * into the combo box, I want to be able to loop through each combination from the combo box values. I need to get the list of values from the combo box and put it into a string array, however it throws a type mismatch if I did something like BRANCHES(x)=CStr(cboBranches(x)) 'cboBranches(x)=cboBranches.Value(x) So I tried to run the sql from vb but found out that I can't return a string value from that (vb sucks). So I'm back to trying to get the values from the combo box. Any help would be appreciated.
Normally I would gather these up into an array using ASP and email the split up comma separated values out to the client via an email.
Code:drinks=Request("drinks")drinks_split=split(drinks,",")'email drinks to client
However, this time I need to store those values in an access database. I am assuming that the client may want to run queries on the different array values, for example, how may people chose coca cola and sprite.
Is it better to store the value separately - one Ms access field for coca cola, one for sprite (which is what I thought I would have to do to create queries on this) - or can they be stored under one field heading "drinks" and queries be performed on different values within that field?
Could someone explain a quick way of doing the latter i possible in MS access and the SQL for querying?
How do I delete values in an array? I want to reuse that array but first I need to delete its values so I would start with Arr(0) rather than where I stopped off with the last loop at Arr(10) [assuming the last calculation inserted until Arr(9)]
Here is my code:
Set rst = CurrentDb.OpenRecordset( _ "Select * from dbo_ProductStructure where ChildProductNbr Like '*" & txtPartNumber & "*'") 'search associated fields with user input While rst.EOF = False ReDim Preserve Arr(i) Arr(i) = rst.Fields("ParentProductNbr") i = i + 1 rst.MoveNext
I'm trying to store all the OrderNumber + Item combinations in 2 arrays and then because the OrderNumber column really contains 2 values I'm interested in, I split it up and store that column's values in 2 arrays. So in total, I have 3 arrays. An array for Item, an array for Order, and an array for RepId (which is the one that I split up from the OrderNumber column).
Anyways, when I print the RepId array with the ' MsgBox PostValCol1(x) ' It prints 4 values like it's supposed to. But when I tested it again by looping through the values and just doing a MsgBox, It goes for a long time and that's because it has a lot of empty values in that array. How to get rid of those empty values/not store them in the first place?
Code:
Set rop = CurrentDb.OpenRecordset("Select OrderNumber, ItemNumber From dbo_EntryStructure Where (ProductNumber = '" & txtPartNumber & "') AND (ActionCode = 'I')")
While rop.EOF = False ReDim Preserve ArrRepOrder(j) ReDim Preserve ArrItem(j)
My report has a header that contains "Shipped-From" and "Ship-To" information. I only display this on the first page of my report by using some code in the Page Header section. The problem is that the field names of the detail section are also in the header. This means that from the second page on the field names are absent. What can I do to include the field names in all of the pages - but the "Shipped-From" and "Ship-To" information only on the first page?
arrParams = Split(arrLocations, ";") For i = 0 To UBound(arrParams) MsgBox (arrParams(i))
strSql1 = "SELECT branchName FROM ustax_customerBranchLocationsTBL WHERE branchName = '" & arrParams(i) & "' AND branchCustomerParentID = " & Me!customerID & "" 'MsgBox (strSql1) DoCmd.RunSQL strSql1 Set rs = db.OpenRecordset(strSql1) Dim count count = rs.RecordCount
Next
Above is my code, what i am trying to do is loop thru the array and check if the values are in a table. I am getting an error after the first loop of 'A RunSQL action requires an argument consisting of an SQL statement', why would I get this error if I am looping thru the array? Wouldn't the sql statement just be read with the new value each time?
I have to deal with string arrays that store text. I need info on copying, comparing, appending arrays. Also on passing arrays as parameters to subs or functions. Where I can get to this info quickly without having to browse through many screens.
In addition to this I have some questions:
I have to find the duplicates of values in an array. Here is the code that I use.
Code: Sub FindDuplicates() Dim I As Integer, J As Integer, IEND As Integer, text() As String ReDim text(IEND) For I = 1 To IEND - 1 For J = I + 1 To IEND If text(I) = text(J) Then text(J) = "" Next J Next I End Sub
It works but is not performing well. Are there more efficient ways of doing this?
I've created an array that I created and declared as a Public array in my module. I created a function that populates the array so that I can use the values in another function. I've gotten the array to populate but when I go to use the values in the array in another function, the array appears at Empty. I seem to be stuck on declaring it properly or something so that it can be used by other functions.
Public arrWebIDs As String Public Function FillArray()
how data is best structured in Access.I have a table of values (for instance: weight) and I need to be able to look up a weight based on the column header (age) and row header (height).How is this sort of data best structured and accessed in Access?
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.
I need to build such a report, Unfortunately, I do not know how to use VBA to create each group (grouping by type) had a different header.
The problem becomes bigger that everything must generate queries dynamically cross, the number of columns in a given type may vary depending on the number or the size of the products in which they occur.
I am working on setting up a Document Control System and have a table, called List_of_SOP where I have, amongst others, the following fields:
- Document Title - Document Number (unique identifier) - Responsible - DL (where this is a multiline lookup column where one ticks the names of the people to whom this document needs to be distributed)
I want to be able to fill an array with the checked values of the DL field, given a particular Document Number. So far, the code is somewhat on these lines:
Dim rs1 As DAO.Recordset Dim rs2 As DAO.Recordset Dim rs3 As DAO.Recordset Dim db As DAO.Database Dim strSQL1 As String Dim strSQL2 As String
