Sub summaries from related file

[shierkev]

I have 2 files, one with data input daily and the other with data input once per month with one entry per month.

I need a report that summarizes the daily number and also puts the montly (one entry) number beside the daily one for comparison.

File 1 (one entry for each day of the month)

Date,ID, Production(number)

File 2 (one entry per month)

Date,ID, Production for the month(number)

My report need to look like this

Date, sumFileoneProduction, sumFile2Production

My problem is that I can get it to do this based on a found set in File 2 and a found set in file 1 but if there are no matching records in File 2, then it displays the sumFile2Production from the first record in File2, not just a blank like it should (in my mind).

Is there a way to get this to work or must I go into exporting to another file etc.etc.

Any ideas?

[shierkev]

It's me again.

Alternatively, I could put the "monthly" entry in the last day of each month and have it summed from there, thus eliminating the File2 thing altogether but....

how can I display only the last or first day of each month's records so the user can only enter one entry per month?

The user should be able to see all previous months in this view so he can see what has happend prior. This would be in a portal.

I'm confused.

[LiveOak]

You have not provided quite enough detail to offere any very constructive suggestions. What exactly are you summing, why don't you sum the daily entries to get the monthly entry, and why can't you relate the two files?

My best guess is that the database organization is not what you need, but without more detail of what exactly you are trying to do, it's hard to help. -bd

[shierkev]

I am trying to sum one group of numbers based on a date range (monthly, bi monthly, whatever) that has one entry for each day of the month. Then, I have another entry that is put in ONCE per month and that entry must be compared to the sum of the monthly entries. I also can't allow for the user to mistakenly put in the single monthly entry twice, or the comparison won't work.

My problem is - should I use two files, and if I do, how can i get the sum of the single monthly entry to summarize properly and compare it to the sum of the daily entries. It doesn't seem to sum properly with related files.

Alternatively, is there a way to do it in one single file? I could simply tag on the 'once monthly' field in a portal for each daily entry but that would allow the user to potentially enter the single value twice or more times per month.

I have daily entries and I need to compare that sum to other sums from another file. It sounds simple but I can't get the summing part to work right. What am I missing?

[LiveOak]

It shouldn't really be that difficult. I think two files is the proper structure. The first stores the daily entries. The second the monthly entry. This structure will make it much easier to check for duplicate monthly entries. Next you need some field to subsummarize by (for months) in the daily file. This same field could be used to relate the data in the monthly file. I would suggest using a combination of the month and year. This field would be created in both files. Call it:

MonthIndex (calculation, text) =

Year(Date) & Right("00" & Month(Date), 2)

if the monthly data file is slightly different with a month number (make sure a number is used, NOT a name) and year being entered instead of a date you could modify the calculation in this file to:

MonthIndex (calculation, text) =

Year & Right("00" & Month, 2)

The reason we are using text padded out is to make the sort work. Using a nubmer index without padding 20001 and 20011 would sort ahead of 200011 and 200011. A text index without padding would put 200011 ahead of 20006.

If you subsumarize by MonthIndex and place the related field RelByMonthIndex::MonthlyNumber in the subsummary part, you'll have your comparison.

For other periods of comparison, you will need to create other fields to sort on to lump entries into the correct groups (bi-monthly, whatever).

If this is all going by you, it might be time to get some hands-on assistance from a consultant or experienced FM user. -bd

[shierkev]

Thanks for the quick response. This will definitely work with respect to comparing the numbers based on dates etc, I guess I was hoping that there might be a simpler solution only because the user wants to have any date range available to them and that means, as you say, to build a new field for each date range they want. I prefer build so that the user has the ultimate flexibility to do things but... in this case because of how they want to put in the data, they have to accept some limits or have more programming time.

Thanks very much for your input.