Major Report Problem

[Kimmie]

OK I am DESPERATE! I have attached my empty file in hopes someone will show me kindness I have created separate tables, booth sales, other sales, and cancellations. The data for each of these gets entered into a separate portal. All records are designated by year. No matter what I do , I cannot create a report that will combine all and breakdown by year and salesperson. I have twisted and turned and as you will see have created summary fields that are not working when shown in another table, and I can't figure out what its pulling. I also created a 4th table for show year that miraculously works but only for the total per year. PLEASE HELP!

TEST_Clone.zip

Edited August 27, 2008 by Guest

[LaRetta]

Password?

[agutleben]

Hi Kimmie,

I have not seen your file, but since you are using separate tables to manage these sales and cancellations, you will need to bring the data back together and then report on the combined data.

One way to do this is by importing the necessary records into a "report" table. The report table should contain all the same fields that are in each of the separate original tables. If your original tables contain fields with the same name, you only need one. Then add any remaining fields so that the data you are importing has a field to go to. This is really easy if you are using FM Advanced's Copy/Paste feature.

The process can then be scripted so that you go to each of your original tables and perform a query for your desired records from that table, then move the report table and import from the original that you just came from. Repeat this for every original table.

This will combine all your records from all original tables into the "report" table which you can then report on. Your report layouts should be based on this "report" table. When you are done with the reporting, you can then delete all the records in the report table.

Hope that helps!

[Kimmie]

Hi Laretta,

Sorry about that, forgot there was a password! tmsadmin. Thanks for taking a look at this!

[Kimmie]

Hi agutleben,

I honestly have no idea how to do that. If the tables are related, shouldn't they automatically summarize? I was able to use the main contact and summarize the fields, and break them down by sales person but I could not break them down by year but only in the main contact table. I know this is because there is no 'YEAR' marked for the main contact and I cannot figure out for the life of me without manually entering the year in the main contact to automatically look at all three tables to say or list the years that contact is involved in. I am hoping that I didn't create this database terribly wrong and hoping I can fix it somehow.

Edited August 28, 2008 by Guest

[Kimmie]

Hi All,

Any help with this would be appreciated before I go bald! lol..THANK YOU!!