Sum Function Only Adding First Record Of Each Group
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 Replies
ADVERTISEMENT
Nov 12, 2006
Hello,
I have several Option Group fields that are stored as Numeric with a datatype as Long Integer. I want to create a function that decodes these numbers into values (i.e 1 = open, 2 = in-work, 3 = shipped) and then use the function in a query.
Any guidance is appreciated.
Markw
View 1 Replies
View Related
Apr 8, 2014
I'm having trouble using the "group by" function in my query. The option in design view is not showing up and I'm not able to figure out what I'm doing wrong. What is there in design view is Field, Table, Sort, Show, Criteria, Or. I seem to be missing the "Totals" option.
View 2 Replies
View Related
Nov 19, 2012
My query includes a few new columns I have added and I need to use GROUP BY so each new column is grouped per store.
When I try to use: GROUP BY newColumn1, newColumn2, newColumn3;
I get the following error: 'You tried to execute a query that does not include the specified expression 'Store Nbr' as part of an aggregate function'
All I want to do is have each new columns distinct value show up once for each particular store, not repeated multiple times. When it is repeated multiple times, it also shows for each store department which messes up the metrics of the report.
View 5 Replies
View Related
May 24, 2007
I have several records to search for. I need to select just 1 record for each Tkt_no group using Tkt_id (max no).
Example, I only want to select
Tkt_NoTkt_IDTicket_ProblemTicket_StatusTicket_Finish_Time
114problem 1Done
210problem 2Done00/01/1900 20:00
32problem 3Ongoing00/01/1900 20:00
43problem 4Done00/01/1900 20:00
711problem 5Done21/05/2007 9:15
913problem 6Done22/05/2007 0:00
from below data
Tkt_NoTkt_IDTicket_ProblemTicket_StatusTicket_Finish_Time
14problem 1Ongoing00/01/1900 20:00
114problem 1Done
25problem 2Ongoing00/01/1900 14:30
26problem 2Ongoing00/01/1900 11:30
27problem 2Ongoing00/01/1900 10:15
28problem 2Ongoing00/01/1900 15:00
29problem 2Ongoing00/01/1900 8:00
210problem 2Done00/01/1900 20:00
31problem 3Ongoing00/01/1900 20:00
32problem 3Ongoing00/01/1900 20:00
43problem 4Done00/01/1900 20:00
711problem 5Done21/05/2007 9:15
913problem 6Done22/05/2007 0:00
Any ideas?
Appreciate the feedbacks.
View 4 Replies
View Related
Dec 20, 2013
I am trying to calculate annual percentiles of a large set of data and I have only been successful at retrieving the percentile of the entire data set (and not by the grouping). See provided example database for code/query. Query1 is what I want to happen to make the Percentiles table.
View 14 Replies
View Related
Mar 28, 2014
How do I add a group header while i am in design view
View 3 Replies
View Related
Oct 24, 2014
I have a invoice system I have created in Access and it did used to work perfectly and then maybe an update Who knows stopped the system working.
I have an customer order screen that has customer details and then a subform within the same form this takes a total of items & costs for this order and then it entered it into the customers table from the order_Details table using me.Order_total = Order_Total.
I know this is bad design but I store it because once the order is places I need the total to be static because the invoice has been sent and so if someone changes the order then the total owed doesn't change.
I then have a reconcile form which is on a datasheet form straight from the tab;e so it is editable which has a checkbox that then once ticked copies the total from the Order_Total in the table to the reconciled field in the same table and then I use a report to show who owes me still.
So I have made a Select query from the Orders table & Order_Details table and used a Group by Order number (Which is the link between the 2 tables) this shows correctly but now not editable because of the rules So I am trying to add an editable checkbox on the same form.
I tried to use a dlookup makes all of the boxes either ticked or not. I've been looking at Recordsets but I'm unsure if this is what I need or not really
So really I just need to see if I should be putting the Total from the Subform into the mainform and then entering it into the table (Like previously) using a calculated unbound field and then using the Afterupdate event to insert the data into the table. but for some reason it just wont work.
I can get the OnClick to work but then as soon as i go to the next record for some reason it then resorts to 0.00 but then the table shows correctly which ever record the form is working on.
Or should I be using the new query based idea to create the reconcile form and if this is the best way I just don't now where to start on how to get a multiple table query that I can then add a reconciled checkbox which then copies the Order_Total from the table to the Paymet_Recieved field.
View 13 Replies
View Related
Mar 1, 2006
Any help would be really appreciated...
I have a database in the database I have products customers orders etc.
In Products table I have productid price etc. I would like to create another table and this table should consist of all product parts for example.
ProductID x uses
metalnumber23
stone45
plasticnumber43
so all metalnumber23, stone45, plasticnumber43 will be in Parts (new table that i will create) when i am entering a productid to Product table for that productid i should be able to choose which parts from parts table that it uses.
so in the order table if i customer ordered productID x I should be able to see all the parts that ProductID needs in the parts Parts table. There will be maybe 2000 parts but ProductID x will use only 3 of them. And if I create a query lets say order from z date to y dates I should be able to see parts needed for those products ordered.
hope you guys understand and give me a head start i would really appreciete
View 2 Replies
View Related
Mar 15, 2006
Hello,
I'm trying to sum values in a table and the result I'm getting is incorrect.
I have a simple table called Test with one row called Number which is Double. I have 4 values in Number field. They are:
-60000
26.6
2661
62849
When I run a query to sum those fields:
SELECT Sum(Number) As Sum FROM Test;
I get the following result:
5536.60000000001
Interestingly if I change the last number in my table from 62849 to 62848 I get this result:
5535.6
The problem seems to be with summing positive and negative numbers together. In my case, if the sum of positive numbers goes over 65535, the resulting sum is incorrect (adding 0.00000000001 to the result).
Does anybody know why this is happening and if there is a way around this? The actual tables that I work with have a large numbers that go well over 65K so this is a problem for me. I couldn't find anything on google about this.
View 2 Replies
View Related
Nov 22, 2006
What is the best way to implement a search function into a switchboard? I need this search function to allow a user to check a database for specific names, so that they do not enter duplicates. Thanks in advance for all advice
View 7 Replies
View Related
Nov 24, 2006
Hi guys and girls,
I'm fairly new to Access and I'd appreciate some help. I've built a form with buttons to open new forms for that specific record (based on queries as all the data is held in a single table). This works fine until I try to add new records. At the moment the only way I can make this work is to add the new record, shut the original form before re-opening it and proceeding on to the other sections (via the buttons I mentioned).
If anyone could tell me how to get Access to save the record before it opens the other forms I'd really appreciate it.
Cheers,
Franny.
View 2 Replies
View Related
Sep 13, 2014
In my DB that we use and a workflow tool, some of our work has and due date and time.If we get the complete_package our work time starts and we have X amount of time to complete our work. This is something i worked on but set it aside, now i am coming back to try and fine tune this so it returns a more accurate value.
So if we receive and [Date_Complete_Package_Received] at 09/13/2014 09:00:00 AM and based on the work being done we have 5 hours to complete the work, then the [Date_and_Time_Complete_Package_DUE] would be 09/13/2014 02:00:00 AM. That part is simple and i have coding that does that just fine. [SLA_Time] is where it gets the amount of time allotted, we have 5,10,12,14,and 16 hours depending on what is being done.
Code:
Me.Date_and_Time_Complete_Package_DUE = DateAdd("h", [SLA_Time], [Date_Complete_Package_Received])
This is done on AfterUpdate of a field on one of my forms and it works the way it is but what i need is to be able to run this through my Workhours Function so i am not getting values that our in off hours.The following doesn't work i know i can't use the Workhours function with the DateAdd but this is just to show what i am trying to do.
Code:
Me.Date_and_Time_Complete_Package_DUE = WorkHours(DateAdd("h", [SLA_Time], [Date_Complete_Package_Received]))
I have a Workhours module and it is used for a lot of things and it works perfectly.
View 1 Replies
View Related
Nov 12, 2013
I have a command button that basically saves the record that i just finished entering. here's the code:
Private Sub cmdAddAnother_Click()
On Error GoTo Err_cmdAddAnother_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Exit_cmdAddAnother_Click:
[Code] ....
What I'd like to do now is add a second function that will make all the controls on the form go to null after the record is saved. so the user can start from scratch and add another record.
I tried adding me.refresh right before "end sub" but that didnt work.
I tried adding "me.controlname.value = null" for every control on the form, and that didnt work either.
I also tried adding this code that i found on bytes.com:
On Error Resume Next
Dim ctl As Control
For Each ctl In Me.Controls
ctl.Value = Null
Next
That didn't work either.
View 4 Replies
View Related
Oct 31, 2007
Hi Folks,
I have created a file sweeping program in Microsoft Access via VBA in a few modules. The program simply reads certain files in certain directories, and determines if, when, and where to copy those files based on certain user inputs.
One of the features I would like to add would be to upload the files that meet that criteria to a conventional FTP server requiring a username and password. However, the last few days of research all tells me that it is not possible to perform FTP functions in Microsoft Access or in VBA.
I am just wondering if that statement is really true? I figure the worse case is that windows has its own version of FTP.exe that you can execute in a DOS window ... would one be able to perhaps create a command line execution via VBA? Any simple ideas or is it not worth the trouble trying to FTP upload some files via Microsoft Access with VBA?
Thank you very much,
Joe
View 1 Replies
View Related
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 3 Replies
View Related
Oct 2, 2013
Is it possible to open a form to add a child record related to the highlighted record in the subform?
View 2 Replies
View Related
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
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
