Table with More than 1 Invoice for each BOL - List Multiples across instead of down

[cjbigford]

I have a table that is by Bill of Lading and shows the invoices associated with the BOL. Sometimes there is more than one invoice per BOL. I would like to create a query that takes the table and shows the invoices going across instead of showing them down. Here is an example of the current view of my table:

BOL Location Date Number Exp Pay Date Amount
137493 Oswego 2/24/2015 NL843024 4/10/2015 2171.89
137493 Oswego 2/25/2015 NL843424 4/11/2015 2316.62
137506 Oswego 2/24/2015 NL843022 4/10/2015 2171.89
137506 Oswego 2/25/2015 NL843422 4/11/2015 2316.62
137512 Oswego 2/24/2015 NL843023 4/10/2015 2171.89
137512 Oswego 2/25/2015 NL843423 4/11/2015 2316.62

And this is how I want it to look:

BOL Location 1 Date 1 Number 1 Exp Pay Date 1 Amount 1 Location 2 Date 2 Number 2 Exp Pay Date 2 Amount 2
137493 Oswego 2/24/2015 NL843024 4/10/2015 2171.89 Oswego 2/25/2015 NL843424 4/11/2015 2316.62


137506 Oswego 2/24/2015 NL843022 4/10/2015 2171.89 Oswego 2/25/2015 NL843422 4/11/2015 2316.62
137512 Oswego 2/24/2015 NL843023 4/10/2015 2171.89 Oswego 2/25/2015 NL843423 4/11/2015 2316.62

Looking for some suggestions.

[orange]

It would be helpful to readers if you could post a jpg of your relationships window showing tables and relationships.
A brief overview of your application/business showing where Invoices and Bills of Lading fit in the whole scheme of things would also be helpful.

[cjbigford]

The invoice table shows the BOL and the Invoice both. I download this from my customer. I also download a report from my system that shows the BOL and the two tables will be linked by the BOL field. What I want to do is populate the table from my system with the invoice numbers from the customer. But I want this to go across instead of listing down. So each BOL is just one row showing all the invoices across.

[NTC]

You will need to learn cross tab query - or union query, which is essentially the same. What you seek has the practical limitation that always surfaces when one wants a side-by-side display - i.e. what if there were 10 invoices - it becomes unwieldy and certainly not printable.

[cjbigford]

There will never be more than 5 invoices. How would I take the information that is listed down and make it list across in a cross tab query?