Query for Exporting to Excel

[Icehousman2]

I've been searching for a good solution for this and can't find it. Hopefully something like this is possible.

I need to produce a report in excel from my database that needs to include information from 5 different tables. The relationship between two of these tables is a many-to-many relationship connected by a junction table. (I hope that is the right terminology)

My report needs to be in a certain format and needs to include all fields in the same order for every record.

The problem arises because of the many-to-many relationship. I have a tblOrder and a tblAgent linked by the junction table tblAgentOrder. For every order there can between 1 and four different agents associated with the order which are stored in the junction table. In addition these agents will be of different types 1-4.

Ex: One order could have an agent of type 1 and an agent of type 4. While another order may only have one agent of type 2.

I need the query to give me the result of OrderID, DateOpened, AgentType1, AgentType2, AgentType3, AgentType4. Regardless of if the order actually had an AgentType3 or 4 associated with it. In this case the fields would be blank.

Right now I'm getting:
OrderID, DateOpened, AgentType1
OrderID, DateOpened, AgentType2

I hope this makes sense, but let me know if I need to clarify anything.

Thanks in advance.

 

[Pat Hartman]

A crosstab query will probably help you.

 

[Icehousman2]

Thanks Pat,

I've been playing with crosstab queries the past couple of days trying to figure this out. If I get really stuck, i'll be sure to plea for more help.