data from 2 rows -- need to put into one row for reports

[Guest_imported]

Hi! I have had much difficulty trying to work out the following problem. I'm doing this for a transport company.


DeliveredFrom <--- column
DC_MINCH
DC_YENN

Store_No <--- column
1112
1234

OriginalID <--- column
abc123
abc123

Would anyone be able to give me any tips on how I can make a report to output these two rows where it displays the &quot;DeliveredFrom&quot; field from the earliest &quot;Date &amp; Time&quot; fields &amp; display the &quot;Store_No&quot; field from the later &quot;Date&quot; &amp; &quot;Time&quot; fields into one row??

Each DeliveredFrom is identified by an &quot;OriginalID&quot;, where there are duplicate &quot;OriginalID&quot;s - I need to take out the &quot;Store_No&quot; with the earlier &quot;Date &amp; Time&quot; and &quot;DeliveredFrom&quot; from the later &quot;date &amp; time&quot; because of cross docking and the company does not require to know this information. Just simply the ORIGIN &amp; where it ends up.

please help me out in any way!
thanks heaps!

 

[mdav2]

Why do you need to show these on one row? You could group the information by the originalID field and list them in the detail section.

I can't see any easy way of combining the information in one row, especially if the number of rows returned is not fixed

 

[JerryDennison]

You'll need to setup two queries then use these queries in another query that your report will be based on. You'll be using a method known as recursive transaction query. It's very easy and extremely powerful method of combining the data in the manner you're asking about.

The first query will find the first date/time record grouped by OriginalID. So all you need is the OriginalID, delivered from name field, and date field. Press the Sum button on the toolbar and set the OriginalID total property to Group By and the Date/Time total property to Min. This will be your ShipFrom by OriginalID query.

The second query will find the last date/time record for the OriginalID. It's similar to the first query only you'll want to Max the date/time field and include the ShipTo name field instead of deliveredfrom.

In your last query you'll want these two queries linked by OriginalID. Place one of the OriginalID fields on the QBE grid then all the other fields from the two queries. Run it and see if it doesn't give you what you're looking for.