Qry To Group & Sum All Data But Only Returning The Location Of Where Max Pts Was
Jan 21, 2007
I have 1 table with multi Text columns (location, coaches,ssn etc), and
multiple Num columns (pts and product counts)
I need to sum all the unit counts & points for each person, but only
bring back the location for each person that had the 'max' sales (pts).
ex. ssn xxxxxx has 3 sales codes : code 1&2 had less sales aquired
then sales code 3. Sales code 1 was location Vt, slsCd 2 was location
CA and slscd 3 was location FL. I need to bring back the sum of all
three codes but only the location FL as it had the most sales pointing
to it.
I have deadline of Monday to get this done.PLEASE help
I have a table with persons age and location and I want to generate a report of all persons by location and age group. The age groups are 0-9, 10 - 19, 20 - 29..., 90-99. The location codes are 10,20,30,...90,99.
FYI: I'm new to Access, but have some knowledge of SQL and VBA. I'm using Access 2000. I've looked through past posts to no avail.
I have a form which allows users to supply one or more criteria to subset a recordset. The "Execute" button on the form kicks off VBA which builds and runs a SQL statement using the selections made in the form.
The recordset that the SQL runs against can have multiple rows for each ProjectID -- based on a combination of a couple of fields. For instance, the following is possible:
If the user wants to select ProjectIDs where Employee="Herman" -- without making a selection on ProjectCategory -- I want only one of the two "Herman" rows above to be returned. And I don't really care which one. (Similarly if the selection is only on ProjectCategory)
The problem is that, using the code below on the example above, both "Herman" rows are returned. I've tried numerous approaches -- this being the most recent. The SQL statement is being built as I expect, and it's executing. It's just not giving the results I want/expect. Also, I've hardcoded selections into a stored query similar to the one below, and it works. Any ideas on what am I doing wrong?
Private Sub cmdExecuteQuery_Click() Dim strSQL As String
strSQL = "SELECT ProjectID, first(ProjectName), " & _ "first(StartDate), first(EndDate), first(ProjectActive), " & _ "first(Sector1), first(Sector2), first(Sector3), first(ClientShortName), " & _ "first(Employee), first(ProjectCategory) " & _ "FROM qryProjectsForReport WHERE (ProjectActive = " Select Case optStatus Case 1 strSQL = strSQL & "True) " Case 2 strSQL = strSQL & "False) " Case 3 strSQL = strSQL & "True or ProjectActive = false) " End Select If Len(cmbCategory) Then strSQL = strSQL & " AND ProjectCategory = " & cmbCategory If Len(cmbMember) Then strSQL = strSQL & " AND Employee = " & cmbEmp If Len(dtStartDate) Then strSQL = strSQL & " AND StartDate >= #" & dtStartDate & "#" If Len(dtEndDate) Then strSQL = strSQL & " AND EndDate <= #" & dtEndDate & "#" If Len(cmbClient) Then strSQL = strSQL & " AND ClientID= " & cmbClient If Len(cmbSector) Then Select Case cmbSector Case 1 strSQL = strSQL & " AND Sector1 = True" Case 2 strSQL = strSQL & " AND Sector2 = True" Case 3 strSQL = strSQL & " AND Sector3 = True" End Select End If strSQL = strSQL & " GROUP BY ProjectID ORDER BY ProjectID;" MsgBox strSQL OpenReport strSQL, chkDatasheet
On a subform that records notes in datasheet view (columns: Date, Note; sorted in reverse chronological order with newest at the top), is there a way to get the blank new record line to appear at the top? Otherwise, you have to counter-intuitively scroll down below the oldest entry to enter a new line.
We could sort in chronological order, but then the most relevant notes would sometimes be scrolled off the bottom of the form and not seen.
Ive been pondering on this problem for far to long now so decided it was time to ask here !
I would like to return (based on a query) some records that would populate a report. I can set the source of the report as the query but my issue is that I need to actually have some code prior to report population.
EG if they have two products I want to list them product1/ product2 and as far as I know this isnt possible when you just set the source for the report...
The data is coming from SQL server so I have the option of a stored procedure but from trsting that seems to make things even more confusing.
Any help appreciated as Ive been stuck on this for far too long !!
I am trying to select all projects with the keyword "sett" in them. About a thousand rows with this keyword exist but why isn't my query returning any data? :confused: Here's an example of the data "STPT-SETT-NY-EQ Legacy"
Now i want to return all rows with "SETT". What am i doing wrong here please :)
SELECT projectList.* FROM projectList WHERE (((projectList.projectName) Like "%SETT%"));
this doesn't work either. No records are returned when query is run:
SELECT projectList.* FROM projectList WHERE (((projectList.projectName)="%SETT%"));
O.k. I have a query working, and it's calculating perfectly and I'm reporting on it fine. However I noticed that when I run the query it doesn't populate the equation for all the results. Let me explain further.
I have a main form for tracking company information, and a sub form that tracks departments for that company and waste breakdown information. We take measurements for the company in two ways. 1 - a total weight for the day, and 2 - we weigh out categories of waste (within the sub form that tracks the department stuff)..we then calculate what percentage of the daily waste a specific category is. This calculation is done in a query, and works fine for the first department of every company, however when it moves to the next department for the same company, all I get is #Error in the field. eg./
Company "X" Total Daily Weight = 750 Kilograms Department "Shipping" Category - Plastic Bottles - 20 Kilograms
I then have a query that calculates what % 20 Kilograms is of 750, then a report based on the query. This works fine; However in my report I look at the next department, with the exact same informaton as above...I see #Error returned in the field.:confused:
Does all of that make sense?? Is there a way to make sure my query calculation flows through all of the departments??
I have to produce a report (preview first)that acts like a cross tab query. By this is mean that it reports specific data in a 12 period sequence for each year. Therefore I have to set up dates which are NOT normal calendar dates ie one could be 02/05/05 to 29/05/05 then the next would be 30/06/05 to 24/07/05
These dates are issues at the start of a contract year. I have to therefore produce the report for each period that is given to me ( not an easy one is it) I have considered many options but I cant quite get the final effect
I have 2 tables, one called Location and the other called PC Inventory. They both have columns called sites. The query is set to search both tables by site name and return the PC's at that site that match the search criteria.The search form has a drop down menu with all the site names. When you choose a site name from the last, the after update event does a re-query to a subform on that same form. The source object of the subform is set the query.[Forms]![PC Search]![Combo6] - this is the criteria on the query that point to the drop down menu.
Private Sub Combo6_After Update() Me.Child19.Requery End Sub
This is the requery that point to the subform (child19)..The problem is that some selections from the list don't return any results to the subform. But there is clearly records that meet the criteria in the table. Example: ABC @ Maryland returns all the PC's at the site. DEF @ Maryland doesn't return any PC's at the site, but there are records with that site name.Even if go into the table copy the site name that isn't working and enter into the search field it doesn't return the results.
Any new data I add to my database isn't showing up when I run a query.I've noticed it on 2 queries. One which is my ChargerSearch query with the field name:
And my JobHistory Query which has multiple fields from many tables. With criteria only on the Charger_ID field "[Enter Charger ID]". Both work fine for data that was already there but any new data I've added isn't returning any results when I enter the correct values in the parameters. The data is definitely there in the appropriate tables, it just won't find it on the queries.
I am designing queries to return averages for quality test data.
I have this query that functions as I want it too [URL] .....
It returns the averages of all the values received for different tests for a lot number (the lot number criteria should be filled out as well)
When I want the query to be more specific and average only certain box numbers in the lot (that start with the prefix PB") the query does not return an average for box numbers starting with PB but splits them up, showing an average for PB1, PB2 instead of combining the data for those boxes into a single unified average ...
I have created a query that is designed to return a count of how many records there are in various tables. There are 10 expressions in all, so when it is run I am expecting to see one row of data with each field populated with the number of records.
It does do this, but the data is repeated over line after line (see the attached picture)
Hi, in the qry attached I would like to group the records by yyyy mm int the field "Year month" I need then to count the number of records in HOTAC which have value "yes" the sum of "Total Flight Time" and finally the count of records that contain the letter F or P in the "Duty" field. Example:
Year Month Hotac Flight Time Duty 2007 05 0 2.18 2 2007 06 8 XX 2
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.
I am new to Access and would be very grateful for some adivce on setting up a query. The data in the table below comes from a photon counter. Both fields are numbers although TIME actually represents seconds. I need to be able to group the DATA field by arbitrary TIME periods. e.g. to divide the table up into bins of, say, 8.7 seconds length, showing the total accumulated in the DATA field for each successive period of 8.7 seconds. The tables are about 300,000 records long. Any help would be much appreciated.
I need to group data (cases) in range like this (0-5, 6-10, 11-15 etc).
I can do this with the iif statement but this is a bit tedious.
I have two simple table:
1. Salestbl with two fields: a. customerid, b. Cases 2. Rangetbl with two fields: a. Range b. Category
The rangetbl is where i would define my range and it look like this the range field have records 0,6,11 with corresponding category which look like this '0-5', '6-10', '11-15'
How can I use this to group my data. that is customer with x cases have group '0-5' or '6-10' etc....
I have an Option Group I created (simple Yes-No) in a form that links to a Yes-No field in a table. I have the default set for this field to "Yes" in the form. The option button works when the form is first opened. If I cange the option to "No", that works. Here's the tricky part that is giving me eye twitches...
If I change the option back to "Yes", the radial button doesn't reflect the change...but it does change the table data! Why do the radial buttons stop changing? Even when I close the form and open it again, the data in the table is no longer reflected with the buttons in the form.
Not all the choices (numeric codes) for my form's option box are being saved to the associated table.
I am using the options choices in the option group to enter data into a table and do additional things - clicking one option is supposed put a code in the table and then take you another form, while clicking a different option will make a new option box visible so I can collect additional data.
My option group has three options, only the data from the second option, which makes a second option group visible, is being saved in the table. If someone clicks on the first or thrid option, which takes them to another form, the data from that option choice does not get saved to the table.
Greetings, I've been banging my head on this for days, and what really makes this difficult is the number of websites which say it should work just fine!
I'm working with Access 2003 and trying to make use of the IIf function. I have given my fields generic names in the examples below.
I have a number of items I want to search upon in my search form. Each item has a check box and a combo box. I want the query to look at the check box. If the box is checked, I want the query to use the value in the combo box. If the check box is UN checked, I want the query to ignore this field and return all values. I thought this was simple, until I tried to do it!
Here is my code, placed in the Field-name criteria box of the query:
This will return all records where Field-name = 2.
So... my original code should work! Because no "false" condition is stated, the query should be blank, and should return all records. But it doesn't! Truly maddening.
Here are some attempts which did not work:
IIf([Forms]![form-name]![checkbox-name]=True,[Forms]![form-name]![combobox-name],>0) ---> The ">0" caused an error. In fact, I can only specify an integer - all else, including things with operators, do not work.
Working with nulls - it does not seem to recognize an unchecked box as a null.
That's the short version of the story - any and all ideas welcome!
I'm trying to create a query that will compare the data in 3 fields in a record, choose the largest (I also have a criteria to order by if more than 1 field has the same entry and it's the largest of the 3), and then group by that.The fields I will need are as follows:
PRODUCT table: ProductName Chemical ChemicalAbstract PhysicalState NFPAHealth NFPAFlammability NFPAReactivity
qryQuantityOnHand query (which doesn't link directly to the PRODUCT table, it links through associations with other tables):QOH...I will eventually need information from another table for the final reports, but I don't think it has to be included in this query.
The fields NFPAHealth, NFPAFlammability, and NFPAReactivity each may be 0, 1, 2, 3, or 4...I need to ignore blanks; if 1 of the above fields is blank, they will all be blank.For any record, I need to compare the number in those 3 fields to each other, and choose the largest number and group by that rating.
In other words, if the largest of the 3 numbers is a 3 in the NFPAFlammability field, all those products need to be grouped together.If the same number appears in at least 2 of the fields, the order that determines the grouping is: Flammability, then Health, then Reactivity..Ultimately the report will be grouped as follows:
and each of the groups will be sub-totalled.I'm stumped at trying to create the query in the first place.The added aggravation here is that we are dealing with 23 stores, each with their own mix of products. I have another table that contains the information about which products are in which store.
I have a table of about 250 items that are sorted based on two fields. I created a query of the table and hide one of the fields because it is only there for sorting purposes to group the data.
For example, I have a "Type" field. In the type field, I differentiate the items with different categories, such as "Materials", "Safety", etc. My next field is "subtype" which assists the "Type" field being sorted properly for my needs. In the "Materials" category, all of the items are either "Fencing", "Plastic", "Wire", or "Miscellaneous". I am sorting the "type" and "subtype" fields in ascending order so that all the "materials" are grouped together, and all of the "fencing, "plastic", etc are grouped together within the materials field.
I am creating a printable report and I do not want the "subtype" field to be on the report, but I need the "subtype" field's sort to be affecting my data so it is grouped properly. I'm having difficulty, is there a way to hide fields in reports?
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?
