SELECT `transaction`.`UnitID`, SUM( `transaction`.`Rent` ), `unit`.`UnitID` AS `testing` FROM `snap`.`transaction` AS `transaction`, `snap`.`unit` AS `unit` WHERE ( `transaction`.`UnitID` = `unit`.`UnitID` ) AND ( ( `testing` = :link_from_UnitID ) ) GROUP BY `transaction`.`UnitID` The SQL command leading to this error is: I tried to fool it by using an alias for one of the UnitID fields, now I get the following error when I open the mainform: There doesn't seem to be a way for me to manually change the sql since the ellipses next to the Query are always grayed out. Could it have anything to do with the fact that I'm using mysql as the backend? For some reason it just doesn't like the two UnitID fields being the same, even though I have tried it with a simpler database (using Base's built in db) with just two tables and there it works. I double checked the query and the table. I will provide some screenshots if all this isn't clear enough yet. ![]() I'm not sure if I correctly configured the field (that will show the sum), which I added to a new subform using form navigator. it showed the total of that particular column, but of course it did this for all units. The query I created similar to the one used in your example did work, i.e. My main form contains a subform of the transaction table in table view and I can successfully navigate through all units and the transactions show correctly for each unit. So a unit can have multiple of those transaction records. The transaction table contains some fields that I'd like to sum up. I have a table called Unit (with primary key UnitID) which has a 1-to-n relationship to a table transaction. I should have been more clear to my problem - the better the question, the better the answer I analyzed it and was trying to use the same approach in my db but I got stuck. ![]() That's the example I was referring to in my initial post. ![]() The example shows vertical aggregation results (sum, cound, min, max.) for every combination of categories in the main form (A_A, A_B, B_C.).
0 Comments
Leave a Reply. |