How To Group Fields And Corresponding Fields, While Calculating Sums?

Aug 4, 2005

I have two tables - Hours and Employees. Employees table has these fields:
EmpID, and First, and Last name.
Hours table has these fields:
EmpID, Hours, Funding Source, Nature of Work, Pay Period

I need to produce a report or a query that will display these results:
A column with a list of all the employees and all the Nature of Work categories going across with the sum of hours for each Nature of Work. I'm attaching an Excel spreadsheet that shows kind of what I need. Also, these reports need to be produced for each Funding Source and
Pay Period, so something like [Enter Funding Source] and [Enter Pay Period]

My question is that I don't know how to make a report that will just list a column with all the names and count the sum of hours for each Nature of Work.

Hope this is not too confusing, and any help would be greatly appreciated. thank you!

View Replies


ADVERTISEMENT

Need Help Calculating Using Fields In MainForm And Fields From Subform

Sep 25, 2006

Ohh Stress. Need Help....I have a mainform that contained two subforms in it. MainForm called "Employee", the two subform called "vac", and "Prevac". There is a field in my mainform called "Hours", that I'd like to use to Add with a field in Vac subform called "VacA" and a field in Prevac called "FHours". I'd like to display the total (sum of all three field on the same mainform. Is there anyone have any idea? Thanks in advance...

View 3 Replies View Related

Group By Data From Different Tables Based On Two Sums

May 23, 2014

I have 2 tables and one query as below

1- Order Table

OrderID | CustomerID | CusromerName | OrdDate

2- Payment Table

OrderID | Amount | PYMDate

3- Order Deatails Query

OrderID | ProductName | Quantity | Price | Total ([Quantity]*[Price])

I want to group the orders based on the sum of total and sum of payments. I made another query as following :

OrderID (From order table) | Payment (from payments table) | Total (From Order Details Query )

At the Total field I set the above fields to

Group By, Sum

and Sum respectively

I got true payments sum, however, the sum total are always duplicated.

Note that the three objects (two tables and query ) have relation (type : one to many) by the order ID

View 10 Replies View Related

Calculating Fields

Nov 10, 2005

i have two text boxes in a form:
1) Todays Hours
2) Total Hours

when i enter a number in text box todays hours i want the value in the text box total hours to increment by that amount. this operation will be done on a daly basis for a internet cafe where the user comes and i put in the hours. it seems simple but i really do not know. could someone help me please :confused:

View 1 Replies View Related

Calculating Across Fields

Dec 20, 2007

Hi,

I am creating a form which has many fields which should have values in them. I need to know how to total all the fields across each field.

I have managed to do this(almost!) by creating a text box, and "on lost focus", putting the VBA:

TextBox = [SumOfConnection Bonus] + [sumofspice bonus] + [sumofvolume bonus]

(When I get this working I'll make a field in the table so there is no need for the text-box.)

This is basically, three types of commision that I want to total at the end in a "total". The amounts of commision are obviously different for each record but when I click in the "TextBox" it totals the values for the record that box is in ONLY, and all records show the same value in this field. I will click in a different box, and it will change, but ALL of them will change. How do a make it so that all "TextBox" field will show their own value.


Thanks,

View 2 Replies View Related

Calculating Difference Between Two Fields?

Feb 22, 2007

I'm trying to create a database for a Cattery business.

One thing I'd like to include is a query that calculates how long a cat has been staying at the cattery and then calculating the amount owed by the owner.

I don't have a cost field in any of my tables, so I need to create this on every run of the query.

So for example

A Cat resides in the cattery from the 22nd of Feb to the 28th of Feb. So 6 days (not including the day the cat is brought into the cattery) at a cost of 4.95 a day equals 29.7

Heres what I have so far
http://www.tdh1987.co.uk/pics/query.JPG

Bear in mind I'm from the UK, so the date field is in a different format to yours.

Any help will be greatly appreciated.

View 3 Replies View Related

Calculating Bound Fields

Oct 3, 2006

I have a table for invoices that need to show Nett, VAT & Total. I have set up a form and have included a calculation on both VAT (Nett*17.5/100) and Total (Nett+VAT) which works well in the form. Because these calculations are in the ControlSource, I do not know how to have these fields bound to the original table.

Am I missing something really obvious???

View 1 Replies View Related

New Member - Calculating Fields Help

Jan 4, 2005

I am new to Access and have made a databse using Access 2000. This is used for a dart league.

In one of the Tables called Members each "record" is made up of the persons details
(Name,address, etc). Included in the Row record I have "fields" (columns) that keep track of
the points each person hits on a nightly basis. I also have a Total Field.

Exampleof fields)

ID#,First Name, Last Name, Address, Phone #, Week 1, week 2, week 3, Total

How do I get the Total Field in the record to add up the nightly points in Week 1 + Week 2 + Week 3.?

In Excel it would be a simple calculation (e.g =sum(a1+b1+c1) etc.

As I said I am new to this database and it is very frustrating at the moment. I have used the help function
but it mostly double dutch to me.

ANY HELP in plain english would be appreciated

View 1 Replies View Related

Calculating New Fields In Access Database

Nov 20, 2007

I am working on a system to store financial information. I will be extracting our accounts payable journal entries on a monthly basis into Excel. I want to import that data into an Access table. I have several calculations to perform to create new datafields that I need to store in my Access table. Is it possible to perform these calculations as I load the data? If so what the best method to do this? Here is an example. I will be downloading our monthly hardware depreciation and need to split it among departments based on % stored in another Access Table.

Thank you for any help you can provide me.

Teresa

View 1 Replies View Related

Calculating Total Costs From 2 Different Fields

Dec 21, 2005

I have a table with products and their costs per unit of measure.

some products are costed by weight and others by piece.

table looks something like this:

product
pcs
weight
unit of measure (EA or KGS)
cost

Query should look like this:

product
pcs
weight
Total Cost

Is it possible to write an expression that, depending on the unit of measure uses the pcs or weight field to calculate the total cost ??????

View 5 Replies View Related

Query Help, Calculating Multiple Fields.

Jul 30, 2007

I've imported an Excel file into a table and now I've created a Query from it.
I now need to Add Fields (names are not in the table) and calculate totals for these renamed fields some of the answers are going to be the result of two or three fields.
Any help would be greatly appreciated.
thanks

View 8 Replies View Related

Calculating Between Fields In A Table And The Sorting

Jan 27, 2008

Below is an example of my table "Pricedata" include 03 fields:

Stock Name ___Price________Status Date
A________________10________ __1/6/2008_
A________________11___________1/7/2008_
A________________12___________1/8/2008__
B________________22___________1/6/2008_
B________________25___________1/7/2008_
B________________29___________1/8/2008__
C________________2____________1/6/2008_
C________________2____________1/7/2008__
C________________3____________1/8/2008_
D________________56___________1/6/2008_
D________________45___________1/7/2008_
D________________27___________1/8/2008__

I want to create a query like this: can calcualte the price change and % price change in certain period, and then sort in % change.

This result should be like the table below:
StockName Change in three days % Change in 03 days
C 1 50%
B 7 31.8%
A 2 20%
D -29 -51.78%

Thanks all,

View 6 Replies View Related

Calculating Two Fields From A Form And A Subform

May 6, 2005

I have created a form with a subform and I want to subtract the total of the subform from the total of the form to get a balance; i.e. I have a total of expenses on the form and the subform is a record of payments against the expenses and I want to show a balance.

I have been all over this one and can not make the expression work. I have been working in Access for some time but this one has me stumped, I am probably over thinking it or just can't see the forest for the trees.

I have gone into the expression builder and created the formula but when I complete the process I get the #name? error statement in the field.

Can anyone help me with this problem? Thank you in advance. You can send responses to sharon.dunn@newmont.com.

View 2 Replies View Related

Of Saving Queries And Calculating Fields

Sep 6, 2005

Morning All,
Just wondering.

When a form needs a calculated field. Say something simple, like

TotalCost = Quan*Cost

Is it better to create a query?
Use a table and create the calculated field in the query builder?
Or use a table and create a new unbound field on the form with the formula in it?

I never really know which one to do.

Lately I’ve been using saved queries for almost everything and now I have several dozen in this data base and its only half way done. Soon I’ll have to create a data base just to keep track of the queries in this one.

View 7 Replies View Related

Expressions For Calculating Text Fields

May 27, 2014

I am trying to create an expression that determined by text fields. The field is a PASS for FAIL field. The result needs to =PASS if 70% of the total column is PASS.Can this even be done using an expression?

View 4 Replies View Related

Forms :: Calculating Fields And Auto Update

Jun 15, 2013

In this table I have two fields Seats Available and Seats Remaining

When I register a student to a Course in the Courses table I would like the corresponding Seats Remaining field for that course to decrease automatically.

I can, of course, run a report or a query to see how many are enrolled in the course and then manually update the Seats Remaining field (which is what I currently do), but I would like this process to be automated each time I register a new student.

View 4 Replies View Related

Queries :: Calculating With Null Fields In A Query

Oct 2, 2014

My training database requires me to identify each training record in the tblEmpTrainHist table as either "Compliant" or "Delinquent". I thought a simple calculation in my query:

RecordStatus: IIF([DateReceived]<=[DateDue],"Compliant","Delinquent")

would do the trick. However, I did not consider the records where the employee has not yet completed the training and the field [DateReceived] is Null. There are two considerations: those employees who have no [DateReceived], but have not yet reached the DateDue (Compliant); and those employees who have exceeded the DateDue (Delinquent).

View 5 Replies View Related

Calculating Elapsed Time In The Table Fields

Jul 8, 2015

I have been trying for about 8 hours to calculate elapsed time between two fields into a third field in Access 2007. Unfortunately, the expression

IIf(IsNull)([Final Suspense]),DateDiff("d",[Routing Status Date],Date()),DateDiff("d",[Routing Status Date],[Final Suspense]))

is not working. Image enclosed....

View 6 Replies View Related

Using Option Group For Yes/No Fields

Oct 13, 2005

Hi, all--
Perhaps a silly question:

I want to use an option group with a seperate check box for yes and no for a series of yes/no fields. I find that when I do this, once a record is started all have these items have "no" checked. I tried defaulting the value to null and other such things, but to no avail.

Any ideas (while retaining the concept)?

Thanks.

Paul

View 1 Replies View Related

Calculating Number Of Work Dates From 2 Date Fields

May 29, 2005

In my Query I have a Date Received field and a Date Approved field.

I would like to calculate the number of work days, which excludes weekends, between Date Received and Data Approved fields.

It would be more desirable to calculate the number of work days, excluding weekends and governmnet obsereved holidays.. but I don't know if that can be done... If not I can stick with the number of work days excluding weekends.

How would I go about doing this? I would need to add a new column to my query?

What would I put for the field and criteria? What kind of expression do I use?


Any help will be appreciated, thanks

View 14 Replies View Related

Query – Group By 2 Like Fields In A Table

Jun 6, 2006

My table looks like this:
Sales(a)||Date(a)||Sales(b)||Date(b)||Sales(c)||Da te(c)
1) 50 05/06 75 06/15 100 08/15
So I want to show all sales with the criteria - Date >05/01 <07/01.
The final query totals will be
Sales||Date
50 05/06
7506/15
So the issue is that there are 38 rows with similar data in each column
I have the table in this format because I also have a form that feeds off this table and it needs to be in this format-but I would also like to run a query/report from this table that summaries sales. I also know that since there is more than 1 date value in the row, this is going to make the query difficult to run (each date is linked to a certain sales total).
I guess what I want to do is transfer the horizontal data to vertical data (with certain criteria)
Please help!!!

View 1 Replies View Related

Option Group ==&gt; Make Fields Disappear

Aug 28, 2005

hey, i hav seen a similar thread about this (i think bout a year ago or so) and i didnt no whether to continue it or start a new thread.Option boxes - Making items visible
that thread did not fully answer my question and after searchin around n finding nuthin i hav decided to ask

in my form i have the payment methods=> cash, cheque, credit. i did have these in a drop down menu, but i wasnt sure how to do what i wanted...which is based on the selection either make a couple of fields (credit card details etc) visible or invisible. so what i have done is changed the drop down list into an option group with radio buttons.
i currently have this coding

Code:Private Sub Frame_Click()If Me.Frame.Value = 3 ThenMe.CardHolder_Name.Visible = TrueMe.Credit_Card_Number.Visible = TrueMe.Expiry_Date.Visible = TrueMe.Type.Visible = TrueElseIf Me.Frame.Value = 2 ThenMe.CardHolder_Name.Visible = FalseMe.Credit_Card_Number.Visible = FalseMe.Expiry_Date.Visible = FalseMe.Type.Visible = FalseElseIf Me.Frame.Value = 1 ThenMe.CardHolder_Name.Visible = FalseMe.Credit_Card_Number.Visible = FalseMe.Expiry_Date.Visible = FalseMe.Type.Visible = FalseExit SubEnd IfEnd Sub

this seems to work although say if my first record has credit card selected, then all the fields are visible, but wen i move onto the next record which is cash or cheque...they are still visible until i reselect cash or credit again..(and vice versa)

i hope this makes sense :P
thanks

View 3 Replies View Related

Queries :: Sum Option Group Fields In Query

Mar 26, 2014

I had to modify how I was storing data on one of my tables. I changed checkboxes to an option group with the values ranging from 1 to 4.I need to count each of these entries for a report which will run them into another formula.I am trying to use the below - obv I'm doing something wrong.

Code:
Police: Sum(IIf([OType],1,0))

And this is the original SQL for my queries if needed:

qry_BLT_RCCounts1

Code:
SELECT Year([tblBlotter].[EntryDate]) AS [Year], Month([tblBlotter].[EntryDate]) AS [Month], Sum(IIf([OType],1,0)) AS Police, Sum(IIf([OType],3,0)) AS Ramp, Sum(IIf([OType],2,0)) AS Terminal, Sum(IIf([OType],4,0)) AS AOA, ([Terminal]+[Ramp]+[AOA]) AS TRATotal, ([Police]+[Terminal]+[Ramp]+[AOA]) AS TotalChecks, Year(DateAdd("m",-1,[tblBlotter].[EntryDate])) AS PrevYear, Month(DateAdd("m",-1,[tblBlotter].[EntryDate])) AS PrevMonth
FROM tblBlotter

[code]....

View 5 Replies View Related

Queries :: Calculating Rates Based On Two Fields - Type Conversion Failure

Apr 24, 2013

I have created a make table query and using expressions for two of the fields. I have set the field properties (format) to "Percent". The expressions are calculating rates based on two other fields. The expressions are currently as such:

PTD_CM_PGM_RATE: IIf((IsError([PTD_CM_REPT_PGM]/[PTD_CM_REPT_ER])),"0%",[PTD_CM_REPT_PGM]/[PTD_CM_REPT_ER])

I'm using the IIF function to avoid potential errors should one or both of the values ("PTD_CM_REPT_PGM" or "PTD_CM_REPT_ER") be zero.

However, when running the query, I am getting the "...type conversion failure is caused when the data in one or more fields doesn't match...." error.

View 1 Replies View Related

Queries :: Compare Data In 3 Fields In 1 Table / Group By Largest

Dec 4, 2013

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:

Flammability
Rating 4
Product 1
Product 2
Product 3

Rating 3
Product 1
Product 2
Product 3

Rating 2
Product 1
Product 2
Product 3

Health
Rating 4
Product 1
Product 2
Product 3

[code]....

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.

View 4 Replies View Related

General :: Group By Query And Adding Checkbox Fields To Update A Table

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







Copyrights 2005-15 www.BigResource.com, All rights reserved