I suppose it depends whether you want your composite list to be dynamic, or a snapshot. You could create a macro which generates a list, but that woudl be a static snapshot of the database at a given point in time. I am not sure about creating a dynamic list.
What question does the long list answer? How often is it needed? If the answers justify the extra complication, then do it in SQL and document the SQL. But if that is too complicated create two separate queries creating the same set of output fields. Then either anyone who wants the long list can copy both off into a spreadsheet and sort the results there, or if necessary create another query to combine both as a second step.
"For each team, what are all their inputs from other teams, and all their outputs to other teams?"
As you can see, every interaction must appear twice, once as one team's output, and once as another team's input. But I don't want two records in the base table because that's a recipe for disagreement; every interaction should be stored only once.
I've given up and gone for the SQL, and offered the tool to my colleagues, and we'll see if they like it. If they do, it will be not a bad job for an hour's work yesterday and another hour this morning!
Thinking about it - it seems to be possible to avoid SQL if you want all entries exactly twice, but I think the cure is likely to be worse than the disease.
Basically, set up a table TwoRecords with one field and two records - values "owes" and "is owed by" (or anything else, provided the two values are different).
Create a query ShowEverythingTwice consisting of your original table and TwoRecords without any joins at all. On the Property Sheet, set Output All Fields to "Yes" - the resulting SQL will be "select * from [original table], TwoRecords". The datasheet should show every record in your original table twice together with the extra field from TwoRecords.
You can now rewrite the union query to use ShowEverythingTwice instead. The problem then is that your query2 is presumably something like your query1 with A and B switched round and possibly some other alterations - the rewritten union query is likely to have a number of rather complicated calculated fields. These may be more of a headache to maintain than the union query.
I hadn't realised that not joining two tables in a query would have that effect! That's a useful trick to know in future.
But as you say, there's no provision for swapping the "owes" and "is owed" team names without calculated fields; and the menu option "Query.. SQL specific.. Union" is less obscure in the interface than I thought, so I think I'll just trust my colleagues not to freak out at three lines of simple SQL (I added some sorting too).
Comments 6
Reply
Reply
Reply
"For each team, what are all their inputs from other teams, and all their outputs to other teams?"
As you can see, every interaction must appear twice, once as one team's output, and once as another team's input. But I don't want two records in the base table because that's a recipe for disagreement; every interaction should be stored only once.
I've given up and gone for the SQL, and offered the tool to my colleagues, and we'll see if they like it. If they do, it will be not a bad job for an hour's work yesterday and another hour this morning!
Reply
Basically, set up a table TwoRecords with one field and two records - values "owes" and "is owed by" (or anything else, provided the two values are different).
Create a query ShowEverythingTwice consisting of your original table and TwoRecords without any joins at all. On the Property Sheet, set Output All Fields to "Yes" - the resulting SQL will be "select * from [original table], TwoRecords". The datasheet should show every record in your original table twice together with the extra field from TwoRecords.
You can now rewrite the union query to use ShowEverythingTwice instead. The problem then is that your query2 is presumably something like your query1 with A and B switched round and possibly some other alterations - the rewritten union query is likely to have a number of rather complicated calculated fields. These may be more of a headache to maintain than the union query.
Reply
But as you say, there's no provision for swapping the "owes" and "is owed" team names without calculated fields; and the menu option "Query.. SQL specific.. Union" is less obscure in the interface than I thought, so I think I'll just trust my colleagues not to freak out at three lines of simple SQL (I added some sorting too).
Thanks for the tip.
Reply
Leave a comment