Converting from Access to FM

[Little Mo]

I am a recent Mac convert. My primary position at work is buiding, maintaining, and using a MS SQL database with an Access front-end. I don't want to have to use a Windows box anymore so I am trying to switch over to Filemaker.

I can't seem to find anything about UNION queries in Filemaker on the web anywhere. Does anyone know if this can be done?

The reason for the UNION is to join two unrelated tables so that I can run calculations and summary reports. One table is "Employee Hours" and the other is "Expenses". We are a consulting firm that bills hours and expenses to our employees.

If you need more info I can post examples reports I can currently run in Access.

Thanks for any help.

[Fenton]

It is not so easy to get records from 2 different tables on a printing layout, unless they are related hierarchically, that is one can be emcompassed by the other.

In this case you have 2 tables that each would be related to a "job" table, or to a table with global dates. But one cannot encompass the other.

If the total amount of records from both will ALWAYS fit on 1 page, you could use 2 portals, from one of the table types mentioned above. If can be more than 1 page, but never more than 2, then you could have 1 portal on each page. If, on the other hand, it's any number of pages, and you want it to all fit nicely together, it becomes more difficult.

One method is to capture what would print in Preview mode, as a graphic, then paste that into a global container field. This looks and prints like the real thing, and gives quite a bit of flexibility, but is tricky to set up. You have to do 1 page at a time, into 1 repetition at a time. You have to have "sliding up" checked.

Another alternative is to create another table, a Reports table, then import or set with a Loop your data from the other 2 tables. You need the unique IDs, and whatever fields you need to sort subsummaries. But much of the data you can just read from the original table.

You can either clear the Reports table each time before populating. Or you can synchronize the tables, so only new data is added. It's kind of a pain, but gives you a great tool for overall reports, as you can bring in only summarized data, from other tables.

Maybe someone has more or better techniques. It's a problem sometimes. Not one of FileMaker's strong points.

[Little Mo]

Another alternative is to create another table, a Reports table, then import or set with a Loop your data from the other 2 tables. You need the unique IDs, and whatever fields you need to sort subsummaries. But much of the data you can just read from the original table.

You can either clear the Reports table each time before populating. Or you can synchronize the tables, so only new data is added. It's kind of a pain, but gives you a great tool for overall reports, as you can bring in only summarized data, from other tables.

This is what I do now in Access. It is a pain though. I am hoping to be able to do a script or something that would help automate this. I have not figured out yet how to do that yet though.