sum problem

[cvaccess]

I need help trying to run this query:

SELECT DISTINCTROW pend_summary.TP_NUM AS TP, Sum(pend_summary.TOTAL) AS Pended, Sum(edi_current.ACCEPTED) AS Accept, Sum(pend_summary.[total])/Sum(edi_current.[accepted]) AS [Pend %]
FROM edi_current, [trading partners] INNER JOIN pend_summary ON [trading partners].tradingPartnerNum = pend_summary.TP_NUM
WHERE (((pend_summary.RPT_DATE) Between [Enter Starting Create Date:] And [Enter Ending Create Date:]))
GROUP BY pend_summary.TP_NUM
HAVING ((((Sum([pend_summary].[total])/Sum([edi_current].[accepted])))>[Enter Pend % as a whole number]/100))
ORDER BY pend_summary.TP_NUM, Sum(pend_summary.[total])/Sum(edi_current.[accepted]) DESC;

There are three tables:edi_current,trading partners,pend_summary

edi_current contains DATE, TP, ACCEPTED as fields
trading partners contains TRADINGPARTNERNUM
pend_summary contains RPT_DATE,TP_NUM,TOTAL

I am trying to get the sum of total grouped by Trading Partners(TP_NUM) for specific dates. My query returns the wrong calculation. For example:

pend_summary data:
rpt_date tp_num total
5/2/2002 p09 21
5/3/2002 p15 1

edi_current data:
date tp accepted
5/1/2002 p09 245
5/10/2002 p15 300
5/9/2002 p01 200

trading partners data:
tradingPartnerNum
p01
p09
p15
p20

So with the query the Pended,output field, shows 63 (21 x 3 records from edi_current)for p09 and 3 (1 x 3 records from edi_current)for p15.

Please help.

Thank you.

 

[SteveA]

While you are using three seperate tables in your query, you only have join relationships between two of them.

No relationship has been defined between the edi_current table and the other tables. Accordingly, any record found in the pend_summary table will be shown for each and every entry in the edi_current table:

edi_current:

01/05/2002 - p09 - 245
09/05/2002 - p01 - 200
10/05/2002 - p15 - 300

pend_summary

02/05/2002 - p09 - 21
03/05/2002 - p15 - 1

query_results (before summing occurs):
(02/05/2002 - p09 - 21) - (01/05/2002 - p09 - 245)
(02/05/2002 - p09 - 21) - (09/05/2002 - p01 - 200)
(02/05/2002 - p09 - 21) - (10/05/2002 - p15 - 300)
(03/05/2002 - p15 - 1) - (01/05/2002 - p09 - 245)
(03/05/2002 - p15 - 1) - (09/05/2002 - p01 - 200)
(03/05/2002 - p15 - 1) - (10/05/2002 - p15 - 300)

query_results (after summing occurs):

p09 - 63 (21+21+21) - 745 (245+200+300)
p15 - 3 (21+21+21) - 745 (245+200+300)

I hope this explains the problem.

To get around the problem, you will probably need to run three queries. The first will get the edi values, the second will get the pend_summary values and the third will bring them back together.

An example might be:

edi_count query:

SELECT [edi_current].[tp], Sum([edi_current].[accepted]) AS accepted
FROM edi_current
WHERE ((([edi_current].[date]) Between [Enter Starting Create Date:] And [Enter Ending Create Date:]))
GROUP BY [edi_current].[tp];

pend_count query:

SELECT [pend_summary].[tp_num], Sum([pend_summary].[total]) AS total
FROM pend_summary
WHERE ((([pend_summary].[rpt_date]) Between [Enter Starting Create Date:] And [Enter Ending Create Date:]))
GROUP BY [pend_summary].[tp_num];

edi_pend_count query:

SELECT DISTINCTROW trading_partners.tradingpartnernum, pend_count.total AS Pended, edi_count.accepted AS Accepted, [total]/[accepted] AS [Pend %]
FROM (trading_partners INNER JOIN edi_count ON trading_partners.tradingpartnernum = edi_count.tp) INNER JOIN pend_count ON trading_partners.tradingpartnernum = pend_count.tp_num
GROUP BY trading_partners.tradingpartnernum, pend_count.total, edi_count.accepted
HAVING ((([total]/[accepted])>[Enter Pend % as a whole number]/100))
ORDER BY trading_partners.tradingpartnernum, [total]/[accepted] DESC;

The query the user runs is edi_pend_count. Thankfully, the date questions are only asked once (at least in Access 2000 this is the case). The new results are:

p09 - 21 - 245
p15 - 1 - 300

Hope this helps.

Cheers,
SteveA

 

[cvaccess]

The problem with that is that the user is getting this output. I can't have the user run these queries. They wouldn't know how. Currently I have it set in the form as a command to run my original query. Couldn't I run through the query by command with some kind of union query?

Please help.=)

cvaccess.

 

[SteveA]

If your users were never interacting with the queries, how where you providing the parameters for the query. If you simply had a button they clicked which then asked them for these details, then replace the old query with the last query I gave you, and the user experience will be the same.

Cheers,
Steve A

 

[cvaccess]

Sorry I misunderstood your response. The form is set up for the user to click a command button to open the query. So what you are saying is run these as one query through subqueries? If so, how? I have not worked with subqueries or complex queries.

Thank you.=)

 

[SteveA]

Create the three queries I outlined in the previous email first. The only one you will ever refer to in your code is 'edi_pend_count'. This query already knows how to interact with the other queries.

Code your command button to launch the 'edi_pend_count' query. The use will be prompted to answer the few questions and then the query results will be displayed. At no time do you need to refer to the other two queries in code or ask the users to interact with these queries.

HTH
SteveA