Help on query

i have this query

Code: SELECT Group, ((Progress.DaysSpanned/(SELECT SUM(Progress.DaysSpanned) FROM Progress))*100)*((Progress.PercentCompleted*Weight)*100) AS WeightedProgress FROM Progress ORDER BY Group; it should reutrn the progress as a percent based on two weights (days spanned on task, and percent completed)
the prob i have is if the percent is 100% complete it does not show in the progress, a value of less then 100% (due to the weights)

how would i rewirte the query so that if percent completed is 100% it displays that,

basically if the percent completed is already 100% it should ignore all other calculations and just return 100%

Posted On: Monday 22nd of October 2012 05:36:42 AM Total Views:  668
View Complete with Replies

Related Messages:

Check box and show!   (180 Views)
I will do my best to explain this. I have a form that links to a query for form components. In this query I have a field employee (yes/no), class date, employee expires. I also have for the temporary employee similar fields (tempEm (yes/no), temp expires. In the query I have the employee expires field setup to calculate when their license expires (12 months) and for the temporary employees I have it set to expire in 3 months. The date calculation are working great. The problem lies in that the form has a check box for employee and one for temporary employee. Then next to those check boxes I have a field for the expiration date. I want it setup that if I check the employee box, it displays the employee expiration date. If I check the temporary employee, it displays the temporary employee expiration date. They are two separate display fields links to the query as discribed above. Right now it shows the expiration date for both employee and temp employee even though only one box is checked. Can someone else me with this
Please help: Grouping days and counting day   (250 Views)
, I'm hoping someone can help me with a problem I'm having in a report I'm creating. My report has about 2800 individual records spanning over a period of 5 days with each date having approx 550 records each. I'm trying to show each date only once with a total next to the date showing the number of records corresponding to that date. For example, what it currently looks like: 07/02 07/02 07/02 07/01 07/01 06/30 ect... I would like for the report to look like: 07/02 550 (total # of records that happened on 07/02) 07/01 535 06/30 528 06/29 516 06/28 620 Could someone please give me some guidance on how to group together the date and count the # of times it appears from the table
Need help really bad!!!!   (161 Views)
Ok I am setting up a data base for the company that I work for and they want it for their purchase orders. my first table is the Vendor table and it's fields are this. Vendor ID, Vendor name, Address, City, State, Postal Code, Phone #, Fax#, Account #. The second table is the Purchase order table. PO #, Vender, Date, Internal Code The third table is the Items table Item ID, PO #, Cost code, Vendor, Qty ordered, Qty Recieved, Item #, Description, Unit Price, Amount The problem I am having is setting up the relationships because there are so many purchased orders that have the same vendor. And when I go to make the form it always says that it creates duplitcates. So if someone could please help me it would be awesome.
New to Access and I need help   (171 Views)
Let me start out by saying I am new to Access. I have a project I am working on for work and it was written in Access. I have no problem opening it at work (XP operating service with Access 2000). If I try to open it on my laptop (Vista operating service with Access 2003) I get Error in kc_Decrypt (2): 5 - Invalid procedure call or argument. If I push OK 47 times the error goes away and the file opens. The once it opens, when I try to use any of the functions of the database, the same error comes back up. I tried the file on another computer with XP and Access 2007 and it works fine.
Please help!!!!   (181 Views)
, I have a table with a list of checks, the control plan, and amounts amongst other data. I'm trying to figure out a way to get the checks that include Control Plan 690 as well as the other Control Plans and amounts. For example, there could be a check number 123456 on the table for Control Plans 690, 710, and 780. I want that check number, the amounts, and the control plan. Now there could be a check number 78910 with control plans 780 and 834, I don't want that check number because 690 is not one of the control plans on that check. Can someone please tell me how I can accomplish this. I'm attaching sample data of the actual table I'm working with. The actual table has over a million records in it.
Code help to filter a subform   (171 Views)
I have a form where a user defines a checklist. There is a ChecklistName combo box where the names of all previously defined checklists, and where you would type the name of the new one (LimitToList = No). I am trying to get the subform to Update with the selection of each checklist in that combo box. Right now, I can cycle through records with the record works perfectly, each name matches the checklist. However, when I go to select a checklist from the drop down just changes the name of the showing checklist to whatever I picked (very annoying), and changes the data in the tables to reflect such changes. I've tinkering with this trying to figure it out on my own...but, I'm missing something. I wrote a line of code (modeled after another's) to do another form I have KIND OF does what I'm trying to do here on this form. Here is the code I am using in the AfterUpdate event Dim connDB As ADODB.Connection Dim qry As New Command Dim sql As String Set connDB = CurrentProject.Connection qry.ActiveConnection = connDB sql = "SELECT CategoryID, BehaviorID FROM tblChecklistDetails WHERE ChecklistNameID = " & Me.ChecklistNameID & "" qry.CommandText = sql qry.Execute Me.sfrmChecklist.Requery connDB.Close Set qry = Nothing Set connDB = Nothing A checklist consists of a name, categories, and behaviors. The tables are as follows: tblChecklistName ChecklistNameID ChecklistName tblBehavior BehaviorID Behavior tblCategory CategoryID Category tblChecklist ChecklistID CategoryID BehaviorID ChecklistNameID Let me know if this is not clear and you need more info!
Please help   (293 Views)
i am currently trying to create a database that allows the user to select different options from combo boxes in order to create a specification. i have this done, i am now trying to assign a price to each option so that a total price can be calculated at the end. eg. a price for each interior finish and floor type. does anyone know how i can do this
Code length too long on one row help!   (304 Views)
I have written a bit of code in a Microsoft Access 97. The code basically creates a Microsoft outlook message. I am trying to add in the message that I want to display in Outlook by using the MyMessage ="" command. But the message I want to add is too long to fit on one line of code - I get to the end of the line and can no longer type any more! I have tried to add the second part of the message to the next line but all I get is an "Expected line number or label or statement or end of statement" error message. I think this might be because access keeps putting a " at the end of the first line - every time I remove it and add the " to the end of the second line it keeps adding it back to the end of the first line! any ideas would be gratefully received!!!!!
Database help   (192 Views)
hi i need help if there's anyone who's willing to help me out please's about microsoft access....i have an assignment to give up for tomorrow and im getting trouble with it. If you think you can help please give me a shout out...
Relationship help   (213 Views)
At least I think I would do this as a relationship. I have two tables in my database. One of them has store numbers, address', phone number, ect. I have a form to enter information into the second table, but I don't want it to accept information if the store number is not set up on table 1. Maybe this is done with validation, but I can't figure it out.
Digital Signature for Access help!   (211 Views)
, I am trying to figure out how the digital signature cert works for an access application. I have completed my program, access db and access front end. This is going to be used on a network drive and multiple people will be using it. Right now I get that "This file may not be safe if it contains code that was intended to harm your computer" on all the computer that open the mde file. From what I am reading I need to get a digital signature for the file. Is this something I purchase and code into the file Or do I have to get the cert and have the IT department install it on a server Any explaination of the process would be great.
Query problem, help!   (235 Views)
New problem for you to solve! I'm making this movie db and right now i'm at the search form. First of all, take a look at my search form so you know what i'm talking about. (sorry for the Swedish in the picture) My problem is that when i write a title in the title field, at the same time as all checkboxes are checked and hit "search!" I find all titles in my db. The query do not care about the title field... Right now my query looks kind of like this (mini version) SELECT Movies.Title, Movies.Genre, Movies.Release FROM Movies WHERE Movies.Title LIKE "*" & Forms!frm_searchk!title & "*" AND Movies.Genre="comedy" AND Forms!frm_sok!comedy=TRUE AND Movies.Release="2000" AND Forms!frm_search!year2000=TRUE; How can I get a query that looks at ALL the search criteria and brings me the endresult. Thank you for your help!
Query help   (308 Views)
I made a playlist database with the following tables: Artist, album, song, genre, and a table with all of that information in it (artist, album, song, genre) plus a track input. I want to query the tables now so that, for example, a specific genre is queried kind of like a playlist and the artist, album, track, and song information shows up for all the artists that are in that genre. If you need me to explain further, please let me know.
How to get help??!!   (188 Views)
I've posted here a few times and haven't gotten but 1 answer. If anybody can tell me what I'm doing wrong or if there is another forum for beginners to get "stupid" questions answered I would appreciate it.
Update query help   (548 Views)
I have a table with trailer numbers currently on the yard. I also have a prompt to enter trailers as they are leaving. I would like an update query to then remove the record from the OnYard table. Is this possible
Autolookup help   (384 Views)
Please help (newbie)   (176 Views)
I would like to make my cursor automatically go to field 1 of my form upon entry, I would also like to know how to enter data on a form without it copying to all records. I want the data to stay on the current record only. Could some please assist me with this. I got Access for dummies, but its not helping.
Check Box / Text Box enable help   (160 Views)
I've got a text box that is disabled, and want it to become enabled when the user clicks the required check box. I've got the following code, and can't see anything wrong with it, but for some reason it doesn't work. No error message appears, but the text box stays disabled regardless of whether I check the check box or not. Private Sub Successful_Click() If chkSuccessful = True Then txtDateAppointed.Enabled = True Else txtDateAppointed.Enabled = False End If End Sub Anyone have any ideas Dan
Report help   (170 Views)
I am creating a new report in access and have gotten it done but I am having trouble with the formatting on it. when i view the report its cutting off my field titles as if theres not enough room to show all the text. it is doing this on my entered data as well. i changed the layout to landscape and legal but it didnt help. any suggestions TIA
Need help with basics   (341 Views)
I have a little experience with Access. But thats only really the basics. Basically my database is very simple. It holds data about customers, names, addresses, etc. Along with a review date. Plus it holds information regarding the location (in a filing system) of important papers for that customer. Basically the system is used to find out where the customers papers are located. As well as a query that can be ran on any review date, and the address taken from those records (that match) and shoved into word in a list. My problems are these, 1) If a query returns back with no results. How can I make it so that a message box appears explaining so 2) When editing a record in form view. Is there anyway of creating a 'cancel' button that will undo any changes that have been made to the record It seems that as soon as you close the box it doesnt ask if you want to save the changes 3) How do I create a delete button so that I can delete a record 4) And lastly, what would be the best method of going about getting the address's out of records via their review date as mentioned above I have a query that will allow you to enter in the review date you are looking for, but after that I am lost lol. I know these might sound like very pathetic questions to ask lol, reading the forums you all seem so knowledgable on these things so I really hope you can help a newbie lol.