Parent Child Query Help

[GendoPose]

Hi All,

I'm trying to create a capacity report for my database. Originally, I only had the one table, which summed the number of Packs and number of Images, worked out percentage capacity used and then put it in a report, showing for each week of the year. However since then, I've added a child table to this, allowing me to create subrecords. The reason we did this was so we could easily group together multiple mailings under one single master record, so to speak.

Going back to the capacity, I've managed to work it so the Pack capacity is worked out from the mailing quantity in the subrecord. However, the number of Images is only entered onto the Parent record. Now my capacity query is summing the same number of images as there are subrecords, where in reality I only want it to sum the Images in the main record. I know I could add an Images field to the child table and work it out the same way, but I'd rather not do that if I can help (namely because that's not how our business works).

So essentially the question is, how can I sum the Images from the parent table table without repeating the sum and massively overexagerating the sum?

Thanks for all your help!

 

[plog]

Wow, that's a lot of jargon, database and whatever industry you are in. Can you demonstrate with data what your tables look like and what you would like this report to show based on that sample data?

Be sure to include table and field names, use this format for posting:

TableNameHere
Field1NameHere, Field2NameHere, Field3NameHere, ...
David, 22, 2/1/2009
Larry, 45, 3/13/2008
Sally, 21, 5/5/2010

Again, 2 sets of data--sample starting data and then what you expect as the result from that sample data.

 

[GendoPose]

Wow, that's a lot of jargon, database and whatever industry you are in. Can you demonstrate with data what your tables look like and what you would like this report to show based on that sample data?

Be sure to include table and field names, use this format for posting:

TableNameHere
Field1NameHere, Field2NameHere, Field3NameHere, ...
David, 22, 2/1/2009
Larry, 45, 3/13/2008
Sally, 21, 5/5/2010

Again, 2 sets of data--sample starting data and then what you expect as the result from that sample data.

Hah sorry, it's the print industry and I don't really know how to get around it!

Parent Table (MASTER PLANNER)
MailDate, JobNumber, MailingQty, ImageQty
15/10/2014, S019191, 1000, 2000
15/10/2014, S024651, 150, 150
20/10/2014, S025871, 300, 450

Child Table (MAILING CELLS) (the link between these is the JobNumber)
MailDate, JobNumber, CellNumber, MailingQty
15/10/2014, S019191, 1, 500
16/10/2014, S019191, 2, 250
17/10/2014, S019191, 3, 250
15/10/2014, S024651, 1, 150
20/10/2014, S025871, 1, 250
21/10/2014, S025871, 2, 50

The report that is generated looks like this;

However, the TOTAL NO OF IMAGES is drastically above what it should be. Where the query is calculating the capacity, it's using the child table to work out the TOTAL NO OF PACKS from the mailing qty. It's then repeating the sum of ImageQty for every child record that relates to the parent record.

Obviously I've only including the fields that actually matter in this post, as there are a lot of fields in the tables that are irrelevant to this problem!

 

[plog]

Not a horrible attempt; you gave me table and field names and starting data from relevant tables, but you didn't provide me what your resulting report should look like. Incorrect results doesn't help me see what the correct results should be--explicitly show me the correct data.

What should the report look like based on the sample starting data you provided?

 

[namliam]

You simply do not want the ImageQty to be summed, leave it at "Group" instead of "Sum" or perhaps "Max" if you prefer.

That should solve your problem

 

[GendoPose]

Not a horrible attempt; you gave me table and field names and starting data from relevant tables, but you didn't provide me what your resulting report should look like. Incorrect results doesn't help me see what the correct results should be--explicitly show me the correct data.

What should the report look like based on the sample starting data you provided?

I can't show you what the report would look like because I can't work it out! Essentially, it's just showing a massive number in the NO OF IMAGES when it shouldn't be that big.

You simply do not want the ImageQty to be summed, leave it at "Group" instead of "Sum" or perhaps "Max" if you prefer.

That should solve your problem

I've tried that but I need to sum the quantity, that would just give me a record for each number where I only want one line for each week of the year.

 

[namliam]

In that case, sum the images per week first before you sum/join the second table.

I.e. make a query on the first table save that query, lets call it Query1, this has the Sum(No of Images)
Use Query1 to create Query2, which is the query you are trying to create but no more summing of the images, leave that on group by