Mixing masterdata data

PeterHer

Hi All,


I have 4 tables.
Just to be clear, the first 2 lines are from table 1, next 2 lines from table 2 etc.
Every table has it's own frxMasterData.

Date format is dd/mm/yyyy
At the moment they are display as follows:


Date Code Amount Productname Price
01/01/2011 1500 100 Motherboard 150
02/01/2011 1500 80 Motherboard 150

01/04/2011 1300 10 Mouse 15
01/04/2011 1303 8 Mouse 15

01/01/2011 2500 100 PSU 150
01/01/2011 2500 80 PSU 150

01/05/2011 1600 10 cpu 150
01/05/2011 1603 8 cpu 150


I was wondering if it would be possible to create a output like this

Code Amount Productname Price
Deliverings of 01/01/2011
1500 100 Motherboard 150
2500 100 PSU 150
2500 80 PSU 150

Deliverings of 02/01/2011
1500 80 Motherboard 150

Deliverings of 01/04/2011
1300 10 Mouse 15
1303 8 Mouse 15

Deliverings of 01/05/2011
1600 10 cpu 150
1603 8 cpu 150

So every item is sorted by the delivery date.
The table are not group anymore.
Is it possible to mix MasterDate components and sort them on the delivery date?


Thanks in advance.

Peter.

Alex_de_Kock

Hallo Peter

Try the following:

In a Query, write the following command:
(Given that your Table Names are as stated in your post,eg Table1,Table2,Table2)

Select Table1.Date As Date, Table1.Code As Code, Table1.Amount As Amount, Table1.Productname As Productname, Table1.Price As Price
Union All
Select Table2.Date As Date, Table2.Code As Code, Table2.Amount As Amount, Table2.Productname As Productname, Table2.Price As Price
Union All
Select Table3.Date As Date, Table3.Code As Code, Table3.Amount As Amount, Table3.Productname As Productname, Table3.Price As Price
Union All
Select Table4.Date As Date, Table4.Code As Code, Table4.Amount As Amount, Table4.Productname As Productname, Table4.Price As Price
Order By Date


I find it better to then create a Group Header and Group Footer in my ReportPage, which is linked to for eg, Query1."Date", thus it will sort the data per date.

Hope the above is clear.