Aggregating Results In Queries
May 21, 2005
I have a table with "date" as a field. Another field is "charge."
I want to create a query which returns the sum of all charges for a given date. How do I do this?
I tried doing it in a report; I summed in a group footer. However the table is very large (250,000 entries) and I kept getting "overload" message. I would rather do it in a query then select with a second query only those records I need (which are ones with a sum of charges within a certain range).
Any help would be appreciated! Thanks.
View Replies
ADVERTISEMENT
Jul 31, 2013
I have 3 queries named Mech Final Equipment 3 Mth, Mech Final Equipment 6 Mth, and Mech Historical Final Equipment.They all have two fields-Final equipment and Sum of Sum of Down (calculating the number of minutes each piece of equipment was down in the time period selected).
My ultimate goal is to join the three queries to display a pivot chart that uses the Final Equipment as the category field and 3 Mth, 6Mth, and Historical as seperate data fields.What I have is a join query (Which I have named Mech Final Equipment H63 Joined)
Using this SQL:
Code:
SELECT DISTINCTROW [Mech Final Equipment 3 Mth].[Final Equipment], Sum([Mech Final Equipment 3 Mth].[Sum Of Down]) AS Duration
FROM [Mech Final Equipment 3 Mth]
GROUP BY [Mech Final Equipment 3 Mth].[Final Equipment]
UNION
[code]...
Which returns a table that looks like this:
Final Equipment, Duration
Ancillary Equipment, 225
Ancillary Equipment, 401
Ancillary Equipment, 1787
Brush Unit , 1252
Brush Unit , 2519
Brush Unit , 8004
And so on.What I need the table to look like is this
Final Equipment, 3 Mth, 6 Mth, Historical
Ancillary Equipment, 225, 401, 1787
Brush Unit, 1252, 2519, 8004
And so on, like a cross tab.I tried to do a crosstab query but I don't have enough fields.
View 6 Replies
View Related
Sep 30, 2014
I am working on a report that has some special characteristics.
Let's say I have a list of groups of Vendors in a table, complete with VendorID. I have 3 other tables that use the VendorID: Complaints, Complements, and Terminations.
Each of these tables has a date that the Complaint, Complement, and Termination notice was received.
Every Fiscal Quarter, a report is pulled that looks back over the 4 preceding quarters to determine if a 5% threshold has been crossed by any of the vendor-groups in regards to the amount of Complaints they received.
The equation used for that is : (complaints/vendors_in_group)*100
It is imperative that the information has the current fiscal year and fiscal month (which I am tracking with functions from MS website), and I need to be able to store the information attached to the fiscal year and month.So when a user goes to the form and inputs the desired Fiscal Year and Fiscal Month, the database can display the 4 previous quarters of information...split into Q1, Q2, Q3, and Q4.
What I would like to have happen is to be able to have one table where the information is stored, quarterly, so that it can be retrieved for the report.
Questions:
1. Is it possible to have one line, per VendorID, that has the total number of Complements, Complaints, and Terminations, as well as the threshold percent stored in a table? Right now, I am getting LOTS of duplicates and blank lines when I try to put them all together. It has the right data, but takes about 10 rows per VendorID.
2. It is very important that the total number of Vendors in a group be captured on that quarterly report, so maintaining that number, in the same table, is essential and must be tied to the VendorID.
3. I have looked at Union Queries and Crosstabs, but I just dont know enough about them to make it work.
View 4 Replies
View Related
Feb 9, 2015
I am a beginner Access user. I’m trying to export the results from two queries "TestData" and "ReportDate" to a saved .xlsm file. These queries are generated from tables linked to a SQL server.
The first query “TestData” should be copied to a sheet named ”TestData” and the second query “ReportDate” should be copied to a sheet named “ReportDate” in the same Workbook ”Test.xlsm”.
Both queries have two parametesr passed from Form1 [Start Date] and [End Date].
Code:
Public Sub ExportToExcel()
'Step 1: Declare your variables
Dim XL As Excel.Application
Dim wbTarget As Workbook
Dim ReportDate As QueryDef
Dim TestData As QueryDef
[Code] .....
View 14 Replies
View Related
Jun 16, 2005
Hi, here's the situation:
I have a query A, that gives me the a set of numbers.
I have a query B, that gives me another set of numbers.
Numbers in B are always equal or larger than those in A. (B >= A)
I want to create a new query C, that does the subtraction. (C = B-A)
Here's an example to explain what I want.
Let's say I have A=4,7 and B=6,9
Now, when I create the query, I get the following result:
A | B | C=B-A
4 | 6 | 2
4 | 9 | 5
7 | 6 | -1
7 | 9 | 2
What I want is this:
A | B | C=B-A
4 | 6 | 2
7 | 9 | 2
How do I do that?
Thanks.
View 2 Replies
View Related
Mar 13, 2007
I have two queries combined into one query. The first query shows a sales summary by part number. No problem so far. The second query shows inventory by part number. No problem with that.
Now, I combine the queries, and get only those results that appear in BOTH queries. In other words, if there is no inventory, I do not get zero in the inventory column, the entire result for that part number is skipped.
How do I still display the results of the first query if there is no match in the second query?
View 9 Replies
View Related
Jun 16, 2013
I have a table with sales in (TBL_Sales) as well as another (TBL_Key_Customers) which lists information about specific customers, in particular if they are part of a group e.g I would categorise Dave's Cars, Dave's Bikes and Dave's Coaches as being part of the Dave group.I would like to query the TBL_Sales to see how many sales were made to the Dave group but then also what else was sold. e.g if Factory 1 sold 100 items of which 60 went to Dave's group then the remaining 40 would be shown as "Other".
View 4 Replies
View Related
Mar 5, 2015
I have a query that i can type in an item number and it Sums all the locations and tells me how many i have in the building. If i do not have any in the building then there is no record of it in the table and comes back blank. How do i make it show a show 0 instead of blank if there is no records to sum?
View 9 Replies
View Related
Aug 30, 2007
Hey guys-
I have a database of properties for sale. I want to calculate the asking prices of each record (for sale) against the sold prices of all the houses in the same area. Area is defined as a numeric number, and each property has one assigned to it. My question is this-
I have a table of all the properties. Do I run the query and store the results in a specific cell in the table- and then use a form to display those results? Or do I run the query from a form and not store the calculated results at all? I will be adding/updating info on a daily basis, so these calculation results will also change daily. Obviously I am fairly new to Access and trying to figure out how to do this stuff. Using Access 2002. Either way, I assume i'll be using a form to display the results one way or another...
Thanks!
View 8 Replies
View Related
Nov 26, 2007
Hi,
My SQL is fairly basic so excuse me if this is something simple.
This isn't my database, I am trying to create a report on someone else's and it's a poor structure (sound familiar :-).
If I have created two queries; one of which selects results on students from one table and the other selects results on students from a completely different table.
This means I have two Hi,
My SQL ability is fairly basic so excuse me if this is something simple.
This isn't my database, I am trying to create a report on someone else's and it's a poor structure (sound familiar :-).
If I have created two queries; one of which selects results on students from one table and the other selects results on students from a completely different table, how do I combine these results into one query so I can run a report on it?
To outline the situation, I have two different queries with identical data types but from two different tables. I need to query these two queries and (possibly) link in another student information table so that I can display the results from both queries as if they all came from the same place.
The addition of the extra table in the final query would only be so I can add extra student data into the final report.
Hope this makes sense!
Thanks,
Matt.
different queries with identical data types
View 2 Replies
View Related
Mar 19, 2013
I have a query that is showing two results for one product and I have no clue why. I have my tables in a one to many relationship and if I click on the + it shows the correct data for the product in question. Im guessing I have my table set up wrong its the only thing I can think of.
The table tbl_Carton has two entrys for a product (Flex Tape 2-1/16") (Product_PKEY #21) mabe I have it set up wrong? If I run my (qry_Switchboard ) and enter (21) its giving me an extra result for each of the two results thats suposed to be there.why?
View 4 Replies
View Related
Jul 4, 2013
I'm having a mental block on this one.
I have a table with a yes/no field. It's linked to a form with a yes/no box, but the outcome I want is that if the form box is unchecked, the query returns all the "yes" records - which is trivial; however I want to return ALL the records if the form box is checked.
View 1 Replies
View Related
Jul 29, 2014
I have a table with:
A dDataID (sequential auto-number)
B dDateTimeEntered (date/time field)
C dFactoryOrder (text)
D dSpecificationID (number)
E dStartWeek (text)
F dIncreasingDecreasing (text, values are limited to "Increasing", "Decreasing", or "NA")
I need a query to look up the last 9 values where C, D, and E match variables from a form, then tell me how many "Increasing" values are contained within that set. I want to store the final value in a combo box on the form. I tried writing the code and this is what I got - design view attached as a pic and SQL below. It looks like the "Top 9" is sorting on the Expr1 field, which isn't what I want, but I'm not sure how to force it to use dDataID or dDateTimeEntered to figure out which records to use.
SQL:
SELECT TOP 9 Sum(IIf([tblData]![dIncreasingDecreasing]="Increasing",1,0)) AS Expr1
FROM tblData
WHERE (((tblData.dFactoryOrder)=[Forms]![frmDataEntryDisplay]![dFactoryOrder]) AND ((tblData.dSpecificationID)=[Forms]![frmDataEntryDisplay]![dSpecificationID]) AND ((tblData.dStartWeek)=[Forms]![frmDataEntryDisplay]![dStartWeek]));
View 8 Replies
View Related
Jun 20, 2013
I have an access database which is going to present a front end webpage form. The form has several drop down lists and I need one of those drop down list to display only certain results dependent on what the previous drop down list has selected e.g. if Box A is selected as Aces then I need the next drop down box to only display A building
1-A building
2 A building
3 and not the entire list.
How can i get this to work?
View 2 Replies
View Related
Mar 13, 2015
In Access 2007, I put into the table, in a certain field, a certain word field, so I put into design view for that query, in that field, Like "Field" and even though the word field is in that table in that field, it doesn't show it in that particular query?
View 14 Replies
View Related
Aug 1, 2013
I'm trying to create a query that will return the closest matches to what is input. However the closest i have gotten to this working correctly is by using the following criteria.
Like Left([Forms]![TESTTESTTEST]![Text2],1) & "*"
Now obviously this isn't a great solution as it just produces results that match the first number but i need it to display results closest to the number entered into text2.
Basically i am doing this as a designer can search for a gap on a machine where there new project can fit on causing the least amount of disruption. So by entering in the size of there project the query will display jobs of a similar size that are already planned on the machine.
View 1 Replies
View Related
Sep 25, 2013
The below isn't the actual use but it is easier to explain like this...
I have a system set up on access and I have a front page (form) where I can enter the reference number (1141#2) of a building (YELDR for example).
This then fills in information in the other fields on the front page. For example address, number of doors and entry codes.
My problem is that this only displays one door and one entry code. I need it to display all doors and all entry codes so that I can just type in my reference number and have something that looks like the below but for all of them. It works fine at the moment but only for one of the results when there can be up to 10.
Reference No. YEDJR <---------------- I ENTER THIS
Address - high Street - London - UK <-------------- All of these fields are populated using the query
Doors - Green - front <-------------- All of these fields are populated using the query
- Red - side <-------------- All of these fields are populated using the query
- Orange - back <-------------- All of these fields are populated using the query
Codes - 10111 <-------------- All of these fields are populated using the query
- 10221 <-------------- All of these fields are populated using the query
- 10256 <-------------- All of these fields are populated using the query
I know this is probably nowhere near enough information but is this possible? I don't know SQL so i would have to use the designer.
This is the SQL which I have at the moment though (I didn't write it i just copied this from design view on the query)
Code:
SELECT [Site Info Data].[1141 #2], [Site Info Data].[1141 #2], [Site Info Data].[Site 2 Name],
[Site Info Data].freq, [Site Info Data].status, [Site Info Data].[Site 2 Owner], [Site Info Data].[NGR #2],
[Site Info Data].[Address #2], [Site Info Data].[Postcode S2], [Site Info Data].[Dish size #2],
[Site Info Data].[Dish height #2], [Site Info Data].[Dish Bearing #2], [Site Info Data].RCLO
FROM [Site Info Data]
WHERE ((([Site Info Data].[1141 #2]) Like [Forms]![Front Page]![txt1141] And ([Site Info Data].[1141 #2])
Like [Forms]![Front Page]![txt1141] And ([Site Info Data].[1141 #2]) Like [Forms]![Front Page]![txt1141]));
View 5 Replies
View Related
May 20, 2013
I need to do a calculation on the RESULTS of a query. Each record in the table I'm querying has a date stamp. I have a query that produces a list of the newest records for each machine. I want to calculate how old each of those records is based on today's date.
So far when I try to include the calculation in that query, it returns all the records instead of the newest ones. This makes me think I'm doing something fundamentally incorrect. The calculation is affecting the query results where I just want to do the calculation on the results. What I'm trying to ultimately do is have a way for the users to see a list of machines that are overdue for a certain procedure. I was thinking of putting a button on a form that will run the query and present the list, perhaps with some conditional formatting highlighting the machines that are overdue, etc. Do I need to just use 2 queries?
View 14 Replies
View Related
May 3, 2013
Have created a simple data collecting database with a simple query to narrow down some of the data , the DB all works fine and some queries are ok, but one the simple query where I want to look a one single set of data.Using the Like "*"&[Enter Search Parameter]&"*" gives me no results.
View 9 Replies
View Related
Nov 5, 2014
I have created a booking system for a travel agency and now when the booking is complete I want to export some summary data to the Excel. The issue is that I have separate tables showing lunch, tickets, tour guides, etc for every destination and I have also created queries showing the total for each destination but the issue is that I cannot combine all the results into one query and export that query to excel.
View 4 Replies
View Related
Feb 27, 2006
Hi All,
I have a approximately 70 queries in my database. I would like to be able to run a query which would run all of the queries and output the number of records for each query. Ideally, these would then be written to a table so that the user could then just read the values from the table for the latest results, rathe r than have to execute the whole thing again.
The user may wish to select which queries to run. I was thinking that I would need a table as follows called say tblQueryResults:
QueryToRun - Yes/No - DateRun - NumberOfRecords
Query1 - Yes - -
Query2 - Yes - -
...
...
...
Query70 - Yes - -
So my first dilema is to work out how to run all the chosen queries that the user wishes to run. The user will probably have all 70 ticked as Yes initially.
Should I run this from VB code with a whole lot of VB statements. I would like to loop through the whole table and collect a list of all the queries to run based on a positive Yes for some or all of the queries. The results must then go and be written into the same table under the date it was run and the number of records that was found for each query.
The whole reason for doing this is that queries which return no records need not be run by the user - saving the user time etc. I appreciate that this query will take a considerable amount of time - given that it could be as many as 70 being run one after another.
Thanks,
Evan
View 2 Replies
View Related
Jan 20, 2007
To the point: I have a column which is a combo box (list) consisting of all the departments of company. I don't want 4 of the departments to show up in my query results. In the criteria field I have tried to use the following:
<> "department name", "department name", "department name", "department"
// Note: I've even attempted to place those departments inside parenthesis. And use ; between the names.
Other criteria I have attempted to use without the results needed are:
1.) Not In
Note: some of the department's name consist of a single name; some are two names with a space between the two names. I have entered the names in the same letter case as they were created in the table's combo list.
Suggestions are greatly appreciated.
View 1 Replies
View Related
May 9, 2013
I have 3 main tables: tblEmployees, tblJobs, and tblProcedures. (See attachment for relationship diagram and additional supplemental tables).A job can have multiple procedures and an employee can have multiple procedures too.
I need to write a query such that when searching by a specific job I can see all of the employees who are qualified for that job. This is done by seeing which employees have the procedures that belong to a job. But here's the catch: since a job can have multiple procedures, if an employee only has some of the procedures I don't want that particular employee to return as a search result. The employee must have ALL the procedures that belong to the selected job.
So for instance if I have:
tblJobs
Job1
tblEmployees
Emloyee1
Employee2
[code]...
If I search by Job1, I want only Employee2 to return as a result, NOT Employee1.I am at a lost for how to construct the SQL for something like that.
View 4 Replies
View Related
Aug 8, 2013
The results of my union query of 2 separate tables looks like:
Name - Count of Participations
Joe Blow - 7
Harry Smith - 11
Kate Upshaw - 8
Joe Blow - 3
Harry Smith - 5
Kate Upshaw - 13
I need to combine the duplicate names and total their participations to make a report look like:
Name - Count of Total Participations
Joe Blow - 10
Harry Smith - 16
Kate Upshaw - 21
View 3 Replies
View Related
May 20, 2013
i have created a parameter query which will find a students best and worst time for each exercise they have done. so you enter the student ID when you run the query and it works fine but i have a problem i need to query to find two students in particular and then i have to create a report from this query on the two students identified and this is where i come up stuck. i have tried typing in both student id's into the criteria but this doesnt work no matter if i put and in it or not and im not sure how to get the job done.
View 7 Replies
View Related
Nov 14, 2013
The Query
'SELECT Count(clubbox) AS MTSingles
FROM moves WHERE (((moves.cmrdate)>[Forms]![CPanel]![Text44] And (moves.cmrdate)<[Forms]![CPanel]![Text46]) AND ((moves.driverid)=[Forms]![CPanel]![Text38]) AND ((moves.move)=35) AND ((moves.clubbox)=32));'
returns about 60 records, how do i assign the results to a tempvars
View 9 Replies
View Related
