Grouping rows

[biggcc]

I'm trying to figure out if this is possible or not. Here is a brief overview of I'm trying to accomplish. I have multiple properties that I'm collecting data for so the key field for all queries is the property number. Currently all my queries work as designed. What they have asked for now if to have certain properties grouped together so that the row for the "group" is shown instead of individual properties. Since they want all this on a single report I cannot figure out how to have individual properties in the same query as "grouped" properties.

In other words I have properties A, B, C, D, E, F, G. So on the query they want to have row 1 show the values for property A, row 2 so the totaled values for properties B & C, row 3 has property D, row 4 will have E, F & G.

Any ideas on how to make this work would be greatly appreciated.

Thanks

 

[pbaldy]

I think if you added a field to the table for group, you could then aggregate the data that way. In other words, property A would have 1 in this field, B & C would each have 2, etc. It wouldn't have to be numbers, but whatever gave it meaning to you.

 

[biggcc]

I'm following what you suggested but just have one question. I don't know how I would aggregate it on that field. Could you provide an example of what you are talking about? My end query is a combination of approximately 8 other queries that all supply their piece of information. I guess I'm just not really sure how I would get it to total on the "group" field while leaving the other properties as is. If I can get it to work on one I'm sure I could make it work for the others. Just not sure how it would be done.

Thanks for you patience.

 

[pbaldy]

Generally, and note the word "group" is a reserved word in SQL, so don't use that as your field name:

SELECT GroupField, Sum(AmountField) AS TotalAmount
FROM TableName
GROUP BY GroupField.

 

[biggcc]

Thanks - Now I get it. Unfortunately I don't have time right now to try it but I will either from home tonight or at work tomorrow morning and will let you know how it works. I'm hopeful that it will solve my problem.

I'll let you know how it turns out.