Is this a design problem or SQL problem? (1 Viewer)

I have a table (TbAttendees) for recording numbers of training course participants from different organisations (different rates may be charged to each organisation) and another table (TbGroups) to allocate facilitators and other resources for each group. I have a one to many relationship from the Groups table to the Attendees table (One group consists of many attendees),
The allocation of facilitators and other resources is dependent on the total number of participants. Eg ten people from 3 organisations could be designated to a single group (eg Group A, B or C).
I have a query (QueryGroupSummary) that totals (sums) the number of participants for each group.
if I then run a query that links the totals (QueryGroupSummary) to the Group tables to allocate facilitators and other resources it’s not updateable; eg, i can't change the number of trainers). Is my relationship structure wrong or does the SQL need to change?

I think your expectations/thinking needs to change. I haven't heard anything that sounds like your structure is wrong, or that your SQL is wrong. I just think you are wanting to do too much at once.

That query isn't updateable because you've linked it to a totals query. Its two different levels of data (totals for attendees and individual Groups) so its not going to let you edit at either level. My advice would be to use your totals query to make decisions, but to make any changes you need to make in the table itself.

martinr said:

I have a table (TbAttendees) for recording numbers of training course participants from different organisations (different rates may be charged to each organisation) and another table (TbGroups) to allocate facilitators and other resources for each group. I have a one to many relationship from the Groups table to the Attendees table (One group consists of many attendees),
The allocation of facilitators and other resources is dependent on the total number of participants. Eg ten people from 3 organisations could be designated to a single group (eg Group A, B or C).
I have a query (QueryGroupSummary) that totals (sums) the number of participants for each group.
if I then run a query that links the totals (QueryGroupSummary) to the Group tables to allocate facilitators and other resources it’s not updateable; eg, i can't change the number of trainers). Is my relationship structure wrong or does the SQL need to change?

Click to expand...

bit in bold. I presume you have count rather than sum.

The point is that a "totals query" summarises a domain relationship, showing some sort of "total attribute" about the domain. There are no individual items available to maintain. In order to change the result you need to change the underlying data, and then requery the query.

Thank-you for your reply.
I guess the answer to my question is that my design and what I’m asking it to do both need to change...

I understand your point that you can’t change the data in a totals query, but I’m not trying to change that data – the data I need to change is in a separate table. I’m joining it in the totals query so I can have the relevant information in one place (and realise this won't work). The resource allocation is based on the information/results in the totals query. eg need to know the total attendees for group A to allocate the right number of trainers etc.
So I'll have to figure out how to link this information another way?

How would you do this?
I can think of other scenarios where the same challenge would apply.
eg costing/pricing the manufacturing of a product, where the same part may come from different suppliers at different prices, and some costs may be fixed, and/or there could be different selling prices/packaging requirements for the same product, depending on the buyer/qty purchased etc. then some of the data would need to be totalled/grouped before other decisions could be made and data recorded about an item.

generally you would probably have a form that enabled you to make the relevant selections

you use those selections as parameters to your query

in the query criteria you can either pick the value from the form, or pick the value from a variable

so either
forms!formname!controlname

or a variable. Unfortunately, you cannot use a variable directly. you have to have a function to read the variable. So in the form, you have code that sets a public variable(in a module), and then a function to read the public variable (also in a module). The advantage of this method is the query then becomes available generally, as it does not rely on a particular form.
=functionname()


so all you then need to do is change the form variable, and requery the extract.

if it's a query then docmd.openquery "qryname"
if the query is bound to a form then simply forms!boundform.requery