joining 2 tables

[noni1]

I have 2 tables:

Employee - Department

Meaning - One employee belongs to one department. One department can have one or more employees.

I would like to make 2 report:

* Employe -> department

* Department -> emploeyes

The firs report is very easy.

The second report I cannot find a way to do it

When I show the departmens, I am only able to show the first employee belonging to that department.

(important, I don't want to use a portal)

Thank you

[LaRetta]

Hi Noni, welcome to FM Forums!!!

You mention tables but you show vs. 6 and you didn't indicate the version for this particular post. Please clarify your version for us since it will make a difference in how we answer. :smile2:

[LaRetta]

Well, I think the answer would be same either way ...

You still need to generate your report from the 'many' side, ie, Employees. You would create a layout (columnar report) with grouped data. Let the report assistant step you through it. You would select Department, Employee and any other fields. Then when it asks what field to group by, select Department (and watch how it displays the report results to the right). Let FM create the script because it will be important that the report 1) be sorted by Department and 2) enter Preview Mode.

So your resulting report will show:

Department: Sales

__ Jim Smith

__ Bob Brown

__ Peggy Jones

Department: Accounting

__ Nelson Bender

__ Anita Wilson

etc

[noni1]

I am sorry... I am using filemaker pro 6 - I know in Filemaker you don't use the term "tables", you use the term "files" but I am used to use SQL nomenclature

Edited October 22, 2009 by Guest

[noni1]

Thank you for your answers..

The case is a bit more complicated than I explained before.

The example was a bit too simple.

This one is more accurante:

I have 2 tables like described:

In one I create orders. In the other I have food and the name of the recipe they belong to.

The idea is that I can get all the food ordered for a specific day. The orders are entered as the recipe: Someone will ask "I want 10x Fish and chips" and not "I want 10x fish and 10x potatoes"

Here are the tables:

_Order (date, quantity, productid)

10-10-2009, 10x, 1

10-10-2009, 20x, 1

10-10-2009, 10x, 2

11-10-2009, 10x, 1

11-10-2009, 10x, 2

_Products (id, name, recipe name)

1, rice, fish with rice

1, fish, fish with rice

2, fish, fish and chips

2, potatoes, fish and chips

The report I want is something like

Orders grouped by day and product name. Something like:

10-10-2009

30x Rice

40x Fish

10x Potatoes

11-10-2009

20x Rice

20x Fish

In plain SQL I would just join the tables and group by date.

The issue here is that Filemaker doent join both tables.. so in the end I get this in the report:

10-10-2009

30x Rice

40x Fish

10x Potatoes

11-10-2009

I hope I was clear enough.. The problem isnt simple from where I am standing.

Thank you,

Edited October 22, 2009 by Guest

[bruceR]

Pretty confusing request. I don't think your SQL would work as you claim because you haven't normalized the data. You have **different** products with the same ID? How is that going to work? Please present an accurate simplified example.

_Products (id, name, recipe name)

1, rice, fish with rice

1, fish, fish with rice

2, fish, fish and chips

2, potatoes, fish and chips

[noni1]

Thanks Bruce,

I know this a bit confusing.. The table is unnormalized on purpose... but you're right about the key.

We can look at these 2 tables as 5 (to have it normalize). We would have something like

Order -ManyToMany- Recipe -ManyToMany- Product

So.. An Order will have one or more recipes, and a recipe can be in one or more orders. A Recibe has one or more Product and Product item can belong to one or more recipes.

In the end my question is.. how can I make a report, on a many to many relation?

Thanks...

Edited October 22, 2009 by Guest

[bruceR]

You make it in the join table. But you don't have one.

[noni1]

Ok,

I found my error. The database I am working on is not mine.. plus I am new at FM and I made some mistakes...

Is it possible to generate a report from 4 tables instead of 3?

Thank you.

Edited October 23, 2009 by Guest