Frequency Fizzle

[lizzie]

Greetings

I have 1200 records with recurring fields. I want to calculate the frequency of the recurring fields in isolated ranges i.e. from record 1 - 100, 101 - 200, 201 - 300 etc. . .

Anone know how I can achieve this?

Any help will be greatly appreciated!

[Vaughan]

Get rid of the repeating fields and replace them with a relational structure.

[lizzie]

Still very new at this so no idea how to create a relational structure. . .

Willing to help?

[comment]

I think some more details might be useful. I guess you want to create some kind of a histogram, but what are your bins? A record number is in most cases totally insignificant, it depends on the number of records in the found set and their order - both of which can be changed arbitrarily by finding and sorting.

[lizzie]

Kay. Working out frequencies for... wait for it... yup... the lotto! Playing around and using this project as my way of getting to know FM. Have attached the file... Maybe you can suggest something? i.e. with reference to my original post - how can I calculate frequencies for records 1 - 100, 101 - 200 etc. . .

Thanks in advance (for not rolling your eyes and laughing!)

UK_Lottery.zip

[comment]

Oh no, that again... OK, here's what I suggest you do before everything. I am not sure it will help you in your quest, but at least it may introduce you to statistical data analysis.

Organize your raw data in a table like this:

DrawNumber

DrawDate

Number

If you think it significant, add also a field for OrderDrawn. This would be a number field, with values 1 to 7 (7 signifying 'bonus').

---

NOTE: Properly, you should have a separate table for Draws, with the fields:

DrawID (auto-entered serial number)

DrawNumber

DrawDate

Then the RawData would only need the fields:

DrawID

Number

OrderDrawn

and there would be a relationship between the two tables, based on DrawID. This way, the information about each draw would be stored only once, instead of seven times.

---

Once you have that in place, you can define some summary fields in the RawData table and get some stats. For example, a summary field defined as Count ( Number ). If you sort the table by Number, and create a report layout with a sub-summary part (when sorted by Number), you can see the frequency of each number.

A relatively quick way to re-organize your raw data: import it seven times from your current table, each time importing from ONE of the Nx fields.

---

EDIT: Since I know of a quicker way, I did it for you. Note the report (under scripts).

UK_Lottery_Copy.fp7.zip

Edited June 27, 2007 by Guest

[lizzie]

Ha ha. I didn't realise it was you until I read the "oh no... ". . . Thanks for the reply. As intelligent as I thought I was I feel really lost right now. Think it's time I spent some hours going through the VTC's I have.

Am still unsure as to how I go about creating a report that will show me the frequencies over draws 1 - 100, 101 - 200 and so on... will play around a bit!

Thanks again!

[comment]

Oh, you meant DRAWS 1 - 100, etc. Like this:

UK_Lottery_Copy.fp7.zip

[lizzie]

Spending some time going through the VTC's so as to get more familiar with FM. In the meantime - a question - is it possible to have a layout where the sCount values are in columns next to each other? Thus, 1 - 49 would be your row headings and the column headings would be sCount for 1-100, sCount for 101-200. Would just be easier to see...

[comment]

I don't want to say it's impossible, but it would be difficult - and more than that, impractical. In Filemaker, a row is a record, and a column is a field. Adding records is easy, and the report adjusts automatically. Adding/deleting/modifying fields requires re-defining the database.

Why don't you do this in Excel? A spreadsheet can work in both directions, not to mention that Excel has many more statistical functions than FMP.

[Søren Dyhr]

More as a service to you, than a structured relational approach like Michael here provides, have I taken a poke into you present utilization of fields.

To me is it a vast number of fields you there have been working on, which can be made more efficient when you see at the time it takes to generate the whopping number of fields when developing.

I might have forgotten a tiny detail or two (I know there's missing a statisitical thingy), but at present do you utilize 282 fields, to convey a funtionality - which I would use 17 field definitions for!

--sd

test_UK.zip