Complicated Formula For A Report
Dec 1, 2006
hi
i wondered if anyone could help me with a formula i am trying to construct.
i have a report which is based on a query where the user types in the ID of the project they want to view.
here is a screen shot of the report
http://i132.photobucket.com/albums/q19/lost989/formula.jpg
i wondered whether it was possible to have a calcualtion to show how much was spent on different categories.
so there would need to be a formula to calculate all those records named as timber thier indivdual cost times the quantity of each. i need this for each category of items.
is this possible?
any help is appreciated
thanks
View Replies
ADVERTISEMENT
Mar 27, 2015
I want to be able to sum my net amount and other costs in my page footer but it is error why would this error in the page footer =Sum([net amount]) if I did that would it still give the total for more than one page on every page.
View 5 Replies
View Related
Dec 15, 2004
Hi
I would like to count number of items witin a text field,
but breaking it down identifying the different items within the
text field.
e.g.
Got a field with fruits listed. Now I am identifying the fruit but want
to know how many rotten fruit there was for each specified fruit.
At the moment my query is by date and the fruit including the column
specifying if the fruit is rotten or not. But there is 3 options in the last
column. How can I count this last column to count how many of these
3 options there are for each fruit in my report?
Please let me know if this makes any sense or not otherwise I will try to explain
it a little bit better.
View 1 Replies
View Related
Jul 1, 2013
I have an access report that there is One column among 9 of them, refuse to give answer.. Its suppose to Sum all the total of each line in the column. See Attachment. This report is group by ID and sort by Shift. The formula is: Sum([Rates]), and the Rate is list in Details Report where the Sum ([Rates]) is located in Id Footer section.
FYI, when I ran this report, it does not know what the Rate is. However, the rate is the formula which listed accordingly in the report details.
View 3 Replies
View Related
Oct 21, 2015
So I have a company where the bonus amount for a calculation can change quarterly - if a person accomplishes 50-100% of plan they get that % of their bonus amount.
I have that working on a variable detail DB where the historical data is correct for the report.
i.e. if I want to look at January - the report looks at the requested date: January and calculates using the bonus number from the last update made before January (year is also factored in)
So: January 2014 if they make 50% of plan and their bonus is $100 this month - they receive $50
Good - no problem
NOW: Every year the formula on the report Could Change - so next year if the person makes 50-100% of plan and 30% of secondary plan - they get 30%(% of Bonus)
So now: January 2015 if they make 30% of secondary plan and 50% of plan with $100 bonus the report would give .30*(.50*100) = 15
I can change the calculation on the report - BUT then how would I go back and accurately show what they got in January 2014
Would it require a different report per year?
View 1 Replies
View Related
Apr 6, 2007
Hi All:running Access 2000.I have a qry with a formula field that performs some calculation (Extension: CCur(([OnHand]*[Price])/[UnitsPer]))and it works fine. How can I update the actual table with the result of the calculation for every record? The problem I have is that there's some null values in the results therefore when I try to get totals on that field, it errors out saying "invalid null use"thanks
View 7 Replies
View Related
Nov 20, 2006
I have a query that calculates input information into a value that then needs to be compared to another query values and will be used to output a % change in a third query. Is there any way to make this happen? Thank you in advance!
View 2 Replies
View Related
Jul 19, 2006
Hi gurusI'm looking for query to extract records from one table linked to another table based on different ranges. Now here goes.SELECT Invoice.dbInvoiceNum, Invoice.dbInvoiceDate, Invoice.dbFirstName+' '+Invoice.dbFamilyName) AS dbPatient, Invoice.dbdob, Invoice.dbServiceProvider, InvoiceItem.dbItemNum, Invoice.dbPayerName, Invoice.dbTotal, Invoice.dbTotal-Invoice.dbBalance AS dbPaid, InvoiceItem.dbServiceTax, TransactionLog.dbTransCodeFROM ((Invoice INNER JOIN InvoiceItem ON Invoice.dbInvoiceId = InvoiceItem.dbInvoiceId) INNER JOIN LEFT JOIN TransactionLog ON Invoice.dbInvoiceId = TransactionLog.dbInvoiceIdWHERE (dbInvoiceDate Between #4/1/2006# And #4/30/2006#) AND (TransactionLog.dbTransCode <> "CRDC") AND (Invoice.dbActualTotal > 0) AND (InvoiceItem.dbItemCode Between '57506' AND '58115') AND (InvoiceItem.dbItemCredit = False)ORDER BY Invoice.dbInvoiceDate, Invoice.dbInvoiceNum, TransactionLog.dbTransCode DESC;Now problem from above query is that I not only want invoices with items from the range(InvoiceItem.dbItemCode Between '57506' AND '58115')also want invoices with items in range of(InvoiceItem.dbItemCode Between '1' AND '5267') AND/OR (InvoiceItem.dbItemCode Between '57506' AND '58115')Invoices with items could either be just in range of '1' AND '5267' AND '57506' AND '58115'.I am not interested in invoices in range '1' AND '5267' without the range '57506' AND '58115'. For every invoice in the range of '57506' AND '58115' it will have a range '1' to '5267'.The above query just rips out the invoices with items in the range '57506' AND '58115' and showing duplicates and not the items also contained in range '1' to '5267'. Writing separate queries and extracting and looping takes far too long especially if total database dump was involved. It could take 20 mins to create report due to volume of records could be somewhat over 300K. I have hope and done before in one query that takes little time, however this seems little complicated.Any help would be much appreciated.
View 13 Replies
View Related
Apr 7, 2008
Hi, I hope that some one can help. I have column that ends with (1232) how can i extract only the 1232 for example,moved car to blablabla(1232) I want to create a new column and insert only what's in Parentheses. thanks in advance.
View 12 Replies
View Related
Apr 18, 2006
Hi, I have a couple questions about a project I am trying to work on right now.
I am very very new to Access but am learning quickly. I just wanted to run this by everyone and see if any of you had any ideas.
The goal of my database is to track patients from our clinic. I have it set up as follows:
1 table containing basic patient info: Med record #, name, date of birth, age, gender, family history of cancer, etc etc etc.
1 table listing all the possible diagnosis of cancer that we treat in our clinic
1 table listing all the procedures that are performed by our surgeon
--------
Here is the issue. I can enter all the patient info with NO problem. All my lists populate nicely and I have dropdown boxes to make it easy to choose items, etc. I cannot, however, figure out exactly what I should do if a patient has multiple surgeries, multiple diagnosis, and even multiple testing types done on tissues from each of those surgeries.
How on earth can I set up a form so that I can enter all the basic patient info. Then enter a procedure and a diagnosis - then enter a second procedure and a second diagnosis (though the diagnosis would most likely be exactly the same as the first procedure). I don't want to have 3 records for each patient if we perform 3 procedures during one surgery visit. If the patient comes BACK for another surgery, that is a different matter.
Sorry for the long post but I appreciate ANY help anyone can provide.
View 4 Replies
View Related
Jun 16, 2007
Hi ,
say I have a table:
video: id, title, artistname, countryid
that has the values
1, 'VideoA','Artsit123' ,4
2, 'VideoB', 'Artsit567' , 5
3, 'VideoC', 'Artsit167' ,6
4, 'VideoD', 'Artsit890' , 7
5, 'VideoE', 'Artsit468' , 8
6, 'VideoF', 'Artsit752' , 9
And another table country: id
that has the values:
1,4,5
Basically is there a query that can be written for me to check if ALL the countryids from the country table will return an artist, and if not can it let me know. For example obviously there is no countryid with 1 in the video table so Im not interested in that batch in the country table. I know I can just execute the query 3 times to see if a result is produced but what if the country table is massive (btw the country table is generated dynamically using xml). Any help on this would be very mch appreciated
View 2 Replies
View Related
Nov 14, 2007
(Hope this is in the right place)
Hiya Guys,
I am not new to Access, but have only before created basic databases with simple structures and I am having problems with my latest project.
The idea is to have two tables, “tblStudents” and “tblClasses”. I also want to have a form called “frmClasses” and this is proving a bit of a problem. I am trying to display the class data for each class record on the form (Time, date, details), but below that want a table containing all of the students which are registered to that class, which at the moment is just causing me to go round and round in endless circles.
Sometimes, the form just won’t load and after I change it, it will just put in duplicate records containing the same class data but a separate record for every student in that class.
I have a relationship set up in the database which links the field “ID” in students to the field “student_ID” in Classes. But I think that the problem may be there. I can post a copy of the database if you want.
Any help appreciated,
Chris Farrants
View 2 Replies
View Related
Jul 22, 2005
im am making a database to keep track of the soldiers in my company. right now i have come across a problem and am having troubles with entering the phone numbers. i have three tables for this purpose;
soldier(SoldierID, SSN, LastName, FirstName.....)
phone(PhoneID, Number, Type (cell or home))
soldier_phone(SoldierID, PhoneID)
in my main soldier form which uses the soldier tbl for its data i embeded a form to enter in phone numbers, which doesnt work. long story short, i can only get the phone table to be filled out, but the soldier_phone table never gets any values so the phone number never gets linked to the soldier.
any help would be great, thanx.
View 2 Replies
View Related
Aug 4, 2005
I have a query that calculates the 'amount of credit' depending on the draw down date (which is when they started circulating the loan docs b/w two parties). Anyways,, the question is how can I make two columns of 'amount of credit' instead of one. Because one of the team member is dealing with overseas loan, while others are working on domestic. I want to make a column that would seperate his works from other people's work. And, I am kind of confused, how will I do it on a report. I have tried cross-tab query and also simple. Nothing is working.
One column should carry the amount he has to give as a loan and the other should calculate the amount from domestic side.
I don't want to use list box as it is unprofessional and also, its looking bad. If I create two queries, how can I join them together. I tried that too, but didn't work.
I need help badly :(
TIA
View 12 Replies
View Related
Sep 27, 2006
This is a little complicated so I am not sure t can be done!!!
I have a query that lists 40,000 records with some being sent to label printing
The query breaks the records down so that any record with a Qty greater then 17 will be sent to a second query for a different label to be attached.
I then end up with two Queries
Query 1 is =<17
Query 2 is >17
Records less then or equal to 17 are sent for standard label printing.
Here is my problem:
Any record with a Qty field reading less the 3 or 4 I want them to print 3 or 4 of the same label. For example
Jane doe (Qty 6) this will print 1 Jane Doe label
Joe Bloggs (Qty 3) this will print 3 Joe Bloggs labels
Am I trying to do something that just can’t be done!
When this is complete all information will be exported to a CSV file.
View 2 Replies
View Related
Mar 20, 2007
Hi
I am trying to query and summarise the contents of a table. I am unable to reach the results that I want. The table contains the following data:
AuditNo AuditTeam TypeOfTeam pName pAddress
234 CENTRAL SPEC TRUE FALSE
235 CENTRAL SPEC TRUE FALSE
236 WEST FSTL FALSE TRUE
237 EAST JUPT TRUE FALSE
238 EAST JUPT TRUE TRUE
I will like the results of the query to look something like this:
AuditTeam TypeOfTeam pName pAddress
TRUE FALSE TRUE FALSE
CENTRAL SPEC 2 0 0 2
WEST FSTL 0 1 1 0
EAST JUPT 2 0 1 1
In the results the columns pName and pAddress have subheaders of TRUE and FALSE. If this presents more problems then perhaps the subheaders can be removed and instead we will have pNameTrue and pNameFalse. Note there are 116 columns similar to pName and pAddress in the original table.
I don't know whether this is possible. It may require several queries to get to this. I am really stuck with this one so any tips will be very welcomed.
thanks.
View 3 Replies
View Related
Jan 25, 2005
I have created a basic client contact database, and dependent upon whether the client is classed as say, as a decision maker, or as an influencer will affect when they should next be contacted. I have attempted an expression in my Date of Next Contact Field using my Classification field, and Date Last Contacted field. If there is no date in the date last contacted field, I just want the current date returned.
Please note I have never typed expressions in Access before, only Excel !! This probably explains why this isn't working!!!!. It says "the expression you entered contains invalid syntax (you may have entered an operand without an operator)". Can any expert tell me what I've done wrong??? Don't tell me, it's simple.....?! Here goes:
=IIf([Classification]="Decision Maker" And iif(IsNull[Date Last Contacted], Date(), iif([Classification]"Decision Maker"
And iif(IsNotNull[Date Last Contacted], [Date Last Contacted]+30,” ”))))
When I can get this to work, I want to add to the expression for different classifications.
Many Thanks
View 3 Replies
View Related
Oct 4, 2005
I have searched and tried previous examples to only fail in this process.
We have a database that is accessed by over 20 employees at one time and virtually every day we run into the same problem. Someone is trying to edit a record but can not do this due to another user being logged into that specific record. Not knowing who is in the record burns valuable working time trying to hunt down the individual and getting them to filter out of the record so it can be edited by the other user.
To eliminate this pointless search, I would like to create a simple box in the form that displays who is logged into that specific record (if any). That way we know exactly who is in that specific record and we can call them directly.
I have attempted the many examples in the searches of this forum but it may have to do with my lack of knowledge working with Modules or Expressions. Current User only displays your own user ID making it useless in this case.
Any help is greatly appreciated!
John
View 4 Replies
View Related
Nov 15, 2006
I want to build a form based on a query, but I don't seem to be structuring it properly. Please refer to attached picture to help explain what I'm trying to do. I have 4 essential fields in a table, tblInpatientLog. They are POSno, Primary, POS Code, and Length of Stay (calculated as difference between Admit Date and Discharge Date).
What I need to do is have the form display the POSno that are in the table as "21" and "61" ONLY. There are others, but I only need to see these two. I then need to split the form (visually) into two sections. On the right, I need to display the Primary field as ones that are in the table as "Healthy Plan" only and on the left, I need to display Primary as ones that are in the table as "Medicare" only.
Under these headings, I need to show the total number of days, under the Length of Stay field for each of the four hospitals, Hospital 1, Hospital 2, Hospital 3 and Hospital 4, which are identified in the table under the "POS Code" field as "16", "2", "5", and any number OTHER THAN 16, 2, or 5 (or, whatever is left), respectively.
The Length of Stay field is calculated as difference between Admit Date and Discharge Date fields in the form for each individual record. I need access to show me a SUM of all the number of days for all the records that are listed as "Healthy Plan" for the "Primary" field for Hospital 1, then for Hospital 2, then for Hospital 3 and then for Hospital 4.
Then I need Access to calculate the SUM of all the 4 calculate field results for the 4 hospitals and display in the "Total Hospital Days" field on the form. Finally, I need a count of the total number of records that meet the criteria of "POSno" = 21 and 61, "Primary" = Healthy Plan, "POS Code" = all.
I hope that makes some sort of sense for someone. I've tried to create a query, but it doesn't display any results. I've wondered if I need to create more than one query, and have tried various different options without success. Any alternate suggestions would be welcome at this point. Thanks in advance for anyone out there that might be able to help.
View 14 Replies
View Related
Oct 5, 2005
I'm totally new to access so please bare with me, what I want to do is create a query that takes the price from one column and takes 25% off it if there is 2 units in stock, 30% off if there is 3 units, 40% if there is 4 units in stock and 50% if ther is more than 5 units in stock, the results will then be created in a column called DiscountPrice and the percentage off will will be created in a column called SaleInfo. Is this possible?
View 1 Replies
View Related
Apr 5, 2006
Hi there - I've looked at quite a few posts and although some of them seem to start of heading in the right direction there doesn't appear to be anything that quite hits the spot for what I'm looking for.
I'm trying to create a query that creates the following (see spreadsheet as typing it out didn't help.)
I hope that's clear - any help appreciated.
Cheers,
Mark
View 1 Replies
View Related
Sep 26, 2006
First off let me say that I'm VERY new to Access. I've been put in charge of administering a large (to me) database and I've just started working with the insides of the database for about 3 weeks now.
The most urgent thing though is I need to make an "Honour Roll" report for the employees, that is, they get pass/fail inspections and I'm trying to create a query that returns all employees that have 10 or more passes IN A ROW, with at least 2 of those passes being a personal evaluation vs. an after the fact quality verification. There's already a query in the database that returns the employee ID, total passes, total fails. I've managed to add the field that includes type of inspection, but I can't seem to find a way to count the number of PE's in the field.
The second issue is getting the query to ignore all inspections prior to their last fail. I think I can work out moving the date of the inspection into the query, but then using it is another story. If it requires a script of some sort, then I'm hopelessly lost. The last programming I did was BASIC back in school... many years ago.
If I were to break it down into a statement, it would be something like this:
If (employee passes) >= 10 since last (employee fail) AND (inspection type="PE") >=2, then send (employee ID) and info to a report.
Any help with this is appreciated, I'm starting to get desperate!
View 2 Replies
View Related
Apr 5, 2007
I have 3 tables that i wish to pull information from:-
TBLProspects
LeadID (Primary Key)
CompanyName
RenewalDate
Status
TBLNotes
NoteDate
Note
Who
LeadID
TBLQuotes
QuotePremium
Income
Reason
OnCoverPremium
LeadID
I have created the query and at the moment, I am getting duplicated rows of "Note" because there are more than one "QuotePremium" for each CompanyName. Is there anyway of showing the CompanyName, the Note (just once) and then the 2 or 3 QuotePremiums records?
SELECT DISTINCT TBLNotes.NoteDate, TBLProspects.CompanyName, TBLProspects.RenewalDate, TBLNotes.Note, TBLProspects.Status, TBLQuotes.QuotePremium, TBLQuotes.Income, TBLQuotes.Reason, TBLQuotes.OnCoverPrem
FROM (TBLProspects INNER JOIN TBLNotes ON TBLProspects.LeadID = TBLNotes.LeadID) INNER JOIN TBLQuotes ON TBLProspects.LeadID = TBLQuotes.LeadID;
View 2 Replies
View Related
Jan 3, 2008
Hello all!
I find it difficult to build a query...
The scenerario is the following:
I have a table that keeps information for weeks about some files (e.g: if the file has been sent). Then I have another table with information for days in a month (the data for every day).
What I should do is:
Check wheter the week has been sent, if it has been sent, I have to show the data for every day of that week in a report. Only for one month... and I can't figure out how to build that query...
I guess I should do something like:
select week from weekTable where week=week and sent=TRUE
set rcset=db.openrecordset(sql)
if(not rcset.eof)then
'build in here the query to select the data I need from the month table
end if
but i can't figure out how the loop would be for 1 month...
Any help will be much appreciated!
View 2 Replies
View Related
Feb 16, 2005
Hi, got a small problem. As part of a database I have to do for a booking system for a conference centre I have a form (frmSummary) that displays the results of a query (qryT101Book), the query itself displays all the bookings for a certain room from today onwards. This form only displays a summary of the information. I have placed a button on the form to call up the main booking form, (frmBooking) which allows the input of all the data for the booking. On ythis second form, frmBooking, I have placed a button to save the record and update the display on frmSummary. The code I have used for this is Forms![frmSummary].Requery and it works okay, the form is updated. However, tomake it easier to confirm the booking has occurred I would liketo have the cursor move to the last record, i.e. the new booking, on the frmSummary once the command button on frmBooking is clicked. If it was a one record per form situation i would use SetFocus but I'm unsure how to go about this one.
Any ideas please?
Thanks, Lol
View 5 Replies
View Related
Feb 17, 2005
In one of my forms I have a date field. I have a macro button to send that date to appear on an Outlook task.
Is it possible for my date field to appear in different coloured text once the macro button has been pressed, so that I know it's been sent and don't send it twice?
Now I know that for most of you experienced users out there, you would probably just make my macro do a loop?? so that all dates from the database reach Outlook, but I'm so new to this that the above method seemed possibly easier?!
Any ideas? Oh, here is the code used for my macro - so if anyone can tell me how and where I add code to loop this, please let me know!!!
Private Sub cmdOutlookRem_Click()
Dim OutlookApp As Outlook.Application
Dim OutlookTask As Outlook.TaskItem
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookTask = OutlookApp.CreateItem(olTaskItem)
With OutlookTask
.Subject = "Contact " & Me!Forename & " " & Me!Surname & ", " & Me!CompanyName
.Body = "Company Tel No: " & Me!CoTelNo & ", " & "Direct Line: " & Me!DirectLineinCo & ", " & "Mobile: " & Me!MobileNo
.ReminderSet = True
.ReminderTime = DateAdd("n", 2, Me!DateNextContact) 'Remind 2 minutes from now.
.DueDate = DateAdd("n", 5, Me!DateNextContact) 'Due 5 minutes from now.
.StartDate = DateAdd("n", 2, Me!DateNextContact)
.ReminderPlaySound = True
.ReminderSoundFile = "C:WindowsMediaDing.wav" 'Modify path.
.Save
End With
End Sub
View 3 Replies
View Related
