By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,728 Members | 2,462 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,728 IT Pros & Developers. It's quick & easy.

Combined Query, Need help Grouping by ID?

P: 161
I need advice on how group or relate data within a query. I have repeating data from my first record source. The first source of information stores data on a single row per record, the second however stores data over multiple rows per record.

Example of how the query is displaying the data which messes up my report since I essentially have a full report for each AggrType:

ProdID AggrID AggrType
1 1 1
1 1 2
1 1 3
1 1 4
2 2 1
2 2 2
2 2 3
2 2 4

Expand|Select|Wrap|Line Numbers
  1. SELECT qryProduction.ProdID 
  2. qryAggregate.AggrID
  3. FROM qryProduction INNER JOIN qryAggregate ON qryProduction.[ProdID] = qryAggregate.[AggrID];
Apr 4 '19 #1
Share this Question
Share on Google+
4 Replies

P: 161

I was able to semi resolve the issue mentioned at the report level by adding a sub report and changing the record source of the main report from Select "many fields" to Select "source name".

If there is a more proper option to clean this up at a query or table level please chime in.

Apr 5 '19 #2

Expert Mod 15k+
P: 31,489
This question is well enough expressed. You just happened to catch me at a time of prolonged and critical activity elsewhere. My son is now married off though so today I've a bit more time available.

It seems your solution is a decent one for what you want to display if that is ultimately at the [AggrType] level. Another option is to use grouping within a single report. Discovering subreports is certainly worthwhile, but reports (and their subreports of course) is one of Access's real strengths and one of the most important parts of that is their ability to sort and group internally.

Another important thing when working with groups of data is to understand that such data can either be aggregated or not. It doesn't make sense to try to mix the two, as I explained a while ago in Aggregate Query Woes. You may find a read of that article to be helpful as it's an area that many get confused by, especially when just starting to work with databases.
Apr 7 '19 #3

P: 161
Hey Neo,

Its working well for what I need now. Since the option used is a acceptable way to resolve the issue I was having I'm happy with this.

Ill read the article though since Im sure it will be helpful in other situations
Apr 7 '19 #4

Expert Mod 15k+
P: 31,489
That sounds perfectly reasonable, but I would recommend you look into sorting and grouping in reports anyway. It's quite powerful and fun what you can do with them once you get a better understanding of their scope.
Apr 7 '19 #5

Post your reply

Sign in to post your reply or Sign up for a free account.