How i can populate 3 Table with...

[jjc]

Hi i am facing with a complex feature ,till now i have not luck so i decide to advices to understand if it is possible create a report with my method.
I have a Database with 3 DataTables as i list below :

- Hotel_Meals_TBL { HotelCodeID (PK) , HotelName, Stars};

- HotelPeriod_TBL {PeriodID(PK) , HotelCodeID (FK) , PeriodName(Low,High,Peak) , _From, _To};

- HotelRoom_TBL {RoomID(PK), HotelCodeID (FK), RoomName, SGL_LowSeason, DBL_LowSeason, TPL_LowSeason , SGL_HighSeason, DBL_HighSeason, TPL_HighSeason, SGL_PeakSeason, DBL_PeakSeason, TPL_PeakSeason, Dinner, Lunch};

My purpose is to retrieve the data and show in 3 different Table in the Report following the PeriodName with these column as follow:

- First Table : PeriodName= 'Low' :
HotelName , Stars , PeriodName, _From, _To, RoomName, SGL_LowSeason, DBL_LowSeason, TPL_LowSeason, Dinner , Lunch;

-Second Table PeriodName = 'High' :
HotelName , Stars , PeriodName, _From, _To, RoomName, SGL_HighSeason, DBL_HighSeason, TPL_HighSeason, Dinner , Lunch;

- Third Table PeriodName = 'Peak' :
HotelName , Stars , PeriodName, _From, _To, RoomName, SGL_PeakSeason, DBL_PeakSeason, TPL_PeakSeason, Dinner , Lunch;

I use this query to populate the First Table :
SELECT Hotel_Meals_TBL.HotelName, Hotel_Meals_TBL.Stars, HotelPeriod_TBL.PeriodName,
HotelPeriod_TBL._From, HotelPeriod_TBL._To, HotelRoom_TBL.RoomName, HotelRoom_TBL.SGL_LowSeason, HotelRoom_TBL.DBL_LowSeason,HotelRoom_TBL.TPL_LowSeason,
HotelRoom_TBL.Lunch, HotelRoom_TBL.Dinner
FROM HotelRoom_TBL, HotelPeriod_TBL,Hotel_Meals_TBL
WHERE Hotel_Meals_TBL.HotelCodeID = HotelPeriod_TBL.HotelCodeID AND Hotel_Meals_TBL.HotelCodeID = HotelRoom_TBL.HotelCodeID
AND HotelPeriod_TBL.HotelCodeID = HotelRoom_TBL.HotelCodeID AND HotelPeriod_TBL.PeriodName = 'Low'

with this query i can populate the First Table but not in correct way in each row show always the same record !!!

I don't know if my way is right or isn't to populate the 3 Table depending from the PeriodName (Low,High,Peak) so i ask you a suggestion ,idea to clarify my way and help me to go on to work out this trouble.

Thanks in advantage for your attention.

Have a good day.

Best regards

[Jan]

Hello,

Sorry i did not exactly understand your problem. You can create three data sources in report dictionary, one per condition (low, high, peak). After then you can create three data bands - one per data source.

Thank you.

[jjc]

Hi Jan,
i tried to follow your way but the Data Table show just one row instead in the Hotel there are many PeriodName (for example in the Hotel there are 4 Low (PeriodName) 2 High (PeriodName))and different Room Name that change price relating the PeriodName.
I don't know if my feature is feasible .
Do you have an advice about that?

Thanks for your attention.

Have a good time.

Best Regards

[jjc]

Hi Jan,
i don't understand why my SQL query on others software it is right and show the data as i request instead when i write down my query to retrieve the data in my data source it did not retrieve the data as my request.
Where i wrong?

Thanks in advantage.

Have a good day.

Best Regards

[Jan]

Hello,

Can you attach your report to this topic or send it to support@stimulsoft.com?

Thank you.

[Ivan]

Hello,

Using the SQL query in the Hotel_Meals_TBL table you do the selection of required fields from three tables.
But, in the Data band, you directly use fields of all three tables. The data band uses only the specified Hotel_Meals_TBL table and there are no relations. So in other tables the position is not changed and in all table rows the same values are output.

For solving the issue you need the following:
Update the list of columns in the Hotel_Meals_TBL table (delete the old ones and get the ones, created using the query). Then use in the DataBand only the new columns from this table.

Please check the "ReportTentando modified.mrt" in attachment.

Thank you.

[jjc]

Hi Ivan,
that's right ,it is what i wished do,:biggrin:
i am sorry for my poor knowledge about report

Thanks so much for your fast reply and help.

Have a good time.

Best Regards

[Andrew]

Have a good time too!

Thank you.