query producing multiple lines of same data.

N

noservice

hello again everyone.


I am re-working an old report that shows an audit of data entered. It
also should include averages of usage and dollar amounts. It seems to
work just fine, however on the report print out, it has the same line
multiple times, instead of a single line of information for an
account.

This is how the main query is set up.

paysum audit sub 1 gets most of the data, and i've set up three other
queries to calculate totals, and number of entries for my averages.
All of that is fine, it is only that the output shows at the very
least double lines of each entry. (some entries show correctly as one
line).

SELECT [qryPaymentSummary - Audit Sub 1].[Payment Summary],
[qryPaymentSummary - Audit Sub 1].[Acct ID], [qryPaymentSummary -
Audit Sub 1].Client, Accounts.Provider, [qryPaymentSummary - Audit Sub
1].Type, Accounts.Account, [qryPaymentSummary - Audit Sub 1].From,
[qryPaymentSummary - Audit Sub 1].To, [qryPaymentSummary - Audit Sub
1].[Current Utility Charges], [Totals]![SumOfCurrent Utility Charges]/
[EntryCount]![EntryCount] AS [Average Charges], [qryPaymentSummary -
Audit Sub 1].Usage, [Totals]![SumOfUsage]/[EntryCount]![EntryCount] AS
[Average Usage]
FROM (((Data INNER JOIN Accounts ON Data.[Acct ID] = Accounts.[Acct
ID]) INNER JOIN [qryPaymentSummary - Audit Sub 1] ON Accounts.[Acct
ID] = [qryPaymentSummary - Audit Sub 1].[Acct ID]) INNER JOIN Totals
ON Accounts.[Acct ID] = Totals.[Acct ID]) INNER JOIN EntryCount ON
Accounts.[Acct ID] = EntryCount.[Acct ID]
GROUP BY [qryPaymentSummary - Audit Sub 1].[Payment Summary],
[qryPaymentSummary - Audit Sub 1].[Acct ID], [qryPaymentSummary -
Audit Sub 1].Client, Accounts.Provider, [qryPaymentSummary - Audit Sub
1].Type, Accounts.Account, [qryPaymentSummary - Audit Sub 1].From,
[qryPaymentSummary - Audit Sub 1].To, [qryPaymentSummary - Audit Sub
1].[Current Utility Charges], [Totals]![SumOfCurrent Utility Charges]/
[EntryCount]![EntryCount], [qryPaymentSummary - Audit Sub 1].Usage,
[Totals]![SumOfUsage]/[EntryCount]![EntryCount]
HAVING ((([qryPaymentSummary - Audit Sub 1].[Payment
Summary])=GetPaymentSummaryNumber()) AND (([qryPaymentSummary - Audit
Sub 1].Client)=[Forms]![Payment Summary]![Client Lookup]) AND
((Accounts.Provider)=[Forms]![Payment Summary]![cmbProvider])) OR
((([qryPaymentSummary - Audit Sub 1].Client)=[Forms]![Payment Summary]!
[Client Lookup]) AND ((Accounts.Provider)=[Forms]![Payment Summary]!
[cmbProvider]));


I am at a loss to explain why this is occuring. If you could please
assist me in fixing the issue, It would be MOST appreciated.


Sincerley,

M. Carrizales
 
G

Guest

Multiple records is sometimes caused by one of the tables not being joined
(or an incorrect join). If you run the query, do you get the problem? You
make it sound like the query works, but the report doesn't. Run the query to
see if the data being fed to the report is correct.

James

[email protected] said:
hello again everyone.


I am re-working an old report that shows an audit of data entered. It
also should include averages of usage and dollar amounts. It seems to
work just fine, however on the report print out, it has the same line
multiple times, instead of a single line of information for an
account.

This is how the main query is set up.

paysum audit sub 1 gets most of the data, and i've set up three other
queries to calculate totals, and number of entries for my averages.
All of that is fine, it is only that the output shows at the very
least double lines of each entry. (some entries show correctly as one
line).

SELECT [qryPaymentSummary - Audit Sub 1].[Payment Summary],
[qryPaymentSummary - Audit Sub 1].[Acct ID], [qryPaymentSummary -
Audit Sub 1].Client, Accounts.Provider, [qryPaymentSummary - Audit Sub
1].Type, Accounts.Account, [qryPaymentSummary - Audit Sub 1].From,
[qryPaymentSummary - Audit Sub 1].To, [qryPaymentSummary - Audit Sub
1].[Current Utility Charges], [Totals]![SumOfCurrent Utility Charges]/
[EntryCount]![EntryCount] AS [Average Charges], [qryPaymentSummary -
Audit Sub 1].Usage, [Totals]![SumOfUsage]/[EntryCount]![EntryCount] AS
[Average Usage]
FROM (((Data INNER JOIN Accounts ON Data.[Acct ID] = Accounts.[Acct
ID]) INNER JOIN [qryPaymentSummary - Audit Sub 1] ON Accounts.[Acct
ID] = [qryPaymentSummary - Audit Sub 1].[Acct ID]) INNER JOIN Totals
ON Accounts.[Acct ID] = Totals.[Acct ID]) INNER JOIN EntryCount ON
Accounts.[Acct ID] = EntryCount.[Acct ID]
GROUP BY [qryPaymentSummary - Audit Sub 1].[Payment Summary],
[qryPaymentSummary - Audit Sub 1].[Acct ID], [qryPaymentSummary -
Audit Sub 1].Client, Accounts.Provider, [qryPaymentSummary - Audit Sub
1].Type, Accounts.Account, [qryPaymentSummary - Audit Sub 1].From,
[qryPaymentSummary - Audit Sub 1].To, [qryPaymentSummary - Audit Sub
1].[Current Utility Charges], [Totals]![SumOfCurrent Utility Charges]/
[EntryCount]![EntryCount], [qryPaymentSummary - Audit Sub 1].Usage,
[Totals]![SumOfUsage]/[EntryCount]![EntryCount]
HAVING ((([qryPaymentSummary - Audit Sub 1].[Payment
Summary])=GetPaymentSummaryNumber()) AND (([qryPaymentSummary - Audit
Sub 1].Client)=[Forms]![Payment Summary]![Client Lookup]) AND
((Accounts.Provider)=[Forms]![Payment Summary]![cmbProvider])) OR
((([qryPaymentSummary - Audit Sub 1].Client)=[Forms]![Payment Summary]!
[Client Lookup]) AND ((Accounts.Provider)=[Forms]![Payment Summary]!
[cmbProvider]));


I am at a loss to explain why this is occuring. If you could please
assist me in fixing the issue, It would be MOST appreciated.


Sincerley,

M. Carrizales
Click to expand...
 
N

noservice

Multiple records is sometimes caused by one of the tables not being joined
(or an incorrect join). If you run the query, do you get the problem? You
make it sound like the query works, but the report doesn't. Run the query to
see if the data being fed to the report is correct.

James

[email protected] said:
hello again everyone.
Click to expand...
I am re-working an old report that shows an audit of data entered. It
also should include averages of usage and dollar amounts. It seems to
work just fine, however on the report print out, it has the same line
multiple times, instead of a single line of information for an
account.
Click to expand...
This is how the main query is set up.
Click to expand...
paysum audit sub 1 gets most of the data, and i've set up three other
queries to calculate totals, and number of entries for my averages.
All of that is fine, it is only that the output shows at the very
least double lines of each entry. (some entries show correctly as one
line).
Click to expand...
SELECT [qryPaymentSummary - Audit Sub 1].[Payment Summary],
[qryPaymentSummary - Audit Sub 1].[Acct ID], [qryPaymentSummary -
Audit Sub 1].Client, Accounts.Provider, [qryPaymentSummary - Audit Sub
1].Type, Accounts.Account, [qryPaymentSummary - Audit Sub 1].From,
[qryPaymentSummary - Audit Sub 1].To, [qryPaymentSummary - Audit Sub
1].[Current Utility Charges], [Totals]![SumOfCurrent Utility Charges]/
[EntryCount]![EntryCount] AS [Average Charges], [qryPaymentSummary -
Audit Sub 1].Usage, [Totals]![SumOfUsage]/[EntryCount]![EntryCount] AS
[Average Usage]
FROM (((Data INNER JOIN Accounts ON Data.[Acct ID] = Accounts.[Acct
ID]) INNER JOIN [qryPaymentSummary - Audit Sub 1] ON Accounts.[Acct
ID] = [qryPaymentSummary - Audit Sub 1].[Acct ID]) INNER JOIN Totals
ON Accounts.[Acct ID] = Totals.[Acct ID]) INNER JOIN EntryCount ON
Accounts.[Acct ID] = EntryCount.[Acct ID]
GROUP BY [qryPaymentSummary - Audit Sub 1].[Payment Summary],
[qryPaymentSummary - Audit Sub 1].[Acct ID], [qryPaymentSummary -
Audit Sub 1].Client, Accounts.Provider, [qryPaymentSummary - Audit Sub
1].Type, Accounts.Account, [qryPaymentSummary - Audit Sub 1].From,
[qryPaymentSummary - Audit Sub 1].To, [qryPaymentSummary - Audit Sub
1].[Current Utility Charges], [Totals]![SumOfCurrent Utility Charges]/
[EntryCount]![EntryCount], [qryPaymentSummary - Audit Sub 1].Usage,
[Totals]![SumOfUsage]/[EntryCount]![EntryCount]
HAVING ((([qryPaymentSummary - Audit Sub 1].[Payment
Summary])=GetPaymentSummaryNumber()) AND (([qryPaymentSummary - Audit
Sub 1].Client)=[Forms]![Payment Summary]![Client Lookup]) AND
((Accounts.Provider)=[Forms]![Payment Summary]![cmbProvider])) OR
((([qryPaymentSummary - Audit Sub 1].Client)=[Forms]![Payment Summary]!
[Client Lookup]) AND ((Accounts.Provider)=[Forms]![Payment Summary]!
[cmbProvider]));
Click to expand...
I am at a loss to explain why this is occuring. If you could please
assist me in fixing the issue, It would be MOST appreciated.

M. Carrizales
Click to expand...
Click to expand...

I checked and indeed, the query is producing multiple lines. Thanks
for the tip. I will research how to properly join the tables to
resolve this problem. I am still learning the intricacies of access,
so thank you again for your help!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Difficult Query Problem 3
query drops records 2
query from multiple tables 3
Need to Count by two different criteria in same Totals query 4
DBL CLICK Event: data type mismatch in criteria expression 8
Formuli in query 2
[FATAL] Kerberos does not have a ticket for <any of my servers> 5
Query loses data due to joins 3

Top