Programming Puzzle 11 - More Loops

[gkeramidas]

Re: Programming Puzzle 11 - More Loops

didn't actually use a loop, just built-in functions, so it probsably doesn't count.

[Mike Wilson]

Re: Programming Puzzle 11 - More Loops

Jeb,
It looks to me that you have repeated the same strategy in this Puzzle that you did in Puzzle 8 by opening the table and fetching the last record, using that record's recno() value as a count for the number of cycles for your for - next loop. I believe this is a very dangerous approach here for the same reasons explained in puzzle 8. The record number for the last record may not reflect the true number of records in the table and there may be missing (deleted) intervening records. I suggest you stop using this method, as Andy and Tom previously have alluded.

[jeb richardson]

Re: Programming Puzzle 11 - More Loops

My attempt

Puzzle_11.txt

[Tom Cone Jr]

Re: Programming Puzzle 11 - More Loops

My results agree with Mike Wilson, except I get an average quantity sold for ALL products in the
the table to be 3.98. He gets an average of 3. This may be a rounding difference (fractional products
can't be sold).

For brother Mathews I get a different average product price, and a different average dollar value
per invoice. However, our average unit quantity per product per sale agree perfectly.

Three different approaches to the problem were employed.

Stan and Mike used higher level functions supplied by Alpha, and their scripts are much
shorter than mine. To achieve their results they opened multiple instances of the table.
For a small table like the one at hand this is not a matter of much importance. If the
table was much larger, or if the table was being accessed over a local network, or
the internet, these approaches may take subtantially longer.

My approach opened a single instance of the table, and employed lower level
functions to simply fetch through the records, accumulating values and counts
as it went. This can be faster, especially for larger tables retrieved over slower
networks. However, much more code is required to be written, tested, and
debugged.

[Mike Wilson]

Re: Programming Puzzle 11 - More Loops

I took the first question to be as Tom described it again.
The second, I figured needed to be a whole number, so I took the integer instead of rounding (which would round up) or presenting a fraction of an item. I also took the data to a spreadsheet to confirm the dollar average for all the invoices (attached). I'm still studying Stan's code to see where the difference is with that.

[Tom Cone Jr]

Re: Programming Puzzle 11 - More Loops

Stan, I think our manager was heavily influenced by the "advice" she received at her seminar. Since the speakers there were from out of town they had to know what they were talking about, right?

[Stan Mathews]

Re: Programming Puzzle 11 - More Loops

I understand the question but...

No weighted averages are desired.

So if 99 units of a product selling for $1 are sold and 1 unit of a product selling for $100 are sold, the average selling price is $50.50? And thus the result is meaningless.

Sorry, let's all agree to meet surreptitiously and shoot the silly, bossy manageress.
(nine words with two letters repeated consecutively in one 13 word sentence)

[Tom Cone Jr]

Re: Programming Puzzle 11 - More Loops

Let's blame the manager! Always giving ambiguous instructions.

"average product price for all products in the table" should be the average selling price for the products, not the average sale amount for all the transactions. i.e. considering the product mix reflected in the items records, what the heck is the average unit price? The price for each product should be summed and then the total is divided by the number of products. If a product is sold more than once it still only should be counted once when figuring the average product price, ok? No weighted averages are desired.

[Stan Mathews]

Re: Programming Puzzle 11 - More Loops

I think the issue can be interpreted at least two different ways.

For the items in the invoice_items table she wants to know:

a) what's the average product price for all products in the table?

b) what's the average quantity sold for all products in the table?

c) what's the average dollar value for all the invoices reflected in the table?

For the items in the invoice_items table, average price of unique items or average price of items sold? I took the complete table out to a spreadsheet and got the answers my script gives, I think.

[Mike Wilson]

Re: Programming Puzzle 11 - More Loops

I got different values, maybe an interpretation difference, and went for least number of table openings.

[Stan Mathews]

Re: Programming Puzzle 11 - More Loops

Attached.

Puzzle_11 - 06-16-2011 -1.txt

[Tom Cone Jr]

Date Created: 16-Jun-2011 12:52:29 PM
Last Updated: 16-Jun-2011 12:52:29 PM
Created By : Tom
Updated By : Tom
Puzzles and Problems

Beginning Xbasic Programming

Puzzle 11 - More loops

Your manager has been to a seminar (so you know trouble is coming!). She's
decided she can manage sales better if she has more information about the sales
transactions. For the items in the invoice_items table she wants to know:

a) what's the average product price for all products in the table?

b) what's the average quantity sold for all products in the table?

c) what's the average dollar value for all the invoices reflected in the table?


Instructions:

Write an xbasic script that answers these questions.

Display your answers in one or more message boxes on screen.

Extra credit: For each product in the table compute the
quantity that's sold on average. i.e. the average quantity per product