 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / May 2005possibly easy Query problem
Access Newbie needs help with QBE grid... My DB has fields for CompanyName and Status. The Company name does not have an ID or reference number, it's just chosen from a drop down I've provided from a choice of about 80. The Status field might be one of four different Strings of Live, Quoted, NTU, Awaitinfo. BrokerName might be within the 200+ recordset many times and for different status'. I can count the number of BrokerX with status Y and get a list like say Company 1 (20) company 2 (14) company 3 (5) company 4 (4) company 5 (1) What I'd like to do is have a Query using QBE grid which has columns for: Group by Broker. Count Status Where "Firm order" Or "Quoted" and have a second column next to it showing Where Status "Firm Order" only. ...and then do a division between the two to get a fraction. I can do Word and Excel VBA if necessary but not Access This may not be the most elegant, but it works, and is easy to understand... Filter the query for "FirmOrder" and "Quote" statuses. Create a Totals query and add these calculated fields to your query grid and set them to Count... FirmQuote: IIF(Status="Firm Order" OR Status="Quoted",1,0) Quote: IIF(Status="Firm Order",1,0) Then just divide those values to get the calculation you need. You may want to also filter for 0 values that will cause errors on the division. Add Company and BrokerName columns to suit. hth Al Camp > Access Newbie needs help with QBE grid... > [quoted text clipped - 27 lines] > > I can do Word and Excel VBA if necessary but not Access Thanks for looking, but I'm confused over your suggestion Al, or otherwise I'm way out of my depth! I can get the count of the top 25% of brokers who have entries and then filter by quoted OR firm order but just not the additional column of quoted only. Also, I have no idea how to do the division in a further column. How would I reference earlier (same record) row within another record/row? Lance, When responding, please include the complete history of posts. That way we can refer to points or wording in your original question, as well as any subsequent posts. You didn't mention any need for "Top 25% values", so let's just stick with calculating the following... By Company and By Broker... calculate the Count ratio betweeen Status = ("Firm Order" or "Quoted") AND Status = ("Firm Order") Using the Query Design grid... 1. Create a "View/Totals" Select query based on your table. 2. Include the Company and BrokerName field in the grid, and set each Total to GroupBy. 3. In a blank column type this in the Field box... CountOfFirmOrQuote : Sum(IIf([Status]="Firm Order" or Status = "Quoted",1,0)) 4. Set the Total to Expression 5. Set the Criteria to <>0 This should yield the count of each Brokers "Firm Order" OR "Quoted" statuses... (by Company ans omitting any 0 values) With me so far?? Let me know, and we'll go on from there. hth Al Camp > Thanks for looking, but I'm confused over your suggestion Al, or otherwise > I'm way out of my depth! [quoted text clipped - 7 lines] > would > I reference earlier (same record) row within another record/row? Al, i'm with you so far. In the fashion you descibe, I have two rows for each company showing a sum of each status. ie company x firm order 10 company x quoted 20 company y quoted 3 company z firm order 16 company z quoted 30 However I'd already achieved this by simply having two broker columns, one of which counts criteria "quoted" or "firm order" So how do I do the maths on two adjacent rows where in some instances like company y, there is no row for firm orders? I thought I would need to produce a column for each of quoted/firm order on a single row. Not sure what you mean about re-posting - i just click 'reply' button and type....should I paste in the prior posts? Anyway, thanks for looking again! Lance, In your email configuration there should be an option to "Include message in reply". That will show my previous post, and your reply... so we can see the "flow" of the conversation. In Outlook Express, it's in Tools/Options/Send... This is what you wrote originally... >>What I'd like to do is have a Query using QBE grid which has columns for: >>Group by Broker. Count Status Where "Firm order" Or "Quoted" >>and have a second column next to it showing Where Status "Firm Order" >>only. >>...and then do a division between the two to get a fraction. This is not what you asked for... > company x firm order 10 > company x quoted 20 > company y quoted 3 > company z firm order 16 > company z quoted 30 Where's the BrokerName field? Why are you now showing/grouping on Company? Shouldn't the Firm Order caption read Firm Orders and Quoted? If you want help with a problem, you must setup the problem clearly, and stay with that setup throughout the process. 1. First, if some calculations come up with null or 0, and a ratio can not be calculated, filter those records out of the query. Use Not IsNull or <>0... or whatever you need to get those 0/Null recs out of there. 2. According to what you originally asked for (above), I would have this... (Email wrapping may cause columns to misalign.. bear with me) Broker Company Firm&Quoted Quoted Ratio B Smith ABC Co. 10 20 .50 XYZ Co. 15 20 .75 J Jones ABC Co. 6 18 .33 HHH Co. 7 10 .70 etc etc... hth Al Camp > Al, i'm with you so far. In the fashion you descibe, I have two rows for > each company showing a sum of each status. ie [quoted text clipped - 21 lines] > > Anyway, thanks for looking again! Email options: Correctly setup, just doesn't apply to my browser it seems. I've mixed up the word company name and broker name. it's the same thing - different broking companies selected from a drop down. if I use logical operater AND it returns nothing because status is either one or the other, not both at the same time. Your table: Broker Company Firm&Quoted Quoted Ratio B Smith ABC Co. 10 20 .50 XYZ Co. 15 20 .75 J Jones ABC Co. 6 18 .33 HHH Co. 7 10 .70 Is exactly what I'm looking for, just the broker/company name is same thing. I think i'd better buy a few books and study up as I am clearly missing some of the more fundamental facts...like does each new column in the QBE filter out for the next column. if I could stop it doing this, I would already know how to deal with my problem. Or if I could apend query tables by column instead of by row, that would help too. Well thanks for trying Al! Al, I've cracked it. I've manged to use your forumula and get it working..then applied a ratio. phew. Many thanks. Good deal Lance... The ratio calculation just divides the two Firm/Quote vs Quote calculations, and divides them. Al Camp > Al, I've cracked it. I've manged to use your forumula and get it > working..then applied a ratio. phew. Many thanks. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.