Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Records _ SQL Count In Totals Function Of Access Adding Totals For 2 Records With Same Plant Number Id But Different Events

Posted by: mirasimali Sep 16 2019, 06:44 PM

I'm trying to join 2 queries to get a count of docs against a plant# but linked to different events the problem is final query results seems to display aggregate of both records.
Please see attached for details - query

Any help on what am I doing wrong? or How can it be done. Thanks!

 Count_for_Fall_Turaround_for_plant.pdf ( 133.53K ): 9

Posted by: MadPiet Sep 16 2019, 08:21 PM

Create the two aggregate queries, and then join them in a third?
For a minute there, I thought you were gonna make our lives easy and include the relevant tables and some data. Nice psych job.

Posted by: strive4peace Sep 17 2019, 06:39 AM

> "linked to different events"
you are missing a link on event ... you can add this link line yourself in the query design view by clicking on the appropriate field (tajob_event? event_name?) in the first query and then dragging to that field in the second query and then letting go. Be sure to edit the join line so the arrow points the same as the one already there.
Realize, that since you haven't included the table that defines plant number in the query, that you will only get records that 1qry has.

Because event is not a link, nor is it in criteria, you are getting all events for the plant in the count for each record.

Posted by: mirasimali Sep 17 2019, 12:37 PM

Hi guys, thanks for the advice it worked.