Jump to content
Nattani

Summarising several fields/tables into one field

Recommended Posts

Nattani

I have this following formula:

 

Lookup ( Questions::Lastmonthused ; Questions 2::Lastmonthuse )

 

Which lets me find records from two separate tables using the field containing the calculation. However I need to find records in 6 tables. How can I create a formula to do that?

 

Thanks,

 

Nattani.

Share this post


Link to post
Share on other sites
Johnny Boy

With a little more info maybe someone can help.

John

Share this post


Link to post
Share on other sites
Nattani

DEEP breath...

 

The purpose of the database:

This database is atypical. It's used to contain questions used for an examination paper. As such the information contained therein becomes a paper through a cumbersome export/merge process.

 

The issue:

There are 6 question types, all with unique features residing in 6 separate files/databases and come together through relationships (using the primary key) in a 7th database. They have several fields in common, and one (for the sake of the post, although all will need to be set up this way) is called 'Lastmonthused'. In the 7th database I wish to create a layout that summarise some features of all these questions and I wish to filter the questions in these 6 databases by the 'Lastmonthused' field. Put simply, I need to find all of the exam questions for March 2006 and view them through the 7th database.

 

What I need to do is take information from 6 separate fields in 6 databases and display it in one field in one database. The lookup above achieves that with two. But the lookup is not infinite (or can it be? If so, how?).

 

What formula would a field called 'Lastmonthused ALL' need to have to do this?

 

Why I need to do this:

Basically the layout I wish to create is intended to summarise for me how many marks have been allocated across the exam into certain curriculum criteria. And I am trying to combine the summary data for the whole paper as opposed to creating 6 layouts on 6 databases and then having THAT content transposed in the central (7th) database using summary fields. This would be a lot more work/time.

 

I hope this makes more sense now. The attached shows my attempts only, the 6 related databases are not present. Keep in mind this is the first database I have ever built :/

 

Login is Admin

PW is admin

 

Thanks,

 

Nattani.

Share this post


Link to post
Share on other sites
Johnny Boy

Nattani

You have to zip, stuff it to attach it.

John

Share this post


Link to post
Share on other sites
Maarten Witberg

Try:

 

Code:


lookup(questions1::lastmonthused;

lookup(questions2::lastmonthused;

lookup(questions3::lastmonthused;

lookup(questions4::lastmonthused;

lookup(questions5::lastmonthused;questions6::lastmonthused)

)

)

)

)

 


caution 1:

I have not tested this as calc. If it works at all, it won't work as a relationship filter itself to get data from one of six possible tables in e.g. a portal. You can probably modify it to work the same for your other fields, I think that's what you're looking for.

 

caution 2:

But I think you're headed the wrong way if you pursue this. I have a feeling that your 6 questions tables might be put into one, but I have to know a bit more about the questions themselves and how they differ in type. What differs on the content level need not have to lead to differences on the database level.

 

kjoe

Share this post


Link to post
Share on other sites
Nattani

Oops, my bad. Attached.

 

EDIT: More up to date attached below.

Share this post


Link to post
Share on other sites
Nattani

Hi Kjoe,

 

It was originally in one file, but I eventually threw my hands up in disgust and split them because the filtering and exporting needs weren't being met. The questions have been written with almost no constraints, stems, banks, questions, subquestions, answers, sub-answers etc.

 

The 'bank' has also been created with the ultimate objective of exporting the content. The questions must become a publishable paper, as such they need to enter MS Word at some point (producing a paper this way is out of my control). I have this set up, but there is the one report, which this thread relates to, that I need to produce in FMP as a form of assessing the spread of the questions in one exam across psychiatric factors. My weakness is formulas, I'm not a mathematician at the best of times.

 

The attached zip has all of the files, without records. Id and password are as above, admin/admin. The report in question is in the central file a layout called Category Summary.

 

I've also tried to make it as user friendly as possible as well which is why the script to search through the 'lastmonthused' fields to group the records wont work. Especially since the search is date based and not static.

 

A bit off topic so let me know if I should be posting elsewhere.

 

Cheers,

 

Nattani.

Share this post


Link to post
Share on other sites
Maarten Witberg

Hi Nattani,

 

It is not off topic at all.

But I have grave doubts as to your setup. You need to look into a hierarchical setup of question types, questions and answers, and then add all the trimmings, I am sorry I don't understand a lot of the terms used. I dont understand the function of the bank tables. I scanned the files and seeing that 80% of the fields in the Q1-Q6 tables are identical, I'm still not convinced there should be six different tables, you can solve that using different layouts. This will make your reporting and exporting issue that much easier. Also I don't quite understand why you have six separate files, this is not needed at all in v7.

 

I get the feeling that you are trying to force a top-down structure onto a historically and bottom uppily grown mass of questions. I am afraid that is not going to work the way you are headed now. I am sort of blundering about here so sorry if I am off the mark, but either you've got to respect the chaos and use some way of dynamically labelling questions and question types (not hard - just use a text field with a value list of question types). Or you have to sit all the authors around a table and discuss your troubles with them and devise a format which may lead to rewriting some parts of the database content. Maybe both are needed....

 

good luck

 

kjoe

Share this post


Link to post
Share on other sites
Nattani

Hi Kjoe,

 

Many thanks for your feedback KJoe, as a first attempt there are bound to be faults. Apologies for the industry jargon, it does get a bit heavy sometimes (psychiatry).

 

Attached is the database as it was, the main reason I changed it is because each type needed to be automatically numberered independent of the others.

 

I got the report working, now I need to get a field to categorise a range of numbers (age). Ie: 0-12 are child, 13-21 are youth etc. Suggestions? I'm not a mathematician I'm afraid.

Share this post


Link to post
Share on other sites
Maarten Witberg

case(

age >80;”really old”;

age >65;”elderly”;

age >35;”mature adult”;

age >21;”young adult”;

age >12;”youth”;

”child”)

 

 

[ QUOTE ]

I got the report working

 

[/ QUOTE ]

that's cool, how? did you test my suggested calc or something different? just curious.

 

 

kjoe

Share this post


Link to post
Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.




×
×
  • Create New...

Important Information

Terms of Use