I have a query that groups data and produces two totals for each group. A second query takes this first query as its input and divides one total by the other for each group. Is it possible to do this in one step?
View RepliesDesperate help required - I am about to throw my laptop out the window, closely followed by myself!!!
I am self taught and rely on books or perseverence normally so apologies for lack of technical terminology. I have spent days on this to no avail- I believe this needs to be some sort of array formula but am open to any suggestions and advice!
Problem;
I have two workbooks, I need the result of the scary formula to be shown on the summary sheet in one field.
I need the formula to look at the other sheet and recognise (for each of the 100 or so rows) if the entry in column G is "F" or if it is "W" to recognise the number in column N and add it to all the other ones that also fit this spec.
Basically, the summary figure is the sum total of all the numbers in column N that have F or W in their associated column G.
A thousand thank yous to anyone who has the time to help!!!
Laura :confused:
Hey all! This is my first post. Been searching through the net all day trying to find a solution to this problem. Basically i have a table that looks like this (regular text is what i have and bolded text is what I need:Name Date Qty MOBrad 12/12/2007 23323 4423John 12/11/2007 3445 4432 John 12/11/2007 344 4432 John 12/11/2007 45 4432 John 12/11/2007 44 4432 John 12/11/2007 3445 4432 Grand Total: (Qty)And then I'd like to be able to carry this over and display a grand total at the bottom of every page of a report that I would need to generate. Our company produces forms and we sometimes have 60 - 70 people working on a single job. We want to see their hours individually but we would also like to see a grand sum of all their hours. If someone could help with this or needs more info let me know. Thanks for all your help!
View 14 Replies View RelatedI have a field that has a formula that calculates the age and I want to make a query that shows me all the people, for example, with 20 years. I tried to do that but there's no result. I think it is because I have a formula there.
View 2 Replies View RelatedI have a query that includes several columns, one of those columns shows returns on investment as a percentage. I need to figure out the annualised returns on investment using the following excel formula
{=PRODUCT(1+B1:B24)-1^(12/24)-1}
This formula gathers the percentages for the last two years then provides an annualized amount.
I'm just wondering whether this is possible to apply to the query so that when I open the query it can provide me with the 2 year annualized statistics.
Hi,
I have a table that looks something like this:
Company Product Sales
1 A 5
1 B 2
1 C 7
2 A 8
2 B 1
2 C 2
I want a query to produce a table showing Products A though D which is
the sum of Products A and B. Result:
Company Product Sales
1 A 5
1 B 2
1 C 7
1 D 7
2 A 8
2 B 1
2 C 2
2 D 9
I have many rows and many summations and the current union query and
supporting queries are pretty messy and not easily changeable. What
I'd like to do is have a third table which would hold the summed
product names and the formula used to get to it (Product D, A + B) and
have a query that can be dynamically based off of that table. Do I
need to use VBA? Does anyone have a better idea? Any help or even a
good search term would be appreciated!
Thanks!
Nathan
Ok I have Access 2000 and I am a little rusty.
I have a table created that stores all of our relevant production information.
What I want to do is print out a report that shows the daily sales, as well as create totals (forumulas. For the life of me I cannot figure out how to get Access to calculate '# of Units x price per unit= total.' and put that in a report.
I understand I can use the form to enter data into the DB. Will a report do the calculations that I want? Or a query?
I am kinda at a loss and I need to figure this out.
Thanks
hi, i currently use acces to record the concrete order, used, wasted and waste %.
i was wondering if it possible to use access to calculate these, like excel does, and are the formulas similar to type in?
here's what im currently doing:
(For argument sake, i'll put the formulas from row 3 in)
Column A: Estimated Metres : Data manually input
Column B: Used Metres : Data manually input
Column C: Waste Metres : =IF(ISBLANK(B3),"",(A3-B3))
Column D: WAste % : =IF(ISBLANK(B3),"",((A3-B3)/A3))
The main bit i want access to do is in bole. ignore the rest, its just to keep my sheet tidy.
Can Microsoft Access do something similar. There is more, but i'l start here, so i can learn bit by bit.
Any info would be great. Thanks!
Hello, I didn’t know how to explain this or where to post this but here we go! I have two databases and one of the PC's here at work can’t use some of the items in the database like on one of the databases there is some formulas in text boxes and that PC won’t calculate what’s needed in them and every PC in the plant will except for this one PC. Also the one PC won’t bring up the Allen Browne's Calendar. When you press the start or end button to bring up the calendar it never pops up but if I enter the start date and end dates and use it that way ect then it works. I have no clue why this is just happening to this one PC. Any ideas?
View 2 Replies View RelatedI am in process of creating a new tracking report. I have to do a scoring system where the number of contacts you get a day equals a daily score. The problem I am having is getting this score to work with entries that are equations. One that I am working on is calls per hr/per day. I have inbound and outbound calls that have to be added-that part works. I then need to divide that number by the time worked for the day- that works. I now need to get that end result to match a score or ranking number. An example would be if a person takes 30 inbound calls and makes 125 out bound calls and works 7.5 hours. My calculation is (30+125)/7.5=20.6. My totals are now expressions because they read: Expr3: (([Extnout]+[ACDCalls])/Expr1). By the scoring set up, 20.6 needs to equal 4. How do I get the expression to read a score and make it equal.
View 2 Replies View RelatedIf i was to use a forumla in excel to add a number of cells together i would use this forumla =sum(c1:c4) rather than having to use =sum(c1+c2+c3+c4) In access i want to add a number of fields together so that i can caluclate the number of days we have had pallets in our warehouse. I have tried entering the formula =[days stored 1]:[days stored 4] ( i have also tried enter "=sum infront aswell")but it comes back with an error message. but when i enter =[days stored 1]+[days stored 2]+[days stored 3] and this gives me the correct sum. is there any way i can use ":" in the formula since i have a 100 fields called days stored(number) and i will be very painful having to type the formula in the long way.
thanks
How do I enter a formula/calculation in a table for follow up dates. I have a basic start date. In my form/table, I want the program to insert the following follow up dates automatically: one month, 3 month and 6 month.
View 2 Replies View RelatedHi,
i currently have a spreadsheet with 6 columns and about 80,000 rows in excel.
I have been told to move it to access to make it faster. but am having problems.
each column has a number in it eg: 1, 5, 23, 45, 56, 43
say i need to find any rows containing eg: 5, 1, 56
currently (in excel) i use an extra column with this formula
=isnumber(MATCH(5,A1:F1,0)*MATCH(1,A1:F1,0)*MATCH( 56,A1:F1,0))
and it tells me which row has the numbers in it with either true or false, and then i filter the trues to get all the rows that have the numbers i need
and the numbers i want to find will change all the time
i have tried to copy the whole spreadsheet to access and it comes across, but my formula doesnt work
Is there any way around this, should i be doing something else?
Thankyou for your help:)
I am hoping someone can help with the following...
I have a form which calculates values based on (of corse) the info entered on the form and some subforms for example...
here is one of my formulas
=Form!QuiltTypes!QuiltPrice*[TotalInches]
I am assuming that because this is a formula the value will not be saved in the table... or at least its not doing that now.
Can someone tell me how I can move my formula values to my table for storage?
This is my first time here so if you need more info or anything please let me know.
Hello,
I need to replace certain values by an abreviation. Is it possible to use a function similar to the following (Excell) formula in a field in an Access Report?
Code:=IF(A1="XXX";"AAA";IF(A2="YYY";"BBB";IF(A3="ZZZ";"CCC";" ")))
If so, what syntax should I use, because this doesn't work in Access.
Thanks in advance!
I have this database, and one field is a start date, and another field is a date to mail out a thank you letter. I want this field to automatically display a date 5 days ahead of what the start date field's date is. I've seen how to do this in a form, but I need to do this from a table, and since I'm relatively new to access, I don't know where to put the formula, I can't figure it out, and I can't find anything pertinent in MS's help...
View 1 Replies View RelatedI'm trying to build an calculated expression, in a table/query, similar to what you can see attached (view jpeg in att.)
View 1 Replies View RelatedI have a table. The table stores a workers name and information such as Primary Station Verified.
The Primary Station Verified is a yes/no checkbox. It also has the dates that each worker is verified.
I want to do a query that will show me the total number of people and out of that total number of
people how many are verified. Then I will turn that data into a graph.
How do I do the Query to show me (example) There are 50 people and (example) 25 of them are
verified? I know how to do the query to show all of the people and who are verified but I just want
the numbers.
I'm needing help with a totals query. I have a table with a Department field, a charge field, and a charge code field.
I'm running a query that first groups by the department, then uses the max function to give me the highest charge within that department. For the third field of the query, I want to return the charge code that aligns with the highest charge, unfortunately none of the general totals seem to work and I can't figure out an expression to work either
Hi,
Im very new to MS Access so bear with me.
I have a table containing travel claims data. In some instances there are multiple claims attached to certain policy numbers. Currently the Table has a seperate line of data for each and every claim. What I need to do is to ensure that there is only ever one line of claims data per policy number and that columns containing data such as: Total Reserves / Total Paid / Net Reserves / Claims fees are totalled on that line.
The reason I need to do this is that I then want to run a query that compares total claims per policy against total premium per policy.
I would be grateful for any help.
Regards
Matt
I have a table that I want to sort by the by class year. But i have like 20 options that are yes/no that i want to count the total of each so that i can do charting in excel.I have no idea how to create the query. I know that I only have to count the yes's.Bascially I need to my query to show me something like thisYear AE ALD TUTOR FS2006 3 5 8 12007 5 5 9 0Year is a numberAE, ALD, Tutor, FS are boolean yes/noi don't want the select to find what is true for all just count each field individually but display it together.Does that make sense?I am really lost please help.
View 1 Replies View RelatedI need a query to show me totals, but I just need the totals on one line and not an entire column of totals. Any suggestions?
View 1 Replies View RelatedHi - Once again I'm asking for your assistance. Ideally, below is how the form (and eventually a report) would look; the first three fields (ID, Date & Vol) I currently have in a table (tblMaster):
ID Date Volume YTDTotal Q1Total CurrQTDTotal Q2Total
A1 Jun-05 10,000 77,000 40,000 37,000 37,000
A1 May-05 15,000 67,000 40,000 27,000
A1 Apr-05 12,000 52,000 40,000 12,000
A1 ------ ------ 40,000 40,000
(Sorry, I couldn't get the above data to line up right :confused: )
How would I build a query such that I could show the above in a form/subform (and eventually a report)? Help is greatly appreciated.
I want to do something fairly simple. I have 2 tables that are structured as follows:
Table_FormLogInfo (Table #1)
-------------------------------------
ID
AreaID
FormReceived
ReceivedDate
* Note that the field "FormReceived" can be set to 1 or 0.
Table_AreaAbbr (Table #2)
---------------------------------
AreaID
AreaAbbr
I'm trying to set up a totals query where I do the following:
AreaAbbr is in column 1 with Total set to "Group By"
FormReceived is in column 2 with Total set to "Count"
FormReceived is in column 3 with Total set to "Where" and Criteria set to 1
The query is working correctly but not exactly how I want it to. It returns only those "AreaAbbrs" where the "FormReceived" field is set to 1 and it counts them. What I would like it to do is return ALL "AreaAbbrs" and set the count of "FormReceived" to zero instead of omitting those that do not have "FormReceived" set to 1.
Can I accomplish this?
Thank You,
Becky McDermott
A crosstab query gives the totals for students in particular classes per the Class they belong to, i e
College A – Class1 4, Class2 12, Class3 17
College B - Class1 12, Class2 124, Class3 12
College C - Class1 5, Class2 16, Class3 7
College D - Class1 4, Class2 13, Class3 9
Etc etc
Experts, is there a way to an overall total of the lot, ie
Class 1 30, Class 2 48, Class 3 28, Class 4 etc etc
please?
I have to make a query using information out of an existing table. In the table there are two columns which I have to use in the query. I need to have the totals of these two columns. The table looks like this:
country amount1 amount2
A 2 3
B 4 6
C 5 2
D 5 3
What I want the query to look like is:
country amount1 amount2
A 2 3
B 4 6
C 5 2
D 5 3
16 14
As I am dutch, my english will not be perfect
Thank you in advance