I have a Main form with a sub form. Every record in the main form has multiple sub form records. (they are linked in a one to many relationship)
Each subform record has a "price" text box. I would like a total of all the "price" text boxes for that main record to be displayed in the main form.
It needs to be updated when a new subform record is added or changed.
Any help is welcomed. Thanks in advance
Joe
Hi,
Attached is a project I'm working on. We want to calculate the total delays on each day. In a form called 'main' there is a subform for the delays on that date. But the 'Total Delays' control does not work.
I have used the following format to get the total from the subform:
=Delays.Forms!TotalDelay
'TotalDelay' is the name of the control which calculates the total of the delays within the subform.
I have used the =[Delays].[Forms]![TotalDelay] format in other projects and it works. However in this program when I want to save the form, the control source is changed to :=Delays.Forms!TotalDelay (the brackets are removed)
Thank you
I quess it should be simple. But I couldn't find an answer or example in Access books and online.
I have an access 2000 database with a Accounts Receivable table. I am building a query that returns accounts what are 60 days past due and calculating total ballance of ONLY past 60 days accounts. I managed to write a code to display 60 days past due acounts, but when I use UNION query, it calculates total from entire Accounts Receivable table, but I need only total ballance of past due accounts.
Here is my SQL code
SELECT [Accounts Receivable].InvoiceID, [Accounts Receivable].[Patient Last Name], [Accounts Receivable].[Patient#], [Accounts Receivable].InvoiceDate, [Accounts Receivable].PaymentAmount
FROM [Accounts Receivable]
WHERE ((([Accounts Receivable].InvoiceDate)<Date()-60))
UNION SELECT 'TOTAL', "","","",Sum([Accounts Receivable].PaymentAmount)
FROM [Accounts Receivable];
I need my query to look like this.
InvoiceID Patient Last Name Invoice Date Payment Amount
23 Smith 05/01/2007 $100
25 Doe 04/03/2007 $200
Total - - $300
Please help.
I am currently making a database project for a school assignment.
I have based the project idea on a local hotel, which consits of a diffrent price dependant on the date booked.
So i have set up a table called "prices" which consits of the following fields:
PriceID
RoomID *
Price
Start Date
End Date
the RoomID is the foreign key, as it is linked (one-to-many) with my Room's table, which consists of the following fields:
RoomID *
Room Number
.. (unecessary fields for the problem)
The room ID is also linked to the bookings table (again one-to-many) with the bookings table consisting of:
BookingID
CustomerID
RoomID
Start Date
End Date
... (uneccessary fields)
what i am hoping to do is (i assume) make a query that will calculate the cost the customer has to pay. So it will look at the start and end date booked in the bookings table and comapre it to the dates in prices table, and find out the cost from this....
any help on going about this would be greatly appreciated, and i hope i have supplied sufficient information (i'll try attach a graphical image of the relationships if that is helpful?)
thanks
michael
hiYA everyone!
in my report footer, i want a number of records displayed
in the report. how do i do that? my report is based on a query.
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 ??????
I need to provide a report on data over a fiscal year that has the following format:
apr 03
may 03
jun 03
q1 total
jul 03
aug 03
sep 03
q2 total
oct 03
nov 03
dec 03
q3 total
jan 04
feb 04
mar 04
q4 total
year total
I can group by quarter and sum() in the group footer which works, but when I group by year I get totals for 03 and 04 separately. I have also tried sum() and dsum() in the page footer, which gives #error, and vba in page footer:
Dim pageTotal As Long
Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
' Add to Page Total
pageTotal = pageTotal + Me![ClientsServed]
End Sub
Sub PageFooter_Print(Cancel As Integer, PrintCount As Integer)
' Show Page Total Price
Me![yrClientsServed] = pageTotal
End Sub
which calculates properly in the detail section (var watching "pageTotal") but outputs nothing in the footer, though the name of the text box in the footer is correct.
If anyone can see an error in what I'm doing or suggest another approach, I would really appreciate it, this is driving me nuts.
I have a personal expenditure data base indicating daily expenditure on various heads ( File Attached). I want table to reflect expenditure on various heads on daily basis in COLTOT column. Any subsequent change in any of the field to be dynamically updated.Presently I am exporting file to excel and calculating the total there and then manually updating the COLTOT field. Other alternative I tried was to create a form and make a text field and sum the fields using formula like =nz([milk])+nz([vegetables]). I feel it may be unwieldy for such large number of expenditure heads.
View 14 Replies View RelatedI have a table in access that has 1 ) persons ID and ) log date/time 3) direction in or out
I need to calculate the amount of time spent by the person in the office. e.g User with ID 1 will come in at 8 in the morning and go out after an hour then come in again and go out. Till he leaves out for the day. I need to find the first time he came in and last time he went out and find the difference for the hours he worked
The data is in Device logs table.
Look at this query:
http://i237.photobucket.com/albums/ff304/hankymskdghdfag/qryUvoz.jpg
I wan't to calculate the price totals but for each instance of Uvoz ID separately. I've sucessfully used Dsum function but I managed only to calculate all price totals for all instances of Uvoz ID. Is it possible to do it for each number of Uvoz ID separately? How do I do it?
Hello my beautiful worldwide friends :D
I am trying to calculate employee Overtime hours from their recorded TimeIn and TimeOut if over 7.5 hours. So anyday they work past 7.5 hours should be calculated and totalled at the end of the week. And i can't seem to figure it out, maybe my maths is bad? Maybe it's my query? The section of my report? What could i be doing wrong here, i have spent hours and im getting no where.
I have attached my report.
Can you help me figure out why my daily total shows but the weekly total is not showing?
I am using the following UNION QUERY to total up equipment tested for a report.
SELECT "Laptops Tested" AS PCEQUIP, Count(*) AS RECORDS
FROM [LAPTOPS]
WHERE (((Date)Between [Enter Start Date] AND [Enter End Date]));
UNION
SELECT "Workstations Tested" AS PCEQUIP, Count(*) AS RECORDS
FROM [WORKSTATION]
[code]...
I have created a report using ACCESS 2000 for this union query and it satisfies the requirement. I am trying to add the proper code and syntax in this query to total the number of all of this equipment tested.In this case the total would be 86. Is this possible?
I am trying to create a running total query that aggregates project funding by fiscal year. The fiscal year is calculated based on a date time field that is never null. The totals field comes from 2 different number fields that are either 0 or > 0. The query is going to be linked to by Excel, so I have to do the running total in the query itself, vs. a report.It is close to working, except that it is not totalling the first fiscal year. The output surrently looks like this:
FYear BudgetedCostIndCont Commitment
2010
2011 8585643 4742000 3843643
2012 2297116511432165 11539000
2013 3618726216963282 19223980
2014 4457769020706644 23871046
2015 4963815023206644 26431506
As you can see, the first row for FY 2010 is blank. I know there is data there, as this query is fed by a subquery that selects these rows based on contract signed date. Below is the SQL of each query:
Code:
SELECT Year([DateContractSigned])-IIf([DateContractSigned]<DateSerial(Year([DateContractSigned]),4,1),1,0)+1
AS FYearExport
FROM tblProject
GROUP BY Year([DateContractSigned])-IIf([DateContractSigned]<DateSerial(Year([DateContractSigned]),4,1),1,0)+1, tblProject.ProjID, tblProject.FPAccepted
HAVING (((tblProject.FPAccepted)=True));
and the Aggregate query:
Code:
SELECT qryDashboardChart1.FYearExport,
DSum("[BudgetedCost]","tblProject","Year([DateContractSigned])<=" & [FyearExport]-1 & "")
AS RunTotBudgetedCost, ([RunTotBudgetedCost]-[RunTotTECTERRACommitment])
[code]....
I should also mention that I cannot implement the NZ() function, as Excel balks at this when trying to link to Access queries.
Access Query. I am creating a time sheet / pay roll database and I want to be able to get a total of the daily hours in a query.
For example I have 'Mon Start' and 'Mon Finish' for Mondays in/out times and I have a 'Mon Total' which gives me the total hours worked for Monday.
The problem I have is that Mon Total only works if the hours are say between 07:00 and 17:00, anything after midnight (00:00) like 21:00 to 07:00 and 'Mon Total' goes crazy !!
At the moment 'Mon Total' is the result of CDate 'Mon Finish' - 'Mon Start' (bit rough I know).
I am using calculated field as a data type in access 2010.
They are working fine.
However, I added a new field and now the final calc won't work.
I have Subtotal adding loads of fields together. Works fine.
Then I have a VATunit field which is a double integer, so enter 20 and my next field is VATTotal calculates the SubTotal + the VATunit by doing (Subtotal/100)*VATunit. This calculation is fine and gives me the correct amount.
The next field is a Total field. Which adds Subtotal and the VATTotal together. Howver, the Total is the same as Subtotal. It is not adding the VATTotal to it?
I have a database consisting of two tables. One is "articles" and the other is "tasks". To put it simply, I would like to find how much time the article spends in tasks, but one article can have many tasks, and they often (but not always) overlap. Tasks have a start and end date field.
View 8 Replies View RelatedI have created a form with company ID, company name, and so on. I put a subform in the main form which includes invoice & points information. Somehow I need to calculate a balance for each customers points. In the form I have points earned and points redeemed...how would i get the totals for each customer's balance?
The main form is linked to the subform, so that you can see each individuals record of points. Please let me know how I can get the balance for each customer.
Thanks.
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.
New to Access Form Design. Trying to create a form that will display the amount that is owed to an employee.
Our office gives its employees a $125 supply allowance each year. When an employee submits a receipt for a partial amount (say they spend only $35), I enter the amount in the PurchaseAmount field of my subform.
The ReimbursementField control source is =IIf([PurchaseAmount]>=125,125,[PurchaseAmount]).
The correct amount of $35 is displayed.
(There is no beginning balance or ending balance field on the form.)
When a subsequent purchase amount is entered and is over the amount of the remaining balance, (let's say spent $100 and only has $90 remaining) how can I have the form display the correct amount of $90? The closest answers I came across suggested using recordset clone in an AfterUpdate event, but I just can't figure out the proper way to write the calculation. My main form name is "Supply Purchases"; subform name is "Supply Purchases Subform.
I have a form with general information and then a subform where I enter items, price and quantity. I want to see the total form the lines that I have enter and keep the total up to date as I enter new lines.
Can anyone explain how to do this? I have tried a few things, but none give the result I would like...
Ugggh, I think the moral of the story here is, don't drink on a work night . Anyway, I have this form with subform (this is basically an order form) that I can't get the total of the subform to the main form.
The Form is named "Place Order"
The subform is name "order details"
Ok, so in the subform, I have quantity and price, and then a textbox named "ttls" with the multiplication of both:
nz([quantity])*nz([price])
Then in the footer of the subform I have a textbox named "subttls" which has the following for its control source:
=sum( [ttls] )
In the main form, I have a textbox named "Subtotals" which has the following for it's control source:
[order details Subform].Form![subttl]
When I run the form, I get #Error in the "Subtotals" textbox. I've done some basic error checking.... the value of [order details Subform].Form![subttl] is not null (I used an iif statement to check this), and it is not numeric (used iif with isnumeric). I can't get the value of [order details Subform].Form![subttl] to display anywhere, so I'm guessing that it just holds the value #Error.
Again, this is probably something simple, but right now my head feels like it's the size of a beach ball . Alright AOG, I know this is right up your alley
Heh, believe it or not, this is the first time I've ever tried pulling a total from a subform to the main form. OK, maybe you can believe it
On my master form I have a subform called invoices this is tied by Site Number to the record on the form which is has the following important information, site code. group code, exp code an amount.
On another subform via a query I have a summary of all the invoices by exp code and group code.On another subform I need to create a Income an Exp.
I have two combo boxes 1 for the Group Code, and another for the expense code what I want it to now do is look up group code exp code = Total amount. There is currently only 1 line as you will see but there will be many rows of summary data...do i need a subform for the summary?
And I need to do it this way as not all exp appears on the income and exp and I want to be able to lay out the expenses for each site differently Attached is the zip file, of the database the only record that has data is site code 0289S
ok guys..
i dunno what exactly happen..i think i done it at right way..
ok..i create Main Form that contain 2 (material and workmanship) subforms. subfrom i create using wizard from query
then i use =Sum([TotalAmount]) at subform footer to calculate total price from selected part (i create workshop's part management system) n it works fine..for both subform..
but another prob here..
at Main Form that contain material n workmanship subform. i want to calculate Grand Price, where it from TotalAmount from each subform..
i create 2 dummy control (to hold TotalAmount from each subform,coz i think its hard to make sum for 2 TotalAmount), 1 for material n 1 for workmanship..
then i want to sum 2 dummy control into Grand Price box...
but when i use =MaterialSubform.[Form]!TotalAmount at dummy's ControlSource it failed.. show #name?..
y? am i missing sumting at my Main form or subform? plz help me..
i want to send attachment but my fileto big to send...
*(i hope u can understand, coz my english not good ^_^)
Hi ALl,
I have a subform that accounts and adds all the expenses for a certain project (for example, total is $27,000). I would like to know how to duplicate the final cost in the subform and show it on an unbound textbox of that linked main form (the unbound textbox in the main form shows $27,000 also).
Thanks in advance, guys...
Caliboi
After having no luck with the standard datasheet E-Totals, i'm now trying to have a textbox on the main form that totals a subforms column called lineTotal.Line Total is a calculated query field that works out the Qty*cost.So say if they put in 5 lines on the subform, the textbox field will sum all of there linetotal and shows a rolling grand total.
View 10 Replies View RelatedI am trying to create a time sheet that enables my workers to fill out separate work orders and the hours from those Work orders are auto transferred to a sub form within their daily time sheet. I have gotten all this done and I have the total hours transferring to the main form but I need to use that transferred number in a new sum on the main form. I know that this cannot be easily done just by using the expression builder.
View 10 Replies View Related