SUM Query For Field
Dec 7, 2006
Hi all,
I attached a screenshot below
What i would like to get done is calculate the sum of the row NUM when every field except SERIAL_NUMBER has the same record.
So for the screen shot it would calculate 55+57 for the first 2 rows, 15+16 for the second 2 rows etc... and display them in a new column Named SUM i.e
SERIAL_NUMBER | PRECINCT | CONTEST_NAME | CANDIDATE_PARTY_NAME | CANDIDATE_NAME | CONTEST_ORDER | NUM | SUM
9200|7000003|Referendum 1| |YES|195|1|55|112
9218|7000003|Referendum 1| |YES|195|1|57|112
9200|7000003|Referendum 1| |NO|195|2|15|31
9218|7000003|Referendum 1| |NO|195|2|16|31
Is there any way to do this.
Here is my query for the ss
SELECT DISTINCT Turnout_machine.SERIAL_NUMBER, Pct_Pollnum.PRECINCT, Canvass.CONTEST_NAME, Canvass.CANDIDATE_PARTY_NAME, Canvass.CANDIDATE_NAME, Canvass.CONTEST_ORDER, Canvass.CANDIDATE_ORDER, Canvass.TOTAL AS NUM
FROM (Turnout_machine INNER JOIN Canvass ON Turnout_machine.SERIAL_NUMBER = Canvass.SERIAL_NUMBER) INNER JOIN Pct_Pollnum ON Turnout_machine.PRECINCT_ID = Pct_Pollnum.PRECINCT_ID
WHERE (((Pct_Pollnum.PRECINCT)=[forms]![Canvass]![pctID]))
Thanks once again to everyone that helps.
Take care
View Replies
ADVERTISEMENT
Mar 4, 2015
Here is what I am trying to do. I have a query with 2 fields. "Time In" & "Time Out". What I would like to happen is this. Whenever a character, let's say a "t", is entered into that field I would like the current time to populate that field. Right now we are actually typing in the time. I have the fields set up as DateTime fields currently.
View 10 Replies
View Related
Jul 31, 2014
I currently import data into a table and it it has a text field which looks like this: 12,345.67 GBP...I need to use a query to make this a number field so that I can sum it's contents. I've managed to remove the 'GBP' part but can't seem to get rid of the comma?
View 4 Replies
View Related
Jun 28, 2013
I have two tables that are joined - called A and B. A has two fields, "PLACE" and "RAND" and is joined to B via field "RAND". Other than "RAND", B has several fields named 01 TOTAL, 02 TOTAL, 03 TOTAL, etc...for about 60 fields.
The "PLACE" field in table A has data that is 01, 02, 03, ect.... How do I structure a query so that whatever "PLACE" is, I can match the XX TOTAL value? In other words, i need to have the query field header be somehow dependent on the value in "PLACE".
Is this even possible?
View 4 Replies
View Related
Apr 24, 2013
I’m developing a claim tracking database that tracks dates of events that occur in the course of processing a claim; such as, Loss Date, Report Date, Estimate Date, Payment Date, etc. There are 16 different “Events” in all.I currently have the following tables set up:
tblClaim
ClaimID
ClaimNumber
fkEmpID
tblEmployee
EmpID
EmpName
[code]....
What I need to do is create a form where management can choose two or more events, and calculate the average number of days between two of any of the events, for an employee, or all employees.I have created a crosstab query to change the values in the EventName field in tblEvents to field names, and the EventDate as values for the related EventNames. I created another query based on this query to do the DateDiff.
I created combo boxes on my form with the Row Source Type set to Field List, for a list of fields in my crosstab query. I’ve tried to use the following DateDiff function to get the days between the two fields selected in my combo boxes:
Code:
DateDiff("d",[Forms]![frmReportBuilder]![cboEvent1],[Forms]![frmReportBuilder]![cboEvent2])
But I get an error about unrecognized field name or expression for my combo boxes. So I added my combo boxes in the query parameter window, with a data type as both text and value, but with both I get an error “This expression is typed incorrectly or is too complex to be evaluated.” I also specified the column headings in the crosstab but I still am getting the “too complex” error.I’m pretty sure it’s trying to do a Datediff on the literal values in the comboboxes and not recognizing that I’m trying to specify field names.Is it possible to assign field names in DateDiff this way?
View 1 Replies
View Related
Apr 25, 2013
I have 3 peices of data that I am working with: Group number, plans and benefit elections. On my form I have fields for the group number and plan and 2 queries. When I enter a group number the first query displays a list of plan descriptions for that group. From there I can enter the plan description into the form's plan field and that runs the second query to give me a list of valid elections for that plan. This all works fine but I want to make the plan selection a little less tedious. What I would like is to be able to double click the query field housing the plan description and have it copied to the form's plan field. I could use the ID instead of the plan description but I work in a production environment and very key stroke counts so I would really like to have a simple double click process.
Is this even possible? If so how would I set that up? I tried using the double click on event macro builder but it does not seem to have this kind of option.
My form contains fields for group number and Plan. The same form houses 2 querys, one that pulls plans based on the group number and the other to pull elections based on the plan field (not the plan query). Would like to double click a plan within the plan query and have that description populated into the form's plan field.
View 6 Replies
View Related
Nov 18, 2005
As the title says, i have an identifier field which combines two codes eg. AAAAAA/1234.
I am using a query to extract the number part, however when i try to link to another query it says 'type mismatch'.
I assume that this is because the main query is based on a table where [ShipID] is numeric and the extracted data is based upon a underlying table where [PackageID] is a text field.
Is there any way to get round this?
Many thanks
View 2 Replies
View Related
Feb 28, 2007
Hi,
I have a text field in a table and need to convert it in a query to a number field for sorting purposes, but not have it change in the table.
Thanks,
Jeff
View 3 Replies
View Related
Oct 8, 2014
I'm trying to create a new field based on two existing fields as part of a select query. Two tables in the query each have a "HOSPITAL" field which is an indicator for "Y" or "N" for each department (in the DEPTLOOKUP table) and for each facility (in the Facility Lookup table). I want to create a field whereby a new indicator is created so that a Y is given for each record only where the facility and department HOSPITAL indicator field is both "Y". I have attached a word document that has a screenshot of the query in design view as well as the access sql code.
View 3 Replies
View Related
Jan 13, 2015
I need to create a query in Microsoft Access to enter 1 or 0 in a field based on whether numbers in another field is positive or negative. Here are the details.
1 entered in a "late" field if "TotalHoursAheadOrBehindScedule" field has a positive number
0 entered in a "late" field if "TotalHoursAheadOrBehindScedule" field has a negative number
View 2 Replies
View Related
Jun 6, 2014
I am trying to run graph on a report from query but what's happening is query shows name but report on graph shows ID'S of the field .
Below is my query
SELECT tblMainTWTTPSheet.txtRootCause, Count(tblMainTWTTPSheet.txtRootCause) AS CountOftxtRootCause
FROM tblMainTWTTPSheet
GROUP BY tblMainTWTTPSheet.txtRootCause, tblMainTWTTPSheet.Date
HAVING (((tblMainTWTTPSheet.txtRootCause) Is Not Null) AND ((Count(tblMainTWTTPSheet.txtRootCause)) Is Not Null) AND ((tblMainTWTTPSheet.Date) Between [Forms]![frmStratificationOfRootCauses]![startDate] And [Forms]![frmStratificationOfRootCauses]![endDate]));
My bound column on main menu form and back end table is 1
column count 2
column width 0;1
View 1 Replies
View Related
Oct 13, 2005
hey guys . .
I have 3 fields: TEST, NETQTY, EXTENDED
NETQTY usually = EXTENDED depending on the TEST
BUT the Test PTCGCD has a EXTENDED value that is TWICE the value of NETQTY
How do i create a query which will update the EXTENDED field depending on the TEST value>?
For Example:
The general TESTS: AFP, ANAS, CYC etc . . EXTENDED = NETQTY(1)
PTCGCD: EXTENDED = NETQTY(2)
?
View 1 Replies
View Related
Oct 19, 2006
I have two fields the have differing dates in them. I need an action query to make the date in one field the same as in the other. I have thousands of records to do this to. Is there a way via an action query (update?) to do this or does it have to be an SQL statement query?
View 2 Replies
View Related
Aug 17, 2006
I have sequence field in query and want it show in form.
Please help me. Thanks
View 1 Replies
View Related
May 11, 2013
I have a query where I prompt for a Report_Date to create a new field; Report_Date:[ Enter date for report]
Is it possible to use the result from this prompt as a criteria in a different field?
for example, Order_Date based on the criteria of <=[Report_Date]
View 4 Replies
View Related
Dec 12, 2013
I have a Form that has a subform and in that there is a subform that runs a query. I need to take the value of in one of the fields returned in that query and transfer it to a field in my original subform.
View 4 Replies
View Related
Jul 15, 2014
I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?
View 9 Replies
View Related
Mar 8, 2005
I'm building a report for annual software license renewals. The report data source is a query that combines the customer information, their computer information, and the licenses purchased for that computer. I am having no trouble with the form displaying the customer info page, then a page with the computer info at the top and a list of licenses purchased for that computer underneath.
That would be great, if that's what my boss wanted. However, she wants the whole list of available licenses displayed on each page, in the event someone want to purchase additional licenses with this year's renewal.
I'm trying to figure out how I can set up a query/report grouping to do that. I've tried making a new query, relating the qryLicense!licenseID to the qryPurchase!purchLicense and setting the relationship option to show all records from qryLicense and only those related from qryPurchase. I added the qryPurchase!purchCPU field to my query, hidden it, and set the criteria to =1 (the computer ID of one of my dummy computer records). I also have a Sum of the qryPurchase!purchQty field included in the new query that I want to display the total number of that particular license purchased (and 0 if there are no corresponding records). All fields except for the quantity field are set to Group By.
What Im getting from this is simply a list of the licenses purchased for that computer, not the complete list of licenses available showing the quantity purchased where applicable.
Can someone see where Im going wrong?
Slaughter
slaughter at mizzou dot edu
View 9 Replies
View Related
Apr 2, 2008
I am trying to use a single record and cell of data generated from a query as criteria in another query but can't figure out how to do it? Is there a way to reference a query field in the criteria in design view of another query?
Details:
I have a table of data for each month with supplier codes in each table, but no date field.
I am trying to build a query that will automatically pick the most recent month of data. I have built a query that uses an iif statement in SQL assigning a number to the most recent month of data and then I am using the max filter in that query to show the single highest number (But this logic misses suppliers some suppliers but does assign a number to the most recent set of data). In this case its 2 for february data. I want to use this digit to filter a seperate criteria that will show me all the supplier codes for the month of february. One problem is that some suppliers are in every month others, come and go throughout the year, so I have to assign this criteria for each month. Therefore I want to use the 2 from the first query and plug that into each criteria section under each month field of the second query.
The other option that I can think of but can't seem to find a fix, is to merge all 12 tables so there are duplicate entries and can have a date or number assigned for each month that the supplier code shows up, then just use the max number. I can't seem to find a way to add all the codes creating duplicates into one field. I used a union query but there are no duplicates with this method thus foiling my plan.
Please help
Thanks for reading
View 1 Replies
View Related
Oct 6, 2005
Hi
In query:
I want to make table with many records with one field.
For example:
I make table to store many records, but I want to add one field at last column. This column is Yes/No (Click box). This column default value is No. How can I add this column in the query. Because Table has this option Yes/No (click box). So, I think that we can do in query.
Thanks.
View 1 Replies
View Related
Apr 26, 2006
I have 2 tables and i am using append and delete queries to move and delete records from table1 to table2.
When i append from table1 to table2 i want to add in a field to table2 with the current date which will be date of deletion.
I am unsure of how to add this in the append query. Could anyone help.
I am using access 2003
thank you
View 4 Replies
View Related
Aug 30, 2006
I have two tables, Group and Person.A group can hold one or more persons, linked by a field called GroupID.And a person can be a leader of a group.I want to make a query to return the leaders of the group, which I can do, ie. where tblPerson.Leader = true, but I also want to calculate the number of persons in that group.Any ideas or if you need more information, please let me knowBobThinking about it, I suppose its more of a count function than a sum function, but I've never been the best with queries
View 1 Replies
View Related
Aug 22, 2007
Hello,
I would like to reference a field value in a query, and not sure how to do this!
For example, I would like to query all purchases by [myTable].[my_field]. When I try this in a query, Access keeps prompting me for a value and doesnt use the actual value in the table.
How might I achieve this?
Many thanks,
dj_T
View 12 Replies
View Related
Sep 17, 2007
Friends,
I am working on creating a donor-ranking report that displays the top 25 donors to our organization based upon a year of giving records. I have a query that displays the contributions made to our organization and it sorts by the donor number. I have no problem showing only the sum of each donors giving. Thus, it only shows their name once and the sum of their contributions for the period of time I specify (start date - end date).
What I cannot figure out is how to sort by the sum calculation I have on the form (=Sum([Amt]). I think it might be easier to set up my query with a new field to do the summing so that I can sort based upon this field, but I have no idea how to set this up. I did a search of this forum and found three posts, but none help me.
I would like to know exactly what to put in the field that I am creating in the query.
Thanks so much,
Mike
View 1 Replies
View Related
Oct 15, 2007
Can anyone help me create a query to do the following:
I have to fields in a table and want to create a thrid field depending on the values in the other two.
What I need to do is select all the values from column2 and all the values from column1 when there is no coresponding value in column2.
eg.
Column1 Column2 Column3
1 158 158
0 45 45
34 0 34
Any help would be appreciated.
View 1 Replies
View Related
Feb 14, 2008
Hi,
Sorry if this is super easy, I'm just not sure how to get it done.
I have two tables. [Charity] is a line detail, so there are multiple using the same Customer Number. [Summary] is a table holding unique customer details including how much they've donated.
So I have a field called "Total" in Summary (purchased items). I want to grab the SUM of charity values from the [Charity] table and add that value to the "Total" field which is already populated.
Ive linked the two tables by CustomerNumber, and I've tried grouping the totals in [Charity] and adding them like this:
Code:[Summary].[Total] = [Summary].[Total] + Sum([Charity].[cost])
I also tried from the designer view but couldn't get it to Sum([Charity].[cost]).
Any ideas would be greatly appreciated, I feel really silly not getting past this on my own lol
View 14 Replies
View Related
