Report select only only for one group using parameter

[Mycle]

I have been trying to design a report utilising a parameter to select the group of records for the report. Found some help here: this link text
Got the Criteria working but the qryReport does not obey the criteria.

need help.

Cheers,

Mike

BaseVanTripsVer9.5.odb

Edit by @Ratslinger - Original question also asked:

Also I would like the name of the accommodation from the Accommodations table displayed not the ID

Totals in the footer (Accumulations) is not working either.

Originally I wanted to base the report on the qryTripSegments to be able to report the calculated fields then total these in the footer, I’m lost and need help.

Error
End edit.

[Ratslinger]

Please keep questions to one issue only so as not to bury questions/answers in something other than the title question (ie: Accumulations).

[Mycle]

Done thanks

[Ratslinger]

Hello,

qryReport is correct based upon your SQL. It appears (educated guess) that you want all records for a particular Trip Log. If this is the case, the Where condition is in the wrong place. In the current location it only affect the selection of previous segment records which are joined to another. The where condition should instead be at the end of the entire SQL statement.

As for the Name vs ID of the Accommodations, this was given to you in your last question here → How to return table ID and Name from a linked table in a query and that answer showed a link to where it was provided previous to that question.

Report totals/accumulations can be tedious and difficult depending upon what is being done. The documentation definitely needs to be reviewed → LibreOffice Base Handbook. See Chapter 6 - Reports.

Your sample shows an empty field on the Page footer. If this was meant to be your attempt at an accumulation, it is not in the proper place. You would want totals on either a Group or Report footer. Page footers are handy for something like page numbering.

Here are some links to accumulation and calculated fields in reports and a sample on one post:

Accumulation function in base report not working

How to reuse an accumulated value in a Report?

Libre Data - multi-level report

[Mycle]

Thank you,

qryTripSectors3 did return the “Accommodations”.“Name” for the Previous record, I am trying to build a query which includes “Accommodations”.“Name” for the currect record instead of the AccommodationID number from table TripSectors. Tried building a query using qryTripSectors, table TripSectors and Table Accommodations, including “Accommodations”.“Name” and linking the tables AccommodationID fields but this retuned multiples of each record.

Working on placing theb totals box in a group footer now.

Mike

[Ratslinger]

It is the the same code but needs to be placed in the first select to get the name there. In fact have looked back at some of your original samples and you also provided it there. Same coding just different placement. Have stated this before, SQL is very important to learn when using Base. It will help resolve many problems. Go look at the link in the previous answer as noted. Query1 was provided there which has nearly the same SQL you are asking for again.

[Mycle]

I am wanting the current record AccomName not the previous, Tried adding code as described, the only table containing “name” is “Accommodations”.“Name” adding this to first select returns the error in the image posted above.

Mike

[Ratslinger]

I have literally spent the last few hours evaluating this and other questions/answers in hopes of providing you with some answers for your comments. Have found a recurring issue which is causing many of your problems. You keep modifying methods/structures and use copy/paste (especially in SQL) in hopes that something works. This is no way to develop something. Constant change is a sure direction to an unusable product.

You need to understand how to write SQL and not copy something that seems to work elsewhere. The SQL currently used in form ‘Trips’ is actually incorrect but is working because of other conditions in this form. By copying this and trying to use it for a report uncovers the problem. You are using the same AccommodiationID in multiple trip sectors in a single trip. cont…

[Ratslinger]

This was not the case as presented in your sample for this question → Populate controls with previous record using a query.

Another situation is the latest post in the question. The image presented is not even the same SQL which is under discussion. That is SQL from ‘qryTripSectors’ and not ‘qryReport’ which is the topic of the question. Having to try and analyze wrong information is time consuming.

In my estimation, just fixing this situation may only create problems in future enhancements. If this were my project I would stop here, evaluate what I have, determine what else needs to be done and correct the current structure to avoid problems with the remainder of the project. cont…

[Ratslinger]

A band-aid approach to some fix some of the problems is: 1) Change final segment return to home as its own destination and not the same as origination; 2) Add left join to SQL end in order to obtain Accom Name:

 Left Join (Select "Accomodations"."Name" "AccomName", "AccommodationID", "TripLogID", "TripSectorID"
        from "TripSectors", "Accomodations"
             WHERE "Accomodations"."AccommodationsID" = "TripSectors"."AccommodationID") C
                ON ("A"."AccommodationID" = "C"."AccommodationID")
                   AND   ("A"."TripLogID" = "C"."TripLogID")
                   AND   ("A"."TripSectorID" = "C"."TripSectorID")

and add “C”.“AccomName” to main select.

[Mycle]

Thank you this is all very confusing to me, I am doing my best to understand the SQL but my old brain is slow. I will persist with trying to get my head around what is required. qryReport was based on qryTripSectors so I used it to test the additional code.

[Ratslinger]

One query may be based upon another but that doesn’t make them the same. If you look closely you may find the difference. You can’t accurately test an answer without the original question used.

And yes, it is confusing to ALL starting. It’s a matter of learning and not just a copy/paste function. When you get a piece of working code/SQL you need to figure out how it does what it does. Otherwise it is just another roadblock in the future.