I 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.
Desperate 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:
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.
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?
If 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.
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.
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
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?
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.
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...
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?
I 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.
I 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.
I am exporting data from Access to excel, once all the data is exported into multiple sheets. I have one Master Sheet which has formula / reference to other sheets. I want to hide those rows which do not have data but contain formulaes referencing back to another sheet.
How to hide those records has formula but do not have data.
After conditional formatting with every new entry in Datasheet Item Code or Item Description then after row Disc % and Amount default values is shown as #Type! And datasheet is very slow with flickering. If it conditional formatting is remove then working as well. I have attached the Screenshots and dB file ...
My first question is how do I append 00001 so I can have a value for the false side of 9123400001 instead of 912341...My second question is why does it prompt me to enter parameters? It also produces all false values from the if statement...What I am trying to do is use a column that has either a Y or a N and using the if statement to correspond with different formulas depending on the Y or N.This what I have so far.
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!
I have a form with a List Box (List BoxA) that returns a value from a query. I then have a Text Box (Text BoxB) that uses the value from List BoxB and multiplies it by let's say 2. It works giving me the correct value but only becomes visible after I click inside List BoxA. How can I make it visible as soon as the form is opened? I tried refreshing the form data, didn't work and I tried a requery macro which didn't work either.
Im trying to work on an db in Access 2007 that was migrated from Access 2003 (in fact its been migrated several times starting from Access 97). It executes and runs with no problems in both versions. The problem is when trying to open some queries (not all) - Access is unable to open the query in design mode and gives me this error :
" is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.
However, as I cant open it - I cant check it. Im pretty sure none of the fields have invalid characters (they do have spaces) and Im not sure how long is too long....
We currently manually run 5 different queries then copy and paste this data into 5 separate tabs on 1 workbook, I'm trying to automate some of this process if possible.
I am trying to use the 'transferspreadsheet' action within a macro to run a query and post it into a template excel file, using this code:
Trasfer Type Export Spreadsheet Type Excel 8-10 Table Name (query Name) FIle Name (FIle location) Has field names No Range Blank ---- This does seem to work and puts the data on a new tab on the specified workbook.
However I have a few questions:
1. Can you specify which query gets put onto which tab in excel? The tabs have different fixed names.
2. Can you specify which Cell the data gets pasted into to? As each tab has a set of headers and titles which need to remain.i.e would need to get query 1 to start in cell A4.
3. How would you expand the above out so that it runs all 5 queries, would you just add in multiple transfer spreadsheet actions in the same macro?
I have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?
QUERY1
Code: TRANSFORM IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT SELECT PT_LEVEL.INF_YEAR, PT_LEVEL.INF_MONTH, PT_LEVEL.UNIT
I have a crosstab queries which uses the date query parameters. However, when I go to my Export command (code is below), it ask me to enter the date parameters (start date and end date) twice. What do I have to do so that the system will ask me to enter once only?
Code: On Error GoTo Err_cmdTest_Click 'Must 1st set a Reference to the Microsoft Office XX.X Object Library Dim dlgOpen As FileDialog Dim strExportPath As String Const conOBJECT_TO_EXPORT As String = "qryEXPORT"
I have 3 queries named Mech Final Equipment 3 Mth, Mech Final Equipment 6 Mth, and Mech Historical Final Equipment.They all have two fields-Final equipment and Sum of Sum of Down (calculating the number of minutes each piece of equipment was down in the time period selected).
My ultimate goal is to join the three queries to display a pivot chart that uses the Final Equipment as the category field and 3 Mth, 6Mth, and Historical as seperate data fields.What I have is a join query (Which I have named Mech Final Equipment H63 Joined)
Using this SQL:
Code:
SELECT DISTINCTROW [Mech Final Equipment 3 Mth].[Final Equipment], Sum([Mech Final Equipment 3 Mth].[Sum Of Down]) AS Duration FROM [Mech Final Equipment 3 Mth] GROUP BY [Mech Final Equipment 3 Mth].[Final Equipment] UNION
[code]...
Which returns a table that looks like this:
Final Equipment, Duration
Ancillary Equipment, 225 Ancillary Equipment, 401 Ancillary Equipment, 1787 Brush Unit , 1252 Brush Unit , 2519 Brush Unit , 8004
And so on.What I need the table to look like is this
Final Equipment, 3 Mth, 6 Mth, Historical
Ancillary Equipment, 225, 401, 1787 Brush Unit, 1252, 2519, 8004
And so on, like a cross tab.I tried to do a crosstab query but I don't have enough fields.
