possibly easy Query problem

[lance carter via AccessMonster.com]

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

 

[Al Camp]

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

 

[lance carter via AccessMonster.com]

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?

 

[Al Camp]

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

 

[lance carter via AccessMonster.com]

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!

 

[Al Camp]

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...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

 

[lance carter via AccessMonster.com]

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!

 

[lance carter via AccessMonster.com]

Al, I've cracked it. I've manged to use your forumula and get it
working..then applied a ratio. phew. Many thanks.

 

[Al Camp]

Good deal Lance...
The ratio calculation just divides the two Firm/Quote vs Quote calculations,
and divides them.
Al Camp