Sum Only First Record Of Group
Jul 17, 2012
My data is structuredin the following format:
ITEM......................PRICE
X..............................$10
X..............................$10
Y------------------------$25
Y------------------------$25
I would like to calculate each item's price only once in the Access report footer. However, when the SUM formula is entered I get the cumulative price for each item (i.e. $70 as opposed to $35).
Is there any way I could just show the price of the first item of the group only? I'm thinking of creating a new field in a query and adding 1s and 0s, but don't know exactly how.
View Replies
ADVERTISEMENT
Jun 10, 2005
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:
ProjectID---StartDate---Employee---ProjectCategory---... <other fields>
1-----------01/01/05---Herman-----App Dvlpmt
1-----------01/01/05---Hortence---Consulting
1-----------01/01/05---Herman-----Consulting
1-----------01/01/05---Hortence----App Dvlpmt
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
End Sub
Thanks in advance!
DM1968
View 2 Replies
View Related
Apr 30, 2008
hi.
first of all, i am glad to join this lovely forum. i've read some threads, people here are very very helpful..
now the question (please bear with me if it's too basic, i am relatively new to Access):
i've created a 'select' query which does everything for me, except one thing - it doesn't mark one case for a certain group of records. here is the excerpt of how it looks, the last column being the one i can't figure out how to do:
Prop Date Category ID ID_unique
x1 2/3/08 001 2351 1
x1 2/5/08 001 2351 0
x1 2/6/08 001 2351 0
x1 2/9/08 001 2351 0
x1 2/3/08 001 2352 1
x1 2/4/08 001 2352 0
x1 2/3/08 001 2354 1
x1 2/4/08 001 2354 0
x1 2/8/08 001 2354 0
so for each group of records with the same ID, i need to mark only one of them.
is it possible?
thanks.. l
View 12 Replies
View Related
Oct 5, 2004
In have a table which contains customer address details.
I simply want to display a 2-column list. the first column
to display a disnct list of city's from the main table.
the corresponding column must give the no. of records that
contain the entry in the first column.
eg if the main table had 5 customers that lived in london
and 6 in Maidstone then
London 5
Maidstone 6
etc
View 1 Replies
View Related
Aug 30, 2005
I have a database in Access 2000 that contains multiple entries for each individual - in this case they are 'visits'. I wish to create a query that shows the first and last visit only on any particular day for each individual.
Can anyone help me?
View 7 Replies
View Related
Mar 8, 2008
Hi Guys, sorry to bother you but I'm really stumped. I've looked through some similar sounding old posts on here but they don't seem to answer my problem...
I've got a report, which is grouped, in the footer of each group a SUM label should add the contents of the pricce column, however strangely it only adds the first record of each group as a running total. I've tried both the "Over all" and "Over group" attributes of the running sum property, but they make no difference.
Any help would really save my day. Cheers!
View 11 Replies
View Related
Oct 18, 2005
Hi I'm a teacher and have to set targets for classes, I have created a database which allows me to input targets for the classes. I now need to then display the class target for individual pupils in a new layout. I am sure there is an easy solution and I am looking at the problem from the wrong direction. Thank you for any help
graham
View 3 Replies
View Related
Oct 10, 2005
Hi,
I have a table with various customer items, a current price and the date the price changed.
EXAMPLE DATA
CustID/ItemCode/DateChanged/Value
AAA 21 01/01/2004 £3.00
AAA 21 01/06/2005 £5.00
AAA 21 01/03/2005 £4.00
AAA 22 01/01/2005 £6.00
AAA 22 01/06/2005 £7.00
BBB 25 01/01/2005 £8.00
BBB 25 03/07/2005 £9.00
BBB 26 01/06/2005 £1.00
BBB 26 01/09/2005 £2.00
Note: Dates are in "dd/mm/yyyy" format. Also, not all data is in order in the table. It’s not how I would have designed it but that’s what I need to work with!
I would like to run a query that groups by custID & itemcode and also the most recent date for any price change. I would then like the query to display the relating price for the record it pulls out. (EG for custID 'AAA' and item 21, it would display the price of £5 as that is the value for the most recent price as of 01/06/2005)
I then want to link the custID & itemcode to another table that has sales in, to check that we are using the most recent price.
Is there a way I can do this via a query or do I need to put some code together?
Cheers
Red [CODE]
View 2 Replies
View Related
Oct 19, 2006
I have a query based on a table that has the fields as shown below. When I run the query, I will get a separate record for each group as shown below.
prb_no | seq_no |commitment_user_group_cd
602 |3 |ERC
602 |3 |MCE
602 |3 |SRG
602 |4 |MCE
602 |4 |SRG
602 |5 |SRG
602 |6 |MCE
I would like to use a query to show only the records where "SRG" is not listed and where there is another group. So in the example data above, I would like the only record to show in the query to be the last record because it's the only one where SRG is not listed along with another group.
The logic behind this is when a problem is initiated and a group assigned, we do some things and document the results. So I am only interested in the records where we have not documented our results too.
Does anyone know how I can do this with a query?
Thanks,
Jim
I want the query to only show the data where there is a user group
View 6 Replies
View Related
Jan 19, 2007
Hi, ive got a form which requires a user to select atleast 1 option for a check box group.
when i pass the results of the form to access all of the checkboxes in the group are update although not all were selected by the user.
what can be causing this problem. The values are being passed to the correct fields in the DB but it still updates across the board.
Thanks
View 1 Replies
View Related
Oct 24, 2007
I have a table that looks like this:
caseID
date
value
I want to create a crosstab query that looks like this:
Date1 Date2 Date3 etc etc
caseID value value value
There are 500 caseIDs in my table. Each caseID has up to 5 corresponding dates/values. Therefore I only want 5 dates as the column headings....listed as 1, 2, 3, 4, 5, etc NOT the actual date (as I then end up with hundreds of column headers).
Does anyone know how to create a detailed number by each group (similar to the runningsum feature in Reports)? I know I need to somehow assign each date a "date number" but I dont' know how to.
Any ideas??
View 3 Replies
View Related
Oct 23, 2004
I have a report with field "Plot" as Group Header. I need to print the report
which lists the owners of each Plot. When the number of owners is large, it takes more
than one page for the listing. How can I force a new page for each new Plot ?
View 2 Replies
View Related
Feb 25, 2014
I have one query that the main form is based on and another query the subform is based on.
I linked the subform to the main form by a common field "Branch"
My main form displays Grouped Employee overhead totals based on Branch
Example:
Branch 1 Employee overhead cost...........15,000.00
Branch 2 Employee overhead cost...........25,000.00
The subform displays grouped branch expense overhead totals based on Branch
Example:
Branch 1 Branch expense...........125,000.00
Branch 2 Branch expense...........155,000.00
I am trying to display both the employee overhead total and branch expense in the Branch Header of the mainform.
Example:
Branch 1 Employee Overhead...15,000.00 Branch Expense...125,000.00
Branch 2 Employee Overhead...25,000.00 Branch Expense...155,000.00
I tried using an IIF statement in a text box in the mainform branch header section to return the branch expense if subform branch = mainform branch.......When I run the report I get all of the Employee overhead but only the last record for Branch 2 branch expense displays.
Example:
Branch 1 Employee Overhead..15,000.00
Branch 2 Employee Overhead..25,000.00 Branch Expense...155,000.00
Then, I tried just putting a text box in the main branch header subreportName.Report.TotalField
When I run the report it returns all employee overhead with the Branch 2 total for every branch in the mainform...
Example:
Branch 1 Employee Overhead..15,000.00 Branch Expense...155,000.00
Branch 2 Employee Overhead..25,000.00 Branch Expense...155,000.00
View 4 Replies
View Related
Apr 2, 2015
I have a subreport and would like to have a header that repeats on each new page if it extends to multiple pages.I tried the suggestion I have seen to create a dummy group header based on an expression of =1 and set the group header "Repeat Section" property to "Yes".header now repeats before every record, instead of just once at the top of each new page.
View 1 Replies
View Related
Sep 10, 2014
Is there a way to duplicate, triplicate, a record based on an option group selection. i.e.
Form
Field: Option group
Value = 1
Value = 2
Value = 3
Field 2:
Field 3:
Field 4:
and so on.
Basically if value 1 is selected, then I fill out the rest of the fields hit enter and go to a new record. But if value 2 or 3 is selected, I fill out the the fields, hit enter. The information is recorded said number of times based on the selection in the option group. Ideally I would have2 or 3 of the same record in the db, with different Primary keys of course.
View 3 Replies
View Related
May 29, 2015
Despite Google I can't seem to figure this out.
I have some data in a format similar to:
Name / Style / description / speed / distance
john / driver / careful / 80 / 5500
mary / driver / careful / 70 / 7000
pat / racer / reckless / 100 / 6000
anne / driver / careful / 75 / 1000
peter / racer / reckless / 110 / 6500
don / snail / slow / 60 / 6000
I want my report to total by style, without details and to look like:
driver careful 13500
racer reckless 12500
snail slow 6000
How do you get a report to sum the group items by a specific item and to hide the details of that group summing?
View 2 Replies
View Related
Mar 28, 2013
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 Related
Jul 25, 2013
I stumbled upon the Option Group function just yesterday and, happy as a clam, I created a group with 2 options in radio button style. I assigned the values to a field called Registration_Type as the 2 options are "Confirmed Registrants" and "Prospective Attendees".
[Great. That part works well. When I look at the table, a 1 or a 2 is in that field so it's great to know how to control accidental ticking of radio buttons (previous 450 records or so didn't have this option group functionality so one might easily tick one of the buttons. So one part of controlling option group I know I can handle via the table itself for now.]
The challenge is how to ensure the user always ticks one or the other ... I went back to the main table and tested the 'required entry' option for the Registration_Type field but forcing an action like this is not ideal in my mind. The usual error message vagueness for the average user is no good and I don't want to limit the user so much.
Is there a way to simply have a popup come up warning that neither radio button was ticked? Perhaps something linked to the form - i.e., maybe "after update"?? I only learned about attaching code to before and after update on controls a couple of days ago, so not sure if this would be best approach.
Just something to let the user know that nothing has been ticked in the option group as that controls in which of 2 reports the data will show up in so any record not ticked might mean a registrant being left out, which would be rather disastrous <g>.
View 1 Replies
View Related
Dec 22, 2014
I have a combo named cbogroup. I have a tblGroup with several records (active, non-active, nursery, etc.). One of the records is *ALL*. Using the CboGroup the user can pick any of the records. Howeverr, if they pick the *ALL* record, I want the query to pull up animalID based on all records in the TblGroup. If another record is picked (i.e. nursery), then the query will pull up only animalIDs that are in the 'Nursery'.Can I put a (iff then) in a query in order to differentiate a query based on all group records or a query based on only one record?
View 14 Replies
View Related
Mar 12, 2007
I'm trying to create a Query. I have a field "Name" from the table "Products" and the field "Quantity" from the table "Deliveries". Currently, The same product name will appear several times with their quantities. I want to SUM all of those quantities together and only have the Product name appear once. If it's a diffrent product name, then sum all of it's quantities together etc. I'm having a tough time writing a proper formula, It either has syntax errors or produces more than one field, which I don't understand. I need a formula that I can insert into the design view of the query. This formula will also create a new data field to hold the information. Can anyone help?
p.s. Do I have to use SELECT for both Name and Quantity? Where do I insert GROUP BY? I'm so confused!! I've tried things like (SELECT Name, (SELECT SUM(Quantity) FROM Deliveries) FROM Products....by now where am I supposed to write GROUP BY???
View 5 Replies
View Related
Mar 22, 2008
I know you have probably seen this a trillion times, but being new to all this, it is discouraging to say the least! Please help...
I think maybe I should upload the database, let me know.
Here are the names:
Table name: PPM Data
Fields:
Company
PPM Month
PPM Amount
There are 10 different companies, and I need top 6 months data per each Company, to eventually only show the 6 amounts. I have tried all the equations I can find. But I don't think I am doing it right. I have tried to make 10 different queries for each company and than tried to join those queries, and that just didn't work.
I have tried Allen Browne, confusing, and I have tried Acc2002 for their equations for Top N per group, I still can't make out what they are saying without the actual database to look at.
View 6 Replies
View Related
Sep 13, 2005
Hi,
i have a combo box that query a tabel to get the date's i chose from but she repeat the samedate if i have a 2 records with date 12-9-2005 i see 2 in the combo box ho i can join them to see only one date fron them
View 1 Replies
View Related
Jan 18, 2006
I have a field on a table that is a memo field. I am trying to write a query to include this field but get the "Can't group on memo or OLE object. Any ideas on why and how can I query with this memo field included?
View 2 Replies
View Related
Feb 11, 2006
I'm trying to view the last two valuations for each property so that they can be compared. I can pull out the last valuation for each property and can pull out the top two records but not for each property, just shows me the top 2 of all records.
Any suggestions?
View 5 Replies
View Related
Feb 28, 2005
Hi, I have just made my maiden voyage on the good ship "Security Wizard" and the water has been a little rough but as yet I still have my sea-legs intact!
Slight problem though... I want users to be able to add data to a table but not to be able to view the whole table if they try and open it in the database window. Now I know reasonable measures would just hide the DB window but I have the threat of Data Protection Auditors looming over me becuase management "forgot" to tell me security was required and it has already been breached before I used the wizard.
Anyway, I did some research on here and I found a method whereby I can set the table so only I have permission to view/add data, then I can get users to add data via a query with permissions set to Owners.
I'm really hoping that someone will come to my rescue and tell me that there's a much easier way than that...
Please....
Anyone....
Thanks, Tom.
View 1 Replies
View Related
Jun 11, 2005
I need to execute this query from an ASP . Net page. I'm connected to an Access 2002 DB.
SELECT tblBudget.GroupCode, tblBudget.GCDescriptioon, tblBudget.Amount
FROM tblBudget
GROUP BY tblBudget.GroupCode, tblBudget.GCDescription, tblBudget.Amount
ORDER BY tblBudget.GroupCode;
If I delete tblBudget.GCDescription and tblBudget.Amount from the "GROUP BY" instruction it does not work.
What I want to do is that in case tblBudget.GroupCode is repeated (e.g. several records with the same group code) I want to display only one line summarizing all info for the given group code.
Thank you
View 1 Replies
View Related
